Schema-mapped queries and resolvers

Raw queries return untyped driver rows. This page covers the schema-integration layer that sits between those raw queries and full models:

• SqlSchema wraps a query’s execute callback so the request is encoded on the way in and rows are decoded into domain types on the way out. A SchemaError surfaces any mismatch.
• SqlResolver turns a lookup or mutation into a RequestResolver, so many concurrent requests batch and deduplicate into a single SQL operation.

Both import from effect/unstable/sql.

Common case: a query service with SqlSchema

The idiomatic shape is a service that builds its query functions once (capturing the SqlClient) and exposes them as methods. Each helper returns a function (request) => Effect<Result, SchemaError | E, R>.

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

// Domain type. The Result schema decodes rows into this; the Request schema
// encodes the input before it reaches SQL.
const User = Schema.Struct({
  id: Schema.Number,
  name: Schema.String,
  email: Schema.String
})

export class Users extends Context.Service<Users>()("app/Users", {
  effect: Effect.gen(function*() {
    const sql = yield* SqlClient.SqlClient

    // findOne: encode the request (here a bare `id`), run the query, decode the
    // first row. Fails with NoSuchElementError when the result set is empty.
    const getUserById = SqlSchema.findOne({
      Request: Schema.Number,
      Result: User,
      // `execute` receives the ENCODED request — `Schema.Number` encodes to
      // `number`, so `id` here is the encoded value, not the decoded one.
      execute: (id) => sql`SELECT * FROM users WHERE id = ${id}`
    })

    // findAll: decode 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
  }),
  dependencies: []
}) {}

import { Context, Effect } from "effect"

const program = Effect.gen(function*() {
  const users = yield* Users
  const user = yield* users.getUserById(1)
  // => { id: 1, name: "Alice", email: "alice@example.com" }
  const matches = yield* users.searchUsers("ali")
  // => [{ id: 1, name: "Alice", email: "alice@example.com" }, ...]
})

Encoded in, decoded out

execute is called with Request["Encoded"], not Request["Type"]. Any schema transformations, encoding services, nullable columns, JSON values, dates, and bigint representations on the request must match the SQL dialect in use. Result schemas decode rows after the SQL client’s row transforms have run.

Batching with SqlResolver

When the same query runs many times concurrently — for example, loading the author of each post in a list — issuing one SQL statement per call is wasteful. SqlResolver builds a RequestResolver that collects all concurrent requests in a batch window and resolves them with a single SQL operation, then routes each decoded row back to the request that asked for it.

Each request is represented as a SqlRequest. Equality and hashing are by payload, so two requests with equal payloads are the same SqlRequest: they batch together and deduplicate automatically (and integrate with request caching). Model your payload identity deliberately.

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

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

export class UserLoader extends Context.Service<UserLoader>()("app/UserLoader", {
  effect: Effect.gen(function*() {
    const sql = yield* SqlClient.SqlClient

    // A batched loader keyed by id. `execute` receives a NonEmptyArray of all
    // the encoded ids requested in this batch window, and runs ONE query.
    const resolver = SqlResolver.findById({
      Id: Schema.Number,
      Result: User,
      // Map each decoded row back to the id it satisfies.
      ResultId: (user) => user.id,
      execute: (ids) => sql`SELECT * FROM users WHERE ${sql.in("id", ids)}`
    })

    // `request` runs one payload through the resolver as a SqlRequest.
    const loadUser = SqlResolver.request(resolver)

    return { loadUser } as const
  }),
  dependencies: []
}) {}

// Usage: 100 concurrent loadUser calls collapse into a single `WHERE id IN (...)`.
const loadMany = Effect.gen(function*() {
  const { loadUser } = yield* UserLoader
  const ids = [1, 2, 3, 1, 2] // duplicate 1 and 2 are deduplicated
  return yield* Effect.forEach(ids, loadUser, { concurrency: "unbounded" })
  // => [User#1, User#2, User#3, User#1, User#2]   (one SQL query)
})

Resolvers respect the transaction

Batches are split by the active SQL transaction connection. Requests made inside different sql.withTransaction scopes are keyed on different TransactionConnections and are not resolved in the same batch, so a resolver never mixes statements across transactions. Requests outside any transaction batch together.

SqlSchema reference

Every helper takes Request, Result (except void), and execute, and returns a function from the decoded request type to an Effect. The error channel always includes Schema.SchemaError (plus E from execute); the environment includes the request’s EncodingServices and the result’s DecodingServices.

findAll

Zero or more rows decoded into a typed array; an empty result set is an empty array (never a failure).

import { Schema } from "effect"
import { SqlSchema } from "effect/unstable/sql"

const listByName = SqlSchema.findAll({
  Request: Schema.String,
  Result: User,
  execute: (name) => sql`SELECT * FROM users WHERE name = ${name}`
})

// listByName("nobody") => []   (no rows -> empty array, succeeds)
// A row that fails the Result schema => fails with Schema.SchemaError

findNonEmpty

Like findAll, but treats an empty result set as a failure with Cause.NoSuchElementError. Succeeds with a NonEmptyArray.

import { Schema } from "effect"
import { SqlSchema } from "effect/unstable/sql"

const requireUsersByOrg = SqlSchema.findNonEmpty({
  Request: Schema.Number,
  Result: User,
  execute: (orgId) => sql`SELECT * FROM users WHERE org_id = ${orgId}`
})

// requireUsersByOrg(7) => [User, ...]            (at least one row)
// requireUsersByOrg(999) => fails NoSuchElementError   (zero rows)

findOne

Decodes the first row only; fails with Cause.NoSuchElementError when no rows are returned. It does not assert that the query returned exactly one row — extra rows are ignored.

import { Schema } from "effect"
import { SqlSchema } from "effect/unstable/sql"

