Postgres

SQL.Postgres provides tagged-template queries over a Postgres connection. Queries are Effects with typed SqlError failures, interruption, and tracing.

Install the optional Postgres driver:

bun

bun add @effect/sql-pg pg

npm

npm install @effect/sql-pg pg

pnpm

pnpm add @effect/sql-pg pg

yarn

yarn add @effect/sql-pg pg

Connect

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

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

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.

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 are plain objects; supply a row type with sql<Row>. The effect/unstable/sql/Statement API also provides fragments, sql.csv, sql.and, and identifier escaping.

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))),
  ),
);

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

Depend on the generic SqlClient tag, then provide the database layer:

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: connectionString })),
);

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.

Provider setup

Choose a database and deployment guide in SQL databases; the client above only needs a connection URL. Hyperdrive and Fly Managed Postgres cover runtime-specific bindings.

Where next

Read Migrations to apply committed SQL files, or Drizzle on Postgres for typed schemas over the same driver. Connection lifecycle covers per-request cleanup.