Repository navigation
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/mysqland@reylosql/sqlite, sincereylonow installs its engine (#152) - The engine runs only on eight platforms: macOS, Linux and Windows, on x64 or arm64 (#152)
- A production install of
reylodownloads its platform's engine, which the preload needs, so don't install with--omit=optionalwhere it runs (#152) - A query's
.sqlno 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'::dateis-000001-06-01(#153) - On Postgres,
tags[:i]is a slice, withiread as a column: subscript by a parameter astags[(:i)](#145) - Types and cardinalities move: most narrow, and a few become nullable or
manywhere 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.duckdbimport { 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 [ starts a slice, as Postgres reads it, so tags[:n] is the first n elements and n a column. It was read as a parameter: write tags[(:i)] to subscript by one. |
#145 |
MySQL and SQLite
| Statement | Before | Now | Link | |
|---|---|---|---|---|
| MySQL | select a, count(*) from t group by 1 |
maybeOne: 1 was read as the constant |
many: 1 is the first select item |
#146 |
| MySQL | select from_days(3652499) |
non-null, from the server's flag | nullable: NULL past 9999-12-31 | #146 |
| MySQL | select * from v, where v outer-joins another view |
the merged view's NOT NULL flag | read with every view materialized, as other reads are | #146 |
| MySQL | A parameter compared through a join's ON, a derived table, a WITH query, a column list or a set operation | int4 for a BIGINT column |
int8, the column's type |
#147 |
| SQLite | … t1 join t2 on t2.y is not null right join t3 … |
t2.y non-null |
nullable: a later RIGHT JOIN null-extends it | #146 |
| SQLite | update t1 set (b, c, d) = (select x, y …) |
a crash | SQLite's own error, "3 columns assigned 2 values" | #146 |
The playground
The playground at reylo.dev/play gains an API, share cards and more examples. (#132, #133, #134)
curl 'https://reylo.dev/play/api/analyze?q=select+name+from+users+where+id+%3D+%3Aid'| Area | What changed | Link |
|---|---|---|
| API | A request to GET /play/api/analyze?q=<sql>, or a POST of {"sql": "<sql>"}, answers a statement's parameters, return type, cardinality and diagnostics as JSON, as reylo play answers them. Another to GET /play/api/schema lists the playground's tables. |
#132 |
| API docs | An API link in the bar explains each request, with a live answer, and /play/api.md and llms.txt carry the same text. |
#133, 6930d7a |
element |
The answer of the API and of reylo play carries element, one row's type: { name: string } for both { name: string }[] and { name: string } | null. |
#133 |
| Share cards | A link to /play?q=<sql> previews as an image of the query. |
#134 |
| Schema and examples | The schema can be edited and shared, advanced example queries join the picker, and a link under the inferred types opens a prefilled GitHub issue. | 3521148 |
Other changes
| Area | What changed | Link |
|---|---|---|
.sql |
A query's .sql is the compiled statement alone, as select id, name, email from users where id = $1, with no /*file='…'*/ and no trailing newline. The statement .run() sends still ends with the comment unless the project file says sqlcommenter: false. |
#135 |
| Vitest | After a schema change, Vitest 5.0.5's module cache kept serving a .sql module's old types, as many where a new unique index makes it maybeOne. The Vite plugin now keeps .sql modules out of that cache. |
#128 |
| Declarations | The types PostgresErrorFields, MysqlErrorFields and SqliteErrorFields are exported, so a project that emits declarations can export a query. It failed with TS4023. |
#130 |
reylo play |
A column with a COMMENT no longer turns the types of the queries that read it into unknown. |
#143 |
| Windows | On Windows, reylo generate and the project file's messages write paths with /, as tsc does. They used \. |
#138 |
What's Changed
- fix: have vitest make .sql modules again after a migration by @colinhacks in #128
- feat(site): serve the playground's api at /play/api by @colinhacks in #132
- feat: make every import a standard query by @colinhacks in #131
- feat(site): render a share image of /play's query by @colinhacks in #134
- feat(site): explain /play's api in a dialog, and answer element by @colinhacks in #133
- feat(engine)!: let each engine connect itself as a wasip2 component by @colinhacks in #130
- feat!: leave the sqlcommenter comment out of .sql by @colinhacks in #135
- fix(engine): reach the server's extension as reylo, reading its jsonb by @colinhacks in #140
- feat: add a DuckDB dialect by @colinhacks in #142
- fix(cli): keep column comments in reylo play's types by @colinhacks in #143
- fix(duckdb): harden inference against DuckDB's own suite and values by @colinhacks in #144
- fix(engine)!: infer tighter types across joins, functions and writes by @colinhacks in #145
- fix: make the type check work on windows, and check it in ci by @colinhacks in #138
- fix(engine): correct eight bugs that upstream test suites found by @colinhacks in #146
- feat(engine): carry keys through set operations and column lists by @colinhacks in #147
- test: replay each database's own suite, drop tests the oracle covers by @colinhacks in #148
- fix(duckdb): keep webpack and rspack from bundling the driver by @colinhacks in #150
- feat(mapper): type untyped JSON values as Json by @colinhacks in #151
- feat(engine)!: ship one native reylo binary per platform by @colinhacks in #152
- fix!: write date and timestamp years outside 0000-9999 as ISO 8601 does by @colinhacks in #153
- docs: specify the type descriptor format by @colinhacks in #154
- docs: mark the type descriptor spec as settled but living by @colinhacks in #157
- chore(release): 0.7.0 by @colinhacks in #156
Full Changelog: v0.6.0...v0.7.0
Contributors
colinhacks