const getUserById = SqlSchema.findOne({
  Request: Schema.Number,
  Result: User,
  execute: (id) => sql`SELECT * FROM users WHERE id = ${id}`
})

// getUserById(1) => { id: 1, name: "Alice", email: "..." }
// getUserById(999) => fails NoSuchElementError   (no row)

findOneOption

Like findOne, but returns the first row wrapped in Option.some, and Option.none when there are no rows — absence is a value, not a failure.

import { Schema } from "effect"
import { SqlSchema } from "effect/unstable/sql"

const findUserByEmail = SqlSchema.findOneOption({
  Request: Schema.String,
  Result: User,
  execute: (email) => sql`SELECT * FROM users WHERE email = ${email}`
})

// findUserByEmail("alice@example.com") => Option.some({ id: 1, ... })
// findUserByEmail("missing@x.com")     => Option.none()

void

Encodes the request and discards the result — for writes (inserts, updates, deletes) where the encoded input matters but no row is needed. Takes only Request and execute; succeeds with void.

import { Schema } from "effect"
import { SqlSchema } from "effect/unstable/sql"

const NewUser = Schema.Struct({ name: Schema.String, email: Schema.String })

const insertUser = SqlSchema.void({
  Request: NewUser,
  // The encoded request reaches `execute`; the driver result is thrown away.
  execute: (user) => sql`INSERT INTO users ${sql.insert(user)}`
})

// insertUser({ name: "Bob", email: "bob@x.com" }) => void

SqlResolver reference

SqlResolver.request

Runs a payload through a resolver as a SqlRequest, returning Effect<A, E | SchemaError, R>. Curried (request(resolver)) returns a function of the payload; uncurried (request(payload, resolver)) runs it directly.

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

// Curried: build a reusable loader function.
const loadUser = SqlResolver.request(resolver)
// loadUser(1) => Effect<User, SchemaError | E, R>

// Uncurried: run a single payload inline.
const one = SqlResolver.request(1, resolver)
// one => Effect<User, SchemaError | E, R>

SqlResolver.SqlRequest

Constructs a SqlRequest from a payload. The SqlRequest<In, A, E, R> type is a Request carrying payload: In; its equality and hashing are derived from the payload, which is what enables batching and deduplication. You rarely build one directly — request does it for you — but it is available for custom Effect.request flows.

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

const req = SqlResolver.SqlRequest<number, User, never, never>(1)
// req.payload => 1
// Effect.request(req, resolver) => Effect<User, ...>

SqlResolver.ordered

Maps results to requests positionally: execute receives a NonEmptyArray of encoded requests and must return rows in the same order and count. Fails with ResultLengthMismatch when the result count differs from the request count.

import { Schema } from "effect"
import { SqlResolver } from "effect/unstable/sql"

// One row per request, in order — works for value lookups, not `IN` queries
// (which may reorder rows). Preserve input order in SQL before using this.
const insertUserResolver = SqlResolver.ordered({
  Request: Schema.Struct({ name: Schema.String }),
  Result: User,
  execute: (rows) => sql`INSERT INTO users ${sql.insert(rows)} RETURNING *`
})

// If the DB returns 4 rows for 5 requests => fails ResultLengthMismatch
//   ({ expected: 5, actual: 4 })

SqlResolver.grouped

For one-to-many lookups. Groups decoded rows by a key, and completes each request with the NonEmptyArray<Result> for its group. RequestGroupKey derives the key from each request; ResultGroupKey derives it from each decoded result (and its raw row). A request whose group is missing fails with NoSuchElementError.

import { Schema } from "effect"
import { SqlResolver } from "effect/unstable/sql"

const Post = Schema.Struct({
  id: Schema.Number,
  authorId: Schema.Number,
  title: Schema.String
})

// Load all posts for each requested author id in one query.
const postsByAuthor = SqlResolver.grouped({
  Request: Schema.Number, // an author id
  RequestGroupKey: (authorId) => authorId,
  Result: Post,
  ResultGroupKey: (post) => post.authorId,
  execute: (authorIds) => sql`SELECT * FROM posts WHERE ${sql.in("author_id", authorIds)}`
})

// SqlResolver.request(postsByAuthor)(7) => [Post, Post, ...]   (all of author 7's posts)
// Requesting an author with no posts => fails NoSuchElementError

SqlResolver.findById

Batches where id in (...) lookups returning one row per id. Id is the id schema, ResultId maps a decoded row back to its id, and execute receives a NonEmptyArray of encoded ids. Missing ids fail with NoSuchElementError.

import { Schema } from "effect"
import { SqlResolver } from "effect/unstable/sql"

const userById = SqlResolver.findById({
  Id: Schema.Number,
  Result: User,
  ResultId: (user) => user.id,
  execute: (ids) => sql`SELECT * FROM users WHERE ${sql.in("id", ids)}`
})

// SqlResolver.request(userById)(1) => User#1
// Requesting an id with no matching row => fails NoSuchElementError

SqlResolver.void

A batched resolver for side-effecting statements (inserts, updates, deletes) that need no row back. execute receives a NonEmptyArray of encoded requests; every request completes with void.

import { Schema } from "effect"
import { SqlResolver } from "effect/unstable/sql"

const touchUser = SqlResolver.void({
  Request: Schema.Number, // a user id
  execute: (ids) => sql`UPDATE users SET seen_at = now() WHERE ${sql.in("id", ids)}`
})

// SqlResolver.request(touchUser)(1) => void   (batched with other touches)

See also

• Request & RequestResolver — the resolver/batching fundamentals SqlResolver builds on, plus Effect.forEach({ concurrency: "unbounded" }).
• Request caching — caching keyed by request payload.
• Schema — the schema basics used for Request and Result.
• Models and migrations — Model-driven repositories that build on these helpers.