grimoire

Catalog / @effect/sql-pg / SQL Resolvers and Batching
grimoire show @effect/sql-pg resolvers-and-batching

SQL Resolvers and Batching

Batched database access using SqlResolver with Effect's request batching

resolverbatchingSqlResolverrequest

SQL Resolvers and Batching

Overview

@effect/sql provides SqlResolver for defining batched database access patterns. When used with { batching: true } in Effect.all or Effect.forEach, multiple individual requests are automatically combined into single SQL queries. The resolver example demonstrates this with the Pg client.

Key Concepts

Resolver Types

SqlResolver.ordered - For insert/update operations where result order matches request order:

const Insert = yield* SqlResolver.ordered("InsertPerson", {
  Request: InsertPersonSchema,
  Result: Person,
  execute: (requests) =>
    sql`INSERT INTO people ${sql.insert(requests)} RETURNING people.*`
})

SqlResolver.findById - For lookup by unique ID:

const GetById = yield* SqlResolver.findById("GetPersonById", {
  Id: Schema.Number,
  Result: Person,
  ResultId: (result) => result.id,
  execute: (ids) =>
    sql`SELECT * FROM people WHERE id IN ${sql.in(ids)}`
})

SqlResolver.grouped - For lookup where multiple results can match each request:

const GetByName = yield* SqlResolver.grouped("GetPersonByName", {
  Request: Schema.String,
  RequestGroupKey: (_) => _,
  Result: Person,
  ResultGroupKey: (_) => _.name,
  execute: (ids) =>
    sql`SELECT * FROM people WHERE name IN ${sql.in(ids)}`
})

Batching in Action

Individual calls are batched into single queries:

// These two execute as ONE SQL statement
const [person1, person2] = yield* Effect.all(
  [Insert.execute({ name: "John" }), Insert.execute({ name: "Jane" })],
  { batching: true }
)

// These three lookups execute as ONE SQL statement
yield* Effect.forEach(
  ["John", "Jane", "John"],
  (name) => GetByName.execute(name),
  { batching: true }
)

Schema Integration

Resolvers use effect/Schema for request/result validation:

class Person extends Schema.Class<Person>("Person")({
  id: Schema.Number,
  name: Schema.String,
  createdAt: Schema.DateFromSelf
}) {}

const InsertPersonSchema = Schema.Struct(Person.fields).pipe(
  Schema.omit("id", "createdAt")
)

Transaction Support

Resolvers work within transactions:

yield* sql.withTransaction(
  Effect.all(
    [Insert.execute({ name: "John" }), Insert.execute({ name: "Jane" })],
    { batching: true }
  )
)

Related Files