# Inference engine

> "What Reylo infers from your SQL. Parameters, columns, nullability, cardinality and nested JSON shapes, from the live database's own analysis of each query."

## How it works

Your database does the typing.

Reylo has it prepare each statement without running it, and reads its catalog. Run `reylo analyze` on a file to see what it learns:

```sql
create table users (
  id int8 primary key,
  verified boolean not null
);
```

```sql
-- Whether a user has verified their email.
select verified from users where id = :id
```

| Field | What it holds |
| --- | --- |
| `file` | The file `reylo analyze` read the statement from. Only the CLI adds it: the format has no such field, and a statement given with `--sql` has none |
| `$schema` | The format's version, as the URL of its JSON Schema |
| `dialect` | The database, and its server's version |
| `sql` | The statement as the database runs it, with the database's own placeholders |
| `kind` | What the statement does: `select`, `insert`, `update`, `delete`, `merge` or `other` |
| `comment` | The comment before the statement |
| `params` | Each parameter's name and [type](https://reylo.dev/typedefs) |
| `result` | Each column's name and type, and the cardinality: how many rows the statement returns |
| `capabilities` | The tables the statement reads or writes |
| `diagnostics` | What the database reports where it refuses the statement |

Reylo caches each answer in [`reylo/.cache/`](https://reylo.dev/faq#faq-reylo-directory), keyed by a fingerprint of the schema, so the next check after a migration sees the new schema.

## Primitives

Each SQL type has one TypeScript type, the same on every driver.

In TypeScript, a 64-bit integer is a `bigint`, a decimal its exact text, and an instant its ISO 8601 text in UTC: no value is a `Date`. On Postgres, an instant always has six fraction digits, so two of them sort as strings. Pass back the text `.run()` returned, since a `Date` drops microseconds ([Dates and times](https://reylo.dev/postgres#dates-and-times)).

**Postgres**

**TypeScript**

| SQL type | TypeScript |
| --- | --- |
| `smallint`, `integer`, `oid` | `number` |
| `bigint` | `bigint` |
| `real`, `double precision` | `number` |
| `numeric`, `money` | `string` |
| `text`, `varchar`, `char` | `string` |
| `boolean` | `boolean` |
| `uuid` | `string` |
| `date` | `string`, as `2026-10-20` |
| `timestamp` | `string`, as `2026-10-20T10:00:00.123456` |
| `timestamptz` | `string`, the instant in UTC, as `2026-10-20T08:00:00.123456Z` |
| `time`, `timetz` | `string` |
| `interval` | `string`, an ISO 8601 duration, as `P1DT2H3M4S` |
| `json`, `jsonb` | `Json`, any JSON value, unless [its schema or the query](https://reylo.dev/inference#json) types it |
| `bytea` | `Uint8Array` |
| An array | an array of its element type, a `NULL` element `null` |
| An enum | the union of its labels |
| A domain | its base type |
| A row value of a table, a subquery or a composite type | an object of its fields |
| `point`, `box`, `circle` and the other geometric types | an object of its fields, as `{ x, y }` |
| A range | an object of its bounds, as `{ lower: 1, upper: 5, bounds: "[)" }`; a multirange an array of them |
| pgvector's `vector`, `halfvec` | `number[]` |
| `hstore` | `Record<string, string \| null>` |
| Every other type (`inet`, `cidr`, `tsvector`, `xml`, …) | a `string`, as the database writes it |

**MySQL**

**TypeScript**

| SQL type | TypeScript |
| --- | --- |
| `TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `YEAR` | `number` |
| `BOOLEAN` (a `TINYINT(1)`), `BIT(1)` | `boolean` |
| A comparison, `IS NULL`, `IN`, `BETWEEN`, `LIKE`, `EXISTS`, `NOT`, `AND`, `OR` | `boolean` |
| `BIGINT`, `count(*)` | `bigint` |
| `DECIMAL` | `string` |
| `FLOAT`, `DOUBLE` | `number` |
| `CHAR`, `VARCHAR`, `TEXT` | `string` |
| `ENUM` | the union of its labels |
| `SET` | a `Set` of its labels |
| `DATE` | `string`, as `2026-10-20` |
| `DATETIME`, `TIMESTAMP` | `string`, as `2026-10-20T10:00:00.123` |
| `TIME` | `string` |
| `JSON` | `Json`, any JSON value, unless [its schema or the query](https://reylo.dev/inference#json) types it |
| `BINARY`, `VARBINARY`, `BLOB`, `BIT` wider than 1 | `Uint8Array` |
| `VECTOR` | `number[]` |

**SQLite**

**TypeScript**

| Declared type | TypeScript |
| --- | --- |
| `INTEGER`, `INT`, `count(*)` | `number` |
| `BIGINT`, `INT8`, `UNSIGNED BIG INT` | `bigint` |
| `REAL` | `number` |
| `TEXT` | `string` |
| `BLOB` | `Uint8Array` |
| `BOOLEAN`, `BOOL` | `boolean` |
| A comparison, `IS NULL`, `IN`, `BETWEEN`, `LIKE`, `EXISTS`, `NOT`, `AND`, `OR` | `boolean` |
| `NUMERIC`, `DATE`, `DATETIME` | `number \| string` |
| No type, or `ANY` in a `STRICT` table | `unknown` |
| `json_object(…)`, `json_group_array(…)` | [the object or array it builds](https://reylo.dev/inference#json) |

For every rule and edge case of each database, from 64-bit integers to time zones, nulls, row counts and parameters, see its own page: [Postgres](https://reylo.dev/postgres#data-types), [MySQL](https://reylo.dev/mysql#data-types) and [SQLite](https://reylo.dev/sqlite#data-types).

## Named parameters

Name a parameter with a colon, and Reylo infers its type from where it's used, and compiles it to your database's own placeholder:

```sql
update users
set name = :name
where id = :id
```

```ts
// Params
{ name: string; id: number }
// Returns
void
```

## Casts

Where the query doesn't pin down a parameter's type, cast it. Reylo compiles the cast to each database's own:

```sql
select id, name
from users
where name like concat('%', :q::text, '%')
```

```ts
// Params
{ q: string }
// Returns
{ id: number; name: string }[]
```

**When a parameter's type cannot be inferred**

It's an error that says to [cast it](https://reylo.dev/inference#casts), unless [`allow_untyped_params: true`](https://reylo.dev/configuration#allow_untyped_params) types it `unknown`.

**Postgres**

Postgres types a parameter from its context: a bare `select :x` is a `string`, and `select :x is null` is an error until a cast types it.

**MySQL**

MySQL types every parameter: one that nothing else types, such as a bare `select :x`, is a `string`.

**SQLite**

A bare `select :x`, or an argument of a table-valued function other than `json_each` and `json_tree`, has no type until a cast gives it one:

```sql
select length(:text::text) as n
```

A call to a function your application registers fails the type check, cast or not: Reylo prepares the statement in its own SQLite, which lacks it ("no such function"). Apply that function in TypeScript instead.

## Column types

Postgres and MySQL:

```sql
select id, name
from users
where role = :role
```

```ts
// Params
{ role: "admin" | "member" }
// Returns
{ id: number; name: string }[]
```

SQLite:

```sql
select id, name
from users
where role = :role
```

```ts
// Params
{ role: string }
// Returns
{ id: number; name: string }[]
```

## Left joins

A left join makes the joined table's columns nullable:

```sql
select u.name, p.title
from users u
left join posts p on p.user_id = u.id
where u.id = :user_id::int
```

```ts
// Params
{ user_id: number }
// Returns
{ name: string; title: string | null }[]
```

A test in the `where` that fails on null, like a comparison, makes the whole joined row non-null:

```sql
select u.name, p.title
from users u
left join posts p on p.user_id = u.id
where p.published_at > :since
```

```ts
// Params
{ since: string }
// Returns
{ name: string; title: string }[]
```

## Computed columns

Reylo infers which computed columns can never be null:

Postgres and MySQL:

```sql
select u.name,
  coalesce(u.email, '') as email,
  exists (select 1 from posts p where p.user_id = u.id) as has_posts
from users u
where u.role = :role
```

```ts
// Params
{ role: "admin" | "member" }
// Returns
{ name: string; email: string; has_posts: boolean }[]
```

SQLite:

```sql
select u.name,
  coalesce(u.email, '') as email,
  exists (select 1 from posts p where p.user_id = u.id) as has_posts
from users u
where u.role = :role
```

```ts
// Params
{ role: string }
// Returns
{ name: string; email: string; has_posts: boolean }[]
```

## Nullable parameters

To accept null, cast a parameter to `nullable(…)`. Leaving it out passes `null`:

```sql
select id, title
from posts
where user_id = coalesce(:user_id::nullable(int), user_id)
```

```ts
// Params
{ user_id?: number | null }
// Returns
{ id: number; title: string }[]
```

A parameter written as it is to a column that may hold null, as `:published_at` is to `posts.published_at`, takes null too, and may be left out as well. Any other parameter takes no null: if one gets `null`, or is left out, `.run()` throws before the query runs, since the result's types rely on its value. A JSON parameter there takes `null` as JSON's `null`, which a `not null` JSON column holds; cast it to `nullable(…)` to pass SQL's `NULL` instead.

## Dynamic sorting

To pick the sort order at run time, order by a `case` on a parameter:

```sql
-- a parameter appears once, so one read twice comes from a with
with input as (select :sort::text as sort)
select u.id, u.name
from users u, input
order by
  case when input.sort = 'name' then u.name end,
  case when input.sort = 'newest' then u.created_at end desc,
  u.id
```

```ts
// Params
{ sort: string }
// Returns
{ id: number; name: string }[]
```

## Dynamic filtering

To filter a list by a form's values, pass them as one [JSON parameter](https://reylo.dev/inference#typed-json), which Reylo checks against its schema before the query runs:

Postgres:

```sql
with input as (
  select f ->> 'q' as q, (f ->> 'author_id')::int as author_id, f ->> 'status' as status, f ->> 'sort' as sort
  from (select :filters::jsonb('{
    "type": "object",
    "properties": {
      "q": { "type": "string", "maxLength": 100, "default": "" },
      "author_id": { "type": "integer" },
      "status": { "enum": ["published", "draft", "all"], "default": "all" },
      "sort": { "enum": ["newest", "oldest", "title"], "default": "newest" }
    },
    "additionalProperties": false
  }') as f)
)
select p.id, p.title, u.name as author
from posts p
join users u on u.id = p.user_id
cross join input
where p.title like concat('%', input.q, '%')
  and (input.author_id is null or p.user_id = input.author_id)
  and case input.status
    when 'published' then p.published_at is not null
    when 'draft' then p.published_at is null
    else true
  end
order by
  case when input.sort = 'title' then p.title end,
  case when input.sort = 'oldest' then p.id end,
  p.id desc
```

```ts
// Params
{
  filters: {
    q?: string;
    author_id?: number;
    status?: "published" | "draft" | "all";
    sort?: "newest" | "oldest" | "title";
  };
}
// Returns
{ id: number; title: string; author: string }[]
```

MySQL:

```sql
with input as (
  select f ->> '$.q' as q, f ->> '$.author_id' as author_id, f ->> '$.status' as status, f ->> '$.sort' as sort
  from (select :filters::json('{
    "type": "object",
    "properties": {
      "q": { "type": "string", "maxLength": 100, "default": "" },
      "author_id": { "type": "integer" },
      "status": { "enum": ["published", "draft", "all"], "default": "all" },
      "sort": { "enum": ["newest", "oldest", "title"], "default": "newest" }
    },
    "additionalProperties": false
  }') as f) as f
)
select p.id, p.title, u.name as author
from posts p
join users u on u.id = p.user_id
cross join input
where p.title like concat('%', input.q, '%')
  and (input.author_id is null or p.user_id = input.author_id)
  and case input.status
    when 'published' then p.published_at is not null
    when 'draft' then p.published_at is null
    else true
  end
order by
  case when input.sort = 'title' then p.title end,
  case when input.sort = 'oldest' then p.id end,
  p.id desc
```

```ts
// Params
{
  filters: {
    q?: string;
    author_id?: number;
    status?: "published" | "draft" | "all";
    sort?: "newest" | "oldest" | "title";
  };
}
// Returns
{ id: number; title: string; author: string }[]
```

SQLite:

```sql
with input as (
  select f ->> '$.q' as q, f ->> '$.author_id' as author_id, f ->> '$.status' as status, f ->> '$.sort' as sort
  from (select :filters::json('{
    "type": "object",
    "properties": {
      "q": { "type": "string", "maxLength": 100, "default": "" },
      "author_id": { "type": "integer" },
      "status": { "enum": ["published", "draft", "all"], "default": "all" },
      "sort": { "enum": ["newest", "oldest", "title"], "default": "newest" }
    },
    "additionalProperties": false
  }') as f)
)
select p.id, p.title, u.name as author
from posts p
join users u on u.id = p.user_id
cross join input
where p.title like concat('%', input.q, '%')
  and (input.author_id is null or p.user_id = input.author_id)
  and case input.status
    when 'published' then p.published_at is not null
    when 'draft' then p.published_at is null
    else true
  end
order by
  case when input.sort = 'title' then p.title end,
  case when input.sort = 'oldest' then p.id end,
  p.id desc
```

```ts
// Params
{
  filters: {
    q?: string;
    author_id?: number;
    status?: "published" | "draft" | "all";
    sort?: "newest" | "oldest" | "title";
  };
}
// Returns
{ id: number; title: string; author: string }[]
```

A filter left out takes its default, and one with none, like the author, is null, which turns it off:

```ts
import searchPosts from "./search_posts.sql";

// POST /posts/search with { "q": "sql", "status": "draft" }
const posts = await searchPosts.run(db, { filters: await request.json() });
```

Read with `->>`, each filter is a value the database can plan with, so on Postgres and MySQL a filter left out drops out of the plan and one given, like the author, can use an index.

**Which filters use an index, in each dialect**

- **Postgres** plans each run with its values on pg and PGlite. On postgres.js and Bun.sql, which prepare each statement once per connection, it can switch to one plan for every value after five runs, which reads the whole table; create the client with `connection: { plan_cache_mode: "force_custom_plan" }` to keep planning each run with its values. The same filter read from `jsonb_to_record` reads the whole table under every plan.
- **MySQL** plans each run with its values. The same filter read from `json_table` reads the whole table.
- **SQLite** plans a statement before its values are bound, so a filter that may be left out reads every row.

## Unique keys

Reylo infers each query's cardinality, how many rows it returns. A query that matches a unique key returns one row or null:

```sql
-- Schema (Postgres)
create table users (
  id serial primary key,
  name text not null,
  email text unique
);
```

```sql
-- Schema (MySQL)
create table users (
  id int auto_increment primary key,
  name text not null,
  email varchar(255) unique
);
```

```sql
-- Schema (SQLite)
create table users (
  id integer primary key,
  name text not null,
  email text unique
) strict;
```

```sql
select id, name
from users
where email = :email::text
```

```ts
// Params
{ email: string }
// Returns
{ id: number; name: string } | null
```

**Which matches count as a key, in each dialect**

**Postgres**

Also a key match:

- a join on the joined table's key
- a unique index on an expression or with a `where`
- a `distinct` or `group by` over values the `where` pins
- a subquery, a `with` query or a view that keeps a key

**MySQL**

Also a key match:

- a join on the joined table's numeric key
- a `distinct` or `group by` over values the `where` pins
- a derived table, a `with` query or a view that keeps a key

A unique index on an expression gives `many`.

**SQLite**

Also a key match:

- a join on the joined table's key
- a `distinct` or `group by` over values the `where` pins
- a subquery, a `with` query or a view that keeps a key

A unique index on an expression or with a `where` gives `many`.

At run time, `.run()` throws rather than return a row count its type rules out.

## Aggregates

An aggregate without a group by returns exactly one row:

Postgres:

```sql
select count(*)::int as posts
from posts
where user_id = :user_id::int
```

```ts
// Params
{ user_id: number }
// Returns
{ posts: number }
```

MySQL:

```sql
select count(*) as posts
from posts
where user_id = :user_id::int
```

```ts
// Params
{ user_id: number }
// Returns
{ posts: bigint }
```

SQLite:

```sql
select count(*) as posts
from posts
where user_id = :user_id::int
```

```ts
// Params
{ user_id: number }
// Returns
{ posts: number }
```

A rollup adds a row for the total, whose grouped column is null:

Postgres:

```sql
select user_id, count(*)::int as posts
from posts
group by rollup (user_id)
order by user_id
```

```ts
// Params
void
// Returns
{ user_id: number | null; posts: number }[]
```

MySQL:

```sql
select user_id, count(*) as posts
from posts
group by user_id with rollup
order by user_id
```

```ts
// Params
void
// Returns
{ user_id: bigint | null; posts: bigint }[]
```

## Single values

A column with no name returns its values instead of rows:

Postgres:

```sql
select count(*)::int
from posts
where user_id = :user_id::int
```

```ts
// Params
{ user_id: number }
// Returns
number
```

MySQL:

```sql
select count(*)
from posts
where user_id = :user_id::int
```

```ts
// Params
{ user_id: number }
// Returns
bigint
```

SQLite:

```sql
select count(*)
from posts
where user_id = :user_id::int
```

```ts
// Params
{ user_id: number }
// Returns
number
```

An expression with no `as` has no name of its own, only what your database makes up for it, which can differ by driver. Add an `as` to get rows back, as in the [query above](https://reylo.dev/inference#aggregates). A column read as it is, like `select id`, already has a name and keeps its rows.

- Where such a value may be null and the query may also match no row, `null` stands for both. Name the column to tell them apart.
- Among several columns, a made-up name that isn't an identifier, like `count(*)` or `?column?`, is a type error. Name the column with `as`.

## Window functions

Reylo types each window function:

Postgres and MySQL:

```sql
select id, title,
  row_number() over (
    partition by user_id
    order by published_at
  ) as n,
  lag(title) over (order by id) as prev
from posts
```

```ts
// Params
void
// Returns
{ id: number; title: string; n: bigint; prev: string | null }[]
```

SQLite:

```sql
select id, title,
  row_number() over (
    partition by user_id
    order by published_at
  ) as n,
  lag(title) over (order by id) as prev
from posts
```

```ts
// Params
void
// Returns
{ id: number; title: string; n: number; prev: string | null }[]
```

## Subqueries

Run unrelated subqueries side by side, in one query and one round trip. With no `from`, the query returns exactly one row, and `.run()` resolves to it:

Postgres:

```sql
-- the latest posts
with recent as (select id, title from posts order by id desc limit :limit::int)
select
  (select count(*)::int from users) as user_count,
  (select count(*)::int from posts) as post_count,
  (
    select coalesce(jsonb_agg(jsonb_build_object(
      'id', r.id,
      'title', r.title
    )), '[]')
    from recent r
  ) as recent_posts
```

```ts
// Params
{ limit: number }
// Returns
{
  user_count: number;
  post_count: number;
  recent_posts: { id: number; title: string }[];
}
```

MySQL:

```sql
-- the latest posts
with recent as (select id, title from posts order by id desc limit :limit::int)
select
  (select count(*) from users) as user_count,
  (select count(*) from posts) as post_count,
  (
    select coalesce(json_arrayagg(json_object(
      'id', r.id,
      'title', r.title
    )), json_array())
    from recent r
  ) as recent_posts
```

```ts
// Params
{ limit: number }
// Returns
{
  user_count: bigint;
  post_count: bigint;
  recent_posts: { id: number; title: string }[];
}
```

SQLite:

```sql
-- the latest posts
with recent as (select id, title from posts order by id desc limit :limit::int)
select
  (select count(*) from users) as user_count,
  (select count(*) from posts) as post_count,
  (
    select json_group_array(json_object(
      'id', r.id,
      'title', r.title
    ))
    from recent r
  ) as recent_posts
```

```ts
// Params
{ limit: number }
// Returns
{
  user_count: number;
  post_count: number;
  recent_posts: { id: number; title: string }[];
}
```

A lateral join runs its subquery once per row:

Postgres:

```sql
select u.name, p.title, p.published_at
from users u
left join lateral (
  select title, published_at
  from posts
  where user_id = u.id
  order by published_at desc nulls last
  limit 1
) p on true
```

```ts
// Params
void
// Returns
{
  name: string;
  title: string | null;
  published_at: string | null;
}[]
```

MySQL:

```sql
select u.name, p.title, p.published_at
from users u
left join lateral (
  select title, published_at
  from posts
  where user_id = u.id
  order by published_at desc
  limit 1
) p on true
```

```ts
// Params
void
// Returns
{
  name: string;
  title: string | null;
  published_at: string | null;
}[]
```

## Returning rows

An insert that returns its row resolves to exactly that row:

Postgres and SQLite:

```sql
insert into users (name)
values (:name::text)
returning id
```

```ts
// Params
{ name: string }
// Returns
{ id: number }
```

So does an upsert, whether it inserts the row or updates it:

Postgres and SQLite:

```sql
insert into users (name, email)
values (:name, :email::text)
on conflict (email) do update
  set name = excluded.name
returning id, name
```

```ts
// Params
{ name: string; email: string }
// Returns
{ id: number; name: string }
```

A column the write sets to a value that is never null returns that value, so it isn't null either:

Postgres and SQLite:

```sql
update users
set email = :email::text
where id = :id
returning email
```

```ts
// Params
{ email: string; id: number }
// Returns
{ email: string } | null
```

On Postgres 18, an update can return its values from before and after, as `old` and `new`:

Postgres:

```sql
update users
set name = :name
where id = :id
returning old.name as old_name, new.name as new_name
```

```ts
// Params
{ name: string; id: number }
// Returns
{ old_name: string; new_name: string } | null
```

## Enums

A text column whose check constraint lists its values is a union of them, as a Postgres enum is:

```sql
-- Schema
create table events (
  id serial primary key,
  kind text not null check (kind in ('click', 'key'))
);
```

Postgres:

```sql
select id, kind
from events
order by id desc
limit :limit::int
```

```ts
// Params
{ limit: number }
// Returns
{ id: number; kind: "click" | "key" }[]
```

So is a column the query fills with constants, each arm of a `union all` adding its own:

Postgres and SQLite:

```sql
select 'user' as kind, id, name as label from users
union all
select 'post', id, title from posts
```

```ts
// Params
void
// Returns
{ kind: "user" | "post"; id: number; label: string }[]
```

MySQL:

```sql
select 'user' as kind, id, name as label from users
union all
select 'post', id, title from posts
```

```ts
// Params
void
// Returns
{ kind: "user" | "post"; id: bigint; label: string }[]
```

## JSON aggregation

Type information bubbles up through JSON aggregation:

Postgres:

```sql
select u.name,
  jsonb_agg(jsonb_build_object('id', p.id, 'title', p.title)) as posts
from users u
join posts p on p.user_id = u.id
where p.published_at > :since
group by u.id
```

```ts
// Params
{ since: string }
// Returns
{ name: string; posts: { id: number; title: string }[] }[]
```

MySQL:

```sql
select u.name,
  json_arrayagg(json_object('id', p.id, 'title', p.title)) as posts
from users u
join posts p on p.user_id = u.id
where p.published_at > :since
group by u.id
```

```ts
// Params
{ since: string }
// Returns
{ name: string; posts: { id: number; title: string }[] }[]
```

SQLite:

```sql
select u.name,
  json_group_array(json_object('id', p.id, 'title', p.title)) as posts
from users u
join posts p on p.user_id = u.id
where p.published_at > :since
group by u.id
```

```ts
// Params
{ since: string }
// Returns
{ name: string; posts: { id: number; title: string }[] }[]
```

## Nested fetching

Fetch each row's related rows with a subquery. Postgres collects them into an array of rows, MySQL and SQLite into JSON, and Reylo types every field:

Postgres:

```sql
select u.id, u.name,
  -- every post, as an array
  array(
    select p from (
      select id, title from posts where user_id = u.id order by id
    ) p
  ) as posts,
  -- the latest post, or null
  (
    select p from (
      select id, title from posts
      where user_id = u.id
      order by published_at desc nulls last
      limit 1
    ) p
  ) as latest_post
from users u
where u.id = :id::int
```

```ts
// Params
{ id: number }
// Returns
{
  id: number;
  name: string;
  posts: { id: number; title: string }[];
  latest_post: { id: number; title: string } | null;
} | null
```

MySQL:

```sql
select u.id, u.name,
  -- every post, as an array
  (
    select coalesce(json_arrayagg(json_object(
      'id', p.id,
      'title', p.title
    )), json_array())
    from posts p
    where p.user_id = u.id
  ) as posts,
  -- the latest post, or null
  (
    select json_object('id', p.id, 'title', p.title)
    from posts p
    where p.user_id = u.id
    order by p.published_at desc
    limit 1
  ) as latest_post
from users u
where u.id = :id::int
```

```ts
// Params
{ id: number }
// Returns
{
  id: number;
  name: string;
  posts: { id: number; title: string }[];
  latest_post: { id: number; title: string } | null;
} | null
```

SQLite:

```sql
select u.id, u.name,
  -- every post, as an array
  (
    select json_group_array(json_object(
      'id', p.id,
      'title', p.title
    ))
    from posts p
    where p.user_id = u.id
  ) as posts,
  -- the latest post, or null
  (
    select json_object('id', p.id, 'title', p.title)
    from posts p
    where p.user_id = u.id
    order by p.published_at desc nulls last
    limit 1
  ) as latest_post
from users u
where u.id = :id::int
```

```ts
// Params
{ id: number }
// Returns
{
  id: number;
  name: string;
  posts: { id: number; title: string }[];
  latest_post: { id: number; title: string } | null;
} | null
```

On Postgres, each field of an array of rows is read as a column is, so a `bigint` stays exact. [Inside JSON, values can differ](https://reylo.dev/postgres#json). JSON is still the smaller result when rows nest three levels deep and hold JSON documents, since a row's text escapes the quotes of what it holds at each level:

Postgres:

```sql
select u.id,
  (
    select coalesce(jsonb_agg(p), '[]')
    from (select id, title from posts where user_id = u.id order by id) p
  ) as posts
from users u
```

```ts
// Params
void
// Returns
{ id: number; posts: { id: number; title: string }[] }[]
```

## JSON Schema

Declare a JSON Schema, and Reylo types the JSON it describes. A JSON column declares one in a check constraint:

```sql
-- Schema (Postgres)
create extension if not exists pg_jsonschema;

create table events (
  id serial primary key,
  payload jsonb not null check (jsonb_matches_schema('{
    "type": "object",
    "properties": {
      "x": { "type": "integer" },
      "y": { "type": "integer" }
    },
    "required": ["x", "y"],
    "additionalProperties": false
  }', payload))
);
```

```sql
-- Schema (MySQL)
create table events (
  id int auto_increment primary key,
  payload json not null check (json_schema_valid('{
    "type": "object",
    "properties": {
      "x": { "type": "integer" },
      "y": { "type": "integer" }
    },
    "required": ["x", "y"],
    "additionalProperties": false
  }', payload))
);
```

Postgres and MySQL:

```sql
select id, payload
from events
where id = :id::int
```

```ts
// Params
{ id: number }
// Returns
{ id: number; payload: { x: number; y: number } } | null
```

## JSON parameters

A JSON parameter declares its schema in its cast to `json` or `jsonb`, and Reylo infers the parameter's type from it:

```sql
insert into events (kind, payload)
values ('click', :payload::json('{
  "type": "object",
  "properties": {
    "x": { "type": "integer" },
    "y": { "type": "integer" },
    "button": { "type": "string", "enum": ["left", "right"] }
  },
  "required": ["x", "y"]
}'))
```

```ts
// Params
{ payload: { x: number; y: number; button?: "left" | "right" } }
// Returns
void
```

Reylo also checks the value against the schema when you call `.run()`:

```ts
import recordClick from "./record_click.sql";

// type-checks, but .run() rejects it: x must be an integer
await recordClick.run(db, { payload: { x: 1.5, y: 20 } });
```

A schema can nest objects and arrays, and check every value in them:

```sql
insert into events (kind, payload)
values ('order', :order::json('{
  "type": "object",
  "properties": {
    "customer": { "type": "string", "format": "email" },
    "items": {
      "type": "array",
      "minItems": 1,
      "items": {
        "type": "object",
        "properties": {
          "sku": { "type": "string", "pattern": "^[A-Z]{3}-[0-9]{4}$" },
          "qty": { "type": "integer", "minimum": 1 }
        },
        "required": ["sku", "qty"]
      }
    },
    "shipping": { "enum": ["standard", "express"] }
  },
  "required": ["customer", "items"]
}'))
```

```ts
// Params
{
  order: {
    customer: string;
    items: { sku: string; qty: number }[];
    shipping?: "standard" | "express";
  };
}
// Returns
void
```

A JSON parameter written to a schema-checked column takes that schema as its type, with no cast:

```sql
-- Schema
create extension if not exists pg_jsonschema;

create table events (
  id serial primary key,
  payload jsonb not null check (jsonb_matches_schema('{
    "type": "object",
    "properties": {
      "x": { "type": "integer" },
      "y": { "type": "integer" }
    },
    "required": ["x", "y"],
    "additionalProperties": false
  }', payload))
);
```

Postgres:

```sql
insert into events (payload)
values (:payload)
```

```ts
// Params
{ payload: { x: number; y: number } }
// Returns
void
```

## Filtering by a list

Pass the list as one parameter:

Postgres:

```sql
select id, name from users where id = any(:ids::int[])
```

```ts
// Params
{ ids: number[] }
// Returns
{ id: number; name: string }[]
```

MySQL:

```sql
-- MySQL has no array type, so the list is a JSON parameter
with input as (
  select :ids::json('{
    "type": "array",
    "items": { "type": "integer" }
  }') as ids
)
select id, name from users
where id in (
  select j.id from input, json_table(input.ids, '$[*]' columns (
    id int path '$'
  )) as j
)
```

```ts
// Params
{ ids: number[] }
// Returns
{ id: number; name: string }[]
```

SQLite:

```sql
-- SQLite has no array type, so the list is a JSON parameter
with input as (
  select :ids::json('{
    "type": "array",
    "items": { "type": "integer" }
  }') as ids
)
select id, name from users
where id in (select j.value from input, json_each(input.ids) as j)
```

```ts
// Params
{ ids: number[] }
// Returns
{ id: number; name: string }[]
```

## Bulk inserts

Pass the rows as one [JSON parameter](https://reylo.dev/inference#typed-json): the schema in its cast types them, and Reylo checks every row before the statement runs:

Postgres:

```sql
with input as (
  select :users::jsonb('{
    "type": "array",
    "maxItems": 1000,
    "items": {
      "type": "object",
      "properties": {
        "name": { "type": "string" },
        "email": { "type": ["string", "null"], "format": "email" }
      },
      "required": ["name"]
    }
  }') as users
)
insert into users (name, email)
select u.name, u.email
from input, jsonb_to_recordset(input.users) as u(name text, email text)
returning id
```

```ts
// Params
{ users: { name: string; email?: string | null }[] }
// Returns
{ id: number }[]
```

MySQL:

```sql
insert into users (name, email)
with input as (
  select :users::json('{
    "type": "array",
    "maxItems": 1000,
    "items": {
      "type": "object",
      "properties": {
        "name": { "type": "string" },
        "email": { "type": ["string", "null"], "format": "email" }
      },
      "required": ["name"]
    }
  }') as users
)
select u.name, u.email
from input, json_table(input.users, '$[*]' columns (
  name text path '$.name',
  email varchar(255) path '$.email'
)) as u
```

```ts
// Params
{ users: { name: string; email?: string | null }[] }
// Returns
void
```

SQLite:

```sql
with input as (
  select :users::json('{
    "type": "array",
    "maxItems": 1000,
    "items": {
      "type": "object",
      "properties": {
        "name": { "type": "string" },
        "email": { "type": ["string", "null"], "format": "email" }
      },
      "required": ["name"]
    }
  }') as users
)
insert into users (name, email)
select u.value ->> 'name', u.value ->> 'email'
from input, json_each(input.users) as u
returning id
```

```ts
// Params
{ users: { name: string; email?: string | null }[] }
// Returns
{ id: number }[]
```

## Nested inserts

Users with their posts, in one parameter.

**Postgres**

Postgres writes both tables in one statement:

Postgres:

```sql
with input as (
  select :users::jsonb('{
    "type": "array",
    "items": {
      "type": "object",
      "properties": {
        "name": { "type": "string" },
        "email": { "type": "string", "format": "email" },
        "posts": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": { "title": { "type": "string" } },
            "required": ["title"]
          }
        }
      },
      "required": ["name", "email", "posts"]
    }
  }') as users
),
new_users as (
  insert into users (name, email)
  select u.name, u.email
  from input, jsonb_to_recordset(input.users) as u(name text, email text)
  returning id, email
)
insert into posts (user_id, title)
select n.id, p.title
from input
cross join jsonb_to_recordset(input.users) as u(email text, posts jsonb)
join new_users n on n.email = u.email
cross join jsonb_to_recordset(u.posts) as p(title text)
```

```ts
// Params
{
  users: {
    name: string;
    email: string;
    posts: { title: string }[];
  }[];
}
// Returns
void
```

**MySQL**

On MySQL, a `with` only reads, so the users and the posts are two statements, run in one transaction:

`main.ts`:

```ts
import mysql from "mysql2/promise";
import insertUsers, { type Params } from "./insert_users.sql";
import insertPosts from "./insert_posts.sql";

const db = mysql.createPool(process.env.DATABASE_URL);
const users = [
  {
    name: "Ada",
    email: "ada@example.com",
    posts: [{ title: "Notes" }],
  },
];

const conn = await db.getConnection();
try {
  await conn.beginTransaction();
  await insertUsers.run(conn, { users });
  await insertPosts.run(conn, { users });
  await conn.commit();
} catch (error) {
  await conn.rollback();
  throw error;
} finally {
  conn.release();
}
```

`insert_users.sql`:

```sql
insert into users (name, email)
with input as (
  select :users::json('{
    "type": "array",
    "items": {
      "type": "object",
      "properties": {
        "name": { "type": "string" },
        "email": { "type": "string", "format": "email" }
      },
      "required": ["name", "email"]
    }
  }') as users
)
select u.name, u.email
from input, json_table(input.users, '$[*]' columns (
  name text path '$.name',
  email varchar(255) path '$.email'
)) as u
```

`insert_posts.sql`:

```sql
insert into posts (user_id, title)
with input as (
  select :users::json('{
    "type": "array",
    "items": {
      "type": "object",
      "properties": {
        "email": { "type": "string" },
        "posts": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": { "title": { "type": "string" } },
            "required": ["title"]
          }
        }
      },
      "required": ["email", "posts"]
    }
  }') as users
)
select users.id, u.title
from input, json_table(input.users, '$[*]' columns (
  email varchar(255) path '$.email',
  nested path '$.posts[*]' columns (title text path '$.title')
)) as u
join users on users.email = u.email
where u.title is not null
```

**SQLite**

On SQLite, a `with` only reads, so the users and the posts are two statements, run in one transaction:

`main.ts`:

```ts
import Database from "better-sqlite3";
import insertUsers, { type Params } from "./insert_users.sql";
import insertPosts from "./insert_posts.sql";

const db = new Database("app.db");
const users = [
  {
    name: "Ada",
    email: "ada@example.com",
    posts: [{ title: "Notes" }],
  },
];

db.transaction(() => {
  insertUsers.runSync(db, { users });
  insertPosts.runSync(db, { users });
})();
```

`insert_users.sql`:

```sql
with input as (
  select :users::json('{
    "type": "array",
    "items": {
      "type": "object",
      "properties": {
        "name": { "type": "string" },
        "email": { "type": "string", "format": "email" }
      },
      "required": ["name", "email"]
    }
  }') as users
)
insert into users (name, email)
select u.value ->> 'name', u.value ->> 'email'
from input, json_each(input.users) as u
```

`insert_posts.sql`:

```sql
with input as (
  select :users::json('{
    "type": "array",
    "items": {
      "type": "object",
      "properties": {
        "email": { "type": "string" },
        "posts": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": { "title": { "type": "string" } },
            "required": ["title"]
          }
        }
      },
      "required": ["email", "posts"]
    }
  }') as users
)
insert into posts (user_id, title)
select users.id, p.value ->> 'title'
from input
join json_each(input.users) as u
join users on users.email = u.value ->> 'email'
join json_each(u.value -> 'posts') as p
```

## Discriminated unions with `oneOf`

A `oneOf` in a schema is a [union](https://reylo.dev/inference#unions). Give each member a key with one value, and it is a discriminated union, which TypeScript narrows on that key:

```sql
-- Schema (Postgres)
create extension if not exists pg_jsonschema;

create table events (
  id serial primary key,
  payload jsonb not null check (jsonb_matches_schema('{
    "type": "object",
    "oneOf": [{
      "properties": {
        "kind": { "const": "click" },
        "x": { "type": "integer" },
        "y": { "type": "integer" }
      },
      "required": ["kind", "x", "y"],
      "additionalProperties": false
    }, {
      "properties": {
        "kind": { "const": "key" },
        "code": { "type": "string" }
      },
      "required": ["kind", "code"],
      "additionalProperties": false
    }]
  }', payload))
);
```

```sql
-- Schema (MySQL)
-- MySQL checks JSON Schema draft 4, which has no const
create table events (
  id int auto_increment primary key,
  payload json not null check (json_schema_valid('{
    "type": "object",
    "oneOf": [{
      "properties": {
        "kind": { "enum": ["click"] },
        "x": { "type": "integer" },
        "y": { "type": "integer" }
      },
      "required": ["kind", "x", "y"],
      "additionalProperties": false
    }, {
      "properties": {
        "kind": { "enum": ["key"] },
        "code": { "type": "string" }
      },
      "required": ["kind", "code"],
      "additionalProperties": false
    }]
  }', payload))
);
```

Postgres and MySQL:

```sql
select id, payload
from events
where id = :id::int
```

```ts
// Params
{ id: number }
// Returns
{
  id: number;
  payload:
    | { kind: "click"; x: number; y: number }
    | { kind: "key"; code: string };
} | null
```

A parameter's schema works the same way:

Postgres:

```sql
with input as (
  select :events::jsonb('{
    "type": "array",
    "items": {
      "oneOf": [
        {
          "type": "object",
          "properties": {
            "kind": { "type": "string", "const": "click" },
            "x": { "type": "integer" },
            "y": { "type": "integer" }
          },
          "required": ["kind", "x", "y"]
        },
        {
          "type": "object",
          "properties": {
            "kind": { "type": "string", "const": "key" },
            "code": { "type": "string" }
          },
          "required": ["kind", "code"]
        }
      ]
    }
  }') as events
)
insert into events (kind, payload)
select e.value ->> 'kind', e.value
from input, jsonb_array_elements(input.events) as e(value)
```

```ts
// Params
{
  events: (
    | { kind: "click"; x: number; y: number }
    | { kind: "key"; code: string }
  )[];
}
// Returns
void
```

MySQL:

```sql
insert into events (kind, payload)
with input as (
  select :events::json('{
    "type": "array",
    "items": {
      "oneOf": [
        {
          "type": "object",
          "properties": {
            "kind": { "type": "string", "const": "click" },
            "x": { "type": "integer" },
            "y": { "type": "integer" }
          },
          "required": ["kind", "x", "y"]
        },
        {
          "type": "object",
          "properties": {
            "kind": { "type": "string", "const": "key" },
            "code": { "type": "string" }
          },
          "required": ["kind", "code"]
        }
      ]
    }
  }') as events
)
select e.kind, e.payload
from input, json_table(input.events, '$[*]' columns (
  kind text path '$.kind',
  payload json path '$'
)) as e
```

```ts
// Params
{
  events: (
    | { kind: "click"; x: number; y: number }
    | { kind: "key"; code: string }
  )[];
}
// Returns
void
```

SQLite:

```sql
with input as (
  select :events::json('{
    "type": "array",
    "items": {
      "oneOf": [
        {
          "type": "object",
          "properties": {
            "kind": { "type": "string", "const": "click" },
            "x": { "type": "integer" },
            "y": { "type": "integer" }
          },
          "required": ["kind", "x", "y"]
        },
        {
          "type": "object",
          "properties": {
            "kind": { "type": "string", "const": "key" },
            "code": { "type": "string" }
          },
          "required": ["kind", "code"]
        }
      ]
    }
  }') as events
)
insert into events (kind, payload)
select e.value ->> 'kind', e.value
from input, json_each(input.events) as e
```

```ts
// Params
{
  events: (
    | { kind: "click"; x: number; y: number }
    | { kind: "key"; code: string }
  )[];
}
// Returns
void
```
