# Schema-mapped queries and resolvers

Raw [queries](https://effect.plants.sh/sql/queries/) return untyped driver rows. This page covers the
schema-integration layer that sits between those raw queries and full
[models](https://effect.plants.sh/sql/models-and-migrations/):

- **`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 // 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: []
}) {}
```

```ts
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`](https://effect.plants.sh/batching/) 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](https://effect.plants.sh/caching/)). Model your payload identity deliberately.

```ts
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
`TransactionConnection`s 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).

```ts
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`.

```ts
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.

```ts
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.

```ts
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`.

```ts
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.

```ts
// 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`](https://effect.plants.sh/batching/) 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.

```ts
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.

```ts
// 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`.

```ts
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`.

```ts
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`.

```ts
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](https://effect.plants.sh/batching/) — the resolver/batching fundamentals
  `SqlResolver` builds on, plus `Effect.forEach({ concurrency: "unbounded" })`.
- [Request caching](https://effect.plants.sh/caching/) — caching keyed by request payload.
- [Schema](https://effect.plants.sh/schema/) — the schema basics used for `Request` and `Result`.
- [Models and migrations](https://effect.plants.sh/sql/models-and-migrations/) — `Model`-driven
  repositories that build on these helpers.