Inference engine

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:

queries/is_verified.sql
-- Whether a user has verified their email.
select verified from users where id = :id
$ npx reylo analyze queries/is_verified.sql --format json | jq
{
  "file": "queries/is_verified.sql",
  "$schema": "https://reylo.dev/typedefs/preview.json",
  "dialect": {
    "name": "postgres",
    "version": "18.6"
  },
  "sql": "-- Whether a user has verified their email.\nselect verified from users where id = $1\n",
  "kind": "select",
  "comment": "Whether a user has verified their email.",
  "params": [
    {
      "name": "id",
      "type": {
        "kind": "integer",
        "bits": 64,
        "label": "int8"
      }
    }
  ],
  "result": {
    "kind": "table",
    "cardinality": "at_most_one",
    "columns": [
      {
        "name": "verified",
        "type": {
          "kind": "boolean",
          "label": "bool"
        }
      }
    ]
  },
  "capabilities": {
    "tables": {
      "public.users": "read"
    }
  },
  "diagnostics": []
}
FieldWhat it holds
fileThe 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
$schemaThe format's version, as the URL of its JSON Schema
dialectThe database, and its server's version
sqlThe statement as the database runs it, with the database's own placeholders
kindWhat the statement does: select, insert, update, delete, merge or other
commentThe comment before the statement
paramsEach parameter's name and type
resultEach column's name and type, and the cardinality: how many rows the statement returns
capabilitiesThe tables the statement reads or writes
diagnosticsWhat the database reports where it refuses the statement

Reylo caches each answer in reylo/.cache/, 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).

SQL typeTypeScript
smallint, integer, oidnumber
bigintbigint
real, double precisionnumber
numeric, moneystring
text, varchar, charstring
booleanboolean
uuidstring
datestring, as 2026-10-20
timestampstring, as 2026-10-20T10:00:00.123456
timestamptzstring, the instant in UTC, as 2026-10-20T08:00:00.123456Z
time, timetzstring
intervalstring, an ISO 8601 duration, as P1DT2H3M4S
json, jsonbJson, any JSON value, unless its schema or the query types it
byteaUint8Array
An arrayan array of its element type, a NULL element null
An enumthe union of its labels
A domainits base type
A row value of a table, a subquery or a composite typean object of its fields
point, box, circle and the other geometric typesan object of its fields, as { x, y }
A rangean object of its bounds, as { lower: 1, upper: 5, bounds: "[)" }; a multirange an array of them
pgvector's vector, halfvecnumber[]
hstoreRecord<string, string | null>
Every other type (inet, cidr, tsvector, xml, …)a string, as the database writes it
SQL typeTypeScript
TINYINT, SMALLINT, MEDIUMINT, INT, YEARnumber
BOOLEAN (a TINYINT(1)), BIT(1)boolean
A comparison, IS NULL, IN, BETWEEN, LIKE, EXISTS, NOT, AND, ORboolean
BIGINT, count(*)bigint
DECIMALstring
FLOAT, DOUBLEnumber
CHAR, VARCHAR, TEXTstring
ENUMthe union of its labels
SETa Set of its labels
DATEstring, as 2026-10-20
DATETIME, TIMESTAMPstring, as 2026-10-20T10:00:00.123
TIMEstring
JSONJson, any JSON value, unless its schema or the query types it
BINARY, VARBINARY, BLOB, BIT wider than 1Uint8Array
VECTORnumber[]
Declared typeTypeScript
INTEGER, INT, count(*)number
BIGINT, INT8, UNSIGNED BIG INTbigint
REALnumber
TEXTstring
BLOBUint8Array
BOOLEAN, BOOLboolean
A comparison, IS NULL, IN, BETWEEN, LIKE, EXISTS, NOT, AND, ORboolean
NUMERIC, DATE, DATETIMEnumber | string
No type, or ANY in a STRICT tableunknown
json_object(…), json_group_array(…)the object or array it builds

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, MySQL and SQLite.

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:

Query
update users
set name = :name
where id = :id
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:

Query
select id, name
from users
where name like concat('%', :q::text, '%')
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, unless allow_untyped_params: true types it unknown.

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 types every parameter: one that nothing else types, such as a bare select :x, is a string.

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:

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

Query
select id, name
from users
where role = :role
Params
{ role: "admin" | "member" }
Returns
{ id: number; name: string }[]
Query
select id, name
from users
where role = :role
Params
{ role: "admin" | "member" }
Returns
{ id: number; name: string }[]
Query
select id, name
from users
where role = :role
Params
{ role: string }
Returns
{ id: number; name: string }[]

Left joins

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

Query
select u.name, p.title
from users u
left join posts p on p.user_id = u.id
where u.id = :user_id::int
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:

Query
select u.name, p.title
from users u
left join posts p on p.user_id = u.id
where p.published_at > :since
Params
{ since: string }
Returns
{ name: string; title: string }[]

Computed columns

Reylo infers which computed columns can never be null:

Query
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
Params
{ role: "admin" | "member" }
Returns
{ name: string; email: string; has_posts: boolean }[]
Query
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
Params
{ role: "admin" | "member" }
Returns
{ name: string; email: string; has_posts: boolean }[]
Query
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
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:

Query
select id, title
from posts
where user_id = coalesce(:user_id::nullable(int), user_id)
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:

Query
-- 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
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, which Reylo checks against its schema before the query runs:

Query
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
Params
{
  filters: {
    q?: string;
    author_id?: number;
    status?: "published" | "draft" | "all";
    sort?: "newest" | "oldest" | "title";
  };
}
Returns
{ id: number; title: string; author: string }[]
Query
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
Params
{
  filters: {
    q?: string;
    author_id?: number;
    status?: "published" | "draft" | "all";
    sort?: "newest" | "oldest" | "title";
  };
}
Returns
{ id: number; title: string; author: string }[]
Query
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
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:

import  from "./search_posts.sql";

// POST /posts/search with { "q": "sql", "status": "draft" }
const  = await .(, { : await .() });

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:

Schema
create table users (
  id serial primary key,
  name text not null,
  email text unique
);
Query
select id, name
from users
where email = :email::text
Params
{ email: string }
Returns
{ id: number; name: string } | null
Schema
create table users (
  id int auto_increment primary key,
  name text not null,
  email varchar(255) unique
);
Query
select id, name
from users
where email = :email::text
Params
{ email: string }
Returns
{ id: number; name: string } | null
Schema
create table users (
  id integer primary key,
  name text not null,
  email text unique
) strict;
Query
select id, name
from users
where email = :email::text
Params
{ email: string }
Returns
{ id: number; name: string } | null
Which matches count as a key, in each dialect

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

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.

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:

Query
select count(*)::int as posts
from posts
where user_id = :user_id::int
Params
{ user_id: number }
Returns
{ posts: number }
Query
select count(*) as posts
from posts
where user_id = :user_id::int
Params
{ user_id: number }
Returns
{ posts: bigint }
Query
select count(*) as posts
from posts
where user_id = :user_id::int
Params
{ user_id: number }
Returns
{ posts: number }

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

Query
select user_id, count(*)::int as posts
from posts
group by rollup (user_id)
order by user_id
Params
void
Returns
{ user_id: number | null; posts: number }[]
Query
select user_id, count(*) as posts
from posts
group by user_id with rollup
order by user_id
Params
void
Returns
{ user_id: bigint | null; posts: bigint }[]

Single values

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

Query
select count(*)::int
from posts
where user_id = :user_id::int
Params
{ user_id: number }
Returns
number
Query
select count(*)
from posts
where user_id = :user_id::int
Params
{ user_id: number }
Returns
bigint
Query
select count(*)
from posts
where user_id = :user_id::int
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. 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:

Query
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
Params
void
Returns
{ id: number; title: string; n: bigint; prev: string | null }[]
Query
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
Params
void
Returns
{ id: number; title: string; n: bigint; prev: string | null }[]
Query
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
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:

Query
-- 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
Params
{ limit: number }
Returns
{
  user_count: number;
  post_count: number;
  recent_posts: { id: number; title: string }[];
}
Query
-- 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
Params
{ limit: number }
Returns
{
  user_count: bigint;
  post_count: bigint;
  recent_posts: { id: number; title: string }[];
}
Query
-- 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
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:

Query
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
Params
void
Returns
{
  name: string;
  title: string | null;
  published_at: string | null;
}[]
Query
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
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:

Query
insert into users (name)
values (:name::text)
returning id
Params
{ name: string }
Returns
{ id: number }
Query
insert into users (name)
values (:name::text)
returning id
Params
{ name: string }
Returns
{ id: number }

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

Query
insert into users (name, email)
values (:name, :email::text)
on conflict (email) do update
  set name = excluded.name
returning id, name
Params
{ name: string; email: string }
Returns
{ id: number; name: string }
Query
insert into users (name, email)
values (:name, :email::text)
on conflict (email) do update
  set name = excluded.name
returning id, name
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:

Query
update users
set email = :email::text
where id = :id
returning email
Params
{ email: string; id: number }
Returns
{ email: string } | null
Query
update users
set email = :email::text
where id = :id
returning email
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:

Query
Postgres only
update users
set name = :name
where id = :id
returning old.name as old_name, new.name as new_name
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:

Schema
create table events (
  id serial primary key,
  kind text not null check (kind in ('click', 'key'))
);
Query
Postgres only
select id, kind
from events
order by id desc
limit :limit::int
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:

Query
select 'user' as kind, id, name as label from users
union all
select 'post', id, title from posts
Params
void
Returns
{ kind: "user" | "post"; id: number; label: string }[]
Query
select 'user' as kind, id, name as label from users
union all
select 'post', id, title from posts
Params
void
Returns
{ kind: "user" | "post"; id: bigint; label: string }[]
Query
select 'user' as kind, id, name as label from users
union all
select 'post', id, title from posts
Params
void
Returns
{ kind: "user" | "post"; id: number; label: string }[]

JSON aggregation

Type information bubbles up through JSON aggregation:

Query
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
Params
{ since: string }
Returns
{ name: string; posts: { id: number; title: string }[] }[]
Query
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
Params
{ since: string }
Returns
{ name: string; posts: { id: number; title: string }[] }[]
Query
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
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:

Query
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
Params
{ id: number }
Returns
{
  id: number;
  name: string;
  posts: { id: number; title: string }[];
  latest_post: { id: number; title: string } | null;
} | null
Query
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
Params
{ id: number }
Returns
{
  id: number;
  name: string;
  posts: { id: number; title: string }[];
  latest_post: { id: number; title: string } | null;
} | null
Query
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
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. 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:

Query
Postgres only
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
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:

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))
);
Query
select id, payload
from events
where id = :id::int
Params
{ id: number }
Returns
{ id: number; payload: { x: number; y: number } } | null
Schema
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))
);
Query
select id, payload
from events
where id = :id::int
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:

Query
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"]
}'))
Params
{ payload: { x: number; y: number; button?: "left" | "right" } }
Returns
void

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

import  from "./record_click.sql";

// type-checks, but .run() rejects it: x must be an integer
await .(, { : { : 1.5, : 20 } });
import  from "./record_click.sql";

// type-checks, but .run() rejects it: x must be an integer
await .(, { : { : 1.5, : 20 } });
import  from "./record_click.sql";

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

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

Query
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"]
}'))
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:

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))
);
Query
Postgres only
insert into events (payload)
values (:payload)
Params
{ payload: { x: number; y: number } }
Returns
void

Filtering by a list

Pass the list as one parameter:

Query
select id, name from users where id = any(:ids::int[])
Params
{ ids: number[] }
Returns
{ id: number; name: string }[]
Query
-- 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
)
Params
{ ids: number[] }
Returns
{ id: number; name: string }[]
Query
-- 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)
Params
{ ids: number[] }
Returns
{ id: number; name: string }[]

Bulk inserts

Pass the rows as one JSON parameter: the schema in its cast types them, and Reylo checks every row before the statement runs:

Query
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
Params
{ users: { name: string; email?: string | null }[] }
Returns
{ id: number }[]
Query
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
Params
{ users: { name: string; email?: string | null }[] }
Returns
void
Query
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
Params
{ users: { name: string; email?: string | null }[] }
Returns
{ id: number }[]

Nested inserts

Users with their posts, in one parameter.

Postgres writes both tables in one statement:

Query
Postgres only
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)
Params
{
  users: {
    name: string;
    email: string;
    posts: { title: string }[];
  }[];
}
Returns
void

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

import  from "mysql2/promise";
import , { type Params } from "./insert_users.sql";
type Params = {
    users: {
        name: string;
        email: string;
    }[];
}
import from "./insert_posts.sql"; const = .(..); const = [ { : "Ada", : "ada@example.com", : [{ : "Notes" }], }, ]; const = await .(); try { await .(); await .(, { }); await .(, { }); await .(); } catch () { await .(); throw ; } finally { .(); }
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 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

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

import  from "better-sqlite3";
import , { type Params } from "./insert_users.sql";
type Params = {
    users: {
        name: string;
        email: string;
    }[];
}
import from "./insert_posts.sql"; const = new ("app.db"); const = [ { : "Ada", : "ada@example.com", : [{ : "Notes" }], }, ]; .(() => { .(, { }); .(, { }); })();
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
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. Give each member a key with one value, and it is a discriminated union, which TypeScript narrows on that key:

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",
    "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))
);
Query
select id, payload
from events
where id = :id::int
Params
{ id: number }
Returns
{
  id: number;
  payload:
    | { kind: "click"; x: number; y: number }
    | { kind: "key"; code: string };
} | null
Schema
-- 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))
);
Query
select id, payload
from events
where id = :id::int
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:

Query
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)
Params
{
  events: (
    | { kind: "click"; x: number; y: number }
    | { kind: "key"; code: string }
  )[];
}
Returns
void
Query
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
Params
{
  events: (
    | { kind: "click"; x: number; y: number }
    | { kind: "key"; code: string }
  )[];
}
Returns
void
Query
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
Params
{
  events: (
    | { kind: "click"; x: number; y: number }
    | { kind: "key"; code: string }
  )[];
}
Returns
void
Released under the MIT License. Copyright © 2026 Colin McDonnell.

On this page