v0.5.0

This release connects TinyJoin to the Drizzle ORM and the Kysely query builder, and adds what they need: a typed schema API, foreign keys, fuller ALTER TABLE, and more of the SQL dialect that hand-written statements and query builders use. It also fixes two v0.4.0 bugs, and reads rows faster.

Fixes since v0.4.0

A database with a column declared DEFAULT NULL opens again. v0.4.0 and earlier wrote such a column's default into a persistent database correctly, but read it back as no default at all, so the catalog no longer matched what had been written, and opening the database failed with PAGED_STORAGE_CORRUPT. v0.5.0 reads the default as it was written, so an affected v0.4.0 database opens with its data intact. In-memory databases, which are never reopened, were not affected.

Adding a NOT NULL column without a default to a table with rows fails again. v0.4.0 stopped writing every row again when ALTER TABLE ... ADD COLUMN adds a column, and with that stopped checking that the rows could hold it, so a NOT NULL column without a default could be added to a table with rows, which then held NULL in it: queries returned the NULL, and check() reported the table as corrupt. v0.5.0 refuses the statement with CONSTRAINT_VIOLATION, as v0.3.0 and PostgreSQL do. To repair a table that v0.4.0 left this way, give the column a value in every row, as in UPDATE t SET c = 0 WHERE c IS NULL, or drop its NOT NULL.

ORM support

The new tinyjoin/drizzle entry point connects the Drizzle ORM to a TinyJoin Client: drizzle(client, {schema}) returns a Drizzle database whose queries run in TinyJoin's dialect, whose transactions are TinyJoin's callback transactions, and whose JSON columns store JSON values rather than JSON text. The application installs drizzle-orm 0.45 itself; TinyJoin declares it as an optional peer dependency and never bundles it, so the download sizes are unchanged.

The entry point's migrate() function applies the migrations that drizzle-kit generate writes, from SQL the application bundles, each atomically with the row that records it, so every tab can call it as it starts. Drizzle Kit's push and Drizzle's own migrators are not supported. See the Drizzle guide.

The new tinyjoin/kysely entry point connects the Kysely query builder to a TinyJoin Client. Its TinyJoinDialect, constructed with {client}, is a Kysely dialect whose queries run in TinyJoin's dialect, whose transactions are TinyJoin's callback transactions, whose introspector reads getSchema(), and under which Kysely's Migrator runs, one tab at a time. The application installs kysely 0.28 or later itself; TinyJoin declares it as an optional peer dependency and never bundles it. See the Kysely guide.

create-tinyjoin v0.5.0 asks how its starter app writes its queries: in SQL, or with the Drizzle ORM or the Kysely query builder. Its Drizzle app pushes a Drizzle schema as each tab starts, and its Kysely app creates its table in a migration that Kysely's Migrator runs.

Reading and setting the schema

The new getSchema() method on the Client reads the database's tables, with their columns, primary keys, indexes, and foreign keys, as typed objects rather than through SQL catalog tables, which TinyJoin does not have. Each column carries its runtime type, whether it is nullable, any literal default, and any VARCHAR length. See reading the schema.

The new setSchema() method makes the database's tables match a schema in the shape getSchema() returns, in one atomic change: it creates what is missing, renames tables and columns from the names they had, changes defaults, nullability, and VARCHAR lengths, and makes each table's indexes the schema's, keeping every row, and drops what the schema leaves out only when asked. A schema the database already has changes nothing, and a schema version keeps a tab running older code from undoing newer changes. tinyjoin/drizzle adds push(), which sets a Drizzle schema this way, in place of migrations. See setting the schema.

Foreign keys

Foreign keys are now enforced. A column's REFERENCES, or a table's FOREIGN KEY ... REFERENCES, names the table and columns its values must be found in, unless one is NULL, and each key is checked as its statement ends, inside transactions too. Deleting or updating a referenced row does what the key's ON DELETE and ON UPDATE actions say: NO ACTION and RESTRICT refuse it, CASCADE deletes or updates the referencing rows in the same statement, and SET NULL and SET DEFAULT change their references. ALTER TABLE adds a key, checking every stored row, and drops one; renames follow into the keys; and dropping a table, column, or unique index a key needs takes CASCADE, which drops the key. getSchema() and setSchema() include each table's keys, and Drizzle's .references() pushes as one. See foreign keys.

Creating and altering tables

CREATE TABLE accepts the constraints and spellings that schema tools write: a named primary key, as in CONSTRAINT tags_pk PRIMARY KEY (post_id, tag); UNIQUE on a column or as a table constraint, which creates a unique index named as PostgreSQL would name it; a JSON default written as '{}'::jsonb; and USING btree on an index. ALTER TABLE can add and drop a unique constraint, and accepts DISABLE ROW LEVEL SECURITY, which changes nothing. DROP TABLE and DROP INDEX accept CASCADE and RESTRICT. Together these run the SQL that drizzle-kit generate writes for new tables. See constraints and indexes.

ALTER TABLE can now also rename a table or a column, drop a column, set or drop a column's default, and set or drop NOT NULL, and accepts a column type that restates the runtime type it has, as bigint does for integer. Existing rows keep their values: dropping a column, changing a default, and setting NOT NULL rewrite the table's rows, and SET NOT NULL fails, changing nothing, while a row holds NULL. A dropped column takes every index on it with it, as in PostgreSQL, and indexes follow renamed columns and tables.

A VARCHAR(n) or CHARACTER VARYING(n) column holds at most n characters, and a longer value fails with CONSTRAINT_VIOLATION, as in PostgreSQL, except that TinyJoin also refuses a value whose excess is spaces, which PostgreSQL would trim. n was previously rejected, so schemas that ORMs and migration tools write with lengths, such as Drizzle's varchar({length}) and Kysely's migration tables, now run. getSchema() reports the length as maxLength.

Naming tables and columns

A statement over one table can qualify its columns with the table's name, as a join already could: SELECT tasks.id FROM tasks WHERE tasks.done = false ORDER BY tasks.title, or tasks.* for every column. UPDATE and DELETE accept the same in WHERE and RETURNING, and INSERT in RETURNING. A qualified statement is planned exactly as its plain spelling is, so it uses the same key lookups and index ranges. In ORDER BY, a qualified name is always the table's column, where a plain name matches an output alias first. See qualified columns.

A single-table SELECT, UPDATE, or DELETE also accepts a table alias, with or without AS, as each table of a join already did: SELECT t.id FROM tasks AS t WHERE t.done = false, or DELETE FROM tasks t WHERE t.id = $1. As in PostgreSQL, the alias replaces the table's name as the qualifier. Results, changed tables, and subscriptions still name the table.

A SELECT read with rowMode: "array" may now return several fields of one name, such as the id of each table in a join, because an array row holds its values by position. Object rows still require distinct output names and reject the same statement with INVALID_QUERY, as before. Query builders that map rows by position, as Drizzle does, depend on this. See repeated output names.

A join's select list can use * for every column of every table and table.* for every column of one, as in SELECT p.*, u.name FROM posts p JOIN users u ON u.id = p.user_id, and a single table's can put * beside other outputs, as in SELECT *, price * quantity AS total FROM items. Object rows still need distinct names, so a * over tables that share a column name needs array rows.

Expressions

An UPDATE or ON CONFLICT DO UPDATE assignment can now work its value out from the row it updates, so a counter no longer needs a read before its write: UPDATE counters SET hits = hits + 1 WHERE id = $1. An expression combines columns, literals, and parameters with +, -, *, /, %, unary -, and ||. Integers stay within the JavaScript-safe range and divide by truncating, floats must stay finite and take no %, and there are no implicit casts. In an upsert, the stored row's columns are named with the table's name and the proposed row's with EXCLUDED, as in SET hits = counters.hits + EXCLUDED.hits. Types are checked before any row is read.

Either side of a WHERE comparison can now be an expression too, so a query can compare two columns, as in WHERE updated > created, or a value worked out from the row, as in WHERE price * quantity > $1. A comparison of a column with a value worked out from literals and parameters, such as created > $1 - 86400, still reads only a range of an index or the primary key.

A single-table or join query can also return expressions, as in SELECT id, price * quantity AS total FROM items ORDER BY total DESC, and ORDER BY can name an expression's alias. An expression without an alias is named ?column?, as in PostgreSQL. Aggregate and SELECT DISTINCT queries still return only columns and aggregates. See expressions.

Subqueries and nested queries

IN and NOT IN can take the values of a subquery, as in WHERE user_id IN (SELECT id FROM users WHERE team = $1), in any statement with a WHERE clause. The subquery runs once, before the statement reads any row, must return one column and at most 1,024 rows, and cannot read the statement around it. See subqueries.

A query can nest queries that read each of its rows, as ORMs write them to load related rows: a (SELECT ...) in the select list that returns one value, as in (SELECT count(*) FROM posts p WHERE p.user_id = u.id), and a LEFT JOIN LATERAL (SELECT ...) alias ON true. Their rows can be gathered into JSON with json_agg, json_build_array, to_json, and coalesce(..., '[]'), including from a query in FROM (SELECT ...) alias. Each nested query runs once for every row around it, up to 100,000 in a statement. Drizzle's relational queries with with, and Kysely's jsonArrayFrom and jsonObjectFrom, run this way. See nested queries.

Speed and download size

The warm-up Worker that create() starts runs its loops three-quarters as long, and so finishes sooner. It was still running while a database ran its first statements, and slowed them: in the comparative benchmark, timed workloads now run about 5% faster overall, point reads about 8% faster, and indexed range aggregates about a fifth faster.

A single-table SELECT whose rows arrive in key order no longer builds each row as an object of its values under their names before the Worker writes the row out. Reading all 10,000 rows of the comparative benchmark takes two-fifths less time, and a point read by key about 3% less, for 1.2 KiB more of compressed engine.

The compressed download is now 339 KiB, up from 301 KiB in v0.4.0, for the engine's new SQL, foreign keys, and schema API. The Drizzle and Kysely entry points, and the libraries they connect, load only in an application that imports them.

Upgrading from v0.4.0

No action is needed. Install, rebuild, and redeploy. The page format stays at format 3, so an existing database opens with no migration and no OPFS namespace change. A database that holds a VARCHAR(n) column, a schema version from setSchema(), or a foreign key cannot be opened by v0.4.0, which reports its catalog as corrupt, so do not roll back past v0.5.0 once a database holds any of them.

The Worker protocol moves from version 10 to 11 for the schema request. Client and Worker ship together and are upgraded together by a normal install, so this affects only a deployment that pins or caches a Worker file independently of the client bundle, where a mismatched pair fails cleanly with PROTOCOL_MISMATCH.

The new syntax was previously rejected, so statements that already worked keep their meaning, with one exception: where a table has a column whose quoted name begins with the table's own name and a dot, such as "extra.value" in a table named extra, an unquoted extra.value in WHERE used to read that column and now reads the table's value column. Quote the name to read it.