Queries

The SqlClient service is a callable sql function. Tag a template literal to build a query — interpolated values become bound parameters, so user input is never spliced into the SQL string. The result is an Effect that yields an array of rows and can fail with a SqlError. The same sql value also produces quoted identifiers, fragments, and helpers for inserts, updates, and IN clauses, so even dynamic queries stay parameterized.

import { Effect } from "effect"
import { SqlClient } from "effect/unstable/sql"

const findActiveUsers = Effect.gen(function*() {
  const sql = yield* SqlClient.SqlClient

  // Interpolated values (`true`, `10`) are sent as parameters, not text. The
  // type argument names the row shape for the returned array.
  const users = yield* sql<{ readonly id: number; readonly name: string }>`
    SELECT id, name
    FROM users
    WHERE active = ${true}
    LIMIT ${10}
  `

  return users
})

The query is just an Effect, so it composes with everything else: retry it, add a span, race it, or run it under a transaction. users above is typed as ReadonlyArray<{ id: number; name: string }>, but those are raw driver values — to validate and transform them, decode with Schema (shown below).

Identifiers, fragments, and helpers

Calling sql with a string returns a safely-quoted identifier, useful when a table or column name is dynamic. The constructor also exposes helpers that build SQL fragments for common shapes without hand-writing placeholder lists.

import { Effect } from "effect"
import { SqlClient } from "effect/unstable/sql"

const insertUser = Effect.fn("insertUser")(function*(name: string, email: string) {
  const sql = yield* SqlClient.SqlClient

  // `sql.insert` builds the column list and VALUES tuple from an object.
  // `sql("users")` quotes the identifier for the active dialect.
  yield* sql`
    INSERT INTO ${sql("users")} ${sql.insert({ name, email })}
  `
})

const usersByIds = Effect.fn("usersByIds")(function*(ids: ReadonlyArray<number>) {
  const sql = yield* SqlClient.SqlClient

  // `sql.in` expands an array into a parameterized `IN (?, ?, …)` clause.
  return yield* sql<{ readonly id: number; readonly name: string }>`
    SELECT id, name FROM users WHERE ${sql.in("id", ids)}
  `
})

const setUserName = Effect.fn("setUserName")(function*(id: number, name: string) {
  const sql = yield* SqlClient.SqlClient

  // `sql.update` builds the SET clause from the object's keys; an optional
  // second arg lists columns to omit from SET.
  yield* sql`
    UPDATE ${sql("users")} SET ${sql.update({ name })} WHERE id = ${id}
  `
})

Other useful constructors: sql.and([...]) / sql.or([...]) chain WHERE clauses, sql.csv(...) builds comma-separated lists for ORDER BY / GROUP BY, sql.literal(str) inlines trusted SQL verbatim (no escaping — never pass user input), and sql.unsafe(text, params) builds a fully custom statement.

literal vs. interpolation

Interpolating a value (${value}) always binds a parameter and is safe. sql.literal and sql.unsafe bypass escaping and must only receive SQL you control — never untrusted input.

Decoding rows with Schema

Raw rows are untyped driver output. SqlSchema wraps a query’s execute callback with a request schema (encoded on the way in) and a result schema (decoded on the way out), so your code works with validated domain values and a SchemaError surfaces any row that doesn’t match.

import { Effect, Schema } from "effect"
import { SqlClient, SqlSchema } from "effect/unstable/sql"

const User = Schema.Struct({
  id: Schema.Number,
  name: Schema.String,
  email: Schema.String
})

const makeQueries = Effect.gen(function*() {
  const sql = yield* SqlClient.SqlClient

  // findOne: encodes the request (here just an `id`), runs the query, then
  // decodes the first row. Fails with NoSuchElementError when there are none.
  const getUserById = SqlSchema.findOne({
    Request: Schema.Number,
    Result: User,
    execute: (id) => sql`SELECT * FROM users WHERE id = ${id}`
  })

  // findAll: decodes every row into a typed array (empty array when none).
  const searchUsers = SqlSchema.findAll({
    Request: Schema.String,
    Result: User,
    execute: (term) => sql`SELECT * FROM users WHERE name ILIKE ${`%${term}%`}`
  })

  return { getUserById, searchUsers } as const
})

SqlSchema offers findAll (zero or more rows), findNonEmpty (fails on an empty result), findOne (first row, fails if absent), findOneOption (first row as an Option), and void (encode the request, discard the result — for writes). The execute callback receives the encoded request, so any Schema transformations on the request shape apply before the value reaches SQL.

Streaming large result sets

Loading millions of rows into an array is wasteful. Every statement exposes a .stream property — a Stream that pulls rows from the cursor incrementally, so memory stays bounded regardless of result size.

import { Effect, Stream } from "effect"
import { SqlClient } from "effect/unstable/sql"

const exportEvents = Effect.gen(function*() {
  const sql = yield* SqlClient.SqlClient

  yield* sql<{ readonly id: number; readonly payload: string }>`
    SELECT id, payload FROM events ORDER BY id
  `.stream.pipe(
    // Process each row as it arrives without buffering the whole table.
    Stream.runForEach((event) => Effect.log(`event ${event.id}`))
  )
})

Transactions

sql.withTransaction wraps any effect so every query it runs is part of one transaction: the client issues BEGIN up front, COMMIT on success, and ROLLBACK if the effect fails or is interrupted. Nested withTransaction calls reuse the same connection and use savepoints, so composing transactional effects is safe.

import { Effect } from "effect"
import { SqlClient } from "effect/unstable/sql"

// Transfer between two accounts: both updates commit together, or neither does.
const transfer = Effect.fn("transfer")(function*(from: number, to: number, amount: number) {
  const sql = yield* SqlClient.SqlClient

  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}`
      // If anything below fails, both updates roll back automatically.
      const [account] = yield* sql<{ readonly balance: number }>`
        SELECT balance FROM accounts WHERE id = ${from}
      `
      if (account.balance < 0) {
        return yield* Effect.fail(new Error("insufficient funds"))
      }
    })
  )
})

Joining a transaction

A query only joins the surrounding transaction when it is run through the same SqlClient service. A query is tracked automatically because the transaction connection is threaded through context — you do not pass it around manually.

Errors

Queries fail with SqlError from effect/unstable/sql. It wraps a single structured reason (a tagged union), and exposes message plus an isRetryable flag derived from that reason. Match on the reason tag to handle specific database conditions.

import { Effect } from "effect"
import { SqlClient, SqlError } from "effect/unstable/sql"

const createUser = Effect.fn("createUser")(
  function*(email: string) {
    const sql = yield* SqlClient.SqlClient
    yield* sql`INSERT INTO users ${sql.insert({ email })}`
  },
  // SqlError is the only error channel; branch on the structured reason.
  // Cross-cutting handling rides as a trailing Effect.fn argument, not `.pipe`.
  Effect.catchTag("SqlError", (error: SqlError.SqlError) =>
    error.reason._tag === "UniqueViolation"
      ? Effect.fail(new Error("email already taken"))
      : Effect.fail(error)
  )
)

Reasons include ConnectionError, UniqueViolation, ConstraintError, DeadlockError, SqlSyntaxError, and more — retryable ones (connection, deadlock, serialization, lock timeout) set isRetryable: true, which pairs well with Scheduling retries. For broader error-handling patterns see Error Management.

Next, see Models and migrations to generate repositories from a Model and manage your database schema over time.