The best way to control Claude Code, Codex and more from any computer or phone
Mirrored from https://github.com/Camo67/t3code.git
Synced Nov 21, 04:45 UTC
| 1 | --- |
| 2 | title: Postgres |
| 3 | description: SQL.Postgres turns a connection string into an @effect/sql-pg client — tagged-template queries with typed errors, one pool per execution. |
| 4 | --- |
| 5 | |
| 6 | import PackageManagers from "../../../../components/PackageManagers.astro"; |
| 7 | |
| 8 | export const deps = `@alchemy.run/sql @effect/sql-pg`; |
| 9 | |
| 10 | `SQL.Postgres` provides tagged-template queries over a Postgres connection. Queries are Effects with typed `SqlError` failures, interruption, and tracing. |
| 11 | |
| 12 | Install `@alchemy.run/sql` and the Effect Postgres driver: |
| 13 | |
| 14 | <PackageManagers add={deps} /> |
| 15 | |
| 16 | ## Connect |
| 17 | |
| 18 | ```typescript |
| 19 | import * as SQL from "@alchemy.run/sql/Postgres"; |
| 20 | import * as Effect from "effect/Effect"; |
| 21 | import type * as Redacted from "effect/Redacted"; |
| 22 | |
| 23 | export const makeQueries = <E, R>( |
| 24 | connectionString: |
| 25 | | Redacted.Redacted<string> |
| 26 | | Effect.Effect<Redacted.Redacted<string>, E, R>, |
| 27 | ) => |
| 28 | Effect.gen(function* () { |
| 29 | const sql = yield* SQL.Postgres({ url: connectionString }); |
| 30 | return { |
| 31 | listUsers: () => sql<{ id: number; name: string }>`SELECT id, name FROM users`, |
| 32 | }; |
| 33 | }); |
| 34 | ``` |
| 35 | |
| 36 | `url` accepts a redacted URL or an Effect resolving one, such as `Config.Redacted("DATABASE_URL")` or a runtime binding's `connectionString`. Other `@effect/sql-pg` pool options pass through. Call queries within a request or an explicit `Effect.scoped` block: the pool opens lazily and closes with that [scope](/sql/effect-sql/lifecycle). |
| 37 | |
| 38 | ## Queries |
| 39 | |
| 40 | Interpolated values are parameters, never string concatenation: |
| 41 | |
| 42 | ```typescript |
| 43 | const user = yield* sql`SELECT * FROM users WHERE id = ${id}`; |
| 44 | |
| 45 | yield* sql`INSERT INTO users ${sql.insert({ name, email })}`; |
| 46 | |
| 47 | const rows = yield* sql` |
| 48 | SELECT * FROM users WHERE id IN ${sql.in(ids)} |
| 49 | `; |
| 50 | ``` |
| 51 | |
| 52 | Rows are plain objects; supply a row type with `sql<Row>`. The |
| 53 | [`effect/sql/Statement`](https://effect.website) API also provides |
| 54 | fragments, `sql.csv`, `sql.and`, and identifier escaping. |
| 55 | |
| 56 | ## Errors |
| 57 | |
| 58 | Failures surface as `SqlError` in the typed error channel: |
| 59 | |
| 60 | ```typescript |
| 61 | const users = yield* sql`SELECT * FROM users`.pipe( |
| 62 | Effect.catchTag("SqlError", (e) => |
| 63 | Effect.succeed([]).pipe(Effect.tap(() => Effect.logWarning(e))), |
| 64 | ), |
| 65 | ); |
| 66 | ``` |
| 67 | |
| 68 | ## Transactions |
| 69 | |
| 70 | Wrap a group of queries in `sql.withTransaction` — the whole effect |
| 71 | commits or rolls back together: |
| 72 | |
| 73 | ```typescript |
| 74 | yield* sql.withTransaction( |
| 75 | Effect.gen(function* () { |
| 76 | yield* sql`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${from}`; |
| 77 | yield* sql`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${to}`; |
| 78 | }), |
| 79 | ); |
| 80 | ``` |
| 81 | |
| 82 | ## Provide as a service |
| 83 | |
| 84 | Depend on the generic `SqlClient` tag, then provide the database layer: |
| 85 | |
| 86 | ```typescript |
| 87 | import * as SqlClient from "effect/sql/SqlClient"; |
| 88 | |
| 89 | const makeUsers = Effect.gen(function* () { |
| 90 | const sql = yield* SqlClient.SqlClient; |
| 91 | return { |
| 92 | find: (id: number) => sql<User>`SELECT * FROM users WHERE id = ${id}`, |
| 93 | }; |
| 94 | }); |
| 95 | |
| 96 | const users = yield* makeUsers.pipe( |
| 97 | Effect.provide(SQL.PostgresLayer({ url: connectionString })), |
| 98 | ); |
| 99 | ``` |
| 100 | |
| 101 | The layer provides `SqlClient.SqlClient` and `@effect/sql-pg`'s `PgClient` from one per-execution pool. Other drivers can satisfy the generic service, but changing engines still requires compatible SQL and transaction semantics. |
| 102 | |
| 103 | ## Provider setup |
| 104 | |
| 105 | Choose a database and deployment guide in [SQL databases](/sql/databases); the client above only needs a connection URL. [Hyperdrive](/cloudflare/data/hyperdrive#bind-hyperdrive-to-your-worker) and [Fly Managed Postgres](/fly/data/postgres) cover runtime-specific bindings. |
| 106 | |
| 107 | ## Where next |
| 108 | |
| 109 | Read [Migrations](/sql/effect-sql/migrations) to apply committed SQL files, or [Drizzle on Postgres](/sql/drizzle/postgres) for typed schemas over the same driver. [Connection lifecycle](/sql/effect-sql/lifecycle) covers per-request cleanup. |