SQL

Effect’s SQL integration gives you a single SqlClient service that builds parameterized queries with a tagged-template literal, manages a connection pool, runs transactions (including nested savepoints), and decodes rows with Schema. Drivers for PostgreSQL, SQLite, MySQL, MSSQL, ClickHouse, and others all expose the same SqlClient interface, so your application code is written once against effect/unstable/sql and the dialect is chosen at the edge by the Layer you provide.

import { Config, Effect } from "effect"
import { PgClient } from "@effect/sql-pg"
import { SqlClient } from "effect/unstable/sql"

// A program reads the SqlClient from context and runs a query. The `sql`
// template builds a *parameterized* statement — interpolated values become
// bound parameters, never string concatenation, so injection is impossible.
const program = Effect.gen(function*() {
  const sql = yield* SqlClient.SqlClient

  const users = yield* sql<{ readonly id: number; readonly name: string }>`
    SELECT id, name FROM users WHERE active = ${true}
  `

  yield* Effect.log(`loaded ${users.length} users`)
})

// The driver layer supplies both PgClient and SqlClient. Pool configuration is
// read from the environment via Config, so secrets never appear in source.
const PgLive = PgClient.layerConfig({
  url: Config.redacted("DATABASE_URL")
})

program.pipe(Effect.provide(PgLive))

The sql value is callable: tag a template to build a query, or call it with a string to produce a safely-quoted identifier (sql("table_name")). Every query is an Effect that yields its rows and can fail with a SqlError — a single tagged error whose reason distinguishes connection failures, unique violations, deadlocks, syntax errors, and more.

How the pieces fit together

• Driver layer — PgClient.layer / layerConfig (and the equivalents for other drivers) acquire a pooled connection and provide the SqlClient service. The pool is a scoped resource, closed when the layer’s scope closes.
• SqlClient — the service you depend on everywhere. It builds statements, reserves connections, and wraps effects in withTransaction.
• Schema decoding — SqlSchema and SqlModel connect your queries to Schema, turning untyped driver rows into validated domain types and generating CRUD repositories from a Model.
• Migrations — the Migrator runs numbered migration effects exactly once, recording applied ids in a migrations table inside a transaction.

In this section

• Queries — run statements with the sql template, build inserts/updates and WHERE … IN clauses safely, stream large result sets, and group work into transactions.
• Models and migrations — define schema-backed models, generate repositories with SqlModel.makeRepository, and evolve your database with the Migrator.

The SQL modules live under effect/unstable/sql. Driver packages such as @effect/sql-pg and @effect/sql-sqlite-node are published separately and re-export the shared modules alongside their dialect-specific layer, makeCompiler, and Migrator.