@devries-cameron20 / t3code Mirror · read-only
Fork 0

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

main
mdx 109 lines 3.69 KB
Raw
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.