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:
-- 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": []
}| 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 |
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/, 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 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 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 |
| 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 types it |
BINARY, VARBINARY, BLOB, BIT wider than 1 | Uint8Array |
VECTOR | number[] |
| 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 |
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:
update users
set name = :name
where id = :id{ name: string; id: number }voidCasts
Where the query doesn't pin down a parameter's type, cast it. Reylo compiles the cast to each database's own:
select id, name
from users
where name like concat('%', :q::text, '%'){ q: string }{ 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 nA 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
select id, name
from users
where role = :role{ role: "admin" | "member" }{ id: number; name: string }[]select id, name
from users
where role = :role{ role: "admin" | "member" }{ id: number; name: string }[]select id, name
from users
where role = :role{ role: string }{ id: number; name: string }[]Left joins
A left join makes the joined table's columns nullable:
select u.name, p.title
from users u
left join posts p on p.user_id = u.id
where u.id = :user_id::int{ user_id: number }{ name: string; title: string | null }[]A test in the where that fails on null, like a comparison, makes the whole joined row non-null:
select u.name, p.title
from users u
left join posts p on p.user_id = u.id
where p.published_at > :since{ since: string }{ name: string; title: string }[]Computed columns
Reylo infers which computed columns can never be null:
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{ role: "admin" | "member" }{ name: string; email: string; has_posts: boolean }[]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{ role: "admin" | "member" }{ name: string; email: string; has_posts: boolean }[]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{ role: string }{ name: string; email: string; has_posts: boolean }[]Nullable parameters
To accept null, cast a parameter to nullable(…). Leaving it out passes null:
select id, title
from posts
where user_id = coalesce(:user_id::nullable(int), user_id){ user_id?: number | null }{ 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:
-- 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{ sort: string }{ 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:
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{
filters: {
q?: string;
author_id?: number;
status?: "published" | "draft" | "all";
sort?: "newest" | "oldest" | "title";
};
}{ id: number; title: string; author: string }[]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{
filters: {
q?: string;
author_id?: number;
status?: "published" | "draft" | "all";
sort?: "newest" | "oldest" | "title";
};
}{ id: number; title: string; author: string }[]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{
filters: {
q?: string;
author_id?: number;
status?: "published" | "draft" | "all";
sort?: "newest" | "oldest" | "title";
};
}{ 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 fromjsonb_to_recordreads the whole table under every plan. - MySQL plans each run with its values. The same filter read from
json_tablereads 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:
create table users (
id serial primary key,
name text not null,
email text unique
);select id, name
from users
where email = :email::text{ email: string }{ id: number; name: string } | nullcreate table users (
id int auto_increment primary key,
name text not null,
email varchar(255) unique
);select id, name
from users
where email = :email::text{ email: string }{ id: number; name: string } | nullcreate table users (
id integer primary key,
name text not null,
email text unique
) strict;select id, name
from users
where email = :email::text{ email: string }{ id: number; name: string } | nullWhich 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
distinctorgroup byover values thewherepins - a subquery, a
withquery or a view that keeps a key
Also a key match:
- a join on the joined table's numeric key
- a
distinctorgroup byover values thewherepins - a derived table, a
withquery 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
distinctorgroup byover values thewherepins - a subquery, a
withquery 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:
select count(*)::int as posts
from posts
where user_id = :user_id::int{ user_id: number }{ posts: number }select count(*) as posts
from posts
where user_id = :user_id::int{ user_id: number }{ posts: bigint }select count(*) as posts
from posts
where user_id = :user_id::int{ user_id: number }{ posts: number }A rollup adds a row for the total, whose grouped column is null:
select user_id, count(*)::int as posts
from posts
group by rollup (user_id)
order by user_idvoid{ user_id: number | null; posts: number }[]select user_id, count(*) as posts
from posts
group by user_id with rollup
order by user_idvoid{ user_id: bigint | null; posts: bigint }[]Single values
A column with no name returns its values instead of rows:
select count(*)::int
from posts
where user_id = :user_id::int{ user_id: number }numberselect count(*)
from posts
where user_id = :user_id::int{ user_id: number }bigintselect count(*)
from posts
where user_id = :user_id::int{ user_id: number }numberAn 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,
nullstands 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 withas.
Window functions
Reylo types each window function:
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 postsvoid{ id: number; title: string; n: bigint; prev: string | null }[]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 postsvoid{ id: number; title: string; n: bigint; prev: string | null }[]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 postsvoid{ 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:
-- 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{ limit: number }{
user_count: number;
post_count: number;
recent_posts: { id: number; title: string }[];
}-- 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{ limit: number }{
user_count: bigint;
post_count: bigint;
recent_posts: { id: number; title: string }[];
}-- 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{ limit: number }{
user_count: number;
post_count: number;
recent_posts: { id: number; title: string }[];
}A lateral join runs its subquery once per row:
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 truevoid{
name: string;
title: string | null;
published_at: string | null;
}[]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 truevoid{
name: string;
title: string | null;
published_at: string | null;
}[]Returning rows
An insert that returns its row resolves to exactly that row:
insert into users (name)
values (:name::text)
returning id{ name: string }{ id: number }insert into users (name)
values (:name::text)
returning id{ name: string }{ id: number }So does an upsert, whether it inserts the row or updates it:
insert into users (name, email)
values (:name, :email::text)
on conflict (email) do update
set name = excluded.name
returning id, name{ name: string; email: string }{ id: number; name: string }insert into users (name, email)
values (:name, :email::text)
on conflict (email) do update
set name = excluded.name
returning id, name{ name: string; email: string }{ 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:
update users
set email = :email::text
where id = :id
returning email{ email: string; id: number }{ email: string } | nullupdate users
set email = :email::text
where id = :id
returning email{ email: string; id: number }{ email: string } | nullOn Postgres 18, an update can return its values from before and after, as old and new:
update users
set name = :name
where id = :id
returning old.name as old_name, new.name as new_name{ name: string; id: number }{ old_name: string; new_name: string } | nullEnums
A text column whose check constraint lists its values is a union of them, as a Postgres enum is:
create table events (
id serial primary key,
kind text not null check (kind in ('click', 'key'))
);select id, kind
from events
order by id desc
limit :limit::int{ limit: number }{ id: number; kind: "click" | "key" }[]So is a column the query fills with constants, each arm of a union all adding its own:
select 'user' as kind, id, name as label from users
union all
select 'post', id, title from postsvoid{ kind: "user" | "post"; id: number; label: string }[]select 'user' as kind, id, name as label from users
union all
select 'post', id, title from postsvoid{ kind: "user" | "post"; id: bigint; label: string }[]select 'user' as kind, id, name as label from users
union all
select 'post', id, title from postsvoid{ kind: "user" | "post"; id: number; label: string }[]JSON aggregation
Type information bubbles up through JSON aggregation:
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{ since: string }{ name: string; posts: { id: number; title: string }[] }[]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{ since: string }{ name: string; posts: { id: number; title: string }[] }[]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{ since: string }{ 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:
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{ id: number }{
id: number;
name: string;
posts: { id: number; title: string }[];
latest_post: { id: number; title: string } | null;
} | nullselect 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{ id: number }{
id: number;
name: string;
posts: { id: number; title: string }[];
latest_post: { id: number; title: string } | null;
} | nullselect 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{ id: number }{
id: number;
name: string;
posts: { id: number; title: string }[];
latest_post: { id: number; title: string } | null;
} | nullOn 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:
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 uvoid{ 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:
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))
);select id, payload
from events
where id = :id::int{ id: number }{ id: number; payload: { x: number; y: number } } | nullcreate 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))
);select id, payload
from events
where id = :id::int{ id: number }{ id: number; payload: { x: number; y: number } } | nullJSON parameters
A JSON parameter declares its schema in its cast to json or jsonb, and Reylo infers the parameter's type from it:
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"]
}')){ payload: { x: number; y: number; button?: "left" | "right" } }voidReylo 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:
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"]
}')){
order: {
customer: string;
items: { sku: string; qty: number }[];
shipping?: "standard" | "express";
};
}voidA JSON parameter written to a schema-checked column takes that schema as its type, with no cast:
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))
);insert into events (payload)
values (:payload){ payload: { x: number; y: number } }voidFiltering by a list
Pass the list as one parameter:
select id, name from users where id = any(:ids::int[]){ ids: number[] }{ id: number; name: string }[]-- 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
){ ids: number[] }{ id: number; name: string }[]-- 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){ ids: number[] }{ 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:
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{ users: { name: string; email?: string | null }[] }{ id: number }[]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{ users: { name: string; email?: string | null }[] }voidwith 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{ users: { name: string; email?: string | null }[] }{ id: number }[]Nested inserts
Users with their posts, in one parameter.
Postgres writes both tables in one statement:
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){
users: {
name: string;
email: string;
posts: { title: string }[];
}[];
}voidOn 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";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 uinsert 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 nullOn 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";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 uwith 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 pDiscriminated 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:
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))
);select id, payload
from events
where id = :id::int{ id: number }{
id: number;
payload:
| { kind: "click"; x: number; y: number }
| { kind: "key"; code: string };
} | null-- 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))
);select id, payload
from events
where id = :id::int{ id: number }{
id: number;
payload:
| { kind: "click"; x: number; y: number }
| { kind: "key"; code: string };
} | nullA parameter's schema works the same way:
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){
events: (
| { kind: "click"; x: number; y: number }
| { kind: "key"; code: string }
)[];
}voidinsert 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{
events: (
| { kind: "click"; x: number; y: number }
| { kind: "key"; code: string }
)[];
}voidwith 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{
events: (
| { kind: "click"; x: number; y: number }
| { kind: "key"; code: string }
)[];
}void