The best way to control Claude Code, Codex and more from any computer or phone

main
mdx 78 lines 3.38 KB
Raw
1 ---
2 title: Connection lifecycle
3 description: SQL and Drizzle clients build lazily, reuse a client within the current execution scope, and release resources when that scope closes.
4 ---
5
6 `SQL.Postgres`, `SQL.MySQL`, `SQL.D1`, and their `Drizzle` counterparts share the same scope-based lifecycle. Postgres and MySQL work across runtimes; D1 requires a Cloudflare binding.
7
8 ## Construction runs once, events run many times
9
10 ```typescript
11 import * as SQL from "@alchemy.run/sql/Postgres";
12 import * as Effect from "effect/Effect";
13 import type * as Redacted from "effect/Redacted";
14
15 export const makeQueries = (url: Redacted.Redacted<string>) =>
16 Effect.gen(function* () {
17 const sql = yield* SQL.Postgres({ url });
18 return {
19 listUsers: () => sql`SELECT * FROM users`,
20 };
21 });
22 ```
23
24 Construct the query service once during application initialization and call `listUsers()` from a handler; construction returns a lazy proxy, not a connected pool. This keeps disposable connections out of Worker isolate and Lambda instance initialization.
25
26 ## One client per execution
27
28 ```typescript
29 const users = yield* sql`SELECT * FROM users`;
30 const posts = yield* sql`SELECT * FROM posts`;
31 ```
32
33 For each constructed client, queries in the same scope reuse one underlying client; distinct client constructions do not share pools. The first query builds it and scope closure runs its finalizers. Merely constructing a client during plan or deploy does not connect; migration resources connect separately when applying migrations.
34
35 ## Why not a global pool
36
37 ```typescript
38 // Resolve the proxy during initialization; execute queries in the handler.
39 const sql = yield* SQL.Postgres({ url: connectionString });
40
41 return {
42 fetch: Effect.gen(function* () {
43 const users = yield* sql`SELECT * FROM users`;
44 return yield* HttpServerResponse.json(users);
45 }),
46 };
47 ```
48
49 On workerd, sockets belong to the creating request's I/O context, so a pool cannot be reused across events; [Hyperdrive](/cloudflare/data/hyperdrive) provides cross-request pooling instead. D1 has no socket pool, but its client and prepared-statement cache follow the same scope-based memoization.
50
51 ## Narrowing with Effect.scoped
52
53 ```typescript
54 yield* Effect.scoped(
55 Effect.gen(function* () {
56 yield* sql`SELECT * FROM users`;
57 yield* sql`SELECT * FROM posts`;
58 }),
59 );
60 ```
61
62 A nested `Effect.scoped` gives these queries their own memo and cleanup boundary, releasing the client before the outer request ends. The memo key and finalizer target are the same scope object.
63
64 ## Lambda and servers
65
66 ```typescript
67 const listUsers = sql`SELECT * FROM users`.pipe(Effect.scoped);
68 ```
69
70 Alchemy's runtime bridges supply execution scopes; when writing a standalone script or custom server integration, provide an explicit scope around each unit of work. `Effect.scoped` above creates a fresh lifetime each time `listUsers` runs; see [Runtime](/infrastructure-as-effects/runtime#instance-scope-vs-request-scope) for instance and request boundaries.
71
72 ## Provider setup
73
74 Choose a database and follow its connection and deployment guide in [SQL databases](/sql/databases). These choices do not change the client's scope-based lifecycle.
75
76 ## Where next
77
78 Use [Postgres](/sql/effect-sql/postgres), [MySQL](/sql/effect-sql/mysql), or [D1](/sql/effect-sql/d1) for the client API. [Workers: background work and scopes](/cloudflare/compute/workers#background-work-and-scopes) covers Worker-specific request handling.