github colinhacks/reylo v0.8.0-beta.0

pre-release36 minutes ago

Reylo 0.8.0-beta.0, installed with npm install reylo@beta, adds migrations, makes reylo a native command with reylo analyze and Reylo Type Defs, returns Postgres and MySQL structured values as objects, infers through SQL function bodies and keyed writes, and fixes the types and values an audit found wrong.

Important

Eight changes need attention when upgrading from 0.7. Upgrading from 0.7 lists every change and what to do:

  • Structured values come back as objects: Postgres row values, ranges and geometry, arrays of a subquery's rows, and MySQL SET (#193, #205)
  • One unnamed column returns its values, not rows: select count(*) from users is a bigint (#206)
  • reylo generate no longer fills the cache, and the project file's cli section was removed (#174)
  • strict_params is now allow_untyped_params, with the opposite meaning (#187)
  • The cache moved from .reylo/ to reylo/.cache/ (#179)
  • Postgres ssl=true now checks the certificate (#226)
  • Silent wrong answers now throw: a non-ISO DateStyle, a hand-written client wrapper (#242)
  • DuckDB support was removed, query typing, .run() and migrations alike (#258)

Known issue

Migrations on Postgres 14 and 15 fail in this beta: on Postgres 14 every reylo migrate and reylo db command refuses to start, and on Postgres 15 reylo migrate new writes an empty migration and prints column co.collicurules does not exist. #266 fixes both, after this beta. Postgres 16, 17 and 18 are not affected.

Migrations

Reylo Migrate is part of the reylo command, on Postgres, MySQL and SQLite. Declare the schema you want in reylo/schema.sql, and reylo migrate new writes each change as a plain .sql migration, tried on a shadow database before it is written. (#179)

npx reylo migrate init           # start from an existing database
npx reylo migrate new add_bio    # write the next migration, from schema.sql
npx reylo migrate dev            # run it on the development database, and write the lock
npx reylo migrate deploy         # run what the lock lists, when you deploy
Area What it does Link
The lock reylo/reylo-lock.yaml orders the migrations with chained checksums, so two branches that each add one conflict in git before anything is deployed #179
Data loss A migration, or a reylo db push, that drops something or loses values needs --drop; --rename old=new names a rename #179, #256
Deploys reylo migrate deploy builds no shadow database, so it needs no create database rights, and refuses a database changed by hand #179
Schemaless mode Without schema.sql, the migrations are the schema, and the development database stands in for it #179
Development reylo db push --watch takes the database to schema.sql on every save; db reset, db wipe, db introspect and reylo run cover the rest #179
Scripts and CI Every command takes --json, with stable refusal codes, and the development commands refuse to run where CI is set #179
From code migrate(), exported from reylo, runs reylo migrate deploy on a server #253
Durable Objects migrate(ctx.storage) runs the migrations the Vite or esbuild plugin bundled, on the object's own SQLite #253
import { migrate } from "reylo";

// on a server: reylo migrate deploy, as the command line runs it
const ran = await migrate();

// in a Durable Object's constructor: the bundled migrations, on the object's own SQLite
migrate(ctx.storage);

The Migrations page covers each command.

The reylo command and Reylo Type Defs

The reylo command on npm now runs the native binary (#174). Its new reylo analyze prints what Reylo infers for each statement, as YAML, or as JSON with --format json (#172, #239):

file: queries/get_user.sql
$schema: https://reylo.dev/typedefs/0.1.0.json
dialect:
  name: postgres
  version: "18.1"
sql: |-
  -- One user, by id.
  select id, email, bio from users where id = $1
kind: select
comment: One user, by id.
params:
  - name: id
    type: { kind: integer, bits: 64, label: int8 }
result:
  kind: rows
  cardinality: at_most_one
  columns:
    - name: id
      type: { kind: integer, bits: 64, label: int8 }
    - name: email
      type: { kind: text, size: { max: 255 }, label: varchar }
    - name: bio
      nullable: true
      type: { kind: text, label: text }
      comment: Shown on the profile page
diagnostics: []

The same descriptor comes from code with analyze(), which reads the project's database as the type check does (#191, #195):

import { analyze } from "reylo";

const descriptor = await analyze({ sql: "select id, name from users where id = :id" });

The descriptor is Reylo Type Defs, a format written as a JSON Schema, spec/typedefs.schema.json, which reylo.dev serves at https://reylo.dev/typedefs/0.1.0.json from 0.8.0 (#182, #243). Besides each type, it carries what the database declares and what Reylo infers about it:

Fact Example Link
Declared size and precision varchar(20) is size: { max: 20 }, numeric(10,2) has precision and scale #180, #192, #210
Bounds and patterns from a CHECK check (age between 0 and 150) gives min and max; check (slug like 'a%') a pattern #200, #251
A UUID's version gen_random_uuid() is version 4, uuidv7() version 7 #232
Unique columns and sort order A result's unique column sets, and the order its rows are sorted on (Postgres) #184, #213
Distinct arrays array_agg(distinct …) and json_agg(distinct …) are marked distinct #251

The CLI page covers reylo analyze and analyze(), and the Reylo Type Defs page every kind.

What .run() returns

Value 0.7.0 0.8.0 Link
A Postgres row value of known fields "(1,Ada)" { id: 1, name: "Ada" } #193, #197
A Postgres geometric type "(1.5,-2)" { x: 1.5, y: -2 } #193
A Postgres range "[1,5)" { lower: 1, upper: 5, bounds: "[)" }, or { empty: true } #193
A Postgres multirange "{[1,3)}" An array of ranges #193
array(select c from (…) c) on Postgres The rows' text An array of objects, each field decoded #205
A MySQL SET "a,c" new Set(["a", "c"]) #193
A MySQL ENUM column's type string The union of its labels #193
select count(*) from users A row keyed by the name each database makes up, such as count or count(*) bigint #206
NaN or Infinity inside JSON a Postgres statement builds The string "NaN", typed number | "NaN" | "Infinity" | "-Infinity" The number, typed number #171
A Postgres timestamptz or timestamp "…10:00:00.5Z" "…10:00:00.500000Z", six fraction digits always #190
A Postgres money "$1,234.50", the session's locale text "1234.50", its exact decimal #254

A statement whose one column is an expression with no as returns that column's values the same way through .runSync(), .try(), .with() and a Standard Query: select lower(name) from users is a string[]. An untyped JSON parameter takes anything JSON.stringify writes, typed as the new exported JsonInput. (#206, #193)

Inference

Area What changed Link
SQL function bodies A Postgres language sql function's body is read as the query it is, so select id, body from one_comment(:x) is one row of non-null columns, where the declaration alone made it many and nullable #211
Keyed writes An update … from or delete … using that pins a key returns a row or null, and an insert of one selected row returns that row #190
Constants A column a statement fills with constants is typed as those literals: select 'user' as kind … union all select 'post' … is "user" | "post" #243
Whole rows A whole row of a derived table, subquery, with query or aliased join is typed field by field, inside JSON and alone #167
Arrays Built arrays of arrays are typed with each level; array(select …) takes its subquery's type; array_remove(tags, null) and a CHECK that keeps NULL out make elements non-null #181, #190, #166
CHECK forms tags <@ array['a', 'b'] types ("a" | "b")[]; jsonb_typeof(doc) = 'object' types an object; a bound such as price < 1e9 keeps out NaN and Infinity #190, #171
Triggers A BEFORE trigger whose every return is new, such as an updated_at trigger, no longer makes a write nullable #190
Grouped JSON An object that puts an aggregate beside a grouped column is typed inside a scalar subquery, and on MySQL at the top level too #230
SQLite views A write to a view through its INSTEAD OF trigger type-checks #238
Hovers A query's hover and .try()'s result name the error type, QueryError, instead of listing every code #221
tsc An error in a .sql file no longer hides the TypeScript errors beside it, and tsc --incremental checks again after a schema change #249

Drivers

Driver What changed Link
Knex .run(), .try() and .with() take a Knex instance, transaction or savepoint, over pg, mysql2, mariadb, better-sqlite3 and sqlite3 #233
SQLite Wasm .run() and .runSync() take an oo1 database of @sqlite.org/sqlite-wasm, OPFS ones included #235
Durable Objects A Workers project with Durable Objects and no D1 database is checked on Cloudflare's SQLite, so a function they refuse, such as json_pretty, fails the type check #241
SQLite, mariadb, PlanetScale, Turso, libSQL A row's keys always match the declared column names. They were the driver's own names, such as 1 + 1 /*file='q.sql'*/ #207
MySQL A value error raised outside any column has no column. It was the string "(null)" #216
mysql2 through Cloudflare Hyperdrive Hyperdrive refuses prepared statements, which mysql2 runs every query as, and the error now says so and names mariadb, which Reylo runs without them #261
import knex from "knex";
import getUser from "./get_user.sql";

const db = knex({ client: "pg", connection: process.env.DATABASE_URL });
const user = await getUser.run(db, { id: 1 });

Standard Query

A built module's Standard Query carries more of what Reylo infers, for drivers that run it themselves.

Field What it holds Link
columns, output() A decoder per column, and the cardinality applied to decoded rows, so a driver decodes value by value as it reads #165
key The result columns that identify each row (Postgres) #160
ordered true where the order by makes the order total (Postgres) #163
cardinality How many rows the statement returns #206
readOnly true where nothing in the statement can write, for a wrapper that retries reads #190
inputSchema, outputSchema maxLength, pattern, bounds, multipleOf, enum and description from the schema and its comments #199, #192

Faster and smaller

Change Before After Link
A bundle carries only its query's dialect (one query, esbuild 0.28.2, minified) 162.8 KB for every dialect Postgres 76.7 KB, MySQL 117.7 KB, SQLite 63.2 KB #228
JIT is off on the engine's Postgres connections (566 statements, pgvector/pgvector:pg17) 264 s 20 s #158
The catalog is read once per run (517 statements, local Postgres 18, median of five) 1,878 queries, 10.29 s 849 queries, 6.01 s #190
Postgres rows are decoded faster (25,000 rows × 12 columns through postgres.js, reads per second) 13.5 16.8 #164
A SQLite CASE of 10,000 arms is analyzed faster 7.2 s 0.45 s #238
The Vite, Rollup and Rolldown plugin declares hook filters, so those hosts skip modules that aren't .sql Every module .sql modules only #137

The JIT change matters for Postgres builds that have JIT on, such as the official Docker images most CI services run.

Fixed types and values

Each of these types claimed less than the database could return. Some types widen as a result.

Database Statement Was Now Link
Postgres jsonb_build_object('tags', '[]'::jsonb) { tags: string } Any JSON #225
Postgres A column of a left-joined lateral subquery that reads the table before it Non-null Nullable #225
Postgres insert into p values (unnest(:ids::int[]), 'x') returning id One row Any number of rows #225
Postgres A materialized view Its defining query, run now A stored relation, with its own unique indexes as keys #226
Postgres A key compared under a case-insensitive collation At most one row Many #209
Postgres jsonb_build_object('a', 1, 'a', null::int) { a: number } { a: number | null }, the last value #209
Postgres distinct on (2), and distinct on an output column's name At most one row Many #155
MySQL where id = :id and x = 1 xor y = 2 At most one row Many, since XOR ranks between AND and OR #225
MySQL An IF or CASE with a BIT result DECIMAL unknown #226
SQLite select count(*), name from t name non-null Nullable, NULL over no rows #225
SQLite ifnull(dt, 0) over a DATE column number number | string #238
SQLite qty % 0.5 Non-null Nullable #124, fixes #122
SQLite select 9223372036854775808 An integer A real, as SQLite reads it #248

#222 fixes wrong values at run time and build time:

  • two columns of one name on MySQL and SQLite took the first column's kind;
  • a select * built before a migration put values under the wrong names on mysql2, mariadb, PlanetScale, libSQL and Turso;
  • a parameter named constructor or __proto__ and left out bound JavaScript's own member;
  • D1 refused the bigint .run() returns;
  • webpack's disk cache served a module built for the old schema.

Upgrading from 0.7

What .run() returns

Change What to do Link
Postgres row values, geometric types, ranges and multiranges come back as objects, and a MySQL SET as a Set Remove the code that parsed their text; parameters take the same form #193
A MySQL ENUM is typed as its labels, and an empty '' value is refused Read such a column with nullif(column, '') #193
An array of a subquery's rows comes back as objects on Postgres Remove the code that parsed the text #205
One unnamed column returns its values, not rows Write const count = await q.run(db), or add as count to keep a row #206
On MySQL and SQLite, a made-up column name that isn't an identifier, beside other columns, is an error Name the column with as #206
NaN and Infinity inside built Postgres JSON are numbers Test with Number.isNaN and Number.isFinite #171
A Postgres timestamp always has six fraction digits Update code or tests that compare its text #190
A Postgres money comes back as its decimal text, "1234.50", not the session's locale text Format it for display in the app #254
A keyed update … from or delete … using, an insert of one selected row, a call to a function whose body has a key filter, and a CALL with OUT parameters return a row, not an array Check the row for null instead of reading .length #190, #211, #186
A Postgres array of more than one dimension in a column, or in built JSON, is refused Select to_jsonb(column), or set allow_nested_arrays: true #181, #243

Types and the type check

Change What to do Link
A JSON column whose schema leaves additionalProperties out is typed by the keys it names, with no index signature Name the key in the schema, or give additionalProperties a schema #169
Columns filled with constants are typed as their literals Widen the variable's type where code assigns another value to it #243
A double-quoted string on SQLite, select "hello", fails the type check, as most SQLite drivers refuse it Write string literals in single quotes #215
A SQLite parameter the lexer did not read, such as :été, is an error Rename the parameter #226
A MySQL JSON parameter where MySQL takes a value, as in json_set(doc, '$.name', :name), is an error Write :name::json where the error points #242
Some columns widen to nullable or unknown (above) Handle null where the type check now asks #225, #226, #238, #234

The project file and the command line

Change What to do Link
reylo generate no longer fills the cache, and the project file's cli section was removed Drop it from scripts and CI: the type check and the build steps fill the cache. Delete cli: #174
strict_params was replaced by allow_untyped_params, which reads the other way round Write allow_untyped_params: true for strict_params: false, and delete strict_params: true #187
The cache moved from .reylo/ to reylo/.cache/, which keeps itself out of git Delete .reylo/ and its .gitignore line #179
An unquoted .env value is cut at its first #, as dotenv reads it Quote the value, or write # as %23 #227

Connections and run time

Change What to do Link
ssl=true in a Postgres URL checks the certificate and its name Use ssl=no-verify or sslmode=require for an unchecked connection #226
A Postgres date under a non-ISO DateStyle makes .run() throw Set datestyle = 'ISO, DMY' on the database or the connection #242
A hand-written { query } client wrapper is refused Pass the driver's own pool, client or transaction, or subclass or proxy it #242
A build that cannot reach its database says so, and reads only that server's analysis Unset the URL for a build that relied on another database's analysis #242
A retyped, renamed or dropped column warns and runs, instead of failing the call Watch the log line where code relied on the error #208

DuckDB

Change What to do Link
DuckDB support was removed: a duckdb: URL is refused, and defineDuckdbQuery, DuckdbClient and DuckdbQueryError are gone Stay on 0.7 for a DuckDB project #258

Docs

reylo.dev moves to 0.8's docs when 0.8.0 is released. Until then they are in the repository, under site/content/docs:

Testing and internals

  • The oracle analyzes each Postgres and MySQL statement again under other session settings, such as DateStyle and TimeZone, and fails if the analysis changes (#198).
  • Every inferred type is held to the Reylo Type Defs schema (#188).
  • The release's npm stage job restores no cache and pins npm, and the build checks the SQLite amalgamation's checksum (#222).
  • A branch's checks, and one platform's binary, can be run by hand (#212).

What's Changed

  • fix(postgres): read DISTINCT ON names and positions as postgres does by @colinhacks in #155
  • docs(spec): state the JSON form of every type each database writes by @colinhacks in #159
  • feat(postgres): infer the result key of a statement by @colinhacks in #160
  • fix(site): format tuples, number literals and wide unions in the cards by @colinhacks in #161
  • perf: decode timestamptz by position and build rows with one object literal by @colinhacks in #164
  • fix(postgres): turn jit off on the engine's connections by @colinhacks in #158
  • feat(engine)!: answer every analysis in the type descriptor format by @colinhacks in #162
  • feat(mapper): give a Standard Query per-column decoders and an output by @colinhacks in #165
  • fix(mapper)!: type an open JSON Schema object by the keys it names by @colinhacks in #169
  • fix(engine): list a json union's literals first and null last by @colinhacks in #170
  • feat(engine)!: add a tagged_union kind and make a union's per optional by @colinhacks in #168
  • feat(postgres): read the fields of a whole row of no catalog type by @colinhacks in #167
  • feat(mapper)!: return NaN and infinities inside postgres json as numbers by @colinhacks in #171
  • fix(engine): widen a built object's nullable field as nullable() does by @colinhacks in #173
  • feat(cli): add reylo analyze by @colinhacks in #172
  • feat(postgres): infer whether a statement's order is total by @colinhacks in #163
  • feat(cli)!: run the reylo binary as the reylo command by @colinhacks in #174
  • docs(spec): move the examples out of the type descriptor spec by @colinhacks in #176
  • feat(postgres): read each column's type modifier by @colinhacks in #180
  • fix(postgres)!: type arrays by their dimensions by @colinhacks in #181
  • chore(release): 0.8.0 by @colinhacks in #175
  • feat: add reylo/rtd, each statement's descriptor in reylo type defs by @colinhacks in #178
  • feat(spec): write the type defs format as its json schema by @colinhacks in #182
  • docs(site): compare reylo with other sql tools from real runs by @colinhacks in #183
  • feat(postgres): read a check that keeps null out of an array by @colinhacks in #166
  • feat(spec): drop the union's per field by @colinhacks in #189
  • feat: export compile from reylo's root, reading the project's database by @colinhacks in #191
  • build: drop the .npmrc release-age opt-outs by @colinhacks in #194
  • feat!: infer keyed writes, read the catalog once, write six-digit times by @colinhacks in #190
  • feat!: add js.codecs and return structured values as objects by @colinhacks in #193
  • feat(spec)!: write durations as one maximum, let ranges span any type by @colinhacks in #177
  • feat(cli): rename compile back to analyze, as a function and a command by @colinhacks in #195
  • fix(spec): read a generated file's line endings as nothing it says by @colinhacks in #185
  • feat!: name the project file's loosening flags allow_, off by default by @colinhacks in #187
  • fix(engine)!: narrow sqlite strict, postgres written params and call by @colinhacks in #186
  • test(oracle): analyze each statement again under other session settings by @colinhacks in #198
  • feat(mapper): write what a type knows into a query's json schema by @colinhacks in #199
  • feat(mysql): read each column's type modifier by @colinhacks in #192
  • chore(examples): record the type check on tsc 7.1.0-dev.20261104.1 by @colinhacks in #196
  • fix(postgres): decode a row with no catalog type as its fields by @colinhacks in #197
  • test(spec): hold the examples and every inferred type to the format by @colinhacks in #188
  • feat(postgres): read bounds, a divisor and a pattern from a check by @colinhacks in #200
  • feat(spec)!: drop the any kind, which opaque already says by @colinhacks in #204
  • fix(postgres): correct key collations, repeated JSON keys, function rows by @colinhacks in #209
  • feat(spec)!: return a statement's result as a rows type by @colinhacks in #184
  • feat(mysql): type a parameter written to a column by its definition by @colinhacks in #210
  • feat!: warn when a query's columns change at run time by @colinhacks in #208
  • feat(spec): say which result columns the rows are sorted on by @colinhacks in #213
  • feat(spec)!: call the database's name for a type its label by @colinhacks in #214
  • fix(mysql): read a value error's (null) column as no column by @colinhacks in #216
  • feat(engine)!: infer through SQL function bodies and table macros by @colinhacks in #211
  • fix(postgres): fix what Postgres 19 RC1 breaks, and two row counts by @colinhacks in #203
  • fix(engine): spell the js.codecs set option Array, as its type by @colinhacks in #202
  • fix: align standard query, empty-array schemas and sqlite strings by @colinhacks in #215
  • ci: run a branch's checks and one platform's binary by hand by @colinhacks in #212
  • fix: keep a row's keys to the columns its type declares by @colinhacks in #207
  • fix: name renamed whole rows by type, read duckdb temp tables first by @colinhacks in #217
  • fix(engine): keep a refused describe's position for the host by @colinhacks in #219
  • test: keep the column-name tests passing on windows by @colinhacks in #220
  • feat!: return one unnamed column's values, not rows by @colinhacks in #206
  • fix: name error types in hovers rather than listing every code by @colinhacks in #221
  • fix(duckdb): decode node-api's getRows() values in a standard query by @colinhacks in #218
  • feat(postgres)!: return an array of a subquery's rows as objects by @colinhacks in #205
  • docs(site): show the query a tool got wrong in each comparison popover by @colinhacks in #223
  • fix: correct run-time values, stale caches and the release job by @colinhacks in #222
  • feat(postgres): expose the types a statement builds to a linking host by @colinhacks in #224
  • fix(engine): close seven soundness holes from the audit by @colinhacks in #225
  • fix(engine): close ten more holes from the audit by @colinhacks in #226
  • perf: add sql hook filters by @TheAlexLichter in #137
  • fix: bundle only the run time of the query's dialect by @colinhacks in #228
  • fix(sqlite): treat % by a fractional literal under 1 as nullable by @fengqiaoqu in #124
  • fix!: read .env files as dotenv does by @colinhacks in #227
  • fix(cli): resolve the project in reylo analyze as the type check does by @colinhacks in #229
  • fix(duckdb): return a GEOMETRY as its WKB bytes by @colinhacks in #231
  • feat: run queries on a knex instance or transaction by @colinhacks in #233
  • fix(mysql): widen table integers beside a nested rollup by @colinhacks in #234
  • fix(engine): type grouped json in scalar subqueries and on mysql by @colinhacks in #230
  • feat(sqlite): run queries on SQLite Wasm's oo1 databases by @colinhacks in #235
  • feat(spec): type a uuid's version, keep integer bounds inclusive by @colinhacks in #232
  • fix(spec): build the uuid example from functions postgres 17 has by @colinhacks in #237
  • feat(cli): print reylo analyze's descriptors as yaml by default by @colinhacks in #239
  • fix(sqlite): close eight soundness holes in the engine by @colinhacks in #238
  • fix(postgres): list the error code postgres 19 adds by @colinhacks in #240
  • fix(sqlite): check durable object projects on cloudflare's sqlite by @colinhacks in #241
  • fix!: stop four silent wrong answers at run time and build time by @colinhacks in #242
  • fix(duckdb): read keys from a table's text where constraints fail by @colinhacks in #245
  • fix: load the build-time half where turbopack does not trace it by @colinhacks in #244
  • refactor(postgres): bound statement depth with the engine's own limit by @colinhacks in #246
  • fix(sqlite): type an integer literal past 64 bits as a real by @colinhacks in #248
  • fix(mapper): keep tsc's type errors beside an error in a .sql file by @colinhacks in #249
  • feat(spec)!: add tables, enums, intersections and character sets by @colinhacks in #243
  • feat(cli)!: add migrations by @colinhacks in #179
  • fix(cli): show project paths with forward slashes on windows by @colinhacks in #252
  • feat: export migrate from reylo's root, for servers and durable objects by @colinhacks in #253
  • feat(engine): infer distinct arrays, regex patterns and field comments by @colinhacks in #251
  • feat(duckdb): load extensions once and open files read-only to read by @colinhacks in #250
  • feat: read decimals as decimal.js, bignumber.js or big.js values by @colinhacks in #247
  • feat(cli)!: refuse what db push drops until --drop or --force accepts it by @colinhacks in #256
  • feat: type sql in rust with #[reylo::query] and reylo generate by @colinhacks in #255
  • test: read the rust fingerprint copies as lf on windows by @colinhacks in #257
  • feat!: add vector, geometry and inet codecs and read money as a decimal by @colinhacks in #254
  • refactor!: remove duckdb support by @colinhacks in #258
  • test: skip the vector codec test on a server without pgvector by @colinhacks in #260
  • fix(mysql): name mariadb when a server refuses mysql2's prepares by @colinhacks in #261
  • chore(release): 0.8.0-beta, staged under npm's beta tag by @colinhacks in #262
  • feat(spec): say what each statement may do as its capabilities by @colinhacks in #259
  • chore(release): 0.8.0-beta.0 by @colinhacks in #263

New Contributors

Full Changelog: v0.7.0...v0.8.0-beta.0

Don't miss a new reylo release

NewReleases is sending notifications on new releases.