Reylo
Import .sql files into your TypeScript code with full type inference
If you have a file like this:
select u.id, u.name, count(p.id)::int as post_count
from users u
left join posts p on p.user_id = u.id
where u.id = :id::int
group by u.idYou can import it directly from a TypeScript file like this:
import postgres from "postgres";
import from "./get_author.sql";
const db = postgres(process.env.DATABASE_URL);
// hover over author to see its type
const = await .run(db, { id: 1 });View the schema
create extension if not exists pg_jsonschema;
create type user_role as enum ('admin', 'member');
create table users (
id serial primary key, -- at most one row per id
name text not null, -- name: string
email text unique, -- email: string | null
role user_role not null default 'member', -- role: "admin" | "member"
tags text[] not null default '{}', -- tags: (string | null)[]
created_at timestamptz -- created_at: string
not null default now()
);
create table posts (
id serial primary key,
user_id integer not null references users (id),
title text not null,
body text,
published_at timestamptz
);
create table events (
id serial primary key,
kind text not null
check (kind in ('click', 'key')), -- kind: "click" | "key"
payload jsonb not null -- payload: what the schema admits
check (jsonb_matches_schema('{
"type": "object",
"properties": {
"x": { "type": "integer" },
"y": { "type": "integer" },
"code": { "type": "string" }
}
}', payload))
);create table users (
id int auto_increment primary key, -- at most one row per id
name text not null, -- name: string
email varchar(255) unique, -- email: string | null
role enum('admin', 'member') -- role: string
not null default 'member',
tags json not null default (json_array()), -- tags: unknown
created_at datetime -- created_at: string, UTC
not null default (utc_timestamp())
);
create table posts (
id int auto_increment primary key,
user_id int not null,
title text not null,
body text,
published_at datetime,
foreign key (user_id) references users (id)
);
create table events (
id int auto_increment primary key,
kind text not null,
payload json not null -- payload: what the schema admits
check (json_schema_valid('{
"type": "object",
"properties": {
"x": { "type": "integer" },
"y": { "type": "integer" },
"code": { "type": "string" }
}
}', payload))
);create table users (
id integer primary key, -- at most one row per id
name text not null, -- name: string
email text unique, -- email: string | null
role text not null default 'member' -- role: string
check (role in ('admin', 'member')),
tags text not null default '[]', -- tags: string, JSON text
created_at text -- created_at: string, UTC
not null default (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) strict;
create table posts (
id integer primary key,
user_id integer not null references users (id),
title text not null,
body text,
published_at text
) strict;
create table events (
id integer primary key,
kind text not null,
payload text not null
) strict;Reylo is the perfect option for the agent era:
- 💪 The full power of SQL — any statement, function, operator or syntax your database supports, in its own dialect.
- 🌵 DRY — the types come from the SQL itself: no hand-written row types, no schema file to keep in sync.
- 🎯 Checked against your database — a misspelled column, a wrong parameter or a dropped table is a type error, in
tscand in your editor. - 🤖 Agent-friendly — each fact lives in one place, so an agent cannot leave a stale type behind, and the type check finds every caller it missed.
- 🧩 Postgres, MySQL and SQLite — bring your own driver:
pg, postgres.js,mysql2,better-sqlite3,Bun.sql, Cloudflare D1 and more. - ⚡ No codegen — the type checker asks Reylo directly: no generated files, no watcher.
Setup
To install Reylo, you'll need:
- TypeScript 7.1 (currently in beta). The first release with content mappers.
- Node 24 or later.
- A running dev database. Reylo reads your current schema from it (Database points Reylo at it):
- Postgres 14 to 18, including Neon and PGlite
- MySQL 8.0.22 or later, including PlanetScale
- SQLite, including Cloudflare D1 and Durable Objects, Turso and libSQL
Then set Reylo up in six steps:
Install
Install Reylo, the engine for your database and TypeScript 7.1:
npm install reylo
npm install -D @reylosql/postgres # or mysql, sqlite
npm install -D typescript@betaTypeScript
Add Reylo to tsconfig.json, for .sql files and for its project file, .reylo.yaml or .reylo.yml:
{
"compilerOptions": { /* ... */ },
"contentMappers": [
{ "package": "reylo", "extensions": [".sql", ".reylo.yaml", ".reylo.yml"] }
]
}Then type-check with --runExternalCode, which lets tsc run content mappers:
{
"scripts": {
"typecheck": "tsc --noEmit --runExternalCode"
}
}Runtime / framework
Install the plugin for your runtime or framework of choice:
Load Reylo's preload with --import:
node --env-file=.env --import reylo/register main.tsAdd the preload to bunfig.toml, and under [test] for bun test:
preload = ["reylo/register"]
[test]
preload = ["reylo/register"]For Bun's bundler, use the esbuild plugin:
import reylo from "reylo/esbuild";
await Bun.build({
entrypoints: ["main.ts"],
target: "bun",
plugins: [reylo()],
outdir: "dist",
});Add the preload to nub.jsonc:
{
"preload": ["reylo/register"]
}Add Reylo's plugin to your Vite config:
import { defineConfig } from "vite";
import reylo from "reylo/vite";
export default defineConfig({
plugins: [reylo()],
});Import .sql files from server code only, such as a .server/ directory: Vite bundles a query wherever it is imported, the browser included. For Vitest, this plugin is all you need.
Wrap your Next.js config with withReylo:
import type { NextConfig } from "next";
import { withReylo } from "reylo/next";
const nextConfig: NextConfig = {
// ...
};
export default withReylo(nextConfig);Your server code can now import .sql files, with Turbopack or webpack, and next build fails on a type error in a query or its caller.
To type-check without building, run next typegen first, which writes the route types:
{
"scripts": {
"typecheck": "next typegen && tsc --noEmit --runExternalCode"
}
}How the build checks the types
Next.js type-checks with your project's tsc but without --runExternalCode, so withReylo turns that check off and runs tsc --noEmit --runExternalCode itself, after the compile and before the prerender. Next.js still prints "Skipping validation of types" for its own check.
Add Reylo's plugin to your esbuild build:
import * as esbuild from "esbuild";
import reylo from "reylo/esbuild";
await esbuild.build({
entryPoints: ["main.ts"],
bundle: true,
platform: "node",
format: "esm",
plugins: [reylo()],
outfile: "dist/main.mjs",
});Load .sql files with Reylo's loader in your webpack config:
export default {
// ...
module: {
rules: [{ test: /\.sql$/, use: "reylo/webpack" }],
},
};Compile TypeScript with swc-loader or babel-loader, since TypeScript 7 has no JavaScript API for ts-loader.
Load .sql files with Reylo's webpack loader in your Rspack config:
export default {
// ...
module: {
rules: [{ test: /\.sql$/, use: "reylo/webpack" }],
},
};Add Reylo's Vite plugin beside Cloudflare's:
import { cloudflare } from "@cloudflare/vite-plugin";
import { defineConfig } from "vite";
import reylo from "reylo/vite";
export default defineConfig({
plugins: [reylo(), cloudflare()],
});Pass the D1 binding as the client, as in getUser.run(env.DB, { id: 1 }); you don't need nodejs_compat. For the type check, set direct_url in .reylo.yaml to the local D1 file that wrangler d1 migrations apply --local creates. Don't set it in .env, which Wrangler loads into the Worker as secrets.
Add Reylo's transformer to your Jest config:
export default {
transform: { "\\.sql$": "reylo/jest" },
extensionsToTreatAsEsm: [".sql"],
};# Jest runs ES modules only behind this Node flag
NODE_OPTIONS=--experimental-vm-modules npx jestFor a complete, working project, copy the example for your setup. Its README, under "How Reylo is wired in", names every line Reylo needs:
Database
Reylo requires a connection to a live dev database. It retrieves the types of each compiled query, its parameters and its columns, from the database itself. Reylo finds your dev database through the DATABASE_URL environment variable, and the URL's scheme tells it which database you use:
# or postgresql://
DATABASE_URL=postgres://localhost:5432/app# or mariadb://
DATABASE_URL=mysql://root@localhost:3306/app# or sqlite:; the path starts at package.json
DATABASE_URL=file:./app.dbTo override this, create a .reylo.yaml file in your project root and set the direct_url field:
direct_url: postgres://localhost:5432/appYou can also reference other environment variables via string interpolation:
direct_url: ${APP_DATABASE_URL}Or specify a set of .env files to load:
# the default is [.env.local, .env]
env: [.env.development, .env]Editor
Set up your editor to use the TypeScript 7.1 language server, with content mappers on. Then a hover over a query shows its types, its SQL and the comment at the top of its .sql file, an error in a .sql file is underlined where it is, and go-to-definition on a query opens its .sql file.
-
Install the TypeScript 7 extension (
TypeScriptTeam.native-preview). -
Point it at the project's TypeScript: the extension's own copy is TypeScript 7.0, which has no content mappers.
.vscode/settings.json { "js/ts.experimental.useTsgo": true, "js/ts.tsdk.path": "./node_modules/typescript" } -
Trust the workspace, and click Allow when the extension asks to use the workspace's TypeScript. Content mappers run in a trusted workspace only. Missed it? It waits under the bell in the status bar, or Developer: Reload Window asks again.
Keep DATABASE_URL in .env: the editor doesn't see your shell's environment.
-
Install the TypeScript 7 extension (
TypeScriptTeam.native-preview). -
Point it at the project's TypeScript: the extension's own copy is TypeScript 7.0, which has no content mappers.
.vscode/settings.json { "js/ts.experimental.useTsgo": true, "js/ts.tsdk.path": "./node_modules/typescript" } -
Trust the workspace, and click Allow when the extension asks to use the workspace's TypeScript. Content mappers run in a trusted workspace only. Missed it? It waits under the bell in the status bar, or Developer: Reload Window asks again.
Keep DATABASE_URL in .env: the editor doesn't see your shell's environment.
-
Install the TypeScript Language Server extension by Zed Industries (search for "tsgo").
-
Point it at the project's TypeScript, with content mappers on:
.zed/settings.json { "languages": { "TypeScript": { "language_servers": [ "typescript-ls", "!vtsls", "!typescript-language-server", "..." ] }, "TSX": { "language_servers": [ "typescript-ls", "!vtsls", "!typescript-language-server", "..." ] } }, "lsp": { "typescript-ls": { // the extension's own download is TypeScript 7.0, which has no content mappers "binary": { "path": "node_modules/typescript/bin/tsc", "arguments": ["--lsp", "--stdio"] }, "initialization_options": { "runExternalCode": true } } } } -
Trust the folder. In restricted mode, Zed starts no language server.
Zed doesn't send .sql files to the language server, so an error inside one shows only in the type check.
Skill
Install Reylo's agent skill, which gives your coding agent Reylo's rules for writing and running queries in every session:
npx skills add colinhacks/reylo --skill using-reyloUsage
Write a query in a .sql file, import it, and run it with your database driver. The query is your database's own SQL: any function, operator or syntax it supports works. Reylo uses your live dev database to compile the query and retrieve its types.
import from "postgres";
import from "./get_user.sql";
const = (..);
const = await .(, { : 1 });select id, name, email from users where id = :id::intPass your database client as the first argument of .run(): a pool, a client, or a connection or transaction taken from one, from any of the supported drivers.
.sql
To get the compiled query, as Reylo sends it to your database:
import getUser from "./get_user.sql";
getUser.sql; // "select id, name, email from users where id = $1::int"To get the file's contents as written, before Reylo compiles its parameters, use .raw. Its path, relative to your project, is .source, a name for your logs and traces; Reylo's own errors about the query start with it.
.run()
To run an imported Reylo query, pass a database connection/pool into the .run() method along with any parameters:
import from "postgres";
import from "./get_user.sql";
const = (..);
const user = await .(, { : 1 });Reylo has no driver of its own: it runs each query on your app's database client, from any of these drivers.
Postgres
import from "pg";
import from "./get_user.sql";
const = new .({ : .. });
const = await .(, { : 1 });import from "postgres";
import from "./get_user.sql";
const = (..);
const = await .(, { : 1 });import { } from "bun";
import from "./get_user.sql";
const = new (..);
const = await .(, { : 1 });import { } from "@electric-sql/pglite";
import from "./get_user.sql";
const = new ("./pgdata");
const = await .(, { : 1 });import { } from "@neondatabase/serverless";
import from "./get_user.sql";
const = (..);
const = await .(, { : 1 });import from "pg-promise";
import from "./get_user.sql";
const = ();
const = (..);
const = await .(, { : 1 });import { } from "slonik";
import from "./get_user.sql";
const = await (..!);
const = await .(, { : 1 });// deprecated on npm; type-checked against Reylo, not run in its tests
import { } from "@vercel/postgres";
import from "./get_user.sql";
const = await .(, { : 1 });import { } from "drizzle-orm/node-postgres";
import from "./get_user.sql";
const = (..!);
const = await .(, { : 1 });Transactions are also supported.
const = await .();
try {
await .("begin");
await .(, { : 1 });
await .("commit");
} catch () {
await .("rollback");
throw ;
} finally {
.();
}await .(() => .(, { : 1 }));await .(() => .(, { : 1 }));await .(() => .(, { : 1 }));import { } from "@neondatabase/serverless";
// a transaction takes a Pool, over a WebSocket
const = new ({ : .. });
const = await .();
try {
await .("begin");
await .(, { : 1 });
await .("commit");
} catch () {
await .("rollback");
throw ;
} finally {
.();
}await .(() => .(, { : 1 }));await .(() => .(, { : 1 }));const = await .();
try {
await .("begin");
await .(, { : 1 });
await .("commit");
} catch () {
await .("rollback");
throw ;
} finally {
.();
}await .(() => .(, { : 1 }));MySQL
import from "mysql2/promise";
import from "./get_user.sql";
const = .(..);
const = await .(, { : 1 });// mariadb takes only a mariadb:// URL, which Reylo reads as MySQL
import from "mariadb";
import from "./get_user.sql";
const = .(..);
const = await .(, { : 1 });// @planetscale/database 1.x: a 2.0 client is a type error
import { } from "@planetscale/database";
import from "./get_user.sql";
const = ({ : .. });
const = await .(, { : 1 });import { } from "bun";
import from "./get_user.sql";
const = new (..);
const = await .(, { : 1 });import { } from "drizzle-orm/mysql2";
import from "./get_user.sql";
const = (..!);
const = await .(, { : 1 });Transactions are also supported.
const = await .();
try {
await .();
await .(, { : 1 });
await .();
} catch () {
await .();
throw ;
} finally {
.();
}const = await .();
try {
await .();
await .(, { : 1 });
await .();
} catch () {
await .();
throw ;
} finally {
.();
}await .(() => .(, { : 1 }));await .(() => .(, { : 1 }));await .(() => .(, { : 1 }));SQLite
import from "better-sqlite3";
import from "./get_user.sql";
const = new ("app.db");
const = await .(, { : 1 });import { } from "node:sqlite";
import from "./get_user.sql";
const = new ("app.db");
const = await .(, { : 1 });import { } from "bun:sqlite";
import from "./get_user.sql";
const = new ("app.db");
const = await .(, { : 1 });import { } from "bun";
import from "./get_user.sql";
const = new ({ : "sqlite", : "app.db" });
const = await .(, { : 1 });import { } from "@libsql/client";
import from "./get_user.sql";
const = ({ : "file:app.db" });
const = await .(, { : 1 });import { } from "@tursodatabase/database";
import from "./get_user.sql";
const = await ("app.db");
const = await .(, { : 1 });import type { } from "@cloudflare/workers-types";
import from "./get_user.sql";
export default {
async (: Request, : { : }) {
const = await .(., { : 1 });
return .();
},
};import { } from "cloudflare:workers";
import from "./get_user.sql";
export class extends {
(: number) {
return .(this..., { });
}
}import from "sqlite3";
import from "./get_user.sql";
const = new .("app.db");
const = await .(, { : 1 });import { } from "sqlite";
import from "sqlite3";
import from "./get_user.sql";
const = await ({ : "app.db", : . });
const = await .(, { : 1 });import { } from "drizzle-orm/better-sqlite3";
import from "./get_user.sql";
const = ("app.db");
const = await .(, { : 1 });Transactions are also supported.
// the function must not await, so it runs .runSync()
.(() => .(, { : 1 }))();.("begin");
try {
.(, { : 1 });
.("commit");
} catch () {
.("rollback");
throw ;
}// the function must not await, so it runs .runSync()
.(() => .(, { : 1 }))();await .(() => .(, { : 1 }));const = await .("write");
try {
await .(, { : 1 });
await .();
} finally {
.();
}await .(() => .(, { : 1 }))();export class extends {
(: number) {
return this...(() =>
.(this..., { }),
);
}
}import { } from "node:util";
const = (..());
await ("begin");
try {
await .(, { : 1 });
await ("commit");
} catch () {
await ("rollback");
throw ;
}await .("begin");
try {
await .(, { : 1 });
await .("commit");
} catch () {
await .("rollback");
throw ;
}.(() => .(, { : 1 }));D1 has no transaction a query can join: its batch() takes D1's own prepared statements.
.runSync()
For databases that support synchronous operations, you can use .runSync().
import from "better-sqlite3";
import from "./get_user.sql";
const = new ("app.db");
const user = .(, { : 1 });.try()
To get your database's errors as values, use .try(). It returns { value }, or { error } when the database refuses the query:
import from "./create_user.sql";
const = await .(, {
: "Ada",
: "ada@example.com",
});
if (.?. === "unique_violation") {
..constraint;}import from "./create_user.sql";
const = await .(, {
: "Ada",
: "ada@example.com",
});
if (.?. === "ER_DUP_ENTRY") {
..key;}import from "./create_user.sql";
const = await .(, {
: "Ada",
: "ada@example.com",
});
if (.?. === "SQLITE_CONSTRAINT_UNIQUE") {
..columns;}insert into users (name, email) values (:name, :email)The error is your database's own, typed with every code it defines, so TypeScript catches a misspelled one:
| Database | Its code | What else it names |
|---|---|---|
| Postgres | code, the SQLSTATE, and condition, its name | constraint, table, column, schema, detail, hint, position |
| MySQL | code, the error's name, errno and sqlState | key, constraint, table, column |
| SQLite | code, the result code's name, and errcode | table, columns, constraint |
When the query runs none of your own code, such as a trigger or a function you wrote, Reylo also narrows each error to the names it can carry: a unique violation names one of the unique keys of the tables the query writes, as in the examples above. Your database's page says what it narrows.
Each driver's error is read into the same fields, and the driver's own error is the cause. Any other error still throws, as from .run(): a refused connection, say. The file exports the error's type as QueryError. On a synchronous SQLite driver, .trySync() returns the result itself.
On Postgres, an error aborts the transaction it is in, even an error .try() returned: every later statement in it fails, and the transaction rolls back. MySQL and SQLite keep it open after most errors. The Postgres page shows how to carry on after an error.
.with()
To bind the query to a client, call .with(). It returns a function that takes the parameters:
import from "./get_user.sql";
const = .();
const = await ({ : 1 });import from "./get_user.sql";
const = .();
const = await ({ : 1 });import from "./get_user.sql";
const = .();
const = await ({ : 1 });Tracing
Reylo publishes every query it runs on Node's tracing channel reylo:query, so your tracer or logger can wrap each one in a span:
import { } from "node:diagnostics_channel";
import type { } from "reylo";
const = < & { : }>("reylo:query");
.({
() {
. = .("db.query", {
: {
"db.query.summary": ..,
"db.query.text": ..,
},
});
},
() {
..(.);
},
() {
if (.) ..();
},
() {
..();
},
});Each event carries the query, its params, the client, and then its result or error. Calls to .runSync() and .trySync() set sync and end on end; every other call ends on asyncEnd.
The channel is there on Node, on Bun, and on Cloudflare Workers with the nodejs_compat flag. Elsewhere, queries run untraced.
Inference
Reylo lets you use the full power of SQL provided by your database. Postgres, MySQL and SQLite are all supported.
Reylo is a content mapper, the kind of plugin TypeScript 7.1 introduced for files that aren't TypeScript. Whenever the type checker meets a .sql import, in tsc or in your editor, it hands Reylo the file and takes back the module's types.
To type a query, Reylo has your live database prepare it, without running it, and reads the catalog. From the answer, it learns:
- Parameter types — from where each parameter is used, or its cast
- Column types — from the table, or the expression that computes the column
- Nullability — from
NOT NULLand outer joins - Cardinality — from keys, aggregates,
limit 1andreturning - Column comments — on Postgres and MySQL, shown when you hover a field that reads the column
The TypeScript type of each SQL type, in each dialect
Reylo decodes its own queries' values itself, so every driver gives the same types. 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.
| 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 | typed by its schema or the query |
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 | string |
pgvector's vector, halfvec | number[] |
hstore | Record<string, string | null> |
Every other type (inet, cidr, ranges, point, 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 | string |
SET | string, as a,b |
DATE | string, as 2026-10-20 |
DATETIME, TIMESTAMP | string, as 2026-10-20T10:00:00.123 |
TIME | string |
JSON | typed by its schema or the query |
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.
At run time, the preload or a bundler plugin turns the same file into a module that runs the query through your driver.
Reylo caches each answer in .reylo/, keyed by a fingerprint of the schema, so after a migration the next type check sees the new schema: a dropped column fails in the .sql file that names it, and in the code that reads it.
Where each type comes from, in each dialect
Reylo sends the query to the live database prepared and reads back the database's own type information on its parameters and return type. This information is cross-referenced against the schema catalog, enabling high-fidelity type inference for any query.
| What | Where it comes from |
|---|---|
| Parameter types | the server's description of the prepared statement |
| Column types | the server's description; an enum as its labels, a domain as its base type, a JSON column as what its JSON Schema CHECK admits |
| Nullability | the catalog's NOT NULL, outer joins, the expressions |
| Cardinality | primary and unique keys, aggregates, limit 1, returning |
| Nested JSON | built with jsonb_build_object, jsonb_agg or to_jsonb of a row, typed from their arguments |
Reylo sends the query to the live database prepared and reads back the database's own type information on its parameters and return type.
Reylo adds which parameters may be null, the row count, and the shape of JSON the query builds.
| What | Where it comes from |
|---|---|
| Parameter types | the prepared statement's metadata; whether one may be null, from the statement, as on Postgres |
| Column types | the prepared statement's metadata; a JSON column read from its table as what its JSON Schema CHECK admits |
| Nullability | the server's NOT NULL flag, then what the where and group by rule out |
| Cardinality | primary and unique keys, aggregates; a write is void |
| Nested JSON | built with json_object and json_arrayagg, typed from their arguments |
Reylo reads the schema from the database file and prepares the query in its own copy of SQLite, which reports where each column and parameter comes from.
| What | Where it comes from |
|---|---|
| Parameter types | where SQLite places the parameter: a comparison with a column, an element of a row value, an insert or update column, a cast, limit and offset |
| Column types | the declared type's affinity |
| Nullability | the table's NOT NULL, outer joins, Reylo's table of SQLite's built-in functions |
| Cardinality | unique indexes and integer primary key |
| Nested JSON | built with json_object and json_group_array, typed from their arguments |
Column types
Each column comes back with its type:
select id, name
from users{ id: number; name: string }[]select id, name
from users{ id: number; name: string }[]select id, name
from users{ id: number; name: string }[]Named parameters
For convenience, there is some Reylo-specific syntax that can be used inside your .sql files. This syntax is transpiled into the idiomatic form for your database's dialect.
Name a parameter with a colon, and Reylo infers its type from where it's used:
update users
set name = :name
where id = :id{ name: string; id: number }voidupdate users
set name = :name
where id = :id{ name: string; id: number }voidupdate 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 }[]select id, name
from users
where name like concat('%', :q::text, '%'){ q: string }{ id: number; name: string }[]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 strict_params: false 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.
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{ name: string; title: string | null }[]select u.name, p.title
from users u
left join posts p on p.user_id = u.id{ name: string; title: string | null }[]select u.name, p.title
from users u
left join posts p on p.user_id = u.id{ name: string; title: string | null }[]A null check on one joined column 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 is not null{ name: string; title: string }[]select u.name, p.title
from users u
left join posts p on p.user_id = u.id
where p.published_at is not null{ name: string; title: string }[]select u.name, p.title
from users u
left join posts p on p.user_id = u.id
where p.published_at is not null{ 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{ 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{ 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{ 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 }[]select id, title
from posts
where user_id = coalesce(:user_id::nullable(int), user_id){ user_id?: number | null }{ id: number; title: string }[]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 in insert into posts (published_at) values (: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.
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 }[]-- 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 }[]-- 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. Its schema says what each filter takes and what it defaults to, and Reylo checks it before the query runs:
with input as (
select * from jsonb_to_record(: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(q text, author_id int, status text, sort text)
)
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, f.author_id, f.status, f.sort
from json_table(: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
}'), '$' columns (
q varchar(100) path '$.q',
author_id int path '$.author_id',
status varchar(9) path '$.status',
sort varchar(6) path '$.sort'
)) 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 .() });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 }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 five latest posts
with recent as (select id, title from posts order by id desc limit 5)
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{
user_count: number;
post_count: number;
recent_posts: { id: number; title: string }[];
}-- the five latest posts
with recent as (select id, title from posts order by id desc limit 5)
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{
user_count: bigint;
post_count: bigint;
recent_posts: { id: number; title: string }[];
}-- the five latest posts
with recent as (select id, title from posts order by id desc limit 5)
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{
user_count: number;
post_count: number;
recent_posts: { id: number; title: string }[];
}Returning inserts
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 }Enums
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{ id: number; kind: "click" | "key" }[]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
group by u.id{ 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
group by u.id{ 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
group by u.id{ name: string; posts: { id: number; title: string }[] }[]Nested fetching
Fetch each row's related rows with an aggregated subquery: it collects them into JSON, and Reylo types every field inside it:
select u.id, u.name,
-- every post, as an array
(
select coalesce(jsonb_agg(jsonb_build_object(
'id', p.id,
'title', p.title
) order by p.id), '[]')
from posts p
where p.user_id = u.id
) as posts,
-- the latest post, or null
(
select jsonb_build_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{
id: number;
name: string;
posts: { id: number; title: string }[];
latest_post: { id: number; title: string } | null;
}[]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{
id: number;
name: string;
posts: { id: number; title: string }[];
latest_post: { id: number; title: string } | null;
}[]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{
id: number;
name: string;
posts: { id: number; title: string }[];
latest_post: { id: number; title: string } | null;
}[]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{ id: number; payload: { x: number; y: number } }[]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))
);select id, payload
from events{ id: number; payload: { x: number; y: number } }[]JSON 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" } }voidinsert 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" } }voidinsert 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";
};
}voidinsert 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";
};
}voidinsert 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. The posts find their user by email, a unique key.
Postgres writes both tables in one statement: a with inserts the users, and the statement inserts the posts:
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{
id: number;
payload: {
kind: "click";
x: number;
y: number;
} | { kind: "key"; code: string };
}[]-- 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{
id: number;
payload: {
kind: "click";
x: number;
y: number;
} | { kind: "key"; code: string };
}[]A 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 })[];
}voidConfiguration
Configure Reylo in a .reylo.yaml (or .reylo.yml) file beside package.json. Every key is optional:
# the dev database Reylo reads your schema from (the default)
direct_url: ${DATABASE_URL}
# the .env files Reylo reads (the default)
env: [.env.local, .env]
# an error for a parameter whose type cannot be inferred (the default)
strict_params: true
# a comment naming each query's file, for your database's monitoring (the default)
sqlcommenter: truedirect_url
The dev database Reylo reads your schema from. It defaults to DATABASE_URL, and its scheme names the dialect:
# or postgresql://
DATABASE_URL=postgres://localhost:5432/app# or mariadb://
DATABASE_URL=mysql://root@localhost:3306/app# or sqlite:; the path starts at package.json
DATABASE_URL=file:./app.dbPoint it at a direct connection, not a pooler: describing a query through a pooler in transaction mode, such as PgBouncer's, can fail. You can interpolate other variables from your environment to construct a connection string.
# the default is ${DATABASE_URL}
direct_url: postgres://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:5432/appTo keep a password out of every file, $(…) runs a command, such as a password manager's or a cloud's token command, and puts what it prints in its place. Reylo runs it once per editor session or build, and again when the database refuses the login, as when a token expires.
# or $(aws rds generate-db-auth-token …) for a short-lived token
direct_url: postgres://app:$(op read op://dev/db/password)@db:5432/appenv
The .env files Reylo reads, relative to .reylo.yaml. The first file that sets a variable wins, and the environment wins over every file.
# the default is [.env.local, .env]
env: [.env.development, .env]strict_params
A parameter whose type cannot be inferred is an error that says to cast it. Set strict_params: false to type it unknown instead.
# the default is true
strict_params: falsesqlcommenter
Each compiled query ends with a comment that names its file, in sqlcommenter's format, so database monitoring that reads it, such as Datadog's and PlanetScale's, groups your queries by file. Set sqlcommenter: false to leave it off.
# the default is true
sqlcommenter: falseselect id, name, email from users where id = $1::int /*file='queries%2Fget_user.sql'*/Errors
An error in a query shows at its line and column, in your editor as you type and in tsc. Your database checks each query, so a misspelled column is the database's own error:
select id, nmae, emailfrom users
where id = :id::intselect id, nmae, email
from users
where id = :id::intselect id, nmae, emailfrom users
where id = :id::intReylo checks its own syntax too, such as a typed JSON parameter's schema:
MySQL gives no position for this error, so it covers the whole statement. Reylo checks its own syntax too, such as a typed JSON parameter's schema:
Reylo checks its own syntax too, such as a typed JSON parameter's schema:
insert into events (kind, payload)
values ('click', :payload::json('{ "type": object }'))Two columns of one name are an error, since each row is an object and keeps only the last:
select u.id, p.id, p.titlefrom users u
join posts p on p.user_id = u.idselect u.id, p.id, p.titlefrom users u
join posts p on p.user_id = u.idselect u.id, p.id, p.titlefrom users u
join posts p on p.user_id = u.idSorting or grouping by a parameter is an error, since a parameter is a value and every row would sort the same. To let the caller choose the order, see Dynamic sorting:
select id, name
from users
order by :sort::textselect id, name
from users
order by :sort::textselect id, name
from users
order by :sort::textA parameter whose type cannot be inferred is Reylo's error too, which says to cast it. When a query changes, its types change with it, and code that reads a column the query no longer selects is an error:
import from "./list_posts.sql";
const = await .();
for (const of ) .(.body);import from "./list_posts.sql";
const = await .();
for (const of ) .(.body);import from "./list_posts.sql";
const = await .();
for (const of ) .(.body);select id, title
from posts
order by idA wrong argument is an error where you call .run():
import from "./get_user.sql";
const = await .(, { id: "1" });import from "./get_user.sql";
const = await .(, { id: "1" });import from "./get_user.sql";
const = await .(, { id: "1" });The type check reports the same errors:
$ tsc --noEmit --runExternalCode
get_user.sql:1:12 - error reylo6: column "nmae" does not exist (42703)
1 select id, nmae, email
~~~~
Found 1 error in get_user.sql:1While a .sql file has an error, tsc holds back the TypeScript errors, so fix the SQL first.
FAQ
No. Reylo has your database prepare each query, which checks it and describes its parameters and columns without running it, and reads the rest from the catalog. On SQLite, Reylo prepares the query in its own copy of SQLite, from the schema in your database file.
Not at the moment: bring your own. Reylo types your queries against whatever schema your dev database has, so apply your migrations with the tool you already use, such as Prisma Migrate, Drizzle Kit, Atlas, dbmate or plain SQL files. After a migration, the next type check sees the new schema: a dropped column is an error in the .sql file that names it, and in the code that reads it.
Start the database, apply the migrations, and run tsc --noEmit --runExternalCode with DATABASE_URL set. Your tests need the database too.
It is Reylo's cache of each query's analysis. It has its own .gitignore, so git skips it: don't commit it.
No, not yet: Reylo reads your types from a live database, so a type check or a build needs one.
Reylo decodes its own queries' values itself, so every driver gives the same types. A 64-bit integer is a bigint, since a number loses precision past 2^53; a decimal is its exact text; and an instant is its ISO 8601 text in UTC. No value is a Date, which a driver reads in a time zone nobody chose. Every SQL type's TypeScript type is in Inference.