Releases

Reylo 0.7.0 runs its engine as one native program installed with reylo itself, adds DuckDB as a fourth dialect, and infers tighter types and cardinalities on Postgres, MySQL and SQLite.

Important

Seven changes can surface in an existing project:

The engine packages are no longer used: remove @reylosql/postgres, @reylosql/mysql and @reylosql/sqlite, since reylo now installs its engine (#152)
The engine runs only on eight platforms: macOS, Linux and Windows, on x64 or arm64 (#152)
A production install of reylo downloads its platform's engine, which the preload needs, so don't install with --omit=optional where it runs (#152)
A query's .sql no longer ends with the sqlcommenter comment, which Reylo still adds to the statement it sends (#135)
Postgres dates and timestamps before 1 AD or past 9999 take ISO 8601's extended year, '0002-06-01 BC'::date is -000001-06-01 (#153)
On Postgres, tags[:i] is a slice, with i read as a column: subscript by a parameter as tags[(:i)] (#145)
Types and cardinalities move: most narrow, and a few become nullable or many where the database can return NULL or more rows (#145, #146, #147)

Native engine

Reylo's engine is now one native program, reylo, built for each platform, instead of a WebAssembly component in a package per database. The reylo package lists the eight platform packages as optional dependencies, and the package manager installs the one that matches the machine. (#152)

  "dependencies": {
-   "reylo": "0.6.0"
+   "reylo": "0.7.0"
  },
  "devDependencies": {
-   "@reylosql/postgres": "0.6.0",
    "typescript": "7.1.0-beta"
  }

Platform	Package
macOS on Apple silicon	`@reylosql/engine-darwin-arm64`
macOS on Intel	`@reylosql/engine-darwin-x64`
Linux on x64, glibc	`@reylosql/engine-linux-x64`
Linux on x64, musl	`@reylosql/engine-linux-x64-musl`
Linux on arm64, glibc	`@reylosql/engine-linux-arm64`
Linux on arm64, musl	`@reylosql/engine-linux-arm64-musl`
Windows on x64	`@reylosql/engine-win32-x64`
Windows on arm64	`@reylosql/engine-win32-arm64`

The type check, reylo generate, a bundler plugin's build and the preload need one of these. A bundled app's .run() does not, since the build has already analyzed its queries.

Area	What changed	Link
Production	The preload `reylo/register` builds each query's module as it loads, so it needs the engine in production: keep `reylo` a dependency, not a dev dependency, and install without `--omit=optional`.	#152
`REYLO_ENGINE_PATH`	Names a `reylo` binary to use instead of the installed one.	#152
Missing engine	An install without the platform package, from `--omit=optional` or on another platform, gets an error that names the supported platforms and `REYLO_ENGINE_PATH`.	#152

Time to the first analysis of select 1 as x, from loading the engine, on macOS, three runs each:

Database	WebAssembly engine	Native engine
Postgres	230–274 ms	64–69 ms
MySQL	210–240 ms	47–56 ms
SQLite (`:memory:`)	184–234 ms	43–49 ms

A second analysis on Postgres takes 1.2–1.7 ms, down from 6–8 ms.

Package	Packed	Unpacked
`@reylosql/postgres@0.6.0`, one database	0.95 MB	3.66 MB
`@reylosql/engine-darwin-arm64@0.7.0`, all four databases	7.2 MB	15.5 MB

DuckDB

DuckDB is Reylo's fourth dialect (#142, fixes #30). A duckdb: URL names the database file, and .run() and .try() take a DuckDBConnection:

DATABASE_URL=duckdb:./app.duckdb

import { DuckDBInstance } from "@duckdb/node-api";
import getUser from "./get_user.sql";

const instance = await DuckDBInstance.create("app.duckdb");
const db = await instance.connect();

const user = await getUser.run(db, { id: 1 });

The type check prepares each statement on the project's own @duckdb/node-api, for every parameter's and column's exact type, and infers which columns are never NULL and the statement's cardinality from the statement and the catalog's NOT NULL columns and keys. It opens the file read-only for each analysis and closes it right after, and reads a copy of a file the app holds for writing (#144).

DuckDB type	TypeScript	The value `.run()` returns
`BIGINT`, `HUGEINT`	`bigint`	`9007199254740993n`
`DECIMAL(p, s)`	`string`	`"12345678901234567.89"`
`TIMESTAMPTZ`	`string`	`"2026-10-20T08:00:00Z"`
`INTERVAL`	`string`	`"P1Y2M3DT4H5M6.789S"`
`STRUCT(a T, …)`	`{ a: T \| null; … }`	`{ host: "h", pid: 7 }`
`MAP(K, V)`	`{ key: K; value: V \| null }[]`	`[{ key: "k", value: 1 }]`
`UNION(a T, …)`	`{ tag: "a"; value: T \| null } \| …`	`{ tag: "str", value: "s" }`

Placeholders are :name and DuckDB's own $1, ? and ?1. A parameter DuckDB cannot type, as in select :x, is an error at the parameter that says to cast it (#144). The DuckDB page has every rule.

Keys through set operations and column lists

A subquery, WITH query or view keeps more of its unique keys, so reading one by such a key infers one row at most. (#147)

-- SQLite: was many, now maybeOne
select * from (select id from posts union select author_id from posts) s where s.id = :id

Rule	Postgres	SQLite	MySQL
A set operation without ALL keys its whole row	yes	yes	yes
INTERSECT and EXCEPT keep their left side's keys	yes	yes	yes
A key column the subquery's WHERE pins to one value may be left out of its select list	yes	yes	yes
A column list, as in `with s (a, b) as (…)`, renames the keys by position	yes	positional already	yes, views included
A WITH query reads a sibling WITH query's keys	yes	inlined already	yes
A unique index on an expression the select list keeps under an alias	yes

Dates outside 0000–9999

A Postgres date or timestamp whose year is before 1 AD or past 9999 came back as Postgres's own text, which Temporal.PlainDate.from and Temporal.PlainDateTime.from refuse. It now takes the extended year a timestamptz already had: four digits for 0000–9999, else a sign and six digits, the year 0000 being 1 BC. Every driver returns the same text, and each value goes back in as a parameter. (#153)

Postgres value	0.6.0	0.7.0
`'0002-06-01 BC'::date`	`"0002-06-01 BC"`	`"-000001-06-01"`
`'12345-01-01'::date`	`"12345-01-01"`	`"+012345-01-01"`
`'0002-06-01 12:00:00 BC'::timestamp`	`"0002-06-01T12:00:00 BC"`	`"-000001-06-01T12:00:00"`

Postgres

Area	What changed	Link
Now nullable	Two set-returning calls in one select list, `row(a, b) = row(…)` with a nullable field, and `current_schema()` are nullable. They were typed non-null.	#145
Grouping sets	In `select 1 as one … group by rollup (one)`, a grouping key is nullable in the rows of a set that leaves it out. It was non-null.	#146
Set-returning GROUP BY	A query like `… where a = 'a' group by a, unnest(…)` is `many`, since the call splits the group. It was `maybeOne`.	#146
Narrowing	Columns read as `` and `t.` follow the WHERE's null tests, an inner join's ON narrows the columns it tests, and the WHERE of an UPDATE or DELETE narrows its RETURNING.	#145
WITH queries	A WITH query read twice is nulled only where an outer join nulls that reading, and a recursive one that uses `t.*`, SEARCH or CYCLE keeps its columns' types.	#145
Built-in functions	Built-ins and operators that are non-null on non-null input, such as `substring`, `format`, `in` and `between`, are typed that way.	#145
Set-returning functions	A set-returning function in FROM is typed by its rows: `generate_series`, `jsonb_each`, `WITH ORDINALITY` and `json_table`'s `for ordinality`.	#145
JSON builders	The builders `json_build_array`, `jsonb_build_array`, `JSON_ARRAY`, `JSON_OBJECT` and `array_to_json(array(…))` are typed.	#145
Writes	A write to a schema-qualified table, as `public.kv`, keeps its parameters' nullability, CHECKs and domains, and a parameter written to a column whose CHECK lists values is typed as that union.	#145
Pinned reads	A unique key compared with `lower(:x)` or `:id::int + 1`, or read through a view, is `maybeOne`, and `group by ()` with an aggregate is `one`.	#145
Slices	A colon right after a subscript's `[` start...

@colinhacks

Reylo 0.6.0 runs queries on Slonik, adds .try() for database errors as values and reylo play for a local playground, returns intervals, pgvector vectors and hstore columns in their natural JavaScript form, and pins far more queries to one row.

Important

Four changes can surface in an existing project:

An interval is an ISO 8601 duration, "P1DT2H3M4S", not Postgres's text (#93)
pgvector's vector and halfvec are number[], and their parameters take one (#90)
An hstore column is a Record<string, string | null>, and its parameter takes one (#98)
More queries return Row | null instead of Row[]: a subquery, WITH query, view or MySQL join matched on a unique key is now pinned to one row (#105, #106, #108)

Representation changes

Postgres value	0.5.0	0.6.0	Link
`'1 day 02:03:04'::interval`	`"1 day 02:03:04"`	`"P1DT2H3M4S"`	#93
`'-1 day'::interval`	`"-1 days"`	`"-P1D"`	#93
`'1 mon -1 day'::interval`	`"1 mon -1 days"`	`"P1M-1D"`	#93
`'[1,2.5]'::vector`	`"[1,2.5]"`	`[1, 2.5]`	#90
`'a=>1, b=>NULL'::hstore`	`'"a"=>"1", "b"=>NULL'`	`{ a: "1", b: null }`	#98

An interval whose parts all share a sign reads with Temporal.Duration.from(). One whose months, days and time differ in sign keeps a sign on each part, which Postgres reads back and Temporal refuses. Each value goes back in as a parameter unchanged.

Slonik

.run(), .with() and .try() take a Slonik pool, a connection from pool.connect(), a transaction and a savepoint, and return the same values as on every other driver, whatever type parsers the pool was created with. It needs Slonik 49.12.0 or later, whose sql tag takes type parsers per query. (#121)

import { createPool } from "slonik";
import getUser from "./get_user.sql";

const db = await createPool(process.env.DATABASE_URL!);
const user = await getUser.run(db, { id: 1 });
await db.transaction((tx) => getUser.run(tx, { id: 1 }));

A pool whose interceptor rewrites rows (transformRow, transformRowAsync) is refused before the query runs.

`.try()`

.try() runs a query as .run() does and resolves to { value }, or to { error } when the database refuses the query; .trySync() does the same on the synchronous SQLite clients. (#88)

const result = await createUser.try(db, { name: "Ada", email });
if (result.error?.condition === "unique_violation") {
  result.error.constraint; // "users_email_key" | "users_pkey" | undefined
}

The error's names are narrowed to what the statement writes through, read from the catalog: the constraint above can only be one of the table's own (#96).

`reylo play`

npx reylo play serves the playground of reylo.dev/play on 127.0.0.1 against the project's own database: edit a query, and its parameters, return type and errors update as you type. Nothing is run, and nothing is written to .reylo/. --no-open leaves the browser closed. (#110)

Configuration and drivers

Area	What changed	Link
`direct_url`	A `$(command)` in it is replaced by what the command prints, so the type check's password can come from a password manager, as in `postgres://app:$(op read op://dev/db/password)@db:5432/app`.	#102
postgres.js	A run with parameters takes one round trip instead of two: the statement is prepared on the connection the first time. A client created with `prepare: false` still prepares nothing.	#107

Postgres

Area	What changed	Link
Extensions	Installing pgvector, PostGIS, citext, hstore or another extension that defines its own operators or functions no longer switches off inference on built-in columns. `where id = 5` lost its types once `create extension vector` had run.	#112
User-defined objects	`is [not] distinct from`, unique keys and catalog reads account for a user's own `=`, casts and search path, where a user operator could make a value null that was typed non-null.	#119
Pinned reads	A subquery, WITH query or view matched on a unique key of its rows is `maybeOne`. `select * from (select id, title from posts) s where s.id = :id` was `many`.	#105
JSON keys	A key read with `->`, `->>`, `#>` or `#>>` from a column a JSON Schema `CHECK` holds takes the schema's type. It was `unknown` or `string`, always nullable.	#95
Correlated JSON	JSON built over a subquery that refers to the query around it, as in the latest few children per parent, keeps every field's type. Every field was `unknown`.	#104
Window aggregates	`sum`, `min`, `max` and the other aggregates over a frame that holds the current row follow their argument, and `array_agg` and the JSON aggregates are non-null. They were nullable.	#113
`array_agg` of rows	`array_agg(e)` of a table's whole row, at the top level or in a scalar subquery, keeps the table's `not null` fields. Every field was nullable.	#97, #99
Self-joins	`alias.*` and `to_jsonb(alias)` in a table joined to itself keep each reading's nullability. Every column came out nullable.	#92
JSON values	Inside a JSON document an `oid` is a string and a PostGIS `geometry` is GeoJSON, as Postgres writes them. An `oid` was typed `number`.	#85
Cache	The `.reylo/` cache is keyed on user casts and functions' defaults, volatility and strictness, so changing one re-analyzes the statements that rely on it.	#103

MySQL

Area	What changed	Link
Pinned reads	Joins, derived tables, WITH queries and views matched on a unique key are `maybeOne`. `select p.title, u.name from posts p join users u on u.id = p.user_id where p.id = :id` was `many`.	#108
Grouped aggregates	`sum`, `avg`, `min` and `max` under a `group by` follow their argument, and `json_arrayagg` and json_objectagg are non-null. They were nullable.	#120
Window aggregates	The same aggregates over a frame that holds the current row follow their argument. They were nullable.	#115
Derived-table parameters	A parameter compared with a derived table's or WITH query's column takes the table column's type, so a `BIGINT` key's parameter is `int8`. It was `int4`, which refused a key past 2^31.	#125

SQLite

Area	What changed	Link
Pinned reads	A subquery, WITH query or view matched on a unique key of its rows is `maybeOne`. It was `many`.	#106
Window aggregates	`min` and `max` over a frame that holds the current row follow their argument. They were nullable.	#118

Tracing

Every query Reylo runs is published on Node's tracing channel reylo:query, for a tracer or a logger to wrap in a span (#117). Its events are typed QueryTrace, exported from reylo.

What's Changed

fix(postgres): refuse a slonik client with reylo's own error by @colinhacks in #91
fix(postgres): type oid and extension types inside json as written by @colinhacks in #85
feat: add try(), which returns the database's error as a value by @colinhacks in #88
feat(postgres)!: return an interval as an iso 8601 duration by @colinhacks in #93
test(oracle): check pgvector, postgis and contrib extension types by @colinhacks in #94
fix(postgres): tell a self-join's alias.* apart by its alias by @colinhacks in #92
fix(postgres): keep a table's not null in array_agg of its whole row by @colinhacks in #97
feat(postgres)!: return pgvector's vector and halfvec as number[] by @colinhacks in #90
feat(postgres): type a json key read by the column's json schema by @colinhacks in #95
refactor(engine): one replay loop for every native host by @colinhacks in #83
feat(postgres)!: return an hstore column as an object by @colinhacks in #98
ci: run the checks a pull request reaches, in parallel shards by @colinhacks in https://github.com/colin...

@colinhacks

Reylo 0.5.0 marks a nullable parameter with a nullable(…) cast instead of coalesce, refuses null for every other parameter at run time, and moves the type check's database drivers out of your project and into the engine packages.

Important

Two changes can break an existing project:

coalesce no longer makes a parameter nullable. Mark the parameter with ::nullable(type) instead (#79)
.run() refuses null for every other parameter, and a missing one, before the query runs (#75)

Nullable parameters

A cast to nullable(type) makes a parameter that type or null, for any type the dialect casts to and for a typed JSON parameter. A nullable parameter may also be left out, which binds NULL. (#79)

- select * from posts where created_at > coalesce(:since::timestamptz, created_at)
+ select * from posts where created_at > coalesce(:since::nullable(timestamptz), created_at)

Parameter	Type in 0.4.0	Type in 0.5.0
`coalesce(:p::int, x)`	`p: number \| null`	`p: number`
`coalesce(:p::nullable(int), x)`	not valid	`p?: number \| null`
`:p` written bare to a nullable column	`p: T \| null`	`p?: T \| null`
`:p::nullable(int)` written to a `not null` column	not valid	a type error at `:p`

The statement sent to the database keeps the inner cast alone, as in coalesce($1::int, author_id) on Postgres.

At run time, a parameter the statement takes no null for is checked before anything reaches the driver (#75):

// select :x::int + 1 as y
await query.run(db, { x: null }); // Parameter "x" is null, and the statement takes no null for it: pass a value.
await query.run(db, {});          // Parameter "x" is missing, …

Packaging

The type check's database drivers now ship inside the engine packages, and the code that runs only at build time moved out of reylo with them. (#68)

Change	Detail
Drivers	`@reylosql/postgres` describes through postgres.js and `@reylosql/mysql` through mysql2, each bundled in. `pg`, `postgres` and `mysql2` are no longer peers of `reylo`, and a project needs none of them for the type check.
Build-time code	The analysis, the content mapper, the build steps and the CLI moved from `reylo` into the engine packages, which are development dependencies.
Parameter checks	A built module imports the JSON Schema validator and the structured-parameter and cast checks only when its statement needs them. A query written by hand with `defineQuery` that has such a parameter passes `checks`, exported from `reylo`, and an error says so if it doesn't.

Size	0.4.0	0.5.0
`reylo`, unpacked	293 KB	178 KB
Run time an app bundles (esbuild, minified), no parameter checks	62.7 KB	42.0 KB
`@reylosql/postgres` `engine.wasm`	3.88 MB	3.30 MB

The Postgres engine reads libpg_query's parse tree directly rather than through protobuf. Parsing Postgres 18's 49,975 regression statements takes 175 ms, down from 355 ms. (#56)

Configuration and integrations

Area	What changed	Link
`sqlcommenter`	`sqlcommenter: false` in `.reylo.yaml` leaves off the comment naming its file that 0.4.0 added to every statement. It stays on when the key is absent.	#60
Drizzle	`.run()` takes a Drizzle database over every driver Drizzle wraps that Reylo also takes: Neon's serverless and HTTP drivers, PGlite, `Bun.sql`, PlanetScale, `bun:sqlite`, D1 and a Durable Object's storage, which was refused before.	#81
Next.js	`withReylo` accepts every Next.js config key. `withReylo({ reactStrictMode: true })` and `turbopack: { root }` were type errors.	#73
MySQL clients	The error for a client `.run()` does not take lists mariadb's pool and connection among the ones it does.	#82

Postgres

Area	What changed	Link
`lateral` subqueries	JSON a `lateral` subquery builds is typed, as in `left join lateral (select jsonb_agg(jsonb_build_object('name', t.name)) as items …) x on true`. It was untyped JSON.	#55
`case`	JSON built in one branch of a `case` whose other branches are null is typed, and nullable.	#55
Outer-joined rows	`to_jsonb(o)`, `row_to_json(o)` and `json_agg(o.*)` of a row on the nullable side of an outer join keep its `not null` fields non-null: `{ id: number; user_id: number; note: string \| null } \| null`, where every field was nullable.	#74
Whole-row aggregates	`(select coalesce(jsonb_agg(o), '[]') from orders o where …)` is non-null. A bare `o` aggregated in a scalar subquery left it nullable.	#76
More aggregates	`string_agg`, `array_agg`, `bool_and`, `bool_or`, `every`, `bit_and`, `bit_or`, `json_object_agg` and `jsonb_object_agg` make an ungrouped query one row, as `count` does. So `select string_agg(title, ', ') from posts` has a cardinality of one, and `coalesce(string_agg(…), '')` in a scalar subquery is a `string`.	#71
Nested aggregates	A scalar subquery whose aggregate holds a subquery of its own, as in a tree built from nested `jsonb_agg` calls, is one row, and non-null under `coalesce`.	#69
`hstore` in JSON	An `hstore` inside built JSON is `Record<string, string \| null>`, the object Postgres writes for it. It was `string`.	#70
Outer column names	Inside a scalar subquery, a bare name that is a column of an outer query is read as that column, as Postgres reads it. It was read as the whole row of a subquery item with the same name, a type the rows did not match.	#78
Array delimiters	Arrays of PostGIS's `geometry` and `geography`, which separate values with `:`, and of `box` (`;`), are read and bound correctly. They came back as one merged string, and as parameters failed with `malformed array literal`.	#84

MySQL

Area	What changed	Link
`json_table` collation	A `json_table` text column takes the database's collation, so comparing it with a table's text column no longer fails with `Illegal mix of collations`. A `json_table` column compared with one in the connection's collation, where that differs from the database's, now needs the collation written on it.	#77

SQLite

Area	What changed	Link
Recursive `with`	A recursive `with` query's columns are typed through its reads of itself, so a tree walk's depth counter is `number` where it was `number \| null`.	#66
Nested recursive `with`	A statement with a recursive `with` query nested in another's recursive arm no longer crashes the analysis.	#67

Testing and internals

The release workflow installs the engine packages' drivers before it builds them (#86).
The engine host's Node-free half moved into its own module (#72).
The oracle covers derived tables over schema-qualified tables (#63).

What's Changed

perf(postgres): read libpg_query's tree from its c nodes by @colinhacks in #56
test(postgres): cover derived tables over schema-qualified tables by @colinhacks in #63
feat(postgres): type json a lateral subquery or one case branch builds by @colinhacks in #55
fix(sqlite): type a recursive with query through its reads of itself by @colinhacks in #66
fix(sqlite): describe no with query's table after sqlite frees it by @colinhacks in #67
build: move the build steps and their drivers into the engine packages by @colinhacks in #68
fix(postgres): count an aggregate holding a subquery as its query's own by @colinhacks in #69
fix(postgres): count the other built-in aggregates as one row by @colinhacks in #71
refactor(engine): move the engine's node-free half into engine-core by @colinhacks in #72
fix(mapper): type hstore inside json as an object by @colinhacks in #70
fix: accept every next config key in withreylo's type by @colinhacks in #73
fix(postgres): keep an outer-joined row's own fields in to_jsonb by @colinhacks in #74
fix: refuse null for a parameter the statement takes no null for by @colinhacks in #75
fix(mysql): give a json_table text column the database's collation by @colinhacks in #77
feat(engine)!: replace the coalesce rule with nullable(…) casts by @colinhacks in https://github.com/colinhacks/reylo/pu...

@colinhacks

Reylo 0.4.0 types a .sql file of any one statement, shows column comments in the editor, tags every statement with its file for database monitoring, checks SQLite statements on Cloudflare D1, reports two new mistakes at type-check time, and fixes many types on Postgres, MySQL and SQLite.

Important

Four changes can surface in an existing project:

Every statement ends with a file comment, such as /*file='queries%2Fget_user.sql'*/ (#43)
Two new type errors: a parameter as a whole order by or group by key (#42), and two result columns of one name (#5)
Stricter values at run time: binary parameters take only a Uint8Array (#40), and SQLite refuses a stored value its column's type does not allow (#31)
Some types widen where the database could return a value the old type ruled out, marked "(wider)" in the tables below

Any one statement

A .sql file may hold one statement of any kind: DDL, a session setting, explain, show, pragma, or call on Postgres. One that describes no columns resolves to nothing, and one that returns rows resolves to Row[], every column nullable. A file of several statements is still an error. (#26)

Column comments in the hover

On Postgres and MySQL, a column's comment shows in the hover over every row field that reads the column as it is. (#35)

comment on column users.age is 'Age (in years)';

export type Row = {
  id: number;
  /** Age (in years) */
  age: number | null;
};

The file in every statement

Each statement ends with a sqlcommenter comment naming its .sql file, so database monitoring that reads those tags (Datadog Database Monitoring, PlanetScale's query insights) groups statements by file, and pg_stat_activity and slow-query logs show it. The text is the same on every call, so a driver's statement cache still keeps it. (#43)

select id, name, email from users where id = $1::int /*file='queries%2Fget_user.sql'*/

Cloudflare D1

For a project whose package.json lists wrangler and whose wrangler config binds a D1 database, the type check also compiles each SQLite statement on D1 as wrangler runs it locally. D1 refuses some functions its SQLite has, such as json_pretty, power and ceiling, which passed the type check and failed in the Worker. (#44)

queries/power.sql(1,12): error reylo6: Cloudflare D1, as wrangler 4.151.3 runs it locally, refuses this statement: not authorized to use function: power

New errors

Error	What it catches	Link
`reylo16` (Postgres)	An `insert` that leaves out a `not null` column with no default, or writes `default` to it. Postgres prepares it and refuses each row only when it runs.	#19
`reylo17`	A parameter that is a whole `order by`, `group by`, `partition by` or `distinct on` key, which sorts or groups every row by one value. Choose the column inside an expression instead: `order by case :sort when 'name' then name end`.	#42
`reylo14`	Two result columns of one name. Drivers keep only the last.	#5
Unterminated text	A file that ends inside a string, a quoted name or a block comment is an error where it opens. The import became `any`, with a content mapper failure in the error's place.	#20

Running queries

Area	What changed	Link
Binary parameters	A `bytea`, `binary`, `varbinary` or `blob` parameter takes only a `Uint8Array` (a `Buffer` is one), refused before the statement runs. Each database read a string there differently, and base64 text was stored as its own letters.	#40
SQLite values	A stored value its column's declared type does not allow, such as text in an `integer` column of a table that is not `STRICT`, is refused with an error naming the column.	#31
Package size	`reylo`'s JavaScript ships without comments: 387 KB unpacked in 0.3.0, 293 KB now. The declarations keep every doc comment the editor shows.	#61

Postgres

Area	What changed	Link
Whole rows in JSON	`t.*` inside `jsonb_agg`, `jsonb_build_object` or `to_jsonb` is the whole row. It was typed as its first column.	#5
Arrays in JSON	An array built inside JSON keeps non-null elements where the statement shows them: `array[name]` over a `not null` column is `string[]`.	#5
Non-finite numbers in JSON (wider)	A `numeric`, `float4` or `float8` inside a document is `number \| "NaN" \| "Infinity" \| "-Infinity"`, the text Postgres writes for those values.	#5
JSON beside an aggregate	`jsonb_build_object('name', u.name, 'posts', count(p.id))` under `group by` is typed. Its fields were `unknown`.	#45
JSON over a parameterized `with`	JSON read over a `with` query that holds a parameter is typed. Its fields were `unknown`.	#58
Large statements	A statement of about a hundred lines, with several `with` queries and subqueries, gets the probes it needs, so its last JSON columns are no longer `unknown`.	#50
Name scoping in JSON (wider)	JSON follows Postgres's scoping: a `with` query that reads a later sibling's name, one named after a table, `rollup`, and a write whose target a `with` query shadows. Such statements had types their rows could contradict.	#53
`greatest` and `least`	Non-null when one argument is.	#47
A table read twice	With an outer join anywhere in the statement, every other column no longer turns nullable.	#59

MySQL

Area	What changed	Link
Join nesting (wider)	In `a join b right join c on x`, the columns of `a` are nullable, as MySQL nests that join. Comma joins and multiple-table `update t1, t2 set …` read as MySQL reads them, and the parser refuses 226 of MySQL's own 54,648 test statements, down from 964.	#54
Merged views (wider)	A computed column of an `algorithm = merge` view on the null side of an outer join is nullable.	#46

SQLite

Area	What changed	Link
Mixed types (wider)	A value that may be one of several types (the arms of a `union`, `case`, `coalesce`, `iif`, `lag` or `lead` with a default) is `unknown` where one has no known type, on `STRICT` tables too. A compound's column no longer takes its leftmost arm's declared type.	#49
Nested JSON subqueries (wider)	JSON a scalar subquery builds, nested in other JSON, is `unknown`, since SQLite 3.54 nests it as text where 3.53 nests it as JSON. Wrap the subquery in `json(…)` to type it on every SQLite.	#51

Testing and internals

The CLI finds .sql files with Node's own glob (#48).
The tests expect a numeric's NaN and infinities as JSON strings (#57).

What's Changed

fix: refuse text for a binary parameter by @colinhacks in #40
fix(mapper): keep diagnostics inside the file by @colinhacks in #20
feat(engine): show column comments in the row's hover by @colinhacks in #35
feat(sqlite): compile each statement on cloudflare d1 through wrangler by @colinhacks in #44
feat(postgres): report an insert that leaves out a required column by @colinhacks in #19
fix(mysql): null a merged view's column on an outer join's null side by @colinhacks in #46
feat: end each statement with a comment naming its file by @colinhacks in #43
refactor(cli): find .sql files with node's own glob by @colinhacks in #48
fix: type whole rows, null elements and json numbers soundly by @colinhacks in #5
feat(engine): type a .sql file of any one statement by @colinhacks in #26
fix(postgres): type an aggregate beside a column in built json by @colinhacks in #45
fix(sqlite): type a value of one of several types as unknown by @colinhacks in #49
fix(mysql): read joins, writes and literals as mysql does by @colinhacks in #54
feat(postgres): infer greatest and least non-null by their arguments by @colinhacks in #47
fix(sqlite): type a scalar subquery's json nested in json soundl...

@colinhacks

Reylo 0.3.0 runs queries on a Durable Object's SQL storage, checks each SQLite statement against the SQLite your app's drivers run, types SQLite's full-text search, names the query's file in its errors, and fixes several types that were wrong on Postgres and MySQL.

Important

Three types widen where the database could return a value they ruled out, so code that relied on them may need a null check:

Postgres: a select * over a left-joined with query (#18)
Postgres: a JSON coalesce with a default other than '[]' (#10)
MySQL: a scalar subquery whose with shadows a table (#29)

Durable Objects

.run() and .runSync() take a SQLite-backed Durable Object's ctx.storage.sql, as they take D1's binding, inside ctx.storage.transactionSync() too. An integer past 2^53, which the storage returns rounded, is refused, as on D1, and a BLOB comes back as a Uint8Array. (#37)

export class Board extends DurableObject {
  user(id: number) {
    return getUser.runSync(this.ctx.storage.sql, { id });
  }
}

Your app's SQLite

The type check also prepares each SQLite statement on the drivers your package.json lists (better-sqlite3, @libsql/client, sqlite3, @tursodatabase/database), whose SQLite may be older than Reylo's. A statement one of them refuses is an error at the place SQLite names. (#27)

queries/pretty.sql(1,12): error reylo6: @libsql/client runs SQLite 3.45.1, which refuses this statement: no such function: json_pretty

The query's file

Area	What changed	Link
`.source`	Every query exposes its file, relative to the project's root, for tagging logs and traces: `getUser.source` is `"queries/get_user.sql"`.	#34
Errors	Reylo's errors about a parameter's value, a column's value or the row count start with the file, as in `queries/get_user.sql: Expected exactly one row, got 0`. The driver's own errors pass through unchanged.	#34

SQLite

Area	What changed	Link
Full-text search	FTS5's `bm25` is `number`, `highlight` and `snippet` are `string \| null`, and FTS4's `offsets` is `string` and `matchinfo` `Uint8Array`. They were `unknown`.	#32

Postgres

Area	What changed	Link
Outer-joined `with` queries	A `select *` over a `with` query on the nullable side of an outer join types that query's columns as nullable. They were typed from the table inside the query, non-null where every row can be null.	#18
JSON `coalesce`	`coalesce(x, default)` of built JSON keeps the shape of `x` only where `x` is never null, or is an array defaulted to `'[]'`. Over no rows, `coalesce(jsonb_agg(id), '{}')` returns `{}`, which the old `number[]` ruled out.	#10
Engine fingerprint	The Postgres engine's fingerprint covers the parser's pinned commits, so an analysis cached under an older parser is no longer reused.	#39

MySQL

Area	What changed	Link
Derived tables	A derived table's columns keep what Reylo infers about its query alone, so a count or a non-null column read through one stays non-null. A column computed on the null-extended side of an outer join is nullable, where the server's flag had it non-null.	#33
Subqueries with their own `with`	`(with r as (select id from posts) select count(id) from r)` is non-null and the JSON it builds is typed. Where that `with` shadows a table, its columns are typed from the `with` query, not the table.	#29

Agent skill

An agent skill, using-reylo, carries the rules an agent needs in a project that uses Reylo. Install it with npx skills add colinhacks/reylo --skill using-reylo. (b9b2e05)

Testing and internals

CI builds and tests the engine and checks that each committed engine.wasm matches its sources (#36).
CI runs the soundness oracle on Postgres 18 and MySQL 8.4 (#38).

What's Changed

fix(postgres): null what an outer-joined with query reads by @colinhacks in #18
feat(sqlite): prepare each statement on the app's own sqlite drivers by @colinhacks in #27
fix(mysql): probe a scalar subquery's items with its own with by @colinhacks in #29
fix(postgres): type a json coalesce by the default it falls back to by @colinhacks in #10
feat: expose each query's source file and name it in its errors by @colinhacks in #34
feat(sqlite): type the full-text search functions by @colinhacks in #32
ci: build and test the engine, and check its committed artifacts by @colinhacks in #36
feat(sqlite): run queries on a durable object's sql storage by @colinhacks in #37
fix(engine): hash the pg_query pins into the postgres fingerprint by @colinhacks in #39
fix(mysql): type a derived table's columns as its query alone does by @colinhacks in #33
ci: run the soundness oracle on postgres 18 and mysql 8.4 by @colinhacks in #38
chore(release): 0.3.0 by @colinhacks in #41

Full Changelog: v0.2.0...v0.3.0

@colinhacks

Reylo 0.2.0 runs queries on Drizzle databases, shows a .sql file's comment and statement in the editor, types more of what Postgres and MySQL return, and ships engines up to half the size.

Important

Postgres array elements are nullable: a text[] column is now (string | null)[], since any Postgres array can hold a NULL element whatever the column's not null says (#12). An array the statement builds from non-null values, such as array[id], stays number[].

Drizzle

.run() takes a Drizzle database, and the transactions and savepoints Drizzle hands out, on Postgres, MySQL and SQLite. Reylo runs the statement on the driver client Drizzle holds, so the values are the ones Reylo returns for that driver. (#23)

import { drizzle } from "drizzle-orm/node-postgres";
import getUser from "./get_user.sql";

const db = drizzle(process.env.DATABASE_URL!);

const user = await getUser.run(db, { id: 1 });
await db.transaction((tx) => getUser.run(tx, { id: 1 }));

.runSync() takes a Drizzle database over a synchronous SQLite driver. A Kysely or Prisma client is still refused, and the error now says which client to pass instead.

Editor

Hovering over a .sql import shows the file's leading comment, then its statement. Hovering over its sql property shows the statement as Reylo sends it, with $1 or ? in each parameter's place. (#17)

The signed-in user's profile.
Null when the id no longer exists.

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

Project file and cache

Area	What changed	Link
`.reylo.yml`	The project file may be named `.reylo.yml` as well as `.reylo.yaml`. With both beside one `package.json`, the project is an error that names them.	#13
Content mapper extensions	The setup lists `.reylo.yaml` and `.reylo.yml` beside `.sql` in `contentMappers`, so the project file goes to Reylo rather than to a YAML content mapper.	#11
Offline builds	A build with no `DATABASE_URL`, or one it cannot reach, reads the types `.reylo/` recorded. It had to set the exact URL the cache was filled from, and a SQLite project's cache missed once the project moved, as it does in a container.	#9

Postgres

Area	What changed	Link
JSON from `with` queries	JSON built in a `with` query or derived table keeps its type when the outer statement selects it, wraps it in `jsonb_build_object` or `jsonb_agg`, or falls back with `coalesce(…, '[]')`. It was untyped JSON.	#3
Set operations	A `union` anywhere in a statement no longer makes every column nullable: a column read from a table in the statement's own `from` keeps its `not null` and `check` values, and a `with recursive` query is typed by its arms.	#4
Operators	`like`, `ilike`, `similar to`, the regular expression operators, and the bit, shift, containment, overlap and JSON key operators are non-null over non-null operands.	#15
Array casts	A cast to an array type keeps an array non-null, as in `coalesce(array_agg(id), array[]::int[])`.	#15
Self joins	When one table is read on both sides of an outer join, a column named through the preserved side's alias stays non-null.	#15
One-row subqueries	A scalar subquery with no `from`, such as `(select 1)`, or an aggregate under a `limit`, is non-null.	#15
Views and series	A view over a view keeps its columns' `not null`, and `generate_series` and `unnest(array[…])` over non-null elements are non-null.	#15
Odd JSON arguments	`json_build_object` or `jsonb_build_object` with an odd number of arguments is an error at the call. Postgres refused it only when the statement ran.	#7

MySQL

Area	What changed	Link
`VECTOR`	A MySQL 9 `VECTOR` column is `number[]`, and a `number[]` parameter is sent through `to_vector()`, on every driver that reads one. It was `unknown`, and a parameter type-checked and then failed. Bun.sql cannot read a `VECTOR`.	#8
JSON over derived tables	A guarded JSON aggregate in a scalar subquery over a derived table is non-null, as it was over a `with` query.	#21

Errors

Area	What changed	Link
SQLite's `json_object`	A call with an odd number of arguments is an error at the call.	#7
End of input	A syntax error at the end of a statement is placed on its last word, not on the file's first character.	#7
Repeated parameters	The error for a parameter used twice suggests a form every dialect can type: `with input as (select :id::<type> as id)`.	#14

Engine size

Package	0.1.0, unpacked	0.2.0, unpacked
`@reylosql/postgres`	5.3 MB	3.8 MB
`@reylosql/sqlite`	3.0 MB	2.4 MB
`@reylosql/mysql`	3.8 MB	1.7 MB

The WebAssembly files no longer carry function names (a9ab0dd), and MySQL's leaves out the SQL parser's DDL (2bb5c19). Every analysis is unchanged.

What's Changed

ci(release): describe the tag approval by role by @colinhacks in #2
feat(mysql): type vector columns and parameters as number[] by @colinhacks in #8
chore: remove schema files until their design is settled by @colinhacks in #6
feat(mapper): take .reylo.yaml as a content mapper extension by @colinhacks in #11
feat: accept .reylo.yml as the project file's name by @colinhacks in #13
fix(engine): cast the value in the repeated-parameter error's example by @colinhacks in #14
fix(mapper): read .reylo/ offline whatever the database url, or none by @colinhacks in #9
fix(postgres): type returned array elements as nullable by @colinhacks in #12
fix(postgres): keep not null on columns read beside a set operation by @colinhacks in #4
feat(postgres): type json a with query or derived table builds by @colinhacks in #3
fix: report odd json object arguments and errors at end of input by @colinhacks in #7
fix(mysql): type a json aggregate over a derived table as non-null by @colinhacks in #21
feat(mapper): show the file's comment and statement in the hover by @colinhacks in #17
feat(postgres): infer more expressions non-null by @colinhacks in #15
feat: run on drizzle databases and their transactions by @colinhacks in #23
chore(release): 0.2.0 by @colinhacks in #24

Full Changelog: v0.1.0...v0.2.0

Reylo 0.1.0 is built from this repository's current history, so each package's provenance names a commit on main. The public history was rewritten after 0.0.2, which leaves the commits that 0.0.1 and 0.0.2 were built from off main.

It types and runs as 0.0.2 does, and the README is now a short version of the docs at reylo.dev. From 0.0.2, a parameter's type follows what the statement says about it, on Postgres, MySQL and SQLite alike:

A cast parameter (:p::text, cast(:p as text)) is its cast's type and never null.
A parameter takes | null as the first argument of coalesce (ifnull on MySQL and SQLite), or where it is written bare to a column that may hold null.
A null test (:p is null) or null-safe equality (<=>, is not distinct from) no longer makes a parameter nullable.
A column under a not null domain or a check (col is not null) refuses null, so its parameter is not nullable.
An array parameter's elements are non-null (:ids::int[] is number[]), and an array with a null element is refused before the statement runs.
insert … select :p is typed like insert … values (:p) on every database.

A parameter's type follows what the statement says about it, on Postgres, MySQL and SQLite alike:

A cast parameter (:p::text, cast(:p as text)) is its cast's type and never null.
A parameter takes | null as the first argument of coalesce (ifnull on MySQL and SQLite), or where it is written bare to a column that may hold null.
A null test (:p is null) or null-safe equality (<=>, is not distinct from) no longer makes a parameter nullable.
A column under a not null domain or a check (col is not null) refuses null, so its parameter is not nullable.
An array parameter's elements are non-null (:ids::int[] is number[]), and an array with a null element is refused before the statement runs.
insert … select :p is typed like insert … values (:p) on every database.

The first release of Reylo: import .sql files into your TypeScript code with full type inference, through a TypeScript 7.1 content mapper. Each statement's parameters, columns, nullability and row count come from your development database, and a misspelled column or a wrong parameter is a type error in tsc and in your editor.

npm install reylo
npm install -D @reylosql/postgres  # or @reylosql/mysql, @reylosql/sqlite
npm install -D typescript@beta

reylo: the content mapper, the run time, the bundler plugins (Vite, esbuild, webpack and Rspack, Turbopack and Next.js), the preload for Node, Bun and nub, the Jest transformer and the CLI.
@reylosql/postgres, @reylosql/mysql and @reylosql/sqlite: the analysis engine for each database, built to WebAssembly. Install the one for each database your project uses, at the version of reylo.

Every package is published from this repository's release workflow, with npm provenance. The documentation is at reylo.dev.

v0.7.0

Native engine

DuckDB

Keys through set operations and column lists

Dates outside 0000–9999

Postgres

Contributors

Uh oh!

v0.6.0

Choose a tag to compare

Sorry, something went wrong.

Sorry, something went wrong.

Uh oh!

No results found

Representation changes

Slonik

.try()

reylo play

Configuration and drivers

Postgres

MySQL

SQLite

Tracing

What's Changed

Contributors

Uh oh!

v0.5.0

Nullable parameters

Packaging

Configuration and integrations

Postgres

MySQL

SQLite

Testing and internals

What's Changed

Contributors

Uh oh!

v0.4.0

Any one statement

Column comments in the hover

The file in every statement

Cloudflare D1

New errors

Running queries

Postgres

MySQL

SQLite

Testing and internals

What's Changed

Contributors

Uh oh!

v0.3.0

Durable Objects

Your app's SQLite

The query's file

SQLite

Postgres

MySQL

Agent skill

Testing and internals

What's Changed

Contributors

Uh oh!

v0.2.0

Drizzle

Editor

Project file and cache

Postgres

MySQL

Errors

Engine size

What's Changed

Contributors

Uh oh!

v0.1.0

Uh oh!

v0.0.2

Uh oh!

v0.0.1

Uh oh!

`.try()`

`reylo play`