Skip to content

Postgres

SQL.Postgres opens an @effect/sql-pg client (a connection pool) from a connection URL. Queries are Effects: they carry SqlError in the error channel, participate in interruption, and trace like everything else in your program.

Install the driver — both are optional peers of alchemy:

Terminal window
bun add @effect/sql-pg pg

Connect

Section titled “Connect”

The url may be a plain Redacted string or an Effect of one — Hyperdrive’s connectionString resolves from the Worker environment at runtime, so it slots straight in:

import * as Cloudflare from "alchemy/Cloudflare";
import * as SQL from "alchemy/SQL";
import * as Effect from "effect/Effect";
import * as HttpServerResponse from "effect/unstable/http/HttpServerResponse";
import { Hyperdrive } from "./db.ts";


export default class Api extends Cloudflare.Worker<Api>()(
  "Api",
  { main: import.meta.url },
  Effect.gen(function* () {
    const hd = yield* Cloudflare.Hyperdrive.Connect(Hyperdrive);
    const sql = yield* SQL.Postgres({ url: hd.connectionString });


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

Everything else in @effect/sql-pg’s pool config passes through — maxConnections, idleTimeout, types, and friends. The pool is built lazily on the first query of an execution and closed when the event settles — see Connection lifecycle.

Queries

Section titled “Queries”

Interpolated values are parameters, never string concatenation:

const user = yield* sql`SELECT * FROM users WHERE id = ${id}`;


yield* sql`INSERT INTO users ${sql.insert({ name, email })}`;


const rows = yield* sql`
  SELECT * FROM users WHERE id IN ${sql.in(ids)}
`;

Rows come back as plain objects typed by your annotation: sql<{ id: number; name: string }>\…`. The full statement API — fragments, sql.csv, sql.and, identifier escaping — is [effect/unstable/sql/Statement`](https://effect.website).

Errors

Section titled “Errors”

Failures surface as SqlError in the typed error channel:

const users = yield* sql`SELECT * FROM users`.pipe(
  Effect.catchTag("SqlError", (e) =>
    Effect.succeed([]).pipe(Effect.tap(() => Effect.logWarning(e))),
  ),
);

Uncaught, the error propagates like any Effect failure — no try/catch, no unhandled rejection.

Transactions

Section titled “Transactions”

Wrap a group of queries in sql.withTransaction — the whole effect commits or rolls back together:

yield* sql.withTransaction(
  Effect.gen(function* () {
    yield* sql`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${from}`;
    yield* sql`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${to}`;
  }),
);

Provide as a service

Section titled “Provide as a service”

For services that shouldn’t know which database they run on, depend on the generic SqlClient tag and provide the database with SQL.PostgresLayer:

import * as SqlClient from "effect/unstable/sql/SqlClient";


const makeUsers = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
  return {
    find: (id: number) => sql<User>`SELECT * FROM users WHERE id = ${id}`,
  };
});


const users = yield* makeUsers.pipe(
  Effect.provide(SQL.PostgresLayer({ url: hd.connectionString })),
);

The layer provides two tags from one per-execution pool: the generic SqlClient.SqlClient, and @effect/sql-pg’s PgClient for code that needs Postgres-specific capabilities — including drizzle’s effect-postgres driver. Swapping SQL.PostgresLayer for SQL.D1Layer moves the same service graph to D1 unchanged.

Where next

Section titled “Where next”