Connection lifecycle

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.

Construction runs once, events run many times

import * as SQL from "alchemy/SQL/Postgres";
import * as Effect from "effect/Effect";
import type * as Redacted from "effect/Redacted";

export const makeQueries = (url: Redacted.Redacted<string>) =>
  Effect.gen(function* () {
    const sql = yield* SQL.Postgres({ url });
    return {
      listUsers: () => sql`SELECT * FROM users`,
    };
  });

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.

One client per execution

const users = yield* sql`SELECT * FROM users`;
const posts = yield* sql`SELECT * FROM posts`;

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.

Why not a global pool

// Resolve the proxy during initialization; execute queries in the handler.
const sql = yield* SQL.Postgres({ url: connectionString });

return {
  fetch: Effect.gen(function* () {
    const users = yield* sql`SELECT * FROM users`;
    return yield* HttpServerResponse.json(users);
  }),
};

On workerd, sockets belong to the creating request’s I/O context, so a pool cannot be reused across events; Hyperdrive provides cross-request pooling instead. D1 has no socket pool, but its client and prepared-statement cache follow the same scope-based memoization.

Narrowing with Effect.scoped

yield* Effect.scoped(
  Effect.gen(function* () {
    yield* sql`SELECT * FROM users`;
    yield* sql`SELECT * FROM posts`;
  }),
);

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.

Lambda and servers

const listUsers = sql`SELECT * FROM users`.pipe(Effect.scoped);

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 for instance and request boundaries.

Provider setup

Choose a database and follow its connection and deployment guide in SQL databases. These choices do not change the client’s scope-based lifecycle.

Where next

Use Postgres, MySQL, or D1 for the client API. Workers: background work and scopes covers Worker-specific request handling.