# TinyJoin full reference

All guides and documented public TypeScript declarations from this build. Source links identify the corresponding website pages.

# Getting started

Source: https://tinyjoin.org/guides/getting-started/

TinyJoin is a relational database that runs locally in a browser. The normal
setup is one import and one asynchronous call.

For an in-memory database in Node.js, use the separate
[`tinyjoin/node` entry point](https://tinyjoin.org/guides/node/).

## Install

```sh
npm install tinyjoin
```

TinyJoin publishes JavaScript, TypeScript declarations, its Worker runtime, and
the WebAssembly engine together. An application does not need Rust tooling or a
separate Worker plugin.

For a new application, `npm create tinyjoin@latest` generates a working Vite
starter. Its production build includes [offline loading](https://tinyjoin.org/guides/offline/).

```sh
npm create tinyjoin@latest
```

Follow the prompts to choose JavaScript or TypeScript and how to store your data.

## Open a database

TinyJoin is experimental and verified on Chromium only. Browser storage can
be lost, so start with data you can reconstruct. Read the
[caveats](https://tinyjoin.org/guides/caveats/) before adopting persistent storage.

```ts
import {create} from 'tinyjoin';

const db = await create('opfs://my-app');
```

The Promise resolves after the Worker and database are ready. The string gives
the persistent database a stable local name. Call create() without an
argument while experimenting with an ephemeral database.

Use the same name in every tab. TinyJoin automatically shares one database
owner and delivers committed-change notifications across those Clients. See
[storage and tab handover](https://tinyjoin.org/guides/storage-and-lifecycle/) for lifecycle details.

## Create a table

Schema setup can be idempotent, so the same application startup works for a new
or existing database:

```ts
await db.exec(`
  CREATE TABLE IF NOT EXISTS notes (
    id TEXT PRIMARY KEY,
    body TEXT NOT NULL,
    pinned BOOLEAN NOT NULL DEFAULT false
  )
`);
```

exec() accepts a parameter-free script and commits all of it together. Use it
for schema setup. TinyJoin requires every SQL-created table to have a primary
key.

## Write with parameters

```ts
await db.query('INSERT INTO notes (id, body) VALUES ($1, $2)', [
  crypto.randomUUID(),
  'Hello from the browser',
]);
```

Keep application values in the parameter array. TinyJoin parameters are
one-based (`$1`, `$2`, and so on) and accept JSON-compatible values.

## Read typed rows

```ts
type Note = {
  id: string;
  body: string;
  pinned: boolean;
};

const {rows} = await db.query<Note>(
  'SELECT id, body, pinned FROM notes ORDER BY id',
);
```

The generic describes the expected result to TypeScript; it does not validate
rows at runtime. Use an explicit projection when callers require a stable
shape.

## Close cleanly

```ts
window.addEventListener(
  'pagehide',
  () => void db.close().catch(console.error),
  {once: true},
);
window.addEventListener('pageshow', (event) => {
  if (event.persisted) {
    window.location.reload();
  }
});
```

Closing releases this Client's prepared statements and Worker. Other Clients
for the same name continue, with automatic owner handover when needed. Closing
is asynchronous and safe to call more than once.

Closing cannot be undone. This example reloads a page restored from the
back/forward cache so startup creates a fresh Client. A browser does not await
pagehide cleanup; finish and await writes during normal use. Applications
with their own lifecycle can reopen and recreate their database state instead
of reloading. See [storage and lifecycle](https://tinyjoin.org/guides/storage-and-lifecycle/).

The [Todo starter demo](https://tinyjoin.org/demos/todo-starter/) puts these calls together in a
small browser application.

# Caveats

Source: https://tinyjoin.org/guides/caveats/

TinyJoin is small on purpose, and being small has costs. This page collects
what an application signs up for, in one place, so that the decision gets made
before the schema does.

If any of these are unacceptable, there are
[more mature projects](https://tinyjoin.org/guides/caveats/#if-tinyjoin-is-not-the-right-fit) that solve the same
problem with different trade-offs.

## It is experimental

TinyJoin is at v0.5.0. The JavaScript API, the SQL dialect, the error codes,
and the page format can all change between releases, and a release may require
an application to recreate its persistent database rather than migrate it.

Version the OPFS name alongside the schema generation, as in
`opfs://my-app-v1`, so that a breaking change becomes a new database rather
than a broken one. The [release notes](https://tinyjoin.org/guides/releases/) record each release's
compatibility boundary.

## The SQL is a bounded subset

TinyJoin implements its own PostgreSQL-shaped dialect. It is not PostgreSQL
compiled to WebAssembly, and familiar syntax is implemented only where the
smaller runtime can give it clear and deterministic semantics. Unsupported
forms are rejected explicitly rather than silently reinterpreted.

The omissions most likely to matter are:

- Subqueries only as an uncorrelated `IN (SELECT ...)` of up to 1,024 values,
  and as nested queries in a select list or `LATERAL` join, which run once for
  each row; no `EXISTS`, CTEs, or `UNION`/`INTERSECT`/`EXCEPT`.
- Arithmetic and `||` only in assignments, `WHERE` comparisons, and the select
  lists of queries that do not aggregate, and no casts, `CASE`, or scalar
  functions.
- No regular expressions, full-text search, `ANY`/`ALL`, or JSON path
  operators. `LIKE` and `ILIKE` scan rather than use an index, and `ILIKE`
  folds only ASCII letters.
- No `INSERT ... SELECT`, `MERGE`, or `UPDATE ... FROM`.
- No sequences, `SERIAL`, or generated identity. Generate text identifiers in
  the client.
- No `NUMERIC`/`DECIMAL`, date, time, interval, `UUID`, `BYTEA`, array, enum,
  or user-defined types. Five runtime types cover boolean, integer, float,
  text, and JSON.
- No `HAVING`, distinct or filtered aggregates, window functions,
  `RIGHT`/`FULL`/`CROSS` joins, views, or triggers.

The [SQL compatibility contract](https://tinyjoin.org/guides/sql-compatibility/) is the exact list.
Read it before designing a schema, not after.

## Multiple tabs share one writer

Clients using the same OPFS name automatically share one database-owning
Worker. Election, routing, prepared statement restoration, and cross-tab
notifications are internal. Different names and browser storage partitions
remain independent.

This is one serialized engine. An open transaction holds other Clients until
its callback finishes, and a frozen live owner or transaction holder can delay
other tabs until it resumes or closes. Keep callbacks short. Closing or losing
the owner triggers automatic election, but requests already sent to it fail
with `LEADER_CHANGED` and are never silently replayed. Incompatible releases
fail with `DATABASE_VERSION_MISMATCH`; old versions that do not coordinate
can still hold the underlying OPFS lock. See
[tab handover](https://tinyjoin.org/guides/storage-and-lifecycle/#tab-handover-and-subscriptions).

## It needs a modern browser

The browser entry point requires WebAssembly and dedicated module Workers, and persistence
additionally requires a secure context, OPFS synchronous access handles, Web
Locks, and BroadcastChannel. It
deliberately does not use `SharedArrayBuffer`, so a page does not need
cross-origin isolation headers.

The separate [Node entry point](https://tinyjoin.org/guides/node/) supports in-memory databases
in Node.js 22 or later. It does not provide OPFS or filesystem persistence.

The automated browser suite currently runs on Chromium only. Firefox and WebKit
are not verified, and a successful TypeScript or Vite build says nothing about
them. Test the browsers an application actually targets.

There is also no fallback to IndexedDB or memory when persistent storage is
unavailable, locked, corrupt, or out of quota. create() rejects instead.

TinyJoin does not automatically restore a Client closed during page teardown.
If the browser restores that page from its back/forward cache, the application
must reopen its Client and recreate statements and subscriptions, or reload
the page. The [lifecycle guide](https://tinyjoin.org/guides/storage-and-lifecycle/#opening-and-closing)
describes this boundary.

## Browser storage is not durable storage

OPFS is browser-managed. A user can clear it, and a browser may evict
best-effort storage under pressure. A `navigator.storage.persist()` request is
a request, not a guarantee, and TinyJoin does not make that product decision
during startup.

Treat a TinyJoin database as reconstructable local state. Data that has to
survive needs a copy the application controls. TinyJoin can check a whole
database with check(), but cannot yet repair one that fails.

A storage or commit-result failure can leave a write's outcome uncertain.
`RECOVERY_REQUIRED`, `STORAGE_COMMIT_OUTCOME_UNKNOWN`, and
`STORAGE_ENGINE_POISONED` require closing and reopening the Client. Reconcile
the recovered rows with stable operation identifiers before any replay;
`retryable` does not guarantee that replay is safe. The
[recovery guide](https://tinyjoin.org/guides/storage-and-lifecycle/#recovering-after-an-uncertain-write)
explains how to preserve that distinction.

## Transactions need bounded callbacks

Nested callback transactions are unsupported: awaiting another transaction()
on the same Client inside its callback deadlocks. Pass the active Transaction
to helpers. There is no AbortSignal or built-in timeout; Promise.race() stops
waiting without cancelling work, which can still commit. Read the
[transaction guide](https://tinyjoin.org/guides/transactions-and-changes/#composing-transaction-helpers)
before composing asynchronous application work.

## There is no server, and no sync

TinyJoin has no PostgreSQL wire protocol, server process, roles or grants,
system catalogs, extensions, stored procedures, WAL, replication, or
point-in-time recovery. getSchema() reads its tables, columns, keys, and
indexes in place of a catalog. It does not synchronize with a remote database and does
not propagate offline writes. Its storage is TinyJoin's own page format, not a
PostgreSQL data directory.

## The limits are hard limits

Oversized work fails explicitly rather than growing until the tab dies. A
persistent database is bounded to 256 MiB, one query returns at most 100,000
rows, a join chains at most eight table sources, and SQL text, parameters,
prepared statements, and working memory each have a named bound. The complete
list is in [hard limits](https://tinyjoin.org/guides/sql-compatibility/#hard-limits).

Multi-tab routing also bounds each Client's pending requests and the owner's
waiting queue to 256 requests and approximately 8 MiB each, with a small
reserved allowance for transaction cleanup. Hitting these bounds rejects with
`RESOURCE_LIMIT`; await work or batch related writes. Prepared engine limits
are shared across connected Clients; at most 128 Clients can attach to an owner.

That boundedness is deliberate, but it does mean TinyJoin is sized for
application state rather than for analytics over a large dataset.

## Performance depends on the workload

Keep transactions short and request only the data your UI needs. Measure
populated startup and larger batches with your application's data and target
devices.

The [transaction guide](https://tinyjoin.org/guides/transactions-and-changes/#inserting-many-rows)
explains batching and validation costs. The
[workload report](https://github.com/tinyplex/tinyjoin/tree/main/benchmarks)
records detailed measurements, runtime hashes, and reproduction commands for
mixed transactions, populated startup, and result materialization. Those
measurements cover one desktop and Chromium version.

## If TinyJoin is not the right fit

These projects are larger, more mature, or both, and are the better answer when
the caveats above are not acceptable:

- [PGlite](https://pglite.dev/) is real PostgreSQL compiled to WebAssembly,
  with the full dialect and extensions. Choose it when genuine PostgreSQL
  compatibility matters more than download size.
- [SQLite Wasm](https://sqlite.org/wasm/) is the official SQLite build for the
  browser, with an OPFS backend, decades of stability, and a much larger SQL
  surface.
- [wa-sqlite](https://github.com/rhashimoto/wa-sqlite) is SQLite for the
  browser with pluggable storage backends, including ones designed for
  concurrent tabs.
- [DuckDB-Wasm](https://github.com/duckdb/duckdb-wasm) is columnar analytics in
  the browser, for aggregate queries over large datasets.
- [Dexie](https://dexie.org/) is a typed IndexedDB wrapper, for when relational
  SQL is not the requirement and broad browser support is.
- [TinyBase](https://tinybase.org/) is a reactive data store with persistence
  and synchronization, for when the requirement is local-first sync rather than
  SQL.

None of that is a criticism of those projects. TinyJoin exists because a useful
subset of the same problem fits in a much smaller download.

# SQL compatibility

Source: https://tinyjoin.org/guides/sql-compatibility/

TinyJoin implements its own deliberately bounded, PostgreSQL-shaped SQL
dialect. It is not PostgreSQL compiled to WebAssembly, a PostgreSQL server, or
a general PostgreSQL replacement. Familiar syntax is used where the smaller
runtime can give it clear and deterministic semantics.

This document is the compatibility contract for the current dialect. A form
not listed as supported here is unsupported, even if its keywords happen to be
accepted by PostgreSQL. Unsupported forms fail explicitly rather than being
silently reinterpreted.

## JavaScript entry point

SQL is the primary relational interface. The basic lifecycle has four calls:

```ts
import { create } from "tinyjoin";

const db = await create();

await db.exec(`
  CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    done BOOLEAN NOT NULL DEFAULT false
  );
`);
await db.query("INSERT INTO tasks (id, title) VALUES ($1, $2)", [
  1,
  "Write the compatibility contract",
]);

const { rows } = await db.query<{ id: number; title: string }>(
  "SELECT id, title FROM tasks WHERE done = $1",
  [false],
);

await db.close();
```

Calling create() opens the Worker-backed database and resolves after
initialization. With no argument, or `memory://`, storage is ephemeral. A named
`opfs://database-name` data directory opts into persistent browser storage.
The client also exposes read-only `ready`, `waitReady`, and `closed`
properties.

query(sql, params?, options?) executes one read or write statement with
optional JSON-compatible `$1` parameters. exec(sql, options?) executes one or
more statements without parameters as one implicit transaction and returns one
result per statement. Both use `{rows, fields, affectedRows?, command?,
rowCount?}` results; TinyJoin adds `revision` and `tables`. `fields` contains
ordered `{name, dataTypeID}` entries, including for empty typed results. Each
object row lists its keys in that field order, and `rowMode: "array"` returns
its values in that order. The `sql` tagged template
is a parameterizing form of query(). `rowMode` is the only query option
implemented today; parser, serializer, notice, parameter-type, and blob
options are rejected. The tag accepts parameter values only and does not
provide raw-SQL, identifier, or nested-template helpers.
close() is asynchronous and idempotent.

Interactive atomicity uses transaction(callback), not SQL transaction
statements:

```ts
await db.transaction(async (tx) => {
  await tx.query("UPDATE tasks SET done = true WHERE id = $1", [1]);
  await tx.query("INSERT INTO tasks (id, title) VALUES ($1, $2)", [
    2,
    "Committed together",
  ]);
});
```

Invalidation subscriptions and Worker/storage configuration form the small
JavaScript control surface around the SQL-first engine; relational reads,
writes, and schema changes use SQL.

## Prepared statements

Use a prepared statement when the same parameterized read or row mutation will
execute repeatedly:

```ts
const tasksByDone = await db.prepare<{
  id: number;
  title: string;
  done: boolean;
}>("SELECT id, title, done FROM tasks WHERE done = $1 ORDER BY id");
const setTaskDone = await db.prepare(
  "UPDATE tasks SET done = $1 WHERE id = $2",
);

const { rows } = await tasksByDone.execute([false]);

await db.transaction(async (tx) => {
  await tx.execute(setTaskDone, [true, 1]);
  console.log((await tx.execute(tasksByDone, [true])).rows);
});

await tasksByDone.close();
await setTaskDone.close();
```

prepare<Row>(sql) parses and retains one `SELECT`, aggregate, join, `INSERT`,
`UPDATE`, or `DELETE` statement in the Worker. It rejects DDL, an empty string,
and more than one statement. The returned PreparedStatement<Row> has
execute(params?, options?), asynchronous idempotent close(), and a read-only
`closed` property. execute() returns the same Results<Row> shape as
query() and accepts the same sole option, `rowMode`.

The prepared parameter count is the highest referenced `$n`. Every execution
must provide exactly that many JSON-compatible values; a numbering gap still
occupies a slot. Binding or execution failure leaves the handle open for a later
valid execution. exec() remains parameter-free and is not a prepared-script
API.

Preparation retains parsed syntax and parameter positions, not a schema
snapshot. Every execution resolves tables and columns and validates types
against the current catalog and, inside a transaction, its current staged view.
A compatible DDL change is transparent. An incompatible change returns the
ordinary current table, column, constraint, or type error and does not make the
handle permanently stale. Consequently, `SELECT *` or `RETURNING *` can expose
new fields after `ALTER TABLE ... ADD COLUMN`, including a new positional value
in array row mode. Use an explicit projection when callers require a stable
result shape. As with query<Row>(), the Row generic is a compile-time cast,
not runtime result validation.

A prepared statement is session-local: it belongs to the client that created
it, is not stored in OPFS, cannot be used by another client, and does not survive
db.close() or a Worker restart. Prepare handles before entering a callback
transaction. Inside the callback, use
tx.execute(statement, params?, options?); it accepts only an open statement
from the same client and participates in the same staged commit or rollback as
tx.query(). Direct db.prepare(), statement.execute(), and
statement.close() calls are blocked while that client's transaction callback
is active.

Calling statement.close() seals it immediately, rejects new executions, waits
for executions that already started, and then releases its Worker resources.
Concurrent close calls share the same cleanup, and a cleanup failure does not
reopen the handle. Closing the database seals and releases every remaining
prepared statement. At most 128 handles and 8 MiB of conservatively accounted
prepared-statement state may be retained by one open database.

## How to read the matrices

- **Supported** means the exact form described here is implemented and tested.
- **Narrow** means TinyJoin implements a useful but intentionally smaller form
  than PostgreSQL.
- **No** means the form is rejected.

These labels do not claim compatibility with a particular PostgreSQL release.

## Statements and clauses

| Keyword or form | Status | TinyJoin form and boundary |
| --- | --- | --- |
| `SELECT ... FROM` | Narrow | One table, an aggregate over one table, or a left-deep join over two to eight typed table sources. A single-table projection is `*` or a list of columns and [expressions](https://tinyjoin.org/guides/sql-compatibility/#expressions), each optionally renamed with `AS`. A statement over one table may [qualify its columns](https://tinyjoin.org/guides/sql-compatibility/#qualified-columns) with that table's name or alias. A join's projection lists columns, expressions, `*` for every column of every table, and `table.*` for every column of one; so may a single-table projection, beside `*`. Output names must be distinct unless the rows are [read as arrays](https://tinyjoin.org/guides/sql-compatibility/#repeated-output-names): otherwise duplicates return `INVALID_QUERY`, including for empty results and `LIMIT 0`. There is no `SELECT` without `FROM`. |
| `WHERE` | Supported | Predicates described below, with SQL three-valued null logic. |
| `ORDER BY` | Narrow | Up to 32 plain or [qualified](https://tinyjoin.org/guides/sql-compatibility/#qualified-columns) columns or projected output names for simple queries, projected output names for grouped/aggregate queries, and projected output names or qualified/unambiguous source columns for joins; `ASC`/`DESC` and `NULLS FIRST`/`LAST`. An output name takes precedence over a source column with the same name. JSON values cannot be ordered. |
| `LIMIT`, `OFFSET` | Supported | Non-negative integer literal or `$n` parameter. `LIMIT` is at most 100,000; `OFFSET` and `OFFSET + LIMIT` are at most 4,294,967,295. `OFFSET` may appear alone; when both occur, `LIMIT` must precede `OFFSET`. |
| `GROUP BY` | Narrow | Up to 32 plain boolean, integer, float, or text columns (not JSON) on one typed table. Every selected non-aggregate column must be grouped explicitly. |
| `COUNT`, `SUM`, `AVG`, `MIN`, `MAX` | Narrow | Every aggregate query requires a typed column catalog, including `COUNT(*)`. Functions accept `COUNT(*)` or one plain column argument. `SUM`/`AVG` accept integer or float; `MIN`/`MAX` accept integer, float, or text. Up to 64 aggregate calls. |
| `HAVING`, aggregate `DISTINCT`, `FILTER`, windows | No | No post-group predicate, distinct aggregate, filter clause, or window form. |
| `JOIN`, `INNER JOIN` | Narrow | Adds one typed table to a left-deep chain of at most eight sources. Each `ON` has one or more column equalities joined by `AND`, with at most 32 across the query; every equality connects the incoming source to an earlier source. |
| `LEFT [OUTER] JOIN` | Narrow | The same bounded chain; an unmatched incoming source is represented by `NULL` columns. A later inner join can remove that null-extended row. |
| `LEFT JOIN LATERAL (SELECT ...) alias ON true` | Narrow | A query that runs for each row, as a [nested query](https://tinyjoin.org/guides/sql-compatibility/#nested-queries), and gives it at most one row of columns to return. |
| `RIGHT`, `FULL`, `CROSS`, `NATURAL`, `USING`, other `LATERAL` joins | No | No additional join families, parenthesized relations, or join reordering. |
| `AS` | Narrow | Output aliases on `SELECT` items in single-table, grouped/aggregate, and join queries, plus a table alias on every table a `SELECT` reads and on the table of an `UPDATE` or `DELETE`. A projection alias requires the `AS` keyword, and one column may be returned under several aliases. A table alias may omit `AS`. `INSERT` does not accept a table alias, and no alias takes a column list. |
| `SELECT DISTINCT` | Narrow | Removes duplicate rows from an explicit single-table or join projection. `NULL`s compare as equal to each other, and other values compare by SQL equality; JSON columns are rejected. `ORDER BY` must name projected columns: by output name in a single-table query, and by output name or projected source column in a join. A single-table `DISTINCT` compares at most 32 distinct source columns and shares the aggregate group limits. `DISTINCT *`, `DISTINCT ON`, and `DISTINCT` combined with `GROUP BY` or aggregate functions are rejected. |
| `IN (SELECT ...)` | Narrow | An uncorrelated [subquery](https://tinyjoin.org/guides/sql-compatibility/#subqueries) returning one column of at most 1,024 rows. |
| `(SELECT ...)` in a select list, `FROM (SELECT ...) alias` | Narrow | A [nested query](https://tinyjoin.org/guides/sql-compatibility/#nested-queries), which may read the row around it: one value for each row, or the rows of a query, gathered into JSON. |
| `WITH`, other subqueries, `UNION`/`INTERSECT`/`EXCEPT` | No | No CTEs, `EXISTS`, subqueries in `WHERE` that read the statement around them, or set operations. |
| `CREATE TABLE [IF NOT EXISTS]` | Narrow | Typed columns, a required inline or table-level primary key, and `UNIQUE` constraints. Up to 256 columns. |
| `PRIMARY KEY` | Narrow | One inline single-column declaration or one table-level column list (single or composite), which `CONSTRAINT name` may name. It implies `NOT NULL`; JSON keys are rejected. |
| `UNIQUE` | Narrow | A column's `UNIQUE`, or a table's `[CONSTRAINT name] UNIQUE (columns)`, creates a unique index of that name. An unnamed one is named as PostgreSQL names it, for its table and columns, as in `users_email_key`. The columns follow the `CREATE UNIQUE INDEX` rules. |
| `REFERENCES`, `FOREIGN KEY` | Narrow | A column's `REFERENCES table [(column)]`, or a table's `[CONSTRAINT name] FOREIGN KEY (columns) REFERENCES table [(columns)]`, with `ON DELETE` and `ON UPDATE` actions, checked as each statement ends. See [foreign keys](https://tinyjoin.org/guides/sql-compatibility/#foreign-keys). |
| `CHECK` | No | Check constraints are rejected, inline or as table constraints. |
| `NULL`, `NOT NULL`, `DEFAULT` | Narrow | String, number, boolean, or `NULL` literal defaults, and a string cast to JSON, as in `DEFAULT '{}'::jsonb`. No other casts, default expressions, functions, sequences, or parameters. |
| `CREATE [UNIQUE] INDEX [IF NOT EXISTS]` | Narrow | One or more boolean, integer, or text columns. `USING btree` is accepted, since every index is a B-tree; no other methods, expressions, predicates, `INCLUDE`, ordering, or concurrent build. |
| `ALTER TABLE ... ADD [COLUMN] [IF NOT EXISTS]` | Narrow | Adds one non-primary-key column and atomically backfills its literal default or `NULL`. On a nonempty table, `NOT NULL` requires a non-null default. The column cannot declare `UNIQUE`; add the constraint after it. |
| `ALTER TABLE ... ADD CONSTRAINT ... UNIQUE`, `ADD [CONSTRAINT ...] FOREIGN KEY`, `DROP CONSTRAINT [IF EXISTS]` | Narrow | Adds a unique constraint, as `CREATE UNIQUE INDEX` would, or a foreign key, which every stored row must already meet, or drops a foreign key or unique constraint by name. |
| `ALTER TABLE ... ALTER [COLUMN] ... SET DEFAULT`, `DROP DEFAULT` | Narrow | A literal default, as `DEFAULT` takes. Existing rows keep their values, so the table's rows are rewritten. |
| `ALTER TABLE ... ALTER [COLUMN] ... SET NOT NULL`, `DROP NOT NULL` | Narrow | `SET NOT NULL` rewrites the table's rows, and fails with `CONSTRAINT_VIOLATION`, changing nothing, if a row holds `NULL`. A primary-key column stays `NOT NULL`. |
| `ALTER TABLE ... ALTER [COLUMN] ... [SET DATA] TYPE` | Narrow | Only to another spelling of the column's runtime type, as `bigint` for an `integer` column, which changes nothing, or to a `VARCHAR` of another length, which checks every row when it shortens. No conversion or `USING`. |
| `ALTER TABLE ... DROP [COLUMN] [IF EXISTS]` | Narrow | Drops a column outside the primary key, with every index and foreign key of its table on it, and rewrites the table's rows. A column another table's foreign key references needs `CASCADE`, which drops that key. |
| `ALTER TABLE ... RENAME [COLUMN] ... TO`, `RENAME TO` | Supported | Renames a column, including a key column, or the table, which keeps any schema that qualifies its name. Indexes and foreign keys follow the new names; statements prepared with the old ones fail. |
| `ALTER TABLE ... DISABLE ROW LEVEL SECURITY` | Supported | Changes nothing, since no table has row security. Several changes in one `ALTER TABLE`, and other `ALTER` forms, are rejected. |
| `DROP TABLE [IF EXISTS]` | Narrow | Drops the table and its indexes. A table another table's foreign key references needs `CASCADE`, which drops that key, not the other table. `RESTRICT` is the default. |
| `DROP INDEX [IF EXISTS]` | Supported | Drops one globally named index. A unique index a foreign key needs, because it references the index's columns, needs `CASCADE`, which drops the key. |
| `INSERT ... VALUES` | Narrow | Optional column list, up to 4,096 literal/parameter rows, per-cell `DEFAULT`, and optional `RETURNING`. |
| `INSERT ... DEFAULT VALUES` | Supported | Inserts one row using defaults and `NULL` values. |
| `INSERT ... ON CONFLICT` | Narrow | `ON CONFLICT [(columns)] DO NOTHING` or `ON CONFLICT (columns) DO UPDATE SET column = value, ...`, before any `RETURNING`. See [upserts](https://tinyjoin.org/guides/sql-compatibility/#upserts). |
| `INSERT ... SELECT`, `MERGE` | No | No query-sourced insert or merge statement. |
| `UPDATE ... SET ... [WHERE ...]` | Narrow | Assigns `DEFAULT` or an [expression](https://tinyjoin.org/guides/sql-compatibility/#expressions) of the row's columns, literals, and parameters; optional `RETURNING`. No `UPDATE ... FROM`. |
| `DELETE FROM ... [WHERE ...]` | Narrow | Optional `RETURNING`. No `DELETE ... USING`. |
| `RETURNING` | Narrow | `*` or a list of distinct plain columns, which the table's name or alias may qualify; no expressions or output aliases. Duplicate names return `INVALID_QUERY` before any rows are changed, even when no rows match. |
| `BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT` | No | Use the JavaScript callback transaction API. |
| `PREPARE`, `EXECUTE`, `DEALLOCATE` | No | SQL-level named statements are not implemented. Use the session-local JavaScript prepare() handle and its execute()/close() methods. |
| `COPY`, `TRUNCATE`, `EXPLAIN`, `VACUUM`, `ANALYZE` | No | No server maintenance or bulk-file SQL commands. |

query() accepts exactly one statement, with one optional trailing semicolon.
prepare() has the same one-statement and ordinary SQL text/token limits, but
accepts only the read and row-mutation statement families listed above.
exec() splits only top-level semicolons: strings, quoted identifiers, line
comments, nested block comments, and parentheses cannot accidentally terminate
a statement. A script contains at most 256 statements and 1 MiB of SQL text;
each statement retains the ordinary parser limits below.

## Predicates and expressions

| Form | Status | Semantics |
| --- | --- | --- |
| Strings, numbers, `TRUE`, `FALSE`, `NULL` | Supported | Single-quoted strings escape a single quote as `''`; numbers and booleans use their JSON-compatible scalar forms. |
| `$1`, `$2`, ... | Supported | One-based JSON-compatible parameters; at most 1,024. |
| `=`, `<>`, `!=`, `<`, `<=`, `>`, `>=` | Narrow | Strict scalar comparison, with integer/float cross-comparison. JSON supports structural equality/inequality only. |
| `AND`, `OR`, `NOT`, parentheses | Supported | Precedence is `NOT`, then `AND`, then `OR`; SQL unknown/null propagation is preserved. |
| `IS NULL`, `IS NOT NULL` | Supported | Tests the single runtime null value. |
| `IN (...)`, `NOT IN (...)` | Supported | One to 1,024 literals or parameters, or a [subquery](https://tinyjoin.org/guides/sql-compatibility/#subqueries), with SQL null behavior. |
| `BETWEEN`, `NOT BETWEEN` | Narrow | `column BETWEEN low AND high` means exactly `column >= low AND column <= high`, and `NOT BETWEEN` means `column < low OR column > high`, with those comparisons' type and null rules. Each bound is a literal or parameter. There is no `SYMMETRIC` form, so a reversed range matches nothing. |
| `+`, `-`, `*`, `/`, `%`, `\|\|` | Narrow | In the values `UPDATE` and `ON CONFLICT DO UPDATE` assign, on either side of a comparison, and in the select list of a query that does not aggregate. See [expressions](https://tinyjoin.org/guides/sql-compatibility/#expressions). |
| Casts, `CASE`, scalar functions | No | There are no casts, apart from a JSON column's `DEFAULT '...'::jsonb`, and no conditional expressions or functions other than the aggregates and the JSON functions of [nested queries](https://tinyjoin.org/guides/sql-compatibility/#nested-queries). |
| `LIKE`, `NOT LIKE`, `ILIKE`, `NOT ILIKE` | Narrow | Matches a whole text column value against a literal or parameter pattern, where `%` matches any run of characters and `_` exactly one character; a non-text column is rejected. Backslash makes the next pattern character literal unless `ESCAPE` names another single character, or `''` for none; a pattern ending in its escape character is rejected. `ILIKE` folds only ASCII letters, as PostgreSQL does under the C locale. A `NULL` operand is unknown. A `LIKE` pattern that begins with literal characters, such as `'abc%'`, reads only the part of an index or primary key that can match; other patterns scan. |
| `IS DISTINCT FROM`, `SIMILAR TO`, `ANY`, `ALL` | No | These PostgreSQL predicate families are not implemented. |
| JSON/path operators | No | JSON can be stored, returned, and compared for structural equality only. |

Either side of a comparison may be an [expression](https://tinyjoin.org/guides/sql-compatibility/#expressions), including a
plain column, so `WHERE updated > created` and `WHERE price * quantity > $1`
compare values worked out from the row. `IN`, `BETWEEN`, `LIKE`, and `IS NULL`
take a plain column and literal or parameter values, and only `IN` takes a
subquery. A join's `ON` clause still holds only column equalities.

### Subqueries

`IN` and `NOT IN` can take the values a query returns, in any statement with a
`WHERE` clause, including joins, aggregates, `UPDATE`, and `DELETE`:

```sql
SELECT id, title FROM posts
WHERE user_id IN (SELECT id FROM users WHERE team = $1);

DELETE FROM sessions WHERE user_id NOT IN (SELECT id FROM users);
```

The subquery runs once, before the statement reads any row, and reads what the
statement reads: the rows before the statement changes any, and a
transaction's staged rows. It is a `SELECT` of one table, a join, or an
aggregate, and may hold subqueries of its own, up to 16 in a statement. It must
return exactly one column, and at most 1,024 rows, as many values as an `IN`
list takes, or the statement fails with `QUERY_WORK_LIMIT_EXCEEDED`. As in
PostgreSQL, a `NULL` among its values leaves `NOT IN` unknown for every row it
does not match, so `NOT IN` returns no rows then.

A subquery in `IN` cannot read the statement around it, so a correlated
reference such as `WHERE posts.user_id = users.id` names an unknown column. It
cannot have `ORDER BY`, `LIMIT`, or `OFFSET`, and the values it returns are
compared as an `IN` list's are, so they never narrow the rows the statement
reads. A larger or correlated set is better written as a join. A query in a
select list can read the row around it, as a
[nested query](https://tinyjoin.org/guides/sql-compatibility/#nested-queries).

### Nested queries

A `SELECT` can nest a query that runs once for each of its rows and reads that
row's columns, by the name or alias of the table they come from. The nested
query either returns one value, or gathers its rows into one JSON value. This
is how ORMs load related rows, as Drizzle's relational queries and Kysely's
`jsonArrayFrom` and `jsonObjectFrom` write them, and it works in hand-written
SQL too:

```sql
SELECT u.id, u.name,
  (SELECT count(*) FROM posts p WHERE p.user_id = u.id) AS post_count,
  (SELECT coalesce(json_agg(json_build_array(p.id, p.title) ORDER BY p.id DESC), '[]')
    FROM posts p WHERE p.user_id = u.id) AS posts
FROM users u;

SELECT p.id, author.data AS author
FROM posts p
LEFT JOIN LATERAL (
  SELECT to_json(a) AS data
  FROM (SELECT name, email FROM users WHERE users.id = p.user_id) a
) author ON true;
```

A nested query is one of these forms:

- `(SELECT ...)` as an item of a select list, which must return one column
  and at most one row, or the statement fails with `INVALID_QUERY`. No row
  gives `NULL`.
- `LEFT JOIN LATERAL (SELECT ...) alias ON true`, whose columns the select list
  reads as `alias.column`. No row gives `NULL` in each of them.
- A select list of JSON values made from each row of a table, or of a query in
  `FROM (SELECT ...) alias`: `json_build_array(t.a, t.b)`, an array of
  qualified columns; `to_json(alias)` or `row_to_json(alias)`, the row as an
  object; and `json_agg(...)` of any of these, a qualified column, or a row's
  alias, which gathers every row into one array, or `NULL` where there are
  none. `json_agg` takes `ORDER BY` qualified columns, and `coalesce(json_agg(...),
  '[]')` gives an empty array where there are no rows.

Any other query, inside one of these or around them, is a `SELECT` the dialect
already runs, which may read the columns of every query around it. A query in
`FROM (SELECT ...)` gives only JSON values, and a select list that uses
`json_agg` has only `json_agg` items. A JSON value's field has the JSON type;
a nested query's other values keep their types.

Each nested query runs as a separate query for every row around it, so loading
the posts of 1,000 users runs 1,001 queries. Index the columns that nested
queries match on, such as `posts.user_id`, so that each reads only its own
rows. A statement runs at most 100,000 nested queries, which together return at
most 64 MiB, or fails with `QUERY_WORK_LIMIT_EXCEEDED`. Each nested query keeps
the ordinary limits of its kind.

## Expressions

An `UPDATE` or `ON CONFLICT DO UPDATE` assignment can work its value out from
the row it updates, a `WHERE` comparison can compare values worked out from
the rows it reads, and a query can return them:

```sql
UPDATE counters SET hits = hits + 1, label = label || ' (edited)'
WHERE id = $1;

SELECT id, hits * 2 AS doubled FROM counters
WHERE hits * 2 > target - $1 ORDER BY doubled DESC;

INSERT INTO counters (id, hits) VALUES ($1, 1)
ON CONFLICT (id) DO UPDATE SET hits = counters.hits + EXCLUDED.hits;
```

An expression combines columns, literals, and parameters with `+`, `-`, `*`,
`/`, `%`, unary `-`, `||`, and parentheses. As in PostgreSQL, `*`, `/`, and `%`
bind more tightly than `+` and `-`, and `||` more loosely than both. Every
assignment reads the row as it was before the statement, so
`SET a = b, b = a` swaps two columns of the same type.

- Arithmetic takes integers and floats. Two integers give an integer, and
  integer division truncates toward zero. A result outside the
  JavaScript-safe range fails with `NUMERIC_OVERFLOW`.
- A float operand makes the result a float, which must be finite or the
  statement fails with `NUMERIC_OVERFLOW`. As with PostgreSQL's `double
  precision`, `%` takes only integers.
- Dividing by zero, or taking a remainder by zero, fails with
  `DIVISION_BY_ZERO`.
- `||` joins two texts. There are no implicit casts, so it takes no other type,
  and `'1' + 1` is a `TYPE_MISMATCH`.
- A `NULL` operand gives `NULL`.

Operand types, whether a result's type fits the column it is assigned to, and
whether compared values can be compared, are checked before any row is read.
`SET hits = hits * 1.5` on an integer column fails with `TYPE_MISMATCH` even
when no row matches. A value that fails
as a row is written, such as an overflow or a `NULL` for a `NOT NULL` column,
fails the statement, which changes nothing. An expression that reads no column
is worked out once for the statement.

In `ON CONFLICT DO UPDATE`, a column of the stored row is named with the
table's name, such as `counters.hits`, and a column of the row proposed for
insertion with `EXCLUDED`. A plain name could mean either, so, as in PostgreSQL,
it is rejected.

In a single-table or join query, a select-list expression's field is named by
its `AS` alias, or `?column?` without one, as in PostgreSQL. Two expressions
without aliases therefore share a name, which only
[array rows](https://tinyjoin.org/guides/sql-compatibility/#repeated-output-names) can hold. The field's type is the type of the values the expression gives, and
text for an expression that gives only `NULL`. `ORDER BY` can name an
expression's alias; it then sorts the returned rows, so each of its names must
be an output, or a column an output returns. Aggregate and `SELECT DISTINCT`
queries return only columns and aggregates.

Casts, `CASE`, functions, and comparisons inside an expression are not
supported.

## Runtime types

PostgreSQL type spellings map onto five TinyJoin runtime types. The spelling
does not import PostgreSQL's storage width, coercion, operator, or catalog
semantics.

| Accepted SQL spellings | TinyJoin value | Important difference |
| --- | --- | --- |
| `BOOLEAN`, `BOOL` | JavaScript boolean | No PostgreSQL coercions. |
| `SMALLINT`, `INTEGER`, `INT`, `INT2`, `INT4`, `BIGINT`, `INT8` | One JavaScript-safe integer type | Range is -9,007,199,254,740,991 through 9,007,199,254,740,991. `SMALLINT`/`INTEGER` are wider and `BIGINT` is narrower than PostgreSQL. |
| `REAL`, `FLOAT`, `FLOAT4`, `FLOAT8`, `DOUBLE PRECISION` | One finite binary64 JavaScript number | No real/double distinction, `NaN`, or infinity. |
| `TEXT`, `VARCHAR`, `VARCHAR(n)`, `CHARACTER VARYING` | JavaScript string | A `VARCHAR(n)` holds at most `n` characters, and a longer value fails with `CONSTRAINT_VIOLATION`, even when the excess is spaces, which PostgreSQL would trim. No database collation. Ordering is deterministic Unicode code-point ordering. |
| `JSON`, `JSONB` | The same JSON-compatible value (scalar, array, or object) | No textual/binary distinction, JSON operators, casts, or JSON index type. |

SQL `NULL` and a JSON scalar `null` are the same runtime value, including in a
JSON column. TinyJoin cannot distinguish them for `NOT NULL`, `IS NULL`,
aggregates, or defaults.

A parameter is the JavaScript value it is, so a string bound to a JSON column
is stored as a JSON string. PostgreSQL and PGlite parse a string bound to a
`json` or `jsonb` column as JSON text instead, so code that passes
`JSON.stringify(value)` there stores an object in PGlite and a string in
TinyJoin, without an error. Pass the value itself. The
[Drizzle driver](https://tinyjoin.org/guides/drizzle/) does this for Drizzle's JSON columns.

There are no implicit PostgreSQL casts. Notable unavailable types include
`NUMERIC`/`DECIMAL`, date/time/interval types, UUID, `BYTEA`, arrays,
serial/identity, enum/domain, and user-defined types. Type modifiers other than
a `VARCHAR`'s length, such as `NUMERIC(10, 2)` or `CHAR(3)`, are rejected.

## Identifiers, comments, and table names

- Unquoted identifiers are folded to ASCII lower case. Double-quoted
  identifiers preserve case and use doubled quotes to escape a quote.
  A quoted column name containing a dot is usable in a single-table query,
  but a table containing any such column cannot be a join source, even when
  the column is not selected. Quoted join aliases cannot contain dots either.
- An unquoted identifier may begin with `_`, an ASCII letter, or any non-ASCII
  character. Later characters may additionally be ASCII digits or `$`.
- TinyJoin reserves these unquoted words case-insensitively: `SELECT`, `FROM`,
  `WHERE`, `AND`, `OR`, `IS`, `IN`, `LIMIT`, `OFFSET`, `ORDER`, `BY`, `ASC`,
  `DESC`, `NULLS`, `FIRST`, `LAST`, `NULL`, `TRUE`, `FALSE`, `CREATE`, `TABLE`,
  `IF`, `NOT`, `EXISTS`, `PRIMARY`, `KEY`, `DEFAULT`, `INSERT`, `INTO`,
  `VALUES`, `UPDATE`, `SET`, `DELETE`, `RETURNING`, `AS`, `JOIN`, `INNER`,
  `LEFT`, `OUTER`, `ON`, `GROUP`, and `HAVING`. Double-quote one to use it as
  an identifier. Other words used contextually by supported statements are not
  necessarily reserved.
- `--` line comments and nested `/* ... */` comments are supported.
- Single-quoted strings use doubled single quotes. Dollar-quoted strings are
  not supported.
- A two-part table name such as `public.tasks` is accepted as one flat catalog
  key. It does **not** create or resolve a PostgreSQL schema. `tasks` and
  `public.tasks` are different TinyJoin table names.
- There is no `CREATE SCHEMA`, `search_path`, `information_schema`, or
  `pg_catalog`. Index names are global catalog keys.

### Qualified columns

A `SELECT`, `UPDATE`, or `DELETE` over one table may name a column as
`table.column`, and write `table.*` for `*`, wherever it reads that table's
columns: in a projection, aggregate argument, `WHERE`, `GROUP BY`, `ORDER BY`,
and `RETURNING`, including the `RETURNING` of an `INSERT`. The qualified and
plain spellings are the same statement, and are planned and narrowed alike:

```sql
SELECT tasks.id, tasks.title FROM tasks
WHERE tasks.done = false ORDER BY tasks.title;

UPDATE tasks SET done = true WHERE tasks.id = $1 RETURNING tasks.id;
```

A table alias, with or without `AS`, takes the place of the table's name as
the qualifier, as in PostgreSQL, so the name no longer qualifies anything:

```sql
SELECT t.id, t.title FROM tasks AS t WHERE t.done = false;

DELETE FROM tasks t WHERE t.id = $1;
```

A table named with a schema, such as `public.tasks`, is qualified by the name
after the dot, `tasks`, as it is in a join. `public.tasks.id` is rejected.
Naming any other table is an error, and the columns an `INSERT` lists or an
`UPDATE` assigns in `SET` are never qualified. An alias changes only how a
statement spells its columns: results, changed tables, and subscriptions name
the table itself.

In `ORDER BY`, a plain name refers to an output name before a source column,
but a qualified name is always the table's column. `SELECT title AS id FROM
tasks ORDER BY tasks.id` orders by the `id` column, not by the titles returned
as `id`. A grouped or aggregate query is ordered by its outputs, so there a
qualified column stands for the output that returns it, and must be projected.

A quoted name that holds a dot, such as `"extra.value"`, is one column name
rather than a qualified one.

### Repeated output names

An object row holds one value for each name, so a `SELECT` whose output names
repeat returns `INVALID_QUERY`, and each output needs a distinct `AS` alias.
An array row holds its values by position, so a `SELECT` read with
`rowMode: "array"` may repeat a name. This is how a query builder that reads
rows by position, and so never writes an alias, selects a column of the same
name from each side of a join:

```ts
const { fields, rows } = await db.query(
  `SELECT posts.id, users.id, users.name
   FROM posts JOIN users ON posts.user_id = users.id`,
  [],
  { rowMode: "array" },
);
// fields: id, id, name
// rows: [[10, 1, "Ann"], ...]
```

This applies to single-table, aggregate, and join queries, to prepared
statements, where each execution's `rowMode` decides, and to each statement of
an exec() script. An aggregate without an alias takes its function's name, so
`SELECT COUNT(*), COUNT(email)` returns two fields named `count`.

An `ORDER BY` name that matches more than one output is `INVALID_QUERY`, unless
those outputs all return the same column or the same aggregate. Order by a
qualified column, or give the outputs distinct aliases. `RETURNING` columns
must be distinct in either row mode.

## Upserts

`INSERT ... ON CONFLICT` inserts each proposed row unless it conflicts with an
*arbiter*: the primary key or a unique index named by the conflict target.
The target lists exactly the columns of the primary key or of one unique
index, in any order. `DO NOTHING` may omit the target, in which case the
primary key and every unique index of the table are arbiters.

```sql
INSERT INTO settings (name, value) VALUES ($1, $2)
ON CONFLICT (name) DO UPDATE SET value = EXCLUDED.value;
```

As in PostgreSQL, proposed rows are handled one at a time, so a row can
conflict with a stored row or with a row written earlier in the same
statement:

- `DO NOTHING` skips a conflicting row.
- `DO UPDATE` applies its `SET` assignments to the conflicting stored row. A
  statement that would update one row twice, or update a row it inserted, fails
  with `CONSTRAINT_VIOLATION` and changes nothing.
- A conflict on a constraint that is not an arbiter fails the statement as an
  ordinary insert would.

Each `SET` value is `DEFAULT` or an [expression](https://tinyjoin.org/guides/sql-compatibility/#expressions) of literals,
parameters, and columns: the stored row's, named with the table's name as in
`counters.hits`, and the proposed row's, named as in `EXCLUDED.hits`, which is
its value after column defaults. `DO UPDATE` cannot change a row's primary key,
although assigning it the same value is allowed. `NULL` never conflicts with a
unique index.

A primary-key arbiter uses direct key lookup. A unique-index arbiter reads the
index postings, except inside a callback transaction that has staged changes to
the table, where those rows are not yet indexed and each statement scans the
table once instead.

`RETURNING`, the affected-row count, and [changed keys](https://tinyjoin.org/guides/transactions-and-changes/#refreshing-individual-rows)
cover inserted and updated rows only. A statement that skips every row reports
no changed table. `ON CONFLICT ON CONSTRAINT`, a partial-index `WHERE` in the
target, and `DO UPDATE ... WHERE` are rejected.

## Constraints and indexes

Every SQL-created table has a primary key. TinyJoin currently implements:

- primary-key uniqueness and non-nullability;
- column `NOT NULL`;
- scalar literal column defaults;
- unique indexes, which `CREATE UNIQUE INDEX` or a `UNIQUE` constraint creates;
  and
- foreign keys.

A `UNIQUE` constraint is a unique index of the constraint's name, so getSchema()
lists it among the table's indexes, and either `DROP CONSTRAINT` or `DROP INDEX`
removes it.

It does not implement `CHECK`, exclusion constraints, generated columns,
sequences, or triggers.

A row is identified by its primary key: each table is stored keyed by that
value, and an `UPDATE` which changes a primary key is applied as a removal at
the old key and an insertion at the new one rather than an edit in place. Treat
primary keys as stable, opaque identifiers.

Composite primary and secondary indexes are supported. A unique index omits a
key containing `NULL`, so multiple null-containing keys are allowed, matching
PostgreSQL's default `NULLS DISTINCT` behavior.

### Foreign keys

A foreign key holds that its columns' values, in every row of its table, are
the values of a row of the table it references, unless one of them is `NULL`,
as PostgreSQL's default `MATCH SIMPLE` says. It references that table's primary
key, unless it names columns, which must be the primary key's or a unique
index's, of the same runtime types. A table may reference itself. A reference
to `public.table` names `table` when there is no table of the longer name, as
PostgreSQL tools write it.

```sql
CREATE TABLE posts (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
  editor_id TEXT REFERENCES users (id) ON DELETE SET NULL
);
```

Each key is checked as its statement ends, so rows that one statement inserts
can reference each other, and a statement that would leave a reference without
its row fails with `CONSTRAINT_VIOLATION`, changing nothing. Inside a
transaction, each statement is checked against the rows the transaction has
staged. Keys cannot be deferred to the end of a transaction.

When a statement deletes a referenced row, or changes the values its
references hold, each referencing row is changed as the key's `ON DELETE` or
`ON UPDATE` action says, in the same statement:

- `NO ACTION`, the default, refuses the statement, unless another row holds the
  values once it ends;
- `RESTRICT` refuses it;
- `CASCADE` deletes the referencing rows, or changes their references to the
  new values, and so on through the keys that reference them;
- `SET NULL` and `SET DEFAULT` change the referencing columns to `NULL` or to
  their defaults, which must themselves be allowed.

Finding the rows that reference a deleted or changed row reads the
referencing table like a `WHERE` on its columns, so index the referencing
columns of a large table, as in PostgreSQL. The rows an action changes count
toward the statement's limits, and are reported among its changed tables.

`ALTER TABLE ... ADD FOREIGN KEY` checks every stored row before it adds a key.
Renaming a table or column renames it in the keys that name it, and dropping
what a key needs takes `CASCADE`, which drops the key.

Single-table `SELECT`, aggregates, `UPDATE`, and `DELETE` read only the rows
their `WHERE` clause can match when its `AND`-ed terms allow, and still check
the complete predicate on every row they read:

- complete primary-key equality is a direct lookup, when the key types and
  values permit an exact one, and sees staged changes inside a transaction;
- complete equality on every column of a secondary index reads that index's
  postings;
- comparisons, `BETWEEN`, and `LIKE` patterns that begin with literal
  characters read a range of an index's first column, while the range holds at
  most a quarter of the table's rows, or else a range of the primary key's first
  column. An index is used for a range only if its other columns are
  `NOT NULL`, since a row with a `NULL` indexed value is not indexed.

A comparison narrows the rows read only when one side is a plain column and the
other a value known without reading a row: a literal, a parameter, or an
[expression](https://tinyjoin.org/guides/sql-compatibility/#expressions) of them, such as `created > $1 - 86400`. A value on
the left is read as it would be on the right, so `5 < id` narrows as `id > 5`
does. A comparison that reads the row on both sides, such as `a > b`, is
checked on every row read.

An aggregate that reads only an index's columns and the primary key, in its
select list, `GROUP BY`, and `WHERE` clause, answers a range of that index from
its entries alone, without reading any rows, however much of the table the
range holds.

Inside a callback transaction, a table with staged changes narrows only by a
direct primary-key lookup, because its staged rows are not yet indexed or in key
order. `OR`, `NOT`, `IN`, and partial composite equality do not narrow the rows
read, and neither does a join.

## Aggregates and joins

Aggregate null behavior follows the familiar SQL rules: `COUNT(*)` counts
rows; other aggregates skip `NULL`; a global aggregate over no rows emits one
row with count zero and other aggregates `NULL`; an empty grouped input emits
no rows. Integer `SUM` fails beyond the JavaScript-safe range, and integer
`AVG` returns a floating-point value rather than PostgreSQL `numeric`.

Join keys containing `NULL` never match. Integer and float keys may compare;
JSON join keys are rejected. Every source requires a typed SQL catalog and a
unique alias, and the result must use distinct output names unless its rows
are [read as arrays](https://tinyjoin.org/guides/sql-compatibility/#repeated-output-names).
Unqualified columns are accepted only when exactly one source contains the
name. Without `ORDER BY`, row order is not part of the contract.

### Join projection and identifier boundaries

A join's projection can name columns and expressions, or use `*` for every
column of every table, in the order the query joins them, and `table.*` for
every column of one table, each in the order its table declares them. Output
names must still be distinct when rows are read as objects, so a `*` over
tables that share a column name, such as `id`, needs
[array rows](https://tinyjoin.org/guides/sql-compatibility/#repeated-output-names). A join also rejects any source table
whose catalog contains a column name with a dot, even if the projection and
`ON` clause do not use that column. Quoting does not remove this restriction.

This small schema reproduces both boundaries:

```sql
CREATE TABLE left_items (id INTEGER PRIMARY KEY);
CREATE TABLE right_items (id INTEGER PRIMARY KEY);
CREATE TABLE dotted_items (id INTEGER PRIMARY KEY, "extra.value" TEXT);
INSERT INTO left_items VALUES (1);
INSERT INTO right_items VALUES (1);
INSERT INTO dotted_items VALUES (1, 'kept');
```

These single statements succeed:

```sql
SELECT left_items.id AS id
FROM left_items JOIN right_items ON left_items.id = right_items.id;
```

```sql
SELECT left_items.* FROM left_items JOIN right_items ON left_items.id = right_items.id;
```

```sql
SELECT id, "extra.value" FROM dotted_items;
```

Each of the following statements fails, the first only when its rows are
read as objects, which cannot hold both tables' `id`:

```sql
SELECT * FROM left_items JOIN right_items ON left_items.id = right_items.id;
```

```sql
SELECT left_items.id AS id
FROM left_items JOIN dotted_items ON left_items.id = dotted_items.id;
```

The last query fails even though `"extra.value"` is never selected. Use column
names without dots for tables that will participate in joins.

### Join work budgets

Join chains are evaluated as written, from left to right; TinyJoin does not
reorder them. Each `ON` equality must connect its newly introduced source to
one of the sources already in scope. For each combination of earlier rows, a
joined table's matching rows are found:

- by direct lookup, when the `ON` equalities fix its complete primary key;
- through a secondary index whose every column they fix, unless the table has
  staged changes inside a callback transaction; or
- in a hash table of the table's rows keyed by the `ON` columns, built once
  per query.

`WHERE` terms that read only the first table, or only a table joined without
`LEFT`, also narrow how that table is read, as they would in a single-table
query, and rows that fail them are dropped before joining. A term on a table
joined with `LEFT` must see its null-extended rows, so it is never applied
early. The complete `WHERE` clause is still checked on every joined row.

Across the full chain, candidate-extension, retained-row, result-row, and byte
budgets are global rather than resetting for each `JOIN`. Aggregates over joins
are not supported. A candidate is a row a lookup or hash table returns for one
combination of earlier rows, so the budget counts only rows whose `ON` values
match: a join is never rejected because the Cartesian product is large. Rows of
hash-joined tables are retained and count toward the retained-build-row limit,
and tables read whole, without narrowing terms, are checked against the scan
and retained-build-row limits before execution.

For example, three tables of 100 rows joined on unique matching identifiers
examine 200 candidates and return 100 rows. Two tables of 1,001 rows whose join
keys all match examine over 1,000,000 candidates, and exceed the budget even
when a later `WHERE` term that reads both tables removes every result. An
unordered `LIMIT` can stop early; an ordered join must first collect its
matches. Standalone queries and exec() also charge scans and candidates to
their shared script-work budget, which can be reached before the join's own
limit.

Many-to-many relationships can use a bridge table with a composite primary
key, for example:

```sql
SELECT post.id AS post_id, tag.name AS tag_name
FROM posts AS post
JOIN post_tags AS post_tag ON post.id = post_tag.post_id
JOIN tags AS tag ON post_tag.tag_id = tag.id
ORDER BY post_id, tag_name
```

The bridge table can declare [foreign keys](https://tinyjoin.org/guides/sql-compatibility/#foreign-keys) on `post_id` and
`tag_id`, with `ON DELETE CASCADE`, so that deleting a post or tag deletes its
links.

## Transactions and concurrency

Each query() or standalone prepared-statement write is atomic. A standalone
exec() script runs its supported reads, DDL, and DML against one page candidate
and publishes one durable generation only after every statement succeeds. A
callback transaction stages `INSERT`, `UPDATE`, and `DELETE` statements,
including through tx.execute(), exposes those staged rows to reads through its
transaction object, and publishes the complete result once.
transaction.exec() may group DML and reads as an atomic savepoint within that
staged transaction: a failure installs none of that script's changes. DDL is
rejected before any statement in a transaction script runs and must use a
standalone query() or exec() call.

If a transaction statement fails, that statement installs no partial change,
but the transaction is not put into PostgreSQL's aborted state. If the callback
catches the error, earlier staged writes may still commit. This is also true for
a prepared execution. Letting the error escape the callback rolls the
transaction back.

Calls to the same Client's transaction() queue in order. Awaiting one from
inside its own active callback deadlocks; pass the existing Transaction into
helpers instead. There is no AbortSignal or timeout option, and racing a
Promise against a timer does not cancel the work. See
[transaction composition and errors](https://tinyjoin.org/guides/transactions-and-changes/#composing-transaction-helpers).

Atomicity does not make every failed write's outcome knowable to its caller.
After `RECOVERY_REQUIRED`, `STORAGE_COMMIT_OUTCOME_UNKNOWN`, or
`STORAGE_ENGINE_POISONED`, stop using the Client, close and reopen it, and
reconcile stored state before replaying a write. The `retryable` flag is not a
safe-replay guarantee. See [storage recovery](https://tinyjoin.org/guides/storage-and-lifecycle/#recovering-after-an-uncertain-write).

Requests are serialized through one Worker. OPFS persistence permits one open
Worker for a database name; it is an exclusive writer rather than a
PostgreSQL-style set of concurrent sessions. There is no MVCC session model,
isolation-level selection, user-controlled savepoints, lock manager, or
deadlock detection. Different OPFS names are independent databases and do not
synchronize with one another.

## PostgreSQL facilities that are not present

TinyJoin has no PostgreSQL wire protocol, SQLSTATE-compatible error protocol,
server process, roles or grants, system catalogs, extensions, stored
procedures, triggers, notifications, WAL, replication, point-in-time recovery,
or PostgreSQL file-format compatibility. Rows and parameters are
JSON-compatible JavaScript values. Result fields use the closest stable
PostgreSQL OID as metadata: boolean `16`, integer `20`, text `25`, JSON `114`,
and float `701`. This mapping does not add PostgreSQL storage widths,
coercions, operators, parsers, or wire semantics.

Persistence is TinyJoin's own page format in memory or one browser OPFS file.
It is not a PostgreSQL data directory.

JavaScript prepared statements are Worker-owned parsed statements, not
PostgreSQL named prepared statements, server plan-cache entries, protocol
objects, or persistent database objects.

## Hard limits

Limits are part of the runtime contract: oversized work fails explicitly
rather than growing without bound.

| Resource | Current limit |
| --- | ---: |
| Physical database | 65,536 4 KiB pages (256 MiB) |
| Tables / indexes | 4,096 each |
| Columns per table or projection | 256 |
| Catalog name | 1,023 UTF-8 bytes |
| Complete encoded storage key | 1,024 bytes |
| Encoded logical row data | 1,048,568 bytes |
| Complete paged row / individual encoded JSON value | 1,048,576 bytes |
| JSON nesting | 64 levels |
| SQL text / tokens / parameters | 64 KiB / 4,096 / 1,024 |
| Expanded bound parameter values | 16 MiB per statement |
| Open prepared statements / retained prepared state | 128 / 8 MiB per open database |
| exec() script text / statements | 1 MiB / 256 |
| exec() row, index, scan, and join operations | 1,000,000 across the script |
| exec() retained result work | 16 MiB across the script |
| Predicate nodes / nesting / `IN` values | 256 / 32 / 1,024 |
| Expression terms / nesting | 256 / 32 |
| Subqueries in a statement / rows a subquery returns | 16 / 1,024 |
| Nested queries a statement runs / data they return | 100,000 / 64 MiB |
| Rows in one `INSERT ... VALUES` | 4,096 |
| Explicit `LIMIT` / `OFFSET` / `OFFSET + LIMIT` | 100,000 / 4,294,967,295 / 4,294,967,295 |
| Rows scanned / returned by a query | 1,000,000 / 100,000 |
| Rows changed by one `UPDATE` or `DELETE` | 100,000 |
| Ordered matching rows | 100,000 |
| Transaction overlay | 100,000 keys and 16 MiB |
| Table sources in one joined `SELECT` | 8 total (one base plus seven `JOIN` clauses) |
| `ON` equalities in one joined `SELECT` | 32 across the chain |
| Join candidate row extensions / returned rows | 1,000,000 across the chain / 100,000 |
| Join retained build rows | 100,000 across the chain |
| Join working state / result data | 16 MiB / 16 MiB across the chain |
| Aggregate groups or single-table `DISTINCT` rows / aggregate calls | 100,000 / 64 |
| Aggregate cells (groups times aggregate calls) | 1,000,000 |
| Query, DML result, join, aggregate, or mutation working set | 16 MiB per operation-specific bound |

An ordered query can reach its materialization limit before applying a small
`LIMIT`, unless it is ordered by the primary key: ascending by its leading
columns, or descending by all of them. Such a query reads rows in key order and
stops at its `LIMIT`, so the ordered-row limit does not apply to it, except
when it descends through a secondary index or reads a table with staged
changes inside a transaction. Join candidate-extension and retained-row bounds
apply to the complete left-deep chain, not separately to each step and not just
to returned rows. The candidate limit is enforced by a runtime counter as
candidates are found, including those that later fail another `ON` equality or
the `WHERE` clause. The 1,024-byte secondary-index key limit covers the complete encoded indexed tuple
and primary-key tuple together, not each component independently. A boolean
component takes 1 byte, an integer or float 8, and text its UTF-8 bytes plus 2,
and 1 more for each zero byte. An individual JSON value remains subject to the
smaller budget for the row that contains it.

Every occurrence of a parameter in a statement counts toward the expanded
binding budget. Repeating one large `$1` value many times can therefore fail
with `RESOURCE_LIMIT` even when the supplied parameter array is small. This
check runs before the values are copied into the statement, including prepared
executions and queries with `LIMIT 0`. It is an independent allocation bound,
not a limit on the total memory used by the browser or WebAssembly instance.

# Storage and lifecycle

Source: https://tinyjoin.org/guides/storage-and-lifecycle/

TinyJoin supports an ephemeral memory database and an opt-in persistent browser
database. Both use the same Worker and page-native engine.

## Memory

```ts
const db = await create();
// Equivalent to: await create('memory://')
```

Memory starts empty each time the Worker opens. It is useful for tests,
temporary views, and trying the API.

## Persistent browser storage

```ts
const db = await create('opfs://my-project-v1');
```

The name must contain 1-64 ASCII letters, numbers, dots, underscores, or
hyphens, and start with a letter or number. Include the application, dataset,
and schema generation in it. A name is a namespace, not an encryption or
access-control boundary.

OPFS persistence requires a secure browser context, a dedicated Worker, Web
Locks, and BroadcastChannel. Call create() with the same name in every tab:
TinyJoin elects one database-owning Worker, routes operations to it, and
broadcasts committed changes to all connected Clients. Multiple Clients in one
page work the same way. No additional configuration is needed.

Different names can open independently, but their rows and subscriptions are
separate. Coordination stays within the same origin and browser storage
partition; it does not connect other browser profiles or devices.

The owner retains an exclusive synchronous OPFS access handle. That remains
the final protection against concurrent mutation, including older TinyJoin
versions or custom code that does not participate in coordination. Such an
owner can still cause `STORAGE_LOCKED`; close the older application before
retrying. Mixed TinyJoin releases fail with `DATABASE_VERSION_MISMATCH`
instead of sharing an incompatible engine.

There is no silent fallback to memory if persistent storage is unavailable,
locked, corrupt, or out of quota.

An OPFS name identifies stored data; changing the name opens a different
database and leaves the old data in place. Check the
[release notes](https://tinyjoin.org/guides/releases/) before upgrading TinyJoin: each release
states whether it can open the page format written by earlier ones. The only
such break so far is that v0.1.0 and later cannot open a database written by
v0.0.5. Preserve any needed data with the old version before moving to a new
namespace.

The current persistent database is bounded to 65,536 4 KiB pages (256 MiB).
The [SQL compatibility guide](https://tinyjoin.org/guides/sql-compatibility/#hard-limits) lists the
independent query and mutation limits.

## Browser retention

OPFS is browser-managed storage. A user can clear it and a browser may evict
best-effort storage under pressure. An application that needs stronger local
retention can make a user-appropriate `navigator.storage.persist()` request.
TinyJoin does not make that product decision during startup.

## Reading the schema

getSchema() returns the database's tables as typed objects, with their columns,
primary keys, and indexes, rather than through SQL catalog tables, which
TinyJoin does not have:

```ts
const {version, tables} = await db.getSchema();
const tasks = tables.find(({name}) => name === 'tasks');
// -> {
//   name: 'tasks',
//   columns: [
//     {name: 'id', type: 'text', nullable: false},
//     {name: 'done', type: 'boolean', nullable: false, default: false},
//   ],
//   primaryKey: ['id'],
//   indexes: [{name: 'tasks_done', columns: ['done'], unique: false}],
//   foreignKeys: [],
// }
```

Tables, and each table's indexes, are in name order. Columns are in the order
the table declares them, followed by any that `ALTER TABLE` added. A column's
`type` is one of the five [runtime types](https://tinyjoin.org/guides/sql-compatibility/#runtime-types),
not the spelling it was declared with, so `VARCHAR` reads back as `text` and
`BIGINT` as `integer`; a `VARCHAR(n)` column's `maxLength` is its `n`. `default` is present only for a column that declares
one, and is `null` for `DEFAULT NULL`. The primary key is not listed among the
indexes, and its columns are never nullable. `version` is the version the
application last gave the schema with setSchema(), or zero.

Use it to confirm that a database matches the schema the application expects,
to decide which migration steps an older database still needs, or to generate
code from the tables. It reads only the catalog, not rows, so it is cheap even
for a large database. Call it outside a transaction.

## Setting the schema

setSchema() is the other way round: it takes a schema in the shape getSchema()
returns, and makes the database's tables match it. An application can declare
its schema in code and call setSchema() as it starts, rather than keeping a
series of migrations:

```ts
await db.setSchema({
  version: 2,
  tables: [
    {
      name: 'tasks',
      columns: [
        {name: 'id', type: 'text', nullable: false},
        {name: 'name', renamedFrom: 'title', type: 'text', nullable: false, maxLength: 200},
        {name: 'done', type: 'boolean', nullable: false, default: false},
        {name: 'priority', type: 'integer', nullable: false, default: 0},
      ],
      primaryKey: ['id'],
      indexes: [{name: 'tasks_done', columns: ['done'], unique: false}],
      foreignKeys: [],
    },
  ],
});
```

It compares the schema with the database's and makes the difference in one
change, which commits whole or not at all, with the statements TinyJoin's
[DDL](https://tinyjoin.org/guides/sql-compatibility/#statements-and-clauses) would run:

- a table or column the database lacks is created;
- a table or column whose `renamedFrom` the database still has is renamed,
  keeping its rows and values;
- a column's default, nullability, and `VARCHAR` length become the schema's;
- each table's indexes and foreign keys become exactly the schema's, with
  keys added once every table they reference exists; and
- a table or column the schema leaves out stays, unless setSchema() is given
  `{drop: true}`.

Rows keep their values, so a change that a row prevents fails, changing
nothing: `NOT NULL` where a row holds `NULL`, a shorter `VARCHAR` than a value,
or a `NOT NULL` column without a default added to a table with rows. A change
no statement could make, to a primary key or a column's runtime type, is
refused. Make one by creating a new table under a new name, copying the rows
with the application's own code, and dropping the old table.

A schema the database already has changes nothing and resolves to `false`, so
every tab can call setSchema() as it starts. `version` guards against a tab
that still runs an older copy of the application: setSchema() refuses a schema
whose version is lower than the database's with `SCHEMA_OUTDATED`, and records
a higher one. Give each schema change a higher version, and leave `renamedFrom`
in place for as long as an older database may still have the old name.

The [Drizzle guide](https://tinyjoin.org/guides/drizzle/#push) shows how to set a Drizzle schema
this way.

## Application backups and restoration

TinyJoin has no general public export, import, list-databases, or
delete-database API, and no portable binary backup contract. Keep schemas and
migration rules in your application. For
reconstructible data, fetching it again from its original source may be simpler
than a backup.

For data that must be retained, export explicit columns from each known table
to an application-owned format. Include a schema version, preserve primary
keys, and save the result outside this origin's browser storage, for example
as a user-downloaded file. A second OPFS name on the same origin is not an
independent backup. Verify the export by restoring and reading it before
removing the original.

Here is a bounded example for the `notes` schema in the
[getting-started guide](https://tinyjoin.org/guides/getting-started/#create-a-table). It exports at
most 1,000 small notes and restores into an empty table. Imported values are
validated before they become SQL parameters; a TypeScript row generic alone
does not validate a file.

```ts
import type {Client} from 'tinyjoin';

type SavedNote = {id: string; body: string; pinned: boolean};

async function backupNotes(db: Client): Promise<string> {
  const {rows} = await db.query<SavedNote>(
    'SELECT id, body, pinned FROM notes ORDER BY id LIMIT 1001',
  );
  if (rows.length > 1000) throw new Error('Export needs a larger-data policy');
  return JSON.stringify({schemaVersion: 1, notes: rows});
}

async function restoreNotes(db: Client, text: string): Promise<void> {
  const backup = JSON.parse(text);
  if (
    backup?.schemaVersion !== 1 || !Array.isArray(backup.notes) ||
    backup.notes.length > 1000 ||
    !backup.notes.every((note: SavedNote) =>
      note !== null && typeof note === 'object' &&
      typeof note.id === 'string' && typeof note.body === 'string' &&
      typeof note.pinned === 'boolean'
    )
  ) throw new Error('Invalid notes backup');

  await db.exec(`
    CREATE TABLE IF NOT EXISTS notes (
      id TEXT PRIMARY KEY,
      body TEXT NOT NULL,
      pinned BOOLEAN NOT NULL DEFAULT false
    )
  `);
  await db.transaction(async (tx) => {
    if ((await tx.query('SELECT id FROM notes LIMIT 1')).rows.length) {
      throw new Error('Restore requires an empty notes table');
    }
    for (const note of backup.notes) {
      await tx.query(
        'INSERT INTO notes (id, body, pinned) VALUES ($1, $2, $3)',
        [note.id, note.body, note.pinned],
      );
    }
  });
}
```

This is an application example, not an arbitrary database importer. Stop
application writes while exporting; for several tables, read them in one short
transaction when the full snapshot fits the limits. Large rows, result bytes,
and staged writes can reach the [engine limits](https://tinyjoin.org/guides/sql-compatibility/#hard-limits)
before the example's row cap. There is no streaming backup or cross-batch
snapshot API. Plan bounded batches and a stable maintenance window for larger
datasets. Restore schema DDL before the data transaction, including your known
indexes. If restoring fails, preserve the export and follow the uncertain-write
recovery rules below before retrying.

## Checking a database

Every page carries a checksum, which TinyJoin verifies when it reads the page,
and a statement rejects a row it cannot read. A crash or an interrupted write
therefore surfaces as an error rather than as wrong results, and reopening
returns to the last complete commit. Opening a database checks its catalog but
not every row, so a large database opens as quickly as a small one.

Damage that no statement happens to read, such as a row that is never queried
or an index that no longer matches its table, shows only in a check of the
whole database. check() reads every row and index entry, and every page that
holds them, and rejects with the first problem it finds:

```ts
try {
  await db.check();
} catch (error) {
  // error.code names the first problem, such as STORAGE_CORRUPT.
}
```

Such damage would come from a defect in TinyJoin or from other code rewriting
the database file, not from an interrupted write. A check takes time in
proportion to the database, and statements from every Client of the same OPFS
name wait while it runs, so run it while the application is idle, or in tests.

TinyJoin cannot yet repair a database that fails its check. Restore the
application's own backup, as described above, into a new OPFS name.

## Resetting and removing obsolete databases

A logical reset can use `DELETE FROM notes` to empty a known table, or
`DROP TABLE IF EXISTS notes` followed by the application's schema setup. First
preserve and verify any data that must survive, stop writes, and await close()
on **every Client in every tab or Worker** for this name. Closing only the owner
causes handover to another connected Client. Keep other pages closed, open one
maintenance Client, perform the deliberate reset, and close it before normal
startup resumes. A reset is destructive and is never an uncertain-write
recovery step.

Changing `opfs://my-app-v1` to `opfs://my-app-v2` leaves the first namespace on
disk. Record the names your application creates; TinyJoin does not list or
selectively delete them through its public API. A logical reset also leaves
the namespace allocated. Do not depend on private directory names or remove
individual engine files while a database is open.

To remove obsolete names and their physical storage with current public
facilities, use the browser's **clear site data** control for this origin:

1. Export or otherwise preserve all needed data for every database and other
   application sharing the origin, and verify those backups outside the origin.
2. Stop application work, await all Client closes, and close every page or
   Worker that could reopen these databases.
3. Clear storage for the origin in the browser's site settings. This also
   removes other origin data and offline application caches; it is not a
   TinyJoin-only operation.
4. Reopen the application online, create only its current namespaces, and
   restore the known schemas and retained data before accepting new writes.

If clearing the entire origin is inappropriate, retain the obsolete namespace
until the application has an independently reviewed storage-management policy.
Selective deletion and automatic migration remain separate API decisions.

## Space reuse and quota

`DELETE` and `DROP` free engine pages for internal reuse. The page file normally
keeps its high-water size: fewer rows do not mean a smaller physical file or
immediate browser-quota reclamation. TinyJoin has no public `VACUUM` or
compaction API. Opening a new namespace can temporarily require space for both
databases and does not reclaim the old one.

`navigator.storage.estimate()` can help monitor approximate origin-wide usage
and quota; it is not a per-database size or a reservation. Allow space for
writes and recovery, handle quota failures, and use the deliberate cleanup
procedure above when physical storage must be removed.

## Opening and closing

await create() is the simplest lifecycle: it returns only after
initialization succeeds. The returned Client also exposes `ready`, `waitReady`,
and `closed` for code that constructs a Client directly.

These properties describe initialization and explicit cleanup, not connection
health. `waitReady` resolves once; `ready` can remain `true`, `closed` can remain
`false`, and prepared handles can remain unsealed after a terminal Worker
failure. Operations still reject. Do not poll these properties to decide
whether a failed operation is safe to repeat.

Call close() during application teardown. It seals that Client's prepared
statements, detaches it, and terminates its Worker. Other Clients retain their
data and connection. If the departing Worker owned the database, another
connected Worker automatically opens it. Outstanding cleanup is shared by
repeated close() calls.

Closing is irreversible. A Client, its prepared statements, and its
subscriptions cannot resume after close(). If a `pagehide` handler closes the
database, a page restored from that cache must initialize a new Client and
recreate its statements and subscriptions before accepting work. The
[getting-started example](https://tinyjoin.org/guides/getting-started/#close-cleanly) uses a reload
on `pageshow` when `event.persisted` is true as a simple application policy.

A browser does not await asynchronous `pagehide` cleanup, and teardown events
are not guaranteed to run. Await writes while the application is active;
close() during navigation is not a final-save or cancellation guarantee.

### Terminal Worker failures

A Worker crash (`WORKER_ERROR`), unreadable message (`WORKER_MESSAGE_ERROR`), or
invalid protocol response (`PROTOCOL_MISMATCH`) disposes that connection.
Pending operations reject with the failure; subsequent database operations and
prepared executions reject with `WORKER_TERMINATED`. The Client does not
automatically replace a terminally failed Worker. Its statements' `closed`
properties report whether they were sealed, not whether the Worker is healthy.

Stop accepting work, retain the original error, and call close(). Cleanup seals
the prepared handles and completes Client cleanup even if its Promise rejects
because the Worker is already gone. Open a new Client, recreate statements and
subscriptions, and inspect stored operation identifiers before replaying writes:

```ts
// In the application's recovery path, after preserving the original error:
await db.close().catch((cleanupError) => console.error(cleanupError));
const reopened = await create('opfs://my-app'); // Use the original name.
// Reconcile the original operation using reopened before accepting more work.
```

A crash during a write can lose its acknowledgement after committing. Follow
[uncertain-write reconciliation](https://tinyjoin.org/guides/storage-and-lifecycle/#recovering-after-an-uncertain-write), even
when the error is a Worker failure instead of a storage code. Memory data is
lost with its Worker.

Temporary OPFS owner handover is different: `LEADER_CHANGED` rejects in-flight
operations, but the same Client reconnects and restores prepared statements for
subsequent work. Reconcile interrupted writes; an interrupted transaction ends
with `TRANSACTION_LOST`. The next section describes this recoverable lifecycle.

## Tab handover and subscriptions

The browser's locks elect the next owner when the previous Worker closes or
dies. New operations wait while that owner opens the database. Prepared
statements are restored automatically when needed. Notifications fan out to
every Client, including the one that wrote the data.

Operations already sent to a departing owner reject with `LEADER_CHANGED`.
Their effects may already have committed; TinyJoin never silently repeats
them. The Client reconnects for subsequent operations, so inspect the stored
outcome before retrying a write. A callback transaction interrupted by owner
loss cannot continue: subsequent transaction operations reject with
`TRANSACTION_LOST`. Start a new transaction after reconciliation.

After handover, or when a page becomes visible or resumes, subscriptions may
receive `{revision, tables: [], reset: true}`. Re-query on this notification
even though the precise changed tables are unknown. Filtered subscriptions
also receive it. A subscription is an invalidation signal, not a durable log
of every commit.

Transactions exclude other Clients for the entire callback. Keep callbacks
short and do not wait on work that needs another Client for the same name.
If a client disappears with a transaction open, the owner rolls it back. A
frozen but still live owner or transaction holder can delay other tabs until
it resumes or closes. TinyJoin does not steal a live storage lock based on a
timer, since doing so could let two engines write concurrently.

For offline reopening of the application itself, use the
[offline build integration](https://tinyjoin.org/guides/offline/). OPFS stores the data; a service
worker caches the application and database runtime files.

## Recovering after an uncertain write

A rejected write does not always prove that nothing committed. The following
ClientError codes mean the current engine must no longer be used:

| Code | Meaning |
| --- | --- |
| `RECOVERY_REQUIRED` | The engine cannot safely continue after a storage publication failure; reopening must establish the stored state. |
| `STORAGE_COMMIT_OUTCOME_UNKNOWN` | A storage write or a result after a possible commit could not be confirmed. The requested change may have committed. |
| `STORAGE_ENGINE_POISONED` | A previous uncertain or fatal result already made this engine unusable. |

Stop accepting database work, keep the original error, and close the Client.
For OPFS, open the **same database name** with a new create() call and inspect
the recovered rows before deciding whether to repeat the operation. If open
or inspection fails, keep the application in a recovery state and preserve the
stored data; changing the name or deleting the database would hide the state
you need to reconcile. A new memory database starts empty and cannot recover
the old Client's data.

Give an application operation a stable identifier before its first attempt
and record that identifier in the same transaction as its effects. After
reopening, query that record and compare the intended values. A matching
record means the operation already happened; a missing record may allow a
deliberate retry with the **same identifier**. An unexpected record needs
application reconciliation. Generating a fresh identifier on every retry can
apply an operation twice. TinyJoin has no automatic replay, so this policy
belongs to the application. Recording the identifier with
`ON CONFLICT DO NOTHING` skips an operation that is already recorded, but it
does not compare the recorded values with the intended ones.

The `retryable` property only says that a later attempt or reopen may succeed.
It is not a guarantee that replaying a write is safe, nor that the current
Client remains usable. A rollback attempted after an uncertain commit cannot
establish that the commit was absent. See also
[transaction error handling](https://tinyjoin.org/guides/transactions-and-changes/#errors-and-cancellation).

# Transactions and changes

Source: https://tinyjoin.org/guides/transactions-and-changes/

Each TinyJoin query is atomic. Use a callback transaction when several related
row mutations must commit together.

```ts
await db.transaction(async (tx) => {
  await tx.query('UPDATE accounts SET balance = $1 WHERE id = $2', [40, 'a']);
  await tx.query('UPDATE accounts SET balance = $1 WHERE id = $2', [60, 'b']);
});
```

Reads inside the callback see staged rows. Use the transaction object for all
database work until its callback finishes; direct Client operations fail while
it is active, and a later transaction() call waits its turn. Letting an error
escape before commit discards the staged transaction.

For an OPFS database, other Clients using the same name wait for the entire
callback, including Clients in other tabs. They never read its uncommitted
rows. Do not await work on another Client for that name inside the callback:
that work needs the callback to finish first.

TinyJoin does not put a callback transaction into PostgreSQL's aborted state
after a statement failure. If application code catches that failure, earlier
staged writes may still commit. Call tx.rollback() or rethrow when the whole
unit should be discarded.

Run schema DDL such as `CREATE`, `ALTER`, and `DROP` outside the callback, using
a standalone query() or an atomic exec() script.

## Composing transaction helpers

Nested callback transactions are not supported. In particular, do not await
db.transaction() from inside that same Client's transaction callback: the
inner call queues behind the outer call, while the outer callback waits for
the inner call. Neither can finish, and there is no deadlock detector.

Pass the active Transaction to helpers instead of having each helper open a
transaction:

```ts
import type {Transaction} from 'tinyjoin';

const setBalance = async (tx: Transaction, id: string, balance: number) => {
  await tx.query('UPDATE accounts SET balance = $1 WHERE id = $2', [balance, id]);
};

await db.transaction(async (tx) => {
  await setBalance(tx, 'a', 40);
  await setBalance(tx, 'b', 60);
});
```

Use tx.query(), tx.exec(), or tx.execute() throughout the callback. Prepare
handles before entering it. tx.exec() gives a read/DML script its own atomic
failure boundary within the active transaction; it does not open a nested
transaction or expose general-purpose savepoints.

## Errors and cancellation

An ordinary statement failure changes none of that statement's staged rows.
Rethrow it or call tx.rollback() to discard earlier staged work too. A failure
while publishing a commit has a different boundary: `RECOVERY_REQUIRED`,
`STORAGE_COMMIT_OUTCOME_UNKNOWN`, or `STORAGE_ENGINE_POISONED` requires closing
the Client, reopening the same OPFS name, and reconciling the operation before
replay. Rejection of transaction() alone is not proof that a commit did not
happen. Follow the [recovery procedure](https://tinyjoin.org/guides/storage-and-lifecycle/#recovering-after-an-uncertain-write).

If the database-owning tab closes or crashes, an already sent operation rejects
with `LEADER_CHANGED` and may have committed. A still-running callback cannot
continue on the replacement owner: later transaction operations reject with
`TRANSACTION_LOST`. The Client reconnects automatically for new operations,
but never repeats writes or reruns the callback. Reconcile before retrying.

There is no AbortSignal, query timeout, or transaction timeout option.
Promise.race() with a timer only stops the caller waiting; the callback and
queued database work can continue and may commit. It is not cancellation or
evidence of rollback. Avoid waiting for network requests, user input, or a
nested transaction inside a callback. Keep the callback bounded, and request
an explicit tx.rollback() while it is active when application logic decides
to abandon staged work. Closing a Client is teardown, not a safe way to infer
the outcome of an in-flight write.

## Inserting many rows

Prepare a parameterized `INSERT` once and execute it through the transaction
object. Each statement is validated against totals the transaction keeps for
its staged rows, including unique-index constraints, so it costs what its own
rows cost however many rows are already staged, whether it inserts, updates or
deletes. Final commit still validates and publishes the complete write set.

```ts
const insert = await db.prepare('INSERT INTO tasks (id, title) VALUES ($1, $2)');
try {
  await db.transaction(async (tx) => {
    for (const title of titles) {
      await tx.execute(insert, [crypto.randomUUID(), title]);
    }
  });
} finally {
  await insert.close();
}
```

Repeated tx.exec() calls copy the current transaction state for script
rollback. Keep transactions bounded and prefer multi-row statements when the
application can form them within the
[SQL limits](https://tinyjoin.org/guides/sql-compatibility/#hard-limits).

These limits apply cumulatively across the transaction, even when each
individual statement is small. Transaction reads use the staged row view. A
table with staged changes is read without its secondary indexes or key order,
because its staged rows are not yet indexed; other tables are read as they would
be outside the transaction.

## Re-query after a commit

Subscriptions report changed table names rather than maintaining a live result
object:

```ts
const unsubscribe = db.subscribe({tables: ['tasks']}, async () => {
  const {rows} = await db.query('SELECT * FROM tasks ORDER BY id');
  render(rows);
});
```

The listener runs after commits from any connected Client. Several statements
in one transaction produce one committed revision and one table-level
invalidation. Events arriving while this Client has a transaction open are
coalesced and delivered after its callback finishes, when re-querying is safe.
Call the returned function to unsubscribe before closing the database.

After tab handover or page restoration, a listener can also receive an event
with `reset: true` and an empty `tables` array. Its precise missed changes are
unknown, so every subscription is notified, even one filtered to specific
tables. The example above works for both normal changes and resets because it
always re-queries. See [tab handover](https://tinyjoin.org/guides/storage-and-lifecycle/#tab-handover-and-subscriptions).

This explicit re-query model keeps TinyJoin independent of UI frameworks and
lets an application choose its own caching or rendering policy.

## Refreshing individual rows

An event also names the primary keys that changed, keyed by table, so a
listener can refresh individual rows rather than re-reading a whole table:

```ts
const unsubscribe = db.subscribe({tables: ['tasks']}, async (event) => {
  const keys = event.keys.tasks;
  if (!keys) {
    render(await db.query('SELECT * FROM tasks ORDER BY id'));
    return;
  }
  for (const {id} of keys) {
    refreshRow(await db.query('SELECT * FROM tasks WHERE id = $1', [id]));
  }
});
```

Each key is an object holding only that table's primary-key columns, so a
composite key arrives as one object with one property per column. Keys are
reported for writes from any connected Client, which makes a change made in
another tab resolvable to individual rows.

Reporting is a bounded best effort, and `tables` stays authoritative. A table
appears in `keys` only when every key it changed fits the bound of 1,000 keys
per table. A table listed in `tables` but absent from `keys` changed more rows
than an event can name, so its rows cannot be identified and it must be
re-queried. `keys` is always empty alongside `reset: true` for the same reason.
Always handle the absent case, as the example above does: a listener that
assumes keys are present will silently miss large writes.

# Custom Workers

Source: https://tinyjoin.org/guides/custom-workers/

Most applications should use create(). It constructs TinyJoin's packaged
module Worker and preserves the relative Worker, OPFS runtime, and WebAssembly
assets during the supported Vite build path.

Once per page, create() also starts a second, short-lived copy of the packaged
Worker. It runs the engine's common statements on a scratch in-memory database
for less than a tenth of a second, and then exits. Chromium compiles WebAssembly
one function at a time, as each is first called, and Workers running the same
module share the code either compiles, so the database's own Worker runs its
first statements of each kind without stopping to compile them. The second
Worker needs no permission the first does not, never touches OPFS, and any
failure in it is ignored. A custom Worker does not start one.

For Node.js, use [`tinyjoin/node`](https://tinyjoin.org/guides/node/), which constructs its own
Worker thread and loads WebAssembly without a custom bootstrap.

An application that needs to own the Worker can provide a factory:

```ts
const db = await create({
  workerFactory: () =>
    new Worker(new URL('./tinyjoin.worker.ts', import.meta.url), {
      name: 'tinyjoin',
      type: 'module',
    }),
});
```

The Worker entry starts the same engine and automatic OPFS coordination:

```ts
import {startWorker} from 'tinyjoin/worker';

startWorker();
```

Use at most one of `worker`, `workerFactory`, or `workerUrl`. A custom Worker is
an advanced bundling boundary, not a requirement for persistent storage.

Tabs using the same OPFS name and compatible TinyJoin release share one owner,
even when their Workers use different bundle URLs. Keep startWorker() in the
entry to participate in that protocol. An arbitrary Worker-like replacement is
responsible for implementing its own storage and coordination behavior. The
packaged Worker also requests subscription refreshes when the page becomes
visible or resumes; custom lifecycle integration should re-query on restoration.

Synchronous OPFS access is available in a dedicated Worker, not a SharedWorker.
Ordinary browser Workers also cannot open arbitrary PostgreSQL TCP connections.
Network replication or offline write synchronization would require separately
designed transports, durability, authorization, and conflict semantics; none is
hidden in the current package.

## Production hosting

The default Worker and custom Worker paths are verified with Vite production
builds in Chromium. Other bundlers are not currently verified; a successful
TypeScript build alone does not check runtime asset resolution. Serve and test
the complete emitted build before relying on another bundler.

- Use HTTPS, or a trustworthy localhost origin for development. OPFS and
  service workers require a secure context.
- Keep the application, initial module Worker, service worker, and all runtime
  assets on the same origin. Serve the entire build directory at its configured
  Vite base, preserving the emitted relative paths and private lazy OPFS files.
- Return JavaScript as `text/javascript` and WASM as `application/wasm`.
  Return HTML only for navigation routes: a missing Worker or WASM URL must
  return an error rather than an HTML fallback. JSON manifests use
  `application/json`.
- Deploy the build as one version. Do not remove files still needed by running
  tabs. See [offline updates](https://tinyjoin.org/guides/offline/#safe-application-updates) for
  installation, cache integrity, and waiting-tab behavior.

### Content Security Policy

This restrictive starting policy is tested with the default Worker, OPFS,
two-tab access, and offline close/reopen in a production Vite fixture:

```http
Content-Security-Policy: default-src 'none'; script-src 'self' 'wasm-unsafe-eval'; worker-src 'self'; connect-src 'self'; style-src 'self'; img-src 'self'; base-uri 'none'; object-src 'none'; frame-ancestors 'none'
X-Content-Type-Options: nosniff
```

Send these headers on HTML **and Worker/service-worker JavaScript responses**.
A Worker has its own execution policy; an HTML policy alone does not establish
the Worker script's execution restrictions. Keep the headers on cached
responses too. The fixture checks an offline navigation using those responses.

`worker-src 'self'` permits same-origin Workers and service workers.
`connect-src 'self'` permits runtime and precache fetches.
`script-src 'self' 'wasm-unsafe-eval'` permits the emitted scripts and
WebAssembly compilation without enabling JavaScript `eval`. The narrower
WebAssembly permission is defined by
[Content Security Policy](https://www.w3.org/TR/CSP/#directive-script-src).
No `blob:`, `data:`, inline-script permission, or broad `unsafe-eval` permission
is required by the verified default Vite path.

Adapt styles, images, and other application resources deliberately: this example
uses external scripts and styles on the same origin. Test the actual application
with its response headers; custom Worker URLs and other bundlers may emit a
different asset graph. The production fixture runs with
`npm run build && npm run test:offline:built` in this repository.

# Node

Source: https://tinyjoin.org/guides/node/

Use `tinyjoin/node` to run an in-memory TinyJoin database in Node.js 22 or
later. It uses a dedicated Worker thread and the same WebAssembly engine and
Client API as the browser runtime.

## Get started

Install TinyJoin:

```sh
npm install tinyjoin
```

Save this ESM example as `main.mjs`:

```js
import {create as createNode} from 'tinyjoin/node';

const db = await createNode();
try {
  await db.exec(`
    CREATE TABLE notes (
      id INTEGER PRIMARY KEY,
      body TEXT NOT NULL
    )
  `);
  await db.query('INSERT INTO notes (id, body) VALUES ($1, $2)', [
    1,
    'Hello from Node',
  ]);
  const {rows} = await db.query('SELECT * FROM notes ORDER BY id');
  console.log(rows);
} finally {
  await db.close();
}
```

Run it with `node main.mjs`. No extra dependencies, bundler, Worker entry, or
polyfills are needed. The entry point constructs the Worker and loads the
packaged WebAssembly file from disk automatically.

## API and lifecycle

The Node entry point exports
[`create()`](https://tinyjoin.org/api/node/functions/lifecycle/create/). Call it without arguments,
or with `memory://`, to create a fresh database. The Promise resolves when the
Worker and engine are ready.

The returned Client supports the same parameterized queries, transactions,
prepared statements, subscriptions, and TypeScript row generics as the browser
Client. The [SQL compatibility rules](https://tinyjoin.org/guides/sql-compatibility/) and
[transaction behavior](https://tinyjoin.org/guides/transactions-and-changes/) also apply. The Node
entry point also exports ClientError and the shared Client API types, including
row, result, query-option, transaction, prepared-statement, and subscription
types:

```ts
import {type Client, type Row, create, ClientError} from 'tinyjoin/node';
```

ClientError is the same error class exported by the browser entry point.

Each call owns one Worker thread and an independent database. Always await
db.close() when finished, including error paths, so the Worker is released and
the Node process can exit. A closed Client cannot be reused.

## Storage boundaries

This entry point is experimental and supports memory only. Data is lost when
the Client closes or the process exits. Creating another Client starts with an
empty database, including when both calls use `memory://`.

There is no OPFS or filesystem persistence, shared database between Clients,
or remote synchronization. The Node entry point does not accept the browser
create() options or custom Worker configuration. Use the browser entry point
for [persistent browser storage](https://tinyjoin.org/guides/storage-and-lifecycle/).

# Drizzle

Source: https://tinyjoin.org/guides/drizzle/

Use `tinyjoin/drizzle` to query a TinyJoin database with the
[Drizzle ORM](https://orm.drizzle.team) query builder. It connects Drizzle to a
TinyJoin Client, runs Drizzle's transactions as TinyJoin transactions, and
binds JSON values as JSON.

Drizzle writes PostgreSQL, and TinyJoin runs a
[bounded, PostgreSQL-shaped dialect](https://tinyjoin.org/guides/sql-compatibility/), so most of
Drizzle's query builder works, and some of it does not. This guide says which.

## Get started

Install TinyJoin and Drizzle. TinyJoin declares `drizzle-orm` 0.45 or later as
an optional peer dependency, so an application that does not use Drizzle never
installs it.

```sh
npm install tinyjoin drizzle-orm
```

Describe the tables with Drizzle's PostgreSQL columns, create them with SQL,
and query them through Drizzle:

```ts
import {create} from 'tinyjoin';
import {drizzle} from 'tinyjoin/drizzle';
import {eq, sql} from 'drizzle-orm';
import {boolean, integer, pgTable, text} from 'drizzle-orm/pg-core';

const tasks = pgTable('tasks', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
  done: boolean('done').notNull().default(false),
  edits: integer('edits').notNull().default(0),
});

const client = await create('opfs://my-app-v1');
await client.exec(`
  CREATE TABLE IF NOT EXISTS tasks (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    done BOOLEAN NOT NULL DEFAULT false,
    edits INTEGER NOT NULL DEFAULT 0
  )
`);

const db = drizzle(client, {schema: {tasks}});
await db.insert(tasks).values({id: crypto.randomUUID(), title: 'Try Drizzle'});
await db
  .update(tasks)
  .set({title: 'Tried Drizzle', edits: sql`${tasks.edits} + 1`})
  .where(eq(tasks.done, false));
const open = await db.select().from(tasks).where(eq(tasks.done, false));
```

The Client is still yours: subscribe to it for changes, and close it when the
database is no longer needed. `db.$client` returns it.

## Schemas

Every table needs a primary key. Drizzle's columns map onto TinyJoin's five
runtime types:

| Drizzle column | TinyJoin type |
| --- | --- |
| `boolean` | Boolean |
| `integer`, `smallint`, `bigint({mode: 'number'})` | Integer, within the JavaScript-safe range |
| `real`, `doublePrecision` | Float |
| `text`, `varchar`, with or without a length | Text |
| `json`, `jsonb` | JSON |

TinyJoin has no `serial`, `uuid`, `timestamp`, `date`, `numeric`, enum, or
array types. Generate identifiers in the application,
as `text('id').primaryKey().$defaultFn(() => crypto.randomUUID())` does, and
store a time as an integer or text column, or as a Drizzle `customType` over
one.

Foreign keys work as `.references()` declares them, with their `onDelete` and
`onUpdate` actions; see [foreign keys](https://tinyjoin.org/guides/sql-compatibility/#foreign-keys).
Index a large table's referencing columns.

Create the tables with SQL through the Client, idempotently with
`IF NOT EXISTS`; push the schema from the application, as the next section
describes; or apply the migrations that Drizzle Kit generates. Drizzle Kit's
own `push`, `pull`, and Studio read PostgreSQL's system catalogs, which TinyJoin
does not have; the Client reads the same facts with getSchema().

## Push

push() from `tinyjoin/drizzle` makes the database hold the tables of a Drizzle
schema, as Drizzle Kit's `push` would, but from the application itself and as
one change that commits whole or not at all. There are no migration files to
generate or bundle:

```ts
import {create} from 'tinyjoin';
import {drizzle, push} from 'tinyjoin/drizzle';
import * as schema from './schema';

const db = drizzle(await create('opfs://my-app-v1'), {schema});
await push(db, schema, {version: 2, renames: {'tasks.name': 'title'}});
```

push() reads each table's columns, primary key, unique constraints, and
indexes from Drizzle, and passes them to the Client's
[setSchema()](https://tinyjoin.org/guides/storage-and-lifecycle/#setting-the-schema). It creates
the tables and columns the database lacks, changes defaults, nullability, and
`varchar` lengths to the schema's, and makes each table's indexes the schema's,
keeping every row. A schema the database already holds changes nothing, so
every tab can call push() as it starts.

A rename is not something a schema can show, so name each one in `renames`,
from the new name, as `table` or `table.column`, to the old one; otherwise the
new name is created empty. Tables and columns the schema leaves out stay unless
push() is given `drop: true`. Give each change of schema a higher `version`:
push() then refuses an older schema, from a tab still running an older copy of
the application, with `SCHEMA_OUTDATED`, rather than undoing the change.

Foreign keys are pushed with their actions, and added once every table they
reference is there. A table that declares what TinyJoin cannot hold, such as a
`timestamp` column, an `sql` default, or a descending index, is refused before
anything changes. A default from `$defaultFn` is Drizzle's to fill in, so the
database has none.

Use push() when the schema in code is the record of truth, and
[migrations](https://tinyjoin.org/guides/drizzle/#migrations) when the history of changes, or data moved between
them, matters too. Keep to one of the two for a database.

## Migrations

`drizzle-kit generate` compares the schema with the last one it saw and writes
the SQL that turns one into the other, with a journal of every migration so
far. Configure it for PostgreSQL:

```ts
// drizzle.config.ts
import {defineConfig} from 'drizzle-kit';

export default defineConfig({
  dialect: 'postgresql',
  schema: './src/schema.ts',
  out: './drizzle',
});
```

Drizzle's own migrators read those files from disk and record them in a
`SERIAL` table of a schema of their own, so they cannot run here. migrate()
from `tinyjoin/drizzle` applies the same migrations from SQL that the
application bundles. With Vite:

```ts
import {create} from 'tinyjoin';
import {drizzle, migrate} from 'tinyjoin/drizzle';
import journal from '../drizzle/meta/_journal.json';
import * as schema from './schema';

const db = drizzle(await create('opfs://my-app-v1'), {schema});
await migrate(db, {
  journal,
  migrations: import.meta.glob<string>('../drizzle/*.sql', {
    query: '?raw',
    import: 'default',
    eager: true,
  }),
});
```

migrate() applies, in the journal's order, each migration that the database
has not recorded, and records it in a `__drizzle_migrations` table. A migration
runs as one script together with its record, so it commits whole or not at
all: one that fails rejects with its error and leaves the database as the
migration before it left it. Every tab can call migrate() as it starts, since
a migration that another tab applied first is skipped. A migration must fit in
one script, of at most 255 statements and 1 MiB of SQL.

The SQL that `drizzle-kit generate` writes runs as it is: new tables with their
`UNIQUE` constraints, composite primary keys, `USING btree` indexes, and JSON
defaults; added, renamed, and dropped columns, tables, indexes, and
constraints; and changed defaults and nullability. A column's type can change
only between spellings of one runtime type, such as `integer` and `bigint`,
since TinyJoin cannot convert stored values. Dropping a column, changing its
default, or making it `NOT NULL` rewrites every row of its table, within the
work limits of one script.

## Queries

These work as Drizzle documents them:

- `select`, `insert`, `update`, and `delete`, with `returning`;
- filters such as `eq`, `ne`, `gt`, `and`, `or`, `not`, `isNull`, `like`,
  `ilike`, `between`, and `inArray`, including `inArray` with a subquery;
- comparisons of two columns, and `sql` expressions with `+`, `-`, `*`, `/`,
  `%`, and `||`, in filters, select lists, and `set`;
- `orderBy`, `limit`, and `offset`;
- inner and left joins of up to eight tables, including columns that share a
  name, and table aliases;
- `groupBy` with `count`, `sum`, `avg`, `min`, and `max`, `$count`, and
  `selectDistinct`;
- `onConflictDoNothing` and `onConflictDoUpdate`, assigning `excluded.column`
  in an `sql` template, or an expression of the stored row's columns;
- prepared statements and placeholders;
- relational queries, `db.query.table.findMany()` and `findFirst()`, including
  `with`, and its `columns`, `where`, `orderBy`, `limit`, `offset`, and
  `with` of its own.

Drizzle loads each relation of `with` with a
[nested query](https://tinyjoin.org/guides/sql-compatibility/#nested-queries) for every row it
relates to, so index the columns that relations join on, such as a foreign
key's.

These are refused with an error rather than run:

- `extras` and other SQL functions, such as `lower()`, `coalesce()`, and
  `now()`, casts, and `CASE`;
- `having`, window functions, `$with` CTEs, `union`, correlated subqueries,
  and `exists`;
- right, full, and cross joins, `insert ... select`, and `update ... from`;
- JSON operators.

A failed query rejects with Drizzle's `DrizzleQueryError`, whose `cause` is the
TinyJoin [ClientError](https://tinyjoin.org/api/tinyjoin/classes/errors/clienterror/) with its
`code`. The [SQL compatibility contract](https://tinyjoin.org/guides/sql-compatibility/) is the
exact list of what runs.

## Transactions

Drizzle's `db.transaction` runs on the Client's
[callback transaction](https://tinyjoin.org/guides/transactions-and-changes/). It holds the
database, and every tab sharing it, until the callback finishes, so keep it
short. Use the `tx` it passes, not `db`, inside the callback: awaiting `db`
there waits for the transaction to finish, which waits for the callback.

Drizzle's `tx.rollback` discards the transaction's work and rejects with
`TransactionRollbackError`, as does any error the callback throws. A
transaction cannot nest, since TinyJoin has no savepoints, and cannot take an
isolation level or access mode.

## JSON values

Drizzle encodes a value for a `json` or `jsonb` column as JSON text, which
PostgreSQL parses again. TinyJoin binds every parameter as the JavaScript value
it is, so the driver parses that text back first, and the column stores the
object rather than a string. Reading the column returns the object.

Drizzle's raw `db.execute` resolves to the rows its statement returns, as
objects.

# Kysely

Source: https://tinyjoin.org/guides/kysely/

Use `tinyjoin/kysely` to query a TinyJoin database with the
[Kysely](https://kysely.dev) query builder. Its dialect runs Kysely's queries
on a TinyJoin Client, runs Kysely's transactions as TinyJoin transactions, and
lets Kysely's introspector and Migrator read and change the schema.

Kysely writes PostgreSQL, and TinyJoin runs a
[bounded, PostgreSQL-shaped dialect](https://tinyjoin.org/guides/sql-compatibility/), so most of
Kysely's query builder works, and some of it does not. This guide says which.

## Get started

Install TinyJoin and Kysely. TinyJoin declares `kysely` 0.28 or later as an
optional peer dependency, so an application that does not use Kysely never
installs it.

```sh
npm install tinyjoin kysely
```

Describe the tables as a Kysely database interface, and pass the Client to the
dialect:

```ts
import {create} from 'tinyjoin';
import {TinyJoinDialect} from 'tinyjoin/kysely';
import {Kysely} from 'kysely';

interface Database {
  tasks: {id: string; title: string; done: boolean; edits: number};
}

const client = await create('opfs://my-app-v1');
await client.exec(`
  CREATE TABLE IF NOT EXISTS tasks (
    id TEXT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    done BOOLEAN NOT NULL DEFAULT false,
    edits INTEGER NOT NULL DEFAULT 0
  )
`);

const db = new Kysely<Database>({dialect: new TinyJoinDialect({client})});
await db
  .insertInto('tasks')
  .values({id: crypto.randomUUID(), title: 'Try Kysely', done: false, edits: 0})
  .execute();
await db
  .updateTable('tasks')
  .set((eb) => ({edits: eb('edits', '+', 1)}))
  .where('done', '=', false)
  .execute();
const open = await db.selectFrom('tasks').selectAll().where('done', '=', false).execute();
```

The Client is still yours: subscribe to it for changes, and close it when the
database is no longer needed. Kysely's `db.destroy` leaves it open.

## Schemas and migrations

Every table needs a primary key. Kysely's column types map onto TinyJoin's
five runtime types:

| Kysely data type | TinyJoin type |
| --- | --- |
| `boolean` | Boolean |
| `integer`, `bigint` | Integer, within the JavaScript-safe range |
| `real`, `double precision` | Float |
| `text`, `varchar`, `varchar(n)` | Text |
| `json`, `jsonb` | JSON |

TinyJoin has no `serial`, `uuid`, `timestamp`, `date`, `numeric`, enum, or
array types. Generate identifiers in the application, and store a time as an
integer or text column. Foreign keys work as `references` and
`addForeignKeyConstraint` declare them; see
[foreign keys](https://tinyjoin.org/guides/sql-compatibility/#foreign-keys).

Kysely's schema builder works within TinyJoin's
[DDL](https://tinyjoin.org/guides/sql-compatibility/#statements-and-clauses): creating and dropping
tables and indexes, with primary-key and unique constraints, and altering
tables to add, rename, and drop columns, change defaults and nullability, and
rename the table.

Kysely's `Migrator` works as Kysely documents it. It creates its own tables,
then runs each migration that the database has not recorded. TinyJoin cannot
run DDL inside a transaction, so, as on MySQL, Kysely runs a migration's
statements one at a time: a migration that fails partway leaves the statements
before the failure applied. Write migrations whose statements each stand on
their own, or that an `IF NOT EXISTS` makes safe to run again. A Web Lock keeps
one tab of an application migrating at a time, so every tab can migrate as it
starts.

Instead of migrations, an application can declare its schema and pass it to
the Client's [setSchema()](https://tinyjoin.org/guides/storage-and-lifecycle/#setting-the-schema)
as it starts.

`db.introspection.getTables` lists the tables, without the Migrator's own,
from the Client's getSchema(), with each column's PostgreSQL type: `bool`,
`int8`, `float8`, `text`, `varchar`, or `json`. TinyJoin has no schemas, so
`getSchemas` lists none.

## Queries

These work as Kysely documents them:

- `selectFrom` with `select` and `selectAll`, including `selectAll('table')`
  over a join;
- `where` with comparisons, `and`, `or`, `not`, `in` with values or a
  subquery, `between`, `like`, `ilike`, and `is null`;
- `orderBy`, `limit`, and `offset`;
- inner and left joins of up to eight tables;
- `groupBy` with `countAll`, `count`, `sum`, `avg`, `min`, and `max`;
- `jsonArrayFrom` and `jsonObjectFrom` from `kysely/helpers/postgres`, and
  subqueries in `select` that return one value, which may read the query
  around them, as [nested queries](https://tinyjoin.org/guides/sql-compatibility/#nested-queries)
  that run once for each row;
- `insertInto` with `values`, `defaultValues`, and `onConflict`, with
  `doNothing` or `doUpdateSet`;
- `updateTable` and `deleteFrom`, with `returning` and `returningAll`, and
  arithmetic and `||` in `set`;
- `sql` templates in TinyJoin's dialect.

These are refused with an error rather than run:

- `selectAll()` over a join whose tables share a column name, such as `id`,
  since Kysely reads rows as objects, which cannot hold both;
- `exists`, and subqueries in `where` that read the query around them;
- SQL functions such as `coalesce`, `case`, and casts, outside the JSON
  helpers;
- `distinctOn`, `having`, a distinct `count`, `with`, and `union`;
- `updateTable(...).from` and `deleteFrom(...).using`;
- streaming with `stream`.

A failed query rejects with the TinyJoin
[ClientError](https://tinyjoin.org/api/tinyjoin/classes/errors/clienterror/), with its `code`.
The [SQL compatibility contract](https://tinyjoin.org/guides/sql-compatibility/) is the exact list
of what runs.

## Transactions

Kysely's `db.transaction` and `db.startTransaction` run on the Client's
[callback transaction](https://tinyjoin.org/guides/transactions-and-changes/). It holds the
database, and every tab sharing it, until it commits or rolls back, so keep it
short. A transaction takes no isolation level or access mode, and there are no
savepoints.

Kysely runs one query at a time on the Client, so a query outside a
transaction waits until the transaction ends rather than failing. Use the
transaction Kysely passes inside the callback: a query on `db` there waits for
the transaction to finish, which waits for the callback. Kysely 0.28 does not
run queries one at a time, so there a query outside an open transaction fails
with `TRANSACTION_ACTIVE` instead.

## Results

Rows come back as objects. An integer is a JavaScript number, and a `json`
column holds the value itself, not JSON text, so pass objects rather than
`JSON.stringify` output. Updates and deletes report `numUpdatedRows` and
`numDeletedRows` as Kysely does, as `bigint` values.

# Offline

Source: https://tinyjoin.org/guides/offline/

OPFS persists database rows. Reopening the application without a network also
requires its HTML, JavaScript, CSS, Worker, and WASM files to be available.
TinyJoin's optional Vite plugin caches the complete production build, including
lazy assets that the first visit has not used yet.

## Vite setup

Add tinyjoinOffline to the application's Vite configuration:

```ts
import {defineConfig} from 'vite';
import {tinyjoinOffline} from 'tinyjoin/vite';

export default defineConfig({
  plugins: [tinyjoinOffline()],
});
```

The [starter](https://tinyjoin.org/guides/getting-started/#install) includes this plugin.
No extra Worker entry, asset-copying command, or registration code is needed.
The plugin is a Node-only build integration; importing tinyjoin in application
code does not include it in the browser runtime.

Run the production build and serve the complete output over HTTPS, or localhost
while testing. The Vite development server does not register an offline service
worker. To exercise offline behavior locally, use `vite build` followed by
`vite preview`.

Production hosts must preserve the emitted asset paths, JavaScript/WASM MIME
types, and security headers, including on Worker responses. The
[hosting and CSP checklist](https://tinyjoin.org/guides/custom-workers/#production-hosting) includes
a restrictive policy tested with offline OPFS startup and reopen. The default
and custom Worker paths are verified with Vite in Chromium; other bundlers are
not currently verified.

The first visit needs a network connection. The service worker downloads and
verifies every build file before installation succeeds. After
`navigator.serviceWorker.ready` resolves, a subsequent navigation can use that
installed application offline. The first page is not forcibly taken over while
it is running.

## What gets cached

The plugin produces these files:

| File | Purpose |
| --- | --- |
| `tinyjoin-sw.js` | Application service worker. |
| `tinyjoin-register.js` | External registration script inserted into built HTML. |
| `tinyjoin-precache.json` | Build version, base, and relative asset URLs with SHA-256 content revisions. |
| `tinyjoin-precache.js` | Service-worker helper containing the same manifest. |

Every regular file in the final Vite output is included, including public files,
unused dynamic chunks, and TinyJoin's private lazy OPFS runtime. The service
worker and its manifest/helper are maintained by the browser's service-worker
installation mechanism rather than included recursively in their own cache.

API requests, cross-origin URLs, and files created after the build are outside
this cache. Bundle necessary application resources or provide your own caching
policy for them. Cache Storage and OPFS have separate contents; installing or
updating the application cache does not migrate or delete database rows.
Browsers can still clear or evict either kind of storage. See
[storage retention](https://tinyjoin.org/guides/storage-and-lifecycle/#browser-retention).

The plugin supports Vite bases such as `/`, `/my-app/`, and `./`. Its service
worker only controls the application's base directory. A CDN URL as the Vite
base is not supported because this integration requires same-origin files.

Navigation uses a cached emitted HTML file when one matches. Otherwise it falls
back to `index.html` for client-side routes within the application scope. Change
that file with `tinyjoinOffline({navigationFallback: 'app.html'})`, or disable
the fallback with `tinyjoinOffline({navigationFallback: false})`.

## Safe application updates

A new build gets a separate cache. Its files must all match their recorded
content hashes before it can install. An interrupted or mixed deployment leaves
the previously installed application available.

The new service worker waits until every tab controlled by the old one closes.
Running tabs keep the old application and its old assets, including lazy files
they have not fetched before. Once the replacement activates, it removes this
application's previous TinyJoin caches. Close all application tabs and reopen to
finish an update; reloading only one tab while another remains open can continue
using the old build.

Do not add automatic `skipWaiting()` or `clients.claim()` calls to force updates.
They can make a running application use assets from a different build. Deploy
the complete build together and retain normal static-file MIME types. If a
cached entry is later missing, the helper tries to restore the exact recorded
content from the network. If that content is unavailable or has changed, the
request fails with HTTP 503 rather than mixing in a newer deployment. Close the
application's tabs and reopen online to finish installing a newer release or
repair an incomplete cache from the matching deployment.

Application updates and database schema changes are separate decisions. Keep
schema setup idempotent and observe the
[storage compatibility boundary](https://tinyjoin.org/guides/releases/#v0-1-0).

## Integrating an existing service worker

An application should have one owner for its service-worker lifecycle. Automatic
TinyJoin registration detects another worker already controlling the application
and leaves it alone, with a console message explaining the conflict.

Use manifest mode when an application already owns its service worker:

```ts
export default defineConfig({
  plugins: [tinyjoinOffline({mode: 'manifest'})],
});
```

This emits only `tinyjoin-precache.json` and `tinyjoin-precache.js`. It does not
insert registration code or create `tinyjoin-sw.js`. A classic service worker
beside the build output can use the generated helper:

```js
importScripts('./tinyjoin-precache.js');

const precache = self.createTinyjoinPrecache();

self.addEventListener('install', (event) => {
  event.waitUntil(precache.install());
});
self.addEventListener('activate', (event) => {
  event.waitUntil(precache.activate());
});
self.addEventListener('fetch', (event) => {
  const response = precache.match(event.request);
  if (response) {
    event.respondWith(response);
  }
  // Otherwise leave this request to the application's existing fetch policy.
});
```

Combine this branch with the existing fetch handler so that each request calls
`respondWith` at most once. Keep the native waiting lifecycle described above;
calling activate early would remove assets still needed by old tabs. Register
the existing worker with `updateViaCache: 'none'` so its imported manifest is
checked for changes. For a relative Vite base and a service worker registered
outside the application directory, pass that directory explicitly:
`self.createTinyjoinPrecache(new URL('./my-app/', self.location.origin))`.

An existing precaching system can instead consume the JSON manifest. Resolve
its asset URLs against the application's build base and preserve content
verification and coherent version activation in that integration.

Consume the **complete** asset list, including the private lazy OPFS runtime,
Worker, and WASM files even when the first visit only opens a memory database.
Caching the entry script or the files observed during one online visit is not
sufficient for a later persistent open without a network.

# Benchmarks

Source: https://tinyjoin.org/guides/benchmarks/

TinyJoin is measured against the two databases most often chosen for the same
job: SQLite's official WebAssembly build and PGlite. All three run in a
dedicated Worker, store their data in the origin private file system (OPFS),
and are driven from the page by the same workloads in the same Chromium.

These numbers are published to track progress, not to win an argument.
TinyJoin's engine is young. It is the smallest download and the quickest to
open or reopen a database, and it reads every row, reads rows by key, runs
`LIKE` scans and indexed range aggregates, groups, joins, builds indexes, and
commits single inserts more quickly than either alternative. With OPFS storage,
it is the fastest of the three in half of the workloads, and second in the
other half, taking one to 1.5 times as long as the faster engine: it is never
the slowest. Closing the remaining gaps is ongoing
work, and the suite is designed to be rerun after every optimization.

Measured on 2026-10-04 with headless Chromium 153.0.8010.12 on macOS (25.5.0), Apple M2, 8 logical CPUs, 16 GiB RAM. Each time is the median of 9 samples.

Each workload lists the engines fastest first, and is drawn to its own linear
scale from zero, on which its slowest run reaches the full width, so a quick
workload's bars are as legible as a slow one's. A bar's length and value are
the median of an engine's runs, and a faint bracket at its end spans that
engine's fastest run to its slowest.

## Download

| Size | TinyJoin | SQLite | PGlite |
| --- | ---: | ---: | ---: |
| Download (uncompressed) | **825 KiB** | 1.0 MiB | 16.6 MiB |
| Download (gzip) | **339 KiB** | 457 KiB | 5.6 MiB |
| Download (Brotli) | **279 KiB** | 395 KiB | 3.8 MiB |

Download size counts every file a page fetched to open a database: JavaScript
for the page and the Worker, WebAssembly, and PGlite's file system image. It is
shown uncompressed, compressed with gzip at level 9, and compressed with Brotli
at quality 11, since servers send either.

## Startup

| Median time | TinyJoin | SQLite | PGlite |
| --- | ---: | ---: | ---: |
| Load, create an empty database, and read from it | **51.7 ms** | 85.8 ms | 1.12 s |
| Reopen a 10,000-row database in a new session and count it | **22.9 ms** | 39.0 ms | 217 ms |

First open starts before the engine is fetched and ends when the first query on
a new, empty database returns. Reopen runs in a new browser session with a warm
HTTP cache: it loads the engine again, opens an existing database, and counts
its rows.

Every sample starts in a new browser profile, so each engine's WebAssembly is
compiled afresh, one function at a time as each is first called. As it does in
any page, TinyJoin's create() also starts a short-lived second Worker that
compiles the engine's common statements on a scratch database, so its timed
statements mostly run already compiled. SQLite and PGlite compile theirs as
they go.

Compiled is not yet optimized. A browser optimizes a function only once it has
run many times, and every timed workload is the first run of its statements in
its page, so every engine's statements run faster when run again: a thousand
point reads by key take TinyJoin about 21 ms and SQLite about 25 ms by the
fifth run in one page, against 27 and 29 the first time. See [custom Workers](https://tinyjoin.org/guides/custom-workers/).

## Create

| Median time | TinyJoin | SQLite | PGlite |
| --- | ---: | ---: | ---: |
| 1,000 INSERTs, each committed alone | **418 ms** | 1.61 s | 452 ms |
| One transaction of 10,000 INSERTs | 270 ms | **197 ms** | 1.05 s |
| One transaction of 10,000 INSERTs, indexed text column | 277 ms | **212 ms** | 1.08 s |
| One transaction of 50 INSERTs, 200 rows each | 48.5 ms | **44.1 ms** | 101 ms |

## Read

| Median time | TinyJoin | SQLite | PGlite |
| --- | ---: | ---: | ---: |
| 1,000 SELECTs by primary key | **27.4 ms** | 29.0 ms | 137 ms |
| 100 range aggregates, no index | 48.0 ms | **44.4 ms** | 78.0 ms |
| 100 LIKE aggregates on a text column | **113 ms** | 141 ms | 180 ms |
| 100 range aggregates, indexed column | **3.40 ms** | 3.50 ms | 19.1 ms |
| Read all 10,000 rows in order | **6.40 ms** | 44.8 ms | 45.9 ms |
| 10 GROUP BY aggregates over 10,000 rows | **15.6 ms** | 31.4 ms | 21.1 ms |
| 100 joins: one customer’s orders from 5,000 | **17.5 ms** | 19.9 ms | 31.9 ms |

## Update

| Median time | TinyJoin | SQLite | PGlite |
| --- | ---: | ---: | ---: |
| One transaction of 1,000 UPDATEs by primary key | 34.1 ms | **24.2 ms** | 126 ms |
| One transaction of 100 range UPDATEs, no index | 62.2 ms | **48.7 ms** | 81.7 ms |
| One transaction of 1,000 upserts, half of them new | 35.1 ms | **22.8 ms** | 129 ms |

## Delete

| Median time | TinyJoin | SQLite | PGlite |
| --- | ---: | ---: | ---: |
| One transaction of 1,000 DELETEs by primary key | 32.2 ms | **21.4 ms** | 119 ms |
| One DELETE matching a LIKE pattern | 5.00 ms | 9.80 ms | **4.30 ms** |
| One DELETE of 8,000 rows by indexed range | 7.30 ms | 11.1 ms | **6.00 ms** |

## Schema

| Median time | TinyJoin | SQLite | PGlite |
| --- | ---: | ---: | ---: |
| Create two indexes over 10,000 rows | **13.2 ms** | 20.5 ms | 16.2 ms |

## What the results show

In the results above, TinyJoin is the smallest download and the quickest to
create a new database, where PGlite initializes a new PostgreSQL cluster, and
to reopen one. It reads all 10,000 rows in order seven times as fast as either
engine, reads rows by key in about 6% less time than SQLite, runs indexed range
aggregates as quickly, runs `LIKE` scans, `GROUP BY`, and joins faster than
either, builds indexes and commits single inserts faster than either, and
deletes rows in bulk faster than SQLite. Across the 20 timed workloads, it
places like this, where engines that tie share a place:

| Engine | Fastest | Second | Slowest |
| --- | ---: | ---: | ---: |
| TinyJoin | 10 | 10 | 0 |
| SQLite | 8 | 7 | 5 |
| PGlite | 2 | 3 | 15 |

Where it comes second, it takes at most 1.5 times as long as the faster
engine.

- **Single statements** cost about 27 to 35 microseconds each. About 14
  microseconds of every round trip is the message between the page and the
  Worker, which every engine pays. A point read by primary key takes about 6%
  less time than SQLite's, but an update, upsert, or delete by primary key
  takes 1.4 to 1.5 times as long: a write in a transaction costs 10 to 12
  microseconds more, in TinyJoin's Worker, in planning, checking, and staging
  the row, and in its share of the commit.
- **Scans** take under a tenth longer than SQLite's. TinyJoin reads each
  column in place from the stored row, and each leaf's rows from its own copy
  of the leaf, and compares an integer column with integer bounds directly. A
  range `UPDATE` inside a transaction also passes over the rows the
  transaction has already changed, finding them leaf by leaf, and takes about
  1.3 times as long as SQLite's.
- **Inserts** one at a time take about 1.3 to 1.4 times as long as SQLite's,
  for the same reasons as single statements, and 200 at a time about a tenth
  longer.
- **Bulk deletes** take about 1.2 times as long as PGlite's, which, like
  PostgreSQL, only marks deleted rows and leaves reclaiming their space to a
  later vacuum. TinyJoin removes each row and its index entries at once, and
  rewrites every page they occupied, and is still quicker than SQLite at both.
- **Committing each insert alone** is dominated by the storage flush, one per
  commit, and writes only the pages the insert changed and the superblock. It
  takes about 7% less time than PGlite's commits, and about a quarter as long
  as SQLite's. Its time is mostly the flushes, so it varies most of any
  workload with the state of the disk.
- **Reopening** reads the database's catalog rather than every row, so a
  populated database reopens in under three-fifths of SQLite's time, and a
  large one as quickly as a small one. The full check of every row and index
  entry runs only when an application calls check().

## Features are not equivalent

The workloads use only SQL that all three engines accept, which is TinyJoin's
bounded dialect. That favors TinyJoin: SQLite and PGlite do far more, and an
application that needs what they add should use them. The
[caveats](https://tinyjoin.org/guides/caveats/) and the
[SQL compatibility contract](https://tinyjoin.org/guides/sql-compatibility/) describe TinyJoin's
boundaries in full.

| | TinyJoin | SQLite | PGlite |
| --- | --- | --- | --- |
| SQL dialect | A bounded, PostgreSQL-shaped subset | SQLite | PostgreSQL |
| Subqueries, CTEs, set operations | Uncorrelated `IN (SELECT ...)`, and nested queries that gather rows into JSON | Yes | Yes |
| Expressions, casts, scalar functions | Arithmetic and `\|\|`, without casts or functions | Yes | Yes |
| `HAVING`, window functions | No | Yes | Yes |
| Views, triggers | No | Yes | Yes |
| Joins | Up to eight sources, evaluated as written, through key lookups, indexes, or hash tables | Query planner, with indexes | Query planner, with indexes |
| `INSERT ... SELECT`, `UPDATE ... FROM` | No | Yes | Yes |
| Upserts | `ON CONFLICT`, assigning expressions of the stored and proposed rows | Yes | Yes |
| Types | Boolean, safe integer, float, text, JSON | Dynamic: integer, real, text, blob | The PostgreSQL type system |
| JSON | Stored, returned, and compared for equality | JSON functions | `json` and `jsonb` operators and functions |
| Full-text search | No | FTS5 | `tsvector` |
| Extensions | No | Compiled in only | PostgreSQL contrib extensions |
| Transactions | A callback API | SQL, with savepoints | SQL, with savepoints |
| Worker | create() owns it | The Worker1 API, or the application's own | `PGliteWorker`, or the application's own |
| Tabs sharing a database | Automatic, with owner handover | Not with `opfs-sahpool`, which is exclusive | `PGliteWorker` leader election |
| Change notifications | Changed tables and primary keys | Update hooks in the C API | Live queries, `LISTEN`/`NOTIFY` |
| Node.js | In memory | In memory | In memory or on disk |
| Maturity | Experimental, v0.x | Decades of production use | The PostgreSQL engine, in a v0.x package |

## How the benchmark works

### The engines

| Engine | Package | Version | Storage |
| --- | --- | --- | --- |
| TinyJoin | `tinyjoin` | 0.5.0 | `opfs://` |
| SQLite | `@sqlite.org/sqlite-wasm` | 3.53.4-build1 (3.53.4) | `opfs-sahpool` |
| PGlite | `@electric-sql/pglite` | 0.5.8 (PostgreSQL 18.3) | `opfs-ahp://` |

SQLite uses `opfs-sahpool`, the SQLite project's fastest OPFS file system. Like
TinyJoin's storage, it holds its files exclusively, and it needs no
cross-origin isolation headers. PGlite uses `opfs-ahp`, its OPFS file system.
Every engine keeps its default durability: no relaxed flushing, no
`PRAGMA synchronous`, and no in-memory journal.

### One page, three Workers

A single Vite production build contains a small harness and one chunk per
engine, so a page downloads only the engine it opens. Each engine is reached
through the same five calls: exec() for a parameter-free script, query() for
one statement, prepare() and run() for a repeated statement, and
transaction() for a callback.

- TinyJoin's adapter maps those calls onto create() and the Client API, which
  owns its Worker.
- SQLite and PGlite each run in a Worker of a few dozen lines, answering one
  message per statement. The SQLite Worker caches prepared statements by SQL
  text; PGlite receives the SQL text with every statement.
- A transaction is one round trip per statement for every engine: SQLite and
  PGlite send `BEGIN` and `COMMIT` as statements, and TinyJoin sends each
  statement inside transaction().

Every query result is delivered to the page as an array of row objects, so
timings include the message from the Worker, as an application would
experience it.

### Isolated samples

Every sample launches Chromium with a fresh profile in a new temporary
directory. The profile is on disk, not an incognito context, whose storage would
be held in memory. No engine ever sees another's files or warm caches, and a
sample that exceeds the time limit can be killed without affecting the next.
Engines take turns within each round of samples, and each round starts with
the next engine, so gradual changes in machine load affect them equally. Before
each round, the runner times a short computation, and waits until it runs
within 5% of its time at the start: a laptop slows as it heats over a long run,
and background work takes its cores, and either would otherwise weigh on
whichever workloads ran then.

In each sample, the harness opens a new database, runs the workload's untimed
setup, and then times only the workload itself with performance.now(). Setup
loads rows as multi-row `INSERT` statements in one transaction.

### Checked results

Every workload ends with an untimed check, such as a row count, a column sum,
or a total accumulated from the query results. The runner compares the checks
of all three engines, and fails if any disagree, so every engine is known to
have done the same work.

### Workloads

All tables have an integer primary key. The main table has 10,000 rows with a
sequential integer, a random integer below 100,000, that number spelled out in
English words, and one of 100 group numbers. The data is generated from a fixed
seed, so it is identical for every engine and every run.

| Workload | Group | Speed test origin |
| --- | --- | --- |
| `cold-open`: Load, create an empty database, and read from it | Startup |  |
| `reopen`: Reopen a 10,000-row database in a new session and count it | Startup |  |
| `insert-autocommit`: 1,000 INSERTs, each committed alone | Create | Test 1 |
| `insert-transaction`: One transaction of 10,000 INSERTs | Create | Test 2 |
| `insert-indexed`: One transaction of 10,000 INSERTs, indexed text column | Create | Test 3 |
| `insert-batch`: One transaction of 50 INSERTs, 200 rows each | Create |  |
| `select-pk`: 1,000 SELECTs by primary key | Read |  |
| `select-scan`: 100 range aggregates, no index | Read | Test 4 |
| `select-like`: 100 LIKE aggregates on a text column | Read | Test 5 |
| `select-indexed`: 100 range aggregates, indexed column | Read | Test 7 |
| `select-all`: Read all 10,000 rows in order | Read |  |
| `group-by`: 10 GROUP BY aggregates over 10,000 rows | Read |  |
| `join`: 100 joins: one customer’s orders from 5,000 | Read |  |
| `update-pk`: One transaction of 1,000 UPDATEs by primary key | Update | Test 9 |
| `update-scan`: One transaction of 100 range UPDATEs, no index | Update | Test 8 |
| `upsert`: One transaction of 1,000 upserts, half of them new | Update |  |
| `delete-pk`: One transaction of 1,000 DELETEs by primary key | Delete |  |
| `delete-like`: One DELETE matching a LIKE pattern | Delete | Test 12 |
| `delete-range`: One DELETE of 8,000 rows by indexed range | Delete | Test 13 |
| `create-index`: Create two indexes over 10,000 rows | Schema | Test 6 |

Several workloads are adapted from the classic SQLite database speed
comparison, which [PGlite](https://pglite.dev/benchmarks) also publishes
results for. The adaptations add a primary key to every table, use smaller
counts, and replace the forms TinyJoin could not run when they were written:
arithmetic in `UPDATE ... SET` becomes a literal assignment, and the
`INSERT ... SELECT` tests, which it still cannot run, are omitted.

The join places 5,000 orders across 100 customers, and each query reads one
customer's orders through the index on `customer_id`.

A sample that runs longer than 60 seconds is abandoned and reported as such,
and that engine skips the workload's remaining samples.

### What this does not measure

- One desktop machine and one browser. Mobile devices, Firefox, and Safari
  are not measured.
- Assets are served over loopback HTTP, so download sizes are counted, but
  network transfer time is not.
- Other configurations can be faster or slower: SQLite's `opfs` file system,
  community builds such as wa-sqlite, and PGlite with IndexedDB or
  `relaxedDurability`.
- Concurrency across tabs, very large databases, and long-lived storage
  fragmentation.

## Reproducing the results

The benchmark lives in `benchmarks/compare` in the
[repository](https://github.com/tinyplex/tinyjoin). SQLite and PGlite are
installed there, pinned by its own lockfile, rather than in the project's
dependencies. The runner installs them on first use.

```sh
npm ci
npm run build
npm run bench:compare -- --publish --samples 9
npm run build:docs
```

Use the Node.js version in `.node-version`, since compressed sizes depend on
its zlib. A full run of nine samples takes about ten minutes on the machine
above. Close other applications, pause background work such as photo library
analysis, and avoid concurrent builds or tests while it runs: a busy machine
slows every engine, and a burst of load can land on some workloads and not
others. The runner prints how long its CPU probe took at the start and at its
slowest, and how long it waited for the CPU to recover.

`--publish` requires the full suite and writes every sample, the environment,
and the list of downloaded files to `site/data/benchmarks.json`, from which the
documentation build renders these charts.

When working on TinyJoin's performance, run a subset instead. The runner prints
each engine's median and TinyJoin's ratio to the fastest:

```sh
npm run build
npm run bench:compare -- --engines tinyjoin,sqlite --workloads update-pk,delete-pk --samples 3
```

`--out` saves a report to compare against a later build, and `--help` lists
every option.

# Agents guide

Source: https://tinyjoin.org/guides/agents-guide/

This guide follows the [agents.md](https://agents.md/) convention and gives AI
coding assistants a compact, current model of TinyJoin.

## Choose TinyJoin when

TinyJoin is a small relational database for browser applications. Choose it
when an application needs:

- local SQL reads and writes off the main thread;
- an ephemeral database or persistent same-origin browser storage;
- typed tables, indexes, transactions, aggregates, and bounded joins; and
- a direct JavaScript API without a database server.

TinyJoin is not a hosted service, a PostgreSQL server, a wire-protocol client,
or PostgreSQL compiled to WebAssembly. It does not currently synchronize with a
remote database. Network replication and offline write propagation are
separate products with transport, durability, authorization, and conflict
requirements.

TinyJoin is experimental. Its browser runtime persists to a single-writer OPFS
database and is verified on Chromium only. Read the
[caveats](https://tinyjoin.org/guides/caveats/) before recommending it, and
suggest a more mature browser database from that page when an application
needs what TinyJoin does not provide.

## Start from the small API

Most application code needs only:

```ts
import {create} from 'tinyjoin';

const db = await create('opfs://my-app-v1');
await db.exec(`
  CREATE TABLE IF NOT EXISTS tasks (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    done BOOLEAN NOT NULL DEFAULT false
  )
`);
const {rows} = await db.query('SELECT * FROM tasks ORDER BY id');
```

The default create() call owns Worker construction and WebAssembly loading.
Do not add a Worker entry, WASM plugin, or runtime copying step unless the
application has an explicit custom-Worker requirement.

Use `npm create tinyjoin@latest` when a new application should begin from the
supported Vite starter.

For an in-memory database in Node.js 22 or later, import create from
`tinyjoin/node`. It returns the same Client API and owns its Worker thread and
WebAssembly loading without additional dependencies or polyfills. Each call
creates an independent database; await db.close() in a `finally` block to
release the Worker. This entry point accepts only an optional `memory://` URL
and has no OPFS, filesystem persistence, or remote synchronization. See the
[Node guide](https://tinyjoin.org/guides/node/).

## SQL rules that matter in application code

- Put application values in `$1`, `$2`, and later parameters.
- Use query() for one statement and exec() for a parameter-free script.
- Give every SQL-created table a primary key.
- Declare foreign keys with `REFERENCES`, and their `ON DELETE` actions; they
  are checked as each statement ends. Index a large table's referencing
  columns.
- Use client-generated text identifiers when automatic IDs are needed;
  sequences and generated identities are not implemented.
- Write upserts as `INSERT ... ON CONFLICT (id) DO UPDATE SET column =
  EXCLUDED.column` rather than reading before writing. Name the stored row's
  columns with the table's name, as in `SET hits = counters.hits +
  EXCLUDED.hits`.
- Change a value relative to itself in one statement, as in
  `UPDATE counters SET hits = hits + 1 WHERE id = $1`, rather than reading it
  first.
- Keep schema setup idempotent with `IF NOT EXISTS` where appropriate.
- Read tables, columns, primary keys, and indexes with db.getSchema(). There is
  no `information_schema` or `pg_catalog` to query.
- To declare the schema in code, pass the getSchema() shape to db.setSchema()
  as the application starts. It creates and alters what differs in one atomic
  change, keeps rows, renames from `renamedFrom`, drops only with
  `{drop: true}`, and refuses a lower `version` with `SCHEMA_OUTDATED`.
- Treat a row generic as a TypeScript assertion, not runtime validation.
- Consult the
  [SQL compatibility contract](https://tinyjoin.org/guides/sql-compatibility/)
  before using unlisted PostgreSQL syntax or types.
- Joins run left to right as bounded nested loops, without reordering or
  index-based join lookup. Check actual workload size against the join limits.

Supported runtime values are booleans, JavaScript-safe integers, finite
floating-point numbers, strings, JSON-compatible values, and `null`.

## Drizzle

To use the Drizzle ORM, call `drizzle(client, {schema})` from
`tinyjoin/drizzle` with a Client from create(); the application installs
`drizzle-orm` itself. Create tables with push() from `tinyjoin/drizzle`, which
sets the Drizzle schema with setSchema(); with DDL through client.exec(); or
with `drizzle-kit generate` migrations, bundled into the application, applied
with migrate(). Drizzle's own migrators and Drizzle Kit's `push` do not work. Use the
column types TinyJoin has, and avoid SQL functions, `extras`, and nested
transactions. Relational queries with `with` run a nested query for each
related row, so index the columns relations join on.
See the [Drizzle guide](https://tinyjoin.org/guides/drizzle/).

## Kysely

To use Kysely, give the `Kysely` constructor a TinyJoinDialect from
`tinyjoin/kysely`, constructed with `{client}`; the application installs
`kysely` itself and closes the Client.
Kysely's Migrator and introspector work. Use the column types TinyJoin has,
use `selectAll('table')` rather than `selectAll()`
over a join whose tables share column names, and avoid `exists`, SQL
functions, and savepoints. `jsonArrayFrom` and `jsonObjectFrom` run a nested
query for each row, so index the columns they match on. See the
[Kysely guide](https://tinyjoin.org/guides/kysely/).

## Transactions and changes

Use db.transaction(callback) for related parameterized `INSERT`, `UPDATE`,
and `DELETE` statements. Use the transaction object inside the callback and do
not retain it. Run DDL outside the callback.

Pass the active Transaction to helpers; awaiting another db.transaction() on
the same Client inside its callback deadlocks. There is no AbortSignal or
timeout API. Promise.race() stops waiting but does not cancel a write.

An uncaught callback error before commit discards staged work. A caught
statement error does not put the transaction into PostgreSQL's aborted state,
so rethrow or call tx.rollback() when earlier staged changes must also be discarded.

Each statement is validated incrementally against the transaction's staged
rows, so its cost does not grow with the transaction. Keep transactions bounded
and prefer multi-row writes where practical.

Subscriptions report changed tables. Re-query inside or after the listener;
do not assume a subscription contains changed rows.

## Storage and cleanup

- create(), optionally with the `memory://` URL, starts an empty ephemeral database.
- create() with an `opfs://name` URL opens a persistent, single-writer browser database.
- Clients using the same name automatically share one owner across tabs.
  Different names have independent data and do not synchronize.
- Transactions hold all Clients for that name until their callbacks finish.
  Keep callbacks short; a frozen live owner can delay other tabs.
- Owner loss reconnects automatically. Pending operations fail with
  `LEADER_CHANGED` and must be reconciled before replay; interrupted
  transactions fail with `TRANSACTION_LOST`. Never automatically retry writes.
- Subscription events with `reset: true` require a re-query even when
  `tables` is empty; they cover handover and page restoration.
- `create-tinyjoin` generates offline-capable production builds. Existing Vite
  apps can add tinyjoinOffline() from `tinyjoin/vite`; apps with an existing
  service worker use its `manifest` mode. See the
  [offline guide](https://tinyjoin.org/guides/offline/). Caching does not add remote synchronization.
- Keep the OPFS name stable and version it deliberately with the schema.
- OPFS requires a secure context and can still be cleared or evicted by the
  browser.
- Call db.close() on teardown so storage locks and the Worker are released.
- Opening checks only the catalog; db.check() reads every row and index entry
  and rejects with the first problem found. Nothing repairs a failed check:
  restore an application backup into a new OPFS name.
- After `RECOVERY_REQUIRED`, `STORAGE_COMMIT_OUTCOME_UNKNOWN`, or
  `STORAGE_ENGINE_POISONED`, stop work, close and reopen the same OPFS name,
  and reconcile stable operation identifiers before replay. `retryable` is
  not a safe-replay guarantee. Follow the
  [recovery guide](https://tinyjoin.org/guides/storage-and-lifecycle/#recovering-after-an-uncertain-write).

The [full agent reference](https://tinyjoin.org/llms-full.txt) combines all guides and documented
public TypeScript declarations. Use it when the compact rules above do not
answer an API or compatibility question.

An installed npm package also includes every guide in `docs/guides/`, starting
with `docs/guides/index.md`, and documented declarations in `@types/`. The links
to guides in the packaged `agents.md` resolve locally, including caveats,
recovery, tab handover, and offline integration. These contracts can be read
without accessing the website.

## Repository work

The TypeScript client and Worker host live in `src/`. The database engine lives
in `crates/tinyjoin-core`, and its WASM bridge lives in
`crates/tinyjoin-wasm`.

Public declarations are authored under `src/@types/`. Documentation comments
in each matching `docs.js` file are merged into the declarations during the
build. Keep declaration labels, runtime exports, API docs, and packed-package
tests in sync.

Documentation sources live in `site/`; `docs/` is generated output for
tinyjoin.org. `README.md` and `releases.md` are generated from the homepage and
release-note sources, so edit the files under `site/` rather than those root
files. `site/data/sizes.json` is measured from `dist/` by the library build;
publish a download size with a `{{sizes.<group>.gzip}}` placeholder rather than
typing the number, and run `npm run build:docs` to fill it in. Write internal links in those sources as root-relative URLs. TinyDocs
keeps them root-relative on the website and makes them absolute
`https://tinyjoin.org/...` URLs in the generated Markdown. This guide also
becomes `agents.md` in the publishable package.

Useful validation commands are:

```sh
npm run typecheck
npm run test:ts
npm run test:rust
npm run build
npm run build:docs
npm run check:docs:committed
npm run test:browser
npm run test:package
npm run check:size
```

`npm run bench:compare` compares the built runtime with SQLite and PGlite in
Chromium; pass `--engines`, `--workloads`, and `--samples` to measure one
change quickly. Only a full `--publish` run updates
`site/data/benchmarks.json`, which the benchmarks guide renders as charts.

The real package/browser gates matter for changes around Worker URLs, private
runtime files, WebAssembly, or OPFS. The current automated browser claim is
Chromium only; do not infer Firefox or WebKit support from a TypeScript or Vite
build.

# Releases

Source: https://tinyjoin.org/guides/releases/

This is a reverse chronological summary of TinyJoin releases and their public
compatibility boundaries. Every entry states what upgrading to it requires, so
check the entries between the version in use and the target before upgrading.
A release that needs no action says so explicitly.

## v0.5.0

This release connects TinyJoin to the [Drizzle ORM](https://orm.drizzle.team)
and the [Kysely](https://kysely.dev) query builder, and adds what they need: a
typed schema API, foreign keys, fuller `ALTER TABLE`, and more of the
[SQL dialect](https://tinyjoin.org/guides/sql-compatibility/) that hand-written statements and
query builders use. It also fixes two v0.4.0 bugs, and reads rows faster.

**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`.

The new `tinyjoin/drizzle` entry point connects the
[Drizzle ORM](https://orm.drizzle.team) 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. Its
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](https://tinyjoin.org/guides/drizzle/).

The new `tinyjoin/kysely` entry point connects the [Kysely](https://kysely.dev)
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](https://tinyjoin.org/guides/kysely/).

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](https://tinyjoin.org/guides/storage-and-lifecycle/#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](https://tinyjoin.org/guides/storage-and-lifecycle/#setting-the-schema).

**Foreign keys are 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](https://tinyjoin.org/guides/sql-compatibility/#foreign-keys).

`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](https://tinyjoin.org/guides/sql-compatibility/#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`.

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](https://tinyjoin.org/guides/sql-compatibility/#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](https://tinyjoin.org/guides/sql-compatibility/#repeated-output-names).

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](https://tinyjoin.org/guides/sql-compatibility/#expressions).

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.

`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](https://tinyjoin.org/guides/sql-compatibility/#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](https://tinyjoin.org/guides/sql-compatibility/#nested-queries).

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 needs no action.** 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.

## v0.4.0

**Persistent storage breaks compatibility with v0.3.0 and earlier.** v0.4.0
introduces page format 3, which stores each row as packed binary columns
rather than as JSON text, and encodes keys so that they sort in the same order
SQL compares their values. Rows take less space, a query can read the columns
it needs without parsing the rest of the row, and `ALTER TABLE ... ADD COLUMN`
no longer rewrites every existing row.

v0.1.0 through v0.3.0 use page format 2. Opening a page format 2 database with
v0.4.0 fails with `UNSUPPORTED_PAGE`, and so does opening a page format 3
database with an earlier release. There is no automatic migration, and changing
the OPFS name does not copy existing data.

- For data that can be reconstructed, use a new OPFS namespace, for example
  change `opfs://my-app-v2` to `opfs://my-app-v3`, and rebuild the database.
  The old namespace remains intact.
- To retain existing data, export the application's known tables using a
  client pinned to `tinyjoin@0.3.0` before upgrading. Create the schema in a
  new namespace with v0.4.0, import rows with parameterized statements, and
  verify the data before retiring the old database. TinyJoin does not yet
  provide a general database export or migration API.
- New apps generated by create-tinyjoin v0.4.0 target `tinyjoin@^0.4.0` and
  use a `-db-v3` storage name. Updating the generator does not migrate
  applications it generated previously.

In-memory databases, including every `tinyjoin/node` database, are unaffected.

**Object rows now list their columns in field order.** A row's keys follow the
result's `fields`, as in PostgreSQL, so `SELECT title, id` returns rows whose
first key is `title`, and a changed key lists a composite primary key's columns
in the order the key declares them. Earlier releases sorted both alphabetically.
Code that reads a row or key by column name is unaffected; only code that
depends on key order, such as `Object.keys(row)` or `JSON.stringify(row)`, sees
a difference.

**The Worker protocol version moves from 8 to 10.** 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. A mismatched pair fails cleanly with `PROTOCOL_MISMATCH`, and tabs
running different releases fail with `DATABASE_VERSION_MISMATCH`, rather than
misreading each other's results.

**Numbers keep every bit.** Numbers in JSON values and SQL text are now read
with correct rounding. Earlier releases read about one in ten numbers that
need all 17 significant digits, such as many results of `Math.random()`, as a
neighboring value. In v0.3.0 and earlier, a row holding one could then not be
read at all: reading it failed with `PAGED_STORAGE_CORRUPT`, because the row
no longer matched its stored text.

**Opening a database checks its catalog, not every row.** Earlier releases
read every row and index entry when opening a database, so opening took time
in proportion to its size, and refused a database that failed. Each page is
still checked when it is read, and the new check() reads the whole database on
demand, rejecting with the first problem it finds. A database whose damage no
statement reads now opens, and only check() reports it. See
[checking a database](https://tinyjoin.org/guides/storage-and-lifecycle/#checking-a-database).

This release is also much faster. In the
[comparative benchmarks](https://tinyjoin.org/guides/benchmarks/), most workloads in v0.3.0 took 10
to 1,400 times as long as the faster of SQLite and PGlite. None now takes as
much as twice as long, and reopening a database, reading every row, `LIKE`
scans, `GROUP BY`, joins, building indexes, and committing a single insert are
quicker than in either.

- Updates, upserts, and deletes inside a transaction no longer slow down as the
  transaction grows. Each statement is checked against running totals rather
  than the whole write set, so a thousand of them take about 50 ms rather than
  more than half a minute.
- Comparisons, `BETWEEN`, and `LIKE` patterns that start with literal
  characters read a range of an index or of the primary key, rather than every
  row. An aggregate that reads only the index's columns is answered from the
  index alone. See [constraints and indexes](https://tinyjoin.org/guides/sql-compatibility/#constraints-and-indexes)
  for when a range is used.
- Joins find each table's matching rows by key lookup, through an index, or in
  a hash table, rather than comparing every pair of rows. The join budget now
  counts the rows each lookup actually returns, so a join is never rejected
  because the Cartesian product of its tables is large.
- A query ordered by the leading primary-key columns reads rows in key order
  and stops at its `LIMIT`, rather than collecting and sorting every match, so
  it is no longer held to the limit on ordered matching rows. Inside a
  transaction that has changed the table, it still sorts.
- Scans read only the columns a statement uses, straight from the stored row,
  and so does a statement that finds its row by primary key.
- Each row a statement writes is planned, checked, and measured once. A
  transaction keeps the rows it stages as the encoded records its commit
  writes, rather than as maps it copies and encodes again at commit, and reads
  them in place, as it does stored rows.
- Writes apply each statement's rows to each B-tree in one pass, and index
  builds sort their entries first. A commit writes each page once, a run of
  consecutive pages with a single storage call, and makes them durable with one
  flush rather than three. Its superblock records a fingerprint of the pages it
  wrote, so reopening after an interrupted commit returns to the last complete
  one.
- Rows travel from the engine to the page as JSON text, which only the page
  parses, rather than as objects built one property at a time and then copied
  between threads. A point query takes about a quarter less time, and reading
  10,000 rows well under half. A statement that publishes nothing, such as a
  read or a statement inside a transaction, passes the rest of its result
  through the Worker as text too, and point statements take 4 to 7% less
  time.
- On the tab that owns a database, statements reach the engine as calls rather
  than as messages checked again at every layer.
- A plain `INSERT` plans each row straight into the record its commit writes,
  rather than into a map that staging encodes again. So does an `UPDATE` that
  leaves each row's key in place: it rewrites the stored record, keeping the
  bytes of every column it does not assign, and 1,000 updates by key take about
  a tenth less of the engine's time. A single-row upsert rewrites the stored
  row it conflicts with the same way.
- Staging a single-row statement no longer copies the transaction's
  bookkeeping, and each statement's request and result are checked, and its
  result read, with less work on both sides of the Worker. Point inserts,
  updates, and deletes in a transaction take 5 to 10% less time.
- Scans take each leaf's rows straight from the copy of the leaf they hold,
  checking the tree only as they move between leaves, and read an inline cell
  and an integer column without the general decoders. A range aggregate over
  10,000 rows takes about a quarter less time in the browser.
- A statement's changed rows are kept in the order its scan finds them, rather
  than sorted into maps as they arrive, so deleting 8,000 rows takes about a
  fifth less time. A `DELETE` outside a transaction also plans each row by its
  stored key, rather than as a map of its key columns, which takes a further
  fifth off.
- A result's and a commit's changed keys are ordered and deduplicated by their
  encoded keys, rather than by the JSON text of each, so each table's keys are
  listed in primary-key order: earlier releases listed `{"id": 10}` before
  `{"id": 9}`. They are kept as rows of values rather than a map for each key.
  Inserting 10,000 rows 200 at a time takes a tenth less of the engine's time,
  and a write by primary key 3% less.
- Inside a transaction that has changed a table, a scan finds the rows the
  transaction replaced leaf by leaf, rather than comparing every row's key with
  the next staged one, and an integer column is compared with integer bounds
  as integers. A transaction's 100 range `UPDATE`s take a fifth less of the
  engine's time, and 100 range aggregates about a tenth less.
- Writing a page stores each cell's fixed-size header and slot directly, rather
  than copying them a few bytes at a time, and an index key copies text whole.
  Creating two indexes over 10,000 rows takes 8% less time.
- Stored entries are fingerprinted with XXH64, which reads eight bytes at a
  time, rather than with FNV-1a, which hashed them a byte at a time. Every leaf
  a write creates fingerprints its entries, an index's included, so creating
  two indexes takes 9% less of the engine's time.
- A commit checksums each page with XXH64, which reads eight bytes at a time,
  rather than with CRC-32, which looks each byte up in a table, and passes over
  the zero bytes that fill most of each page. It encodes less besides, writing
  its allocation bitmap and superblock straight into their pages. Committing a
  single insert takes over a third less of the engine's time, and pages read
  back from storage are checked faster too.
- Each superblock carries the allocation bitmap for the first 123 MB of the
  database in the rest of its page, and a commit writes the bitmap's other
  pages only when it changes them, where it rewrote three bitmap pages every
  time. A commit to a smaller database writes its data pages and its
  superblock and nothing else: committing a single insert takes about 5% less
  time with OPFS.
- A write transaction looks for free pages 64 at a time, rather than one at a
  time from the start of the file, and a commit visits only the cached pages
  it wrote or freed, rather than every page in the cache. A commit's cost now
  grows much more slowly with the size of the database: committing single
  inserts into a 100,000-row table takes under half the engine's time it
  did.
- Opening a database reads its catalog rather than every row and index entry,
  so it no longer slows down as the database grows. Reopening the benchmark's
  10,000-row database takes about half as long, and a 100,000-row database
  with one index opens in 2 ms of the engine's time rather than 270.
- Several of the engine's small collections are kept in vectors rather than
  B-tree maps, whose code is compiled anew for each type they hold, which took
  about 15 KiB off the compressed engine. Its hash maps and sets share one
  quick hash rather than SipHash, whose resistance to colliding keys needs
  random keys that WebAssembly without a host source of randomness never gave
  it, and four sets that needed no hashing became vectors: 3 KiB more. The
  catalog's tables and indexes, and the keys a write reports, took 5 KiB
  more, and building the superblock's CRC-32 table when it is first needed,
  rather than shipping it, 1 KiB more.
- Once per page, create() also starts a short-lived second Worker that runs
  the engine's common statements on a scratch in-memory database for about a
  tenth of a second, and then exits. Chromium compiles WebAssembly one function
  at a time, as each is first called, and Workers running the same module
  share what either compiles, so a database's first statements of each kind no
  longer wait for their code to compile. See
  [custom Workers](https://tinyjoin.org/guides/custom-workers/).

The compressed download is now 301 KiB, up from 295 KiB in v0.3.0.

## v0.3.0

This release closes some of the most commonly encountered gaps in the
[SQL dialect](https://tinyjoin.org/guides/sql-compatibility/). Each form is new syntax that was
previously rejected, so an existing statement keeps its meaning.

`BETWEEN` and `NOT BETWEEN` are accepted wherever a `WHERE` clause is, including
in aggregates, joins, `UPDATE`, `DELETE`, and prepared statements.
`price BETWEEN $1 AND $2` means exactly `price >= $1 AND price <= $2`, with the
same type checking and `NULL` behavior as those two comparisons, and it is
inclusive at both ends. There is no `SYMMETRIC` form, so a range whose bounds
are reversed matches nothing rather than being swapped.

A single-table `SELECT` can now rename its columns with `AS`, as aggregate and
join queries already could: `SELECT id AS task_id, title FROM tasks`. The alias
becomes the result field name, `ORDER BY` can refer to it, and one column may be
returned under several names. As in PostgreSQL, an `ORDER BY` name matches an
output alias before a source column of the same name. Output names must still be
distinct.

`SELECT DISTINCT` removes duplicate rows from a single-table or join projection,
which is especially useful for collapsing the rows a many-to-many join
multiplies, as in
`SELECT DISTINCT tag.name FROM post_tags JOIN tags AS tag ON post_tags.tag_id = tag.id`.
`NULL`s compare as equal to one another, and `LIMIT` and `OFFSET` count distinct
rows. As in PostgreSQL, `ORDER BY` must name projected columns. The projection
must list its columns explicitly and cannot contain JSON columns or aggregates,
and `DISTINCT ON` and aggregate `DISTINCT`, such as `COUNT(DISTINCT column)`,
remain unsupported. A column named `distinct` remains usable.

`INSERT ... ON CONFLICT` makes an upsert one atomic statement rather than an
`UPDATE` followed by a conditional `INSERT` inside a transaction, which held
every tab sharing the database for the duration. `ON CONFLICT (id) DO NOTHING`
skips conflicting rows, and
`ON CONFLICT (id) DO UPDATE SET value = EXCLUDED.value` rewrites the stored
row. The target is the primary key or the columns of one unique index, and
`DO NOTHING` may omit it to treat every unique constraint as an arbiter. Rows
are handled one at a time as in PostgreSQL, so a statement that would update
the same row twice fails with `CONSTRAINT_VIOLATION`. `SET` accepts literals,
parameters, `DEFAULT`, and `EXCLUDED` columns, but cannot read the existing row
or change its primary key, and `DO UPDATE ... WHERE` is not supported.
`RETURNING`, the row count, and changed keys include only inserted and updated
rows, so a statement that skips every row reports no change. See
[upserts](https://tinyjoin.org/guides/sql-compatibility/#upserts).

`LIKE`, `ILIKE`, and their `NOT` forms match a text column against a pattern in
which `%` matches any run of characters and `_` exactly one, so a search box no
longer has to filter rows in JavaScript: `WHERE title ILIKE $1` with
`'%' + term + '%'`. Backslash escapes a literal `%` or `_` unless an `ESCAPE`
clause names another character, and a pattern that ends in its escape
character is rejected. A search term from user input should have its own `%`,
`_`, and backslash characters escaped before being wrapped in wildcards.
`ILIKE` folds only ASCII letters, as PostgreSQL does under the C locale, which
is consistent with TinyJoin's code-point ordering, so `'É' ILIKE 'é'` is false.
Pattern matching always scans candidate rows rather than using an index.

**Upgrading from v0.2.0 needs no action.** Install, rebuild, and redeploy. The
Worker protocol stays at version 8 and the page format stays at format 2, so an
existing database opens with no migration and no OPFS namespace change. The new
syntax was previously rejected, so statements that already worked keep their
results.

The engine is now built with Rust 1.98.1 rather than 1.91.1. That more than
offsets the new SQL, so the compressed WebAssembly engine is about 4 KiB
smaller than in v0.2.0.

## v0.2.0

Subscriptions and statement results now report **which rows changed**, not only
which tables. A [`TablesChangedEvent`](https://tinyjoin.org/api/tinyjoin/interfaces/subscriptions/tableschangedevent/)
and every [`Results`](https://tinyjoin.org/api/tinyjoin/interfaces/query-results/results/) carry a
`keys` map of table name to the primary keys that changed, so a listener can
refresh individual rows instead of re-reading a table. Keys are reported for
writes from any connected Client, which makes a change made in another tab
resolvable to individual rows. See
[refreshing individual rows](https://tinyjoin.org/guides/transactions-and-changes/#refreshing-individual-rows).

Reporting is a bounded best effort, and the changed-table list stays
authoritative. A table appears in `keys` only when every key it changed fits
the bound of 1,000 keys per table; a table that changed more rows is absent
rather than partially listed, so an absent table means its rows cannot be named
and must be re-queried. `keys` is always empty alongside `reset: true`. Code
that assumes keys are present will silently miss large writes, so handle the
absent case.

**The Worker protocol version moves from 7 to 8.** 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. A mismatched pair fails cleanly with `PROTOCOL_MISMATCH` rather than
misreading a result. Applications using
[`tinyjoinOffline()`](https://tinyjoin.org/api/vite/functions/offline/tinyjoinoffline/) are unaffected: the
plugin revisions both files by content, so a rebuild replaces them together.

**Upgrading from v0.1.0 needs no action.** Install, rebuild, and redeploy.
Persistent storage is unchanged: the page format remains format 2, so an
existing database opens with no migration and no OPFS namespace change. The
`keys` field is additive, so application code that ignores it behaves exactly
as it did before.

Grouped and aggregate queries now use a secondary index when their predicate
supplies every column of one, instead of always scanning the table. Results are
unchanged, because the predicate is still evaluated for each candidate row.
Because the scan limit counts candidate rows, a grouped query that previously
exceeded `QUERY_WORK_LIMIT_EXCEEDED` on a large table may now succeed.

`tinyjoin/node` no longer fails to start when the parent process runs with a
flag that Node rejects for a worker thread, such as the `--stack-trace-limit`
that several test runners set. Inherited flags are dropped rather than the
database failing to open.

## v0.1.0

The new [Node entry point](https://tinyjoin.org/guides/node/), `tinyjoin/node`, opens in-memory
databases in Node.js 22 or later. It constructs a Worker thread and loads WASM
automatically, returning the same Client API without additional dependencies.
Each Client owns an independent database; filesystem persistence is not included.

**Persistent storage breaks compatibility with v0.0.5.** The npm v0.0.5
release uses page format 1; v0.1.0 uses page format 2. Opening a v0.0.5
database with v0.1.0 fails with `UNSUPPORTED_PAGE`. There is no automatic
migration, and changing the OPFS name does not copy existing data.

- For data that can be reconstructed, use a new OPFS namespace, for example
  change `opfs://my-app-v1` to `opfs://my-app-v2`, and rebuild the database.
  The old namespace remains intact.
- To retain existing data, export the application's known tables using a
  client pinned to `tinyjoin@0.0.5` before upgrading. Create the schema in a
  new namespace with v0.1.0, import rows with parameterized statements, and
  verify the data before retiring the old database. TinyJoin does not yet
  provide a general database export or migration API.
- New apps generated by create-tinyjoin v0.1.0 target `tinyjoin@^0.1.0` and
  use a `-db-v2` storage name. Updating the generator does not migrate
  applications it generated previously.

This release also makes predicate and assignment type validation consistent
across reads and writes, compares heterogeneous JSON values consistently,
and bounds shared parameter-graph traversal and expanded SQL bindings before
copying values. Append-only transactions now validate new row statements
incrementally, including unique-index checks; mixed writes retain complete
staged write-set validation.

Selective multi-table joins now use their actual comparison count rather than
a worst-case Cartesian estimate. Scan, build-row, result, memory, and shared
script-work limits remain in force.

Page checksum calculation is faster while preserving the stored checksum
values, storage format, and corruption checks. This improves mixed writes and
populated startup; the repository's [workload measurements](https://github.com/tinyplex/tinyjoin/tree/main/benchmarks)
retain the before/after samples and runtime hashes.

`UPDATE` and `DELETE` now use direct primary-key lookup for exact complete key
predicates, including staged transaction rows. Other predicates retain their
scan path, and statement validation and constraint checks remain in force.

Persistent startup avoids a redundant table scan for unique indexes whose
columns are all `NOT NULL`. Table validation and complete index-entry checks
remain in force; nullable indexes and indexes without uniqueness retain the
existing count scan.

Duplicate output names in simple projections and `RETURNING` now fail
consistently with `INVALID_QUERY` before execution. `LIMIT` and `OFFSET`
reject nonnumeric parameter values consistently in ordinary, aggregate, and
join queries, including JSON objects shaped like internal prepared placeholders.

Persistent Clients now coordinate automatically across tabs and within a page.
One elected Worker owns the database; operations, prepared statements, and
notifications follow it through handover. Callback transactions exclude other
Clients for their duration. In-flight operations interrupted by owner loss
reject without automatic replay, and incompatible releases reject explicitly.

The optional `tinyjoin/vite` build plugin precaches a complete production
application, including lazy Worker, OPFS, and WASM assets. New starter apps
enable it by default. Updates wait for old controlled tabs to close; existing
service workers can use its generated manifest and helper instead. See the
[offline guide](https://tinyjoin.org/guides/offline/).

## v0.0.5

This release establishes TinyJoin as a standalone, SQL-first browser database
package.

- create() opens the packaged dedicated Worker and WebAssembly engine with no
  application Worker boilerplate.
- Memory and persistent OPFS databases use one bounded page-native engine.
- The JavaScript client provides parameterized queries, atomic scripts,
  callback transactions, reusable prepared statements, and table-level change
  subscriptions.
- Typed tables support primary keys, maintained secondary and unique indexes,
  bounded schema additions, aggregates, and left-deep inner and left joins.
- Results use a familiar `rows`, `fields`, `affectedRows`, `command`, and
  `rowCount` shape, with TinyJoin revision and changed-table metadata.
- The published package includes its runtime, Worker, WASM, declarations,
  compatibility guide, README, release notes, and agent guidance.

TinyJoin remains experimental and deliberately smaller than PostgreSQL. This
release does not include a PostgreSQL server or wire protocol, hosted service,
remote replication, or offline-write synchronization.

# Guides

Source: https://tinyjoin.org/guides/

These guides start with the smallest useful TinyJoin application and reveal
storage, transactions, custom Workers, and compatibility details only when they
become relevant.

The [Node guide](https://tinyjoin.org/guides/node/) covers in-memory databases in Node.js with
the same Client API, and the [Drizzle](https://tinyjoin.org/guides/drizzle/) and
[Kysely](https://tinyjoin.org/guides/kysely/) guides cover querying TinyJoin with those query
builders.

The [caveats](https://tinyjoin.org/guides/caveats/) collect what TinyJoin deliberately does not do,
and which projects to reach for when that matters.

For exact method and type signatures, use the [API reference](https://tinyjoin.org/api/).

# Public API: tinyjoin

Source: https://tinyjoin.org/api/tinyjoin/

````ts
/**
 * The tinyjoin module provides a small PostgreSQL-shaped relational database
 * that runs in a dedicated browser Worker and stores data in memory or OPFS.
 *
 * Start with the create function. It constructs the Worker and loads the
 * WebAssembly engine, so applications do not need to manage either directly.
 * @packageDocumentation
 * @module tinyjoin
 * @since v0.0.5
 */

/**
 * The JsonPrimitive type represents a scalar value accepted by TinyJoin.
 * @category Data types
 * @since v0.0.5
 */
export type JsonPrimitive = null | boolean | number | string;

/**
 * The JsonValue type represents a parameter or result value accepted by the
 * TinyJoin JavaScript API.
 * @category Data types
 * @since v0.0.5
 */
export type JsonValue =
  | JsonPrimitive
  | JsonValue[]
  | {[key: string]: JsonValue};

/**
 * The Row type represents the default object form of a result row.
 * @category Data types
 * @since v0.0.5
 */
export type Row = Record<string, JsonValue>;

/**
 * The DataDir type identifies the database storage mode.
 *
 * Use `memory://` for an ephemeral database, or `opfs://name` for a persistent
 * database. Calling create without a data directory also uses memory.
 * Clients using the same OPFS name automatically share an elected database
 * owner, including across tabs. Pending operations interrupted by owner loss
 * reject without replay; subsequent operations reconnect automatically.
 * Different names have independent data and do not synchronize.
 * @category Configuration
 * @since v0.0.5
 */
export type DataDir = string;

/**
 * The RowMode type selects object rows or positional array rows.
 *
 * An object row holds one value for each name, so a SELECT whose output names
 * repeat is rejected unless its rows are arrays.
 * @category Query results
 * @since v0.0.5
 */
export type RowMode = 'array' | 'object';

/**
 * The QueryOptions interface configures the shape of query results.
 *
 * Only rowMode is supported. There is no AbortSignal or timeout option;
 * Promise.race with a timer does not cancel database work.
 * @category Query results
 * @since v0.0.5
 */
export interface QueryOptions {
  /**
   * The rowMode property selects object rows by default, or arrays whose values
   * follow the order of the fields property. Only array rows can return a
   * SELECT whose output names repeat.
   * @category Option
   * @since v0.0.5
   */
  rowMode?: RowMode;
}

/**
 * The ResultField interface describes one projected result column.
 * @category Query results
 * @since v0.0.5
 */
export interface ResultField {
  /**
   * The name property contains the projected column name or alias, or
   * `?column?` for an expression without an alias.
   * @category Result
   * @since v0.0.5
   */
  name: string;

  /**
   * The dataTypeID property contains the closest stable PostgreSQL OID for the
   * TinyJoin runtime type.
   * @category Result
   * @since v0.0.5
   */
  dataTypeID: number;
}

/**
 * The ChangedKeys type maps a table name to the primary keys that changed in
 * it.
 *
 * Each key is an object holding only that table's primary-key columns, so a
 * composite key arrives as one object with one property per column.
 * @category Query results
 * @since v0.1.1
 */
export type ChangedKeys = {[table: string]: Row[]};

/**
 * The Results interface is returned by queries, prepared statements, and
 * scripts.
 *
 * It follows the familiar PGlite result shape and adds the observed database
 * revision and the names of changed tables.
 * @category Query results
 * @since v0.0.5
 */
export interface Results<RowType = Row> {
  /**
   * The rows property contains the rows returned by the statement.
   * @category Result
   * @since v0.0.5
   */
  rows: RowType[];

  /**
   * The fields property describes the projected columns in result order.
   * @category Result
   * @since v0.0.5
   */
  fields: ResultField[];

  /**
   * The affectedRows property reports the number of rows changed by a write.
   * @category Result
   * @since v0.0.5
   */
  affectedRows?: number;

  /**
   * The command property contains the executed statement family.
   * @category Result
   * @since v0.0.5
   */
  command?: string;

  /**
   * The rowCount property reports the number of rows returned or changed.
   * @category Result
   * @since v0.0.5
   */
  rowCount?: number;

  /**
   * The revision property contains the database revision observed by the
   * statement.
   * @category Result
   * @since v0.0.5
   */
  revision: number;

  /**
   * The tables property contains the tables changed by the statement.
   * @category Result
   * @since v0.0.5
   */
  tables: string[];

  /**
   * The keys property contains the primary keys the statement changed, keyed by
   * table name.
   *
   * A table appears only when every key it changed fits the reporting bound of
   * 1,000 keys per table. A table that changed more rows than that is absent,
   * so an absent table means the rows that changed cannot be named, not that
   * nothing changed. The tables property stays authoritative either way.
   * @category Result
   * @since v0.1.1
   */
  keys: ChangedKeys;
}

/**
 * The ColumnType type names one of the five runtime types a column holds.
 *
 * Each SQL type name maps to one of them: `INTEGER` and `BIGINT` to `integer`,
 * `REAL` and `DOUBLE PRECISION` to `float`, `TEXT` and `VARCHAR` to `text`,
 * and `JSON` and `JSONB` to `json`. The schema keeps the runtime type, not the
 * name the table was declared with, and the length of a `VARCHAR(n)` as its
 * column's maxLength.
 * @category Schema
 * @since v0.5.0
 */
export type ColumnType = 'boolean' | 'integer' | 'float' | 'text' | 'json';

/**
 * The ColumnSchema interface describes one column of a table.
 * @category Schema
 * @since v0.5.0
 */
export interface ColumnSchema {
  /**
   * The name property contains the column's name, as the table declares it.
   * @category Schema
   * @since v0.5.0
   */
  name: string;

  /**
   * The type property contains the column's runtime ColumnType.
   * @category Schema
   * @since v0.5.0
   */
  type: ColumnType;

  /**
   * The nullable property is false when the column is declared `NOT NULL` or
   * is part of the primary key.
   * @category Schema
   * @since v0.5.0
   */
  nullable: boolean;

  /**
   * The default property contains the column's literal `DEFAULT` value, and is
   * absent when the column declares none. A column declared `DEFAULT NULL` has
   * a default of `null`.
   * @category Schema
   * @since v0.5.0
   */
  default?: JsonValue;

  /**
   * The maxLength property contains the most characters a `VARCHAR(n)` column
   * holds, its `n`, and is absent for any other column.
   * @category Schema
   * @since v0.5.0
   */
  maxLength?: number;

  /**
   * The renamedFrom property names a column's former name, which setSchema
   * renames the column from while the table still has a column of that name.
   * getSchema never includes it.
   * @category Schema
   * @since v0.5.0
   */
  renamedFrom?: string;
}

/**
 * The IndexSchema interface describes one secondary index on a table.
 *
 * A primary key is not listed as an index; TableSchema's primaryKey holds it.
 * @category Schema
 * @since v0.5.0
 */
export interface IndexSchema {
  /**
   * The name property contains the index's name.
   * @category Schema
   * @since v0.5.0
   */
  name: string;

  /**
   * The columns property contains the indexed column names, in index order.
   * @category Schema
   * @since v0.5.0
   */
  columns: string[];

  /**
   * The unique property is true for an index created with `CREATE UNIQUE
   * INDEX`.
   * @category Schema
   * @since v0.5.0
   */
  unique: boolean;
}

/**
 * The ForeignKeyAction type names what deleting or updating a referenced row
 * does to the rows that reference it: `no action` and `restrict` refuse it,
 * `cascade` deletes or updates them too, and `set null` and `set default`
 * change their referencing columns to NULL or to the columns' defaults.
 * @category Schema
 * @since v0.5.0
 */
export type ForeignKeyAction =
  | 'no action'
  | 'restrict'
  | 'cascade'
  | 'set null'
  | 'set default';

/**
 * The ForeignKeySchema interface describes one foreign key of a table: columns
 * whose values, unless one is NULL, must be those of a row of the table it
 * references.
 * @category Schema
 * @since v0.5.0
 */
export interface ForeignKeySchema {
  /**
   * The name property contains the foreign key's name.
   * @category Schema
   * @since v0.5.0
   */
  name: string;

  /**
   * The columns property contains the referencing columns, in key order.
   * @category Schema
   * @since v0.5.0
   */
  columns: string[];

  /**
   * The references property names the table the key references, which may be
   * the table's own.
   * @category Schema
   * @since v0.5.0
   */
  references: string;

  /**
   * The referencedColumns property contains the referenced table's columns, by
   * position in columns: its primary key, or a unique index's columns.
   * @category Schema
   * @since v0.5.0
   */
  referencedColumns: string[];

  /**
   * The onDelete property says what deleting a referenced row does to the rows
   * that reference it.
   * @category Schema
   * @since v0.5.0
   */
  onDelete: ForeignKeyAction;

  /**
   * The onUpdate property says what changing a referenced row's referenced
   * values does to the rows that reference it.
   * @category Schema
   * @since v0.5.0
   */
  onUpdate: ForeignKeyAction;
}

/**
 * The TableSchema interface describes one table, its columns, its primary
 * key, its indexes, and its foreign keys.
 * @category Schema
 * @since v0.5.0
 */
export interface TableSchema {
  /**
   * The name property contains the table's name.
   * @category Schema
   * @since v0.5.0
   */
  name: string;

  /**
   * The columns property describes the table's columns, in declaration order,
   * including any added later by `ALTER TABLE`.
   * @category Schema
   * @since v0.5.0
   */
  columns: ColumnSchema[];

  /**
   * The primaryKey property contains the primary-key column names, in key
   * order. A composite key has more than one.
   * @category Schema
   * @since v0.5.0
   */
  primaryKey: string[];

  /**
   * The indexes property describes the table's secondary indexes, in name
   * order.
   * @category Schema
   * @since v0.5.0
   */
  indexes: IndexSchema[];

  /**
   * The foreignKeys property describes the table's foreign keys, in the order
   * they were declared.
   * @category Schema
   * @since v0.5.0
   */
  foreignKeys: ForeignKeySchema[];

  /**
   * The renamedFrom property names a table's former name, which setSchema
   * renames the table from while the database still has a table of that name.
   * getSchema never includes it.
   * @category Schema
   * @since v0.5.0
   */
  renamedFrom?: string;
}

/**
 * The Schema interface is returned by the getSchema method, and describes
 * every table in the database. The setSchema method takes one to make the
 * database's.
 * @category Schema
 * @since v0.5.0
 */
export interface Schema {
  /**
   * The version property contains the version the application last gave the
   * schema with setSchema, or zero. setSchema refuses a schema whose version is
   * lower, as an older copy of the application would give it.
   * @category Schema
   * @since v0.5.0
   */
  version: number;

  /**
   * The tables property describes the database's tables, in name order.
   * @category Schema
   * @since v0.5.0
   */
  tables: TableSchema[];
}

/**
 * The SetSchemaOptions interface configures the setSchema method.
 * @category Schema
 * @since v0.5.0
 */
export interface SetSchemaOptions {
  /**
   * The drop property, when true, makes setSchema drop each table, and each
   * column of a table the schema keeps, that the schema leaves out, and their
   * data with them. Otherwise setSchema leaves them as they are.
   * @category Schema
   * @since v0.5.0
   */
  drop?: boolean;
}

/**
 * The SerializedError interface is the stable error envelope sent by the
 * Worker.
 * @category Errors
 * @since v0.0.5
 */
export interface SerializedError {
  /**
   * The code property identifies the TinyJoin error family.
   * @category Error
   * @since v0.0.5
   */
  code: string;

  /**
   * The message property explains the failure.
   * @category Error
   * @since v0.0.5
   */
  message: string;

  /**
   * The details property contains optional JSON-compatible context.
   * @category Error
   * @since v0.0.5
   */
  details?: JsonValue;

  /**
   * The retryable property indicates whether reopening or retrying may succeed.
   * It does not guarantee that replaying a write is safe or that the current
   * Client remains usable. Reconcile uncertain writes after reopening.
   * @category Error
   * @since v0.0.5
   */
  retryable?: boolean;
}

// Use the host's event shape when available, including DOM event methods in a
// browser, without requiring DOM declarations in Node applications.
/** @ignore */
type WorkerMessageEvent = typeof globalThis extends {
  MessageEvent: {prototype: infer Event};
}
  ? Omit<Event, 'data'> & {readonly data: unknown}
  : {readonly data: unknown};

/** @ignore */
type WorkerErrorEvent = typeof globalThis extends {
  ErrorEvent: {prototype: infer Event};
}
  ? Event
  : {readonly message: string};

/**
 * The WorkerLike interface is the minimal Worker surface accepted by a custom
 * Client configuration.
 *
 * Most applications should let create construct the packaged Worker instead.
 * @category Workers
 * @since v0.0.5
 */
export interface WorkerLike {
  /**
   * The postMessage method sends a request to the Worker.
   * @category Worker
   * @since v0.0.5
   */
  postMessage(message: unknown): void;

  /**
   * This addEventListener overload listens for Worker messages.
   * @param type The `message` event type.
   * @param listener The callback receiving messages from the Worker.
   * @category Worker
   * @since v0.0.5
   */
  addEventListener(
    type: 'message',
    listener: (event: WorkerMessageEvent) => void,
  ): void;

  /**
   * This addEventListener overload listens for message decoding failures.
   * @param type The `messageerror` event type.
   * @param listener The callback receiving message decoding failures.
   * @category Worker
   * @since v0.0.5
   */
  addEventListener(
    type: 'messageerror',
    listener: (event: WorkerMessageEvent) => void,
  ): void;

  /**
   * This addEventListener overload listens for Worker runtime failures.
   * @param type The `error` event type.
   * @param listener The callback receiving Worker runtime failures.
   * @category Worker
   * @since v0.0.5
   */
  addEventListener(
    type: 'error',
    listener: (event: WorkerErrorEvent) => void,
  ): void;

  /**
   * This removeEventListener overload removes a message listener.
   * @param type The `message` event type.
   * @param listener The same callback passed to addEventListener.
   * @category Worker
   * @since v0.0.5
   */
  removeEventListener(
    type: 'message',
    listener: (event: WorkerMessageEvent) => void,
  ): void;

  /**
   * This removeEventListener overload removes a message-error listener.
   * @param type The `messageerror` event type.
   * @param listener The same callback passed to addEventListener.
   * @category Worker
   * @since v0.0.5
   */
  removeEventListener(
    type: 'messageerror',
    listener: (event: WorkerMessageEvent) => void,
  ): void;

  /**
   * This removeEventListener overload removes a Worker error listener.
   * @param type The `error` event type.
   * @param listener The same callback passed to addEventListener.
   * @category Worker
   * @since v0.0.5
   */
  removeEventListener(
    type: 'error',
    listener: (event: WorkerErrorEvent) => void,
  ): void;

  /**
   * The terminate method stops an application-owned Worker when available.
   * @category Worker
   * @since v0.0.5
   */
  terminate?: () => void;
}

/**
 * The ClientOptions interface configures storage or an application-owned
 * Worker.
 *
 * The zero-boilerplate default constructs TinyJoin's packaged module Worker.
 * Provide at most one of worker, workerFactory, or workerUrl.
 * @category Configuration
 * @since v0.0.5
 */
export interface ClientOptions {
  /**
   * The worker property provides an already-created Worker-compatible object.
   * @category Option
   * @since v0.0.5
   */
  worker?: WorkerLike;

  /**
   * The workerFactory property creates an application-owned Worker lazily.
   * @category Option
   * @since v0.0.5
   */
  workerFactory?: () => WorkerLike;

  /**
   * The workerUrl property identifies an application-owned module Worker.
   * @category Option
   * @since v0.0.5
   */
  workerUrl?: string | URL;

  /**
   * The dataDir property selects memory or a named OPFS database.
   * @category Option
   * @since v0.0.5
   */
  dataDir?: DataDir;
}

/**
 * The TablesChangedEvent interface describes one committed table-level
 * invalidation or a request to refresh after database-owner handover.
 * @category Subscriptions
 * @since v0.0.5
 */
export interface TablesChangedEvent {
  /**
   * The reset property is true when database-owner handover or page restoration
   * requires a re-query even though the changed tables are unknown. In that
   * case tables is empty and every subscription is notified, including filtered
   * ones. Normal committed-change events omit this property.
   * @category Event
   * @since v0.1.0
   */
  reset?: boolean;

  /**
   * The revision property contains the committed database revision.
   * @category Event
   * @since v0.0.5
   */
  revision: number;

  /**
   * The tables property contains the names of changed tables. It is empty when
   * reset is true, which means the subscriber should refresh its query anyway.
   * @category Event
   * @since v0.0.5
   */
  tables: string[];

  /**
   * The keys property contains the primary keys that changed, keyed by table
   * name, for the tables whose complete set is known.
   *
   * Use it to refresh individual rows instead of re-reading a whole table. A
   * table listed in tables but absent here changed more rows than the bound of
   * 1,000 keys per table allows an event to name, or changed across a handover;
   * re-query that table instead. It is always empty when reset is true.
   * @category Event
   * @since v0.1.1
   */
  keys: ChangedKeys;
}

/**
 * The SubscriptionOptions interface filters invalidations by table name.
 * @category Subscriptions
 * @since v0.0.5
 */
export interface SubscriptionOptions {
  /**
   * The tables property limits delivery to invalidations that include at least
   * one of these tables. Omit it to observe every table-change invalidation;
   * adjacent changes may be coalesced.
   * @category Option
   * @since v0.0.5
   */
  tables?: string[];
}

/**
 * The PreparedStatement interface represents one parsed SELECT, INSERT,
 * UPDATE, or DELETE statement retained by an open Client.
 * @category SQL
 * @since v0.0.5
 */
export interface PreparedStatement<RowType = Row> {
  /**
   * The execute method binds a complete parameter list and runs the statement.
   * @param params JSON-compatible values for `$1`, `$2`, and so on.
   * @param options Result-shape options.
   * @returns A Promise resolving to the statement results.
   * @category SQL
   * @since v0.0.5
   */
  execute(
    params?: JsonValue[],
    options?: QueryOptions,
  ): Promise<Results<RowType>>;

  /**
   * The close method seals the handle and releases its Worker resources after
   * already-started executions settle.
   * @category Lifecycle
   * @since v0.0.5
   */
  close(): Promise<void>;

  /**
   * The closed property indicates whether the handle has been sealed.
   * It is not a Worker health check. A terminal Worker failure can leave it
   * false while execute rejects. Closing the Client seals its handles.
   * @category Lifecycle
   * @since v0.0.5
   */
  readonly closed: boolean;
}

/**
 * The Transaction interface runs reads and row mutations against one isolated
 * staged database inside a Client.transaction callback.
 *
 * Do not retain this object after its callback completes. Run schema DDL in a
 * standalone Client.query call or Client.exec script.
 * Pass this object to helpers instead of awaiting another transaction on the
 * same Client, which would queue behind the active callback and deadlock.
 * @category Transactions
 * @since v0.0.5
 */
export interface Transaction {
  /**
   * The query method runs one parameterized statement against staged data.
   * @returns A Promise resolving to the statement results.
   * @category Transactions
   * @since v0.0.5
   */
  query<RowType = Row>(
    sql: string,
    params?: JsonValue[],
    options?: QueryOptions,
  ): Promise<Results<RowType>>;

  /**
   * The sql method is a parameterizing tagged-template form of query.
   * @returns A Promise resolving to the statement results.
   * @category Transactions
   * @since v0.0.5
   */
  sql<RowType = Row>(
    strings: TemplateStringsArray,
    ...params: JsonValue[]
  ): Promise<Results<RowType>>;

  /**
   * The exec method runs a parameter-free DML and read script as one savepoint.
   * @returns A Promise resolving to one result per statement, in script order.
   * @category Transactions
   * @since v0.0.5
   */
  exec(sql: string, options?: QueryOptions): Promise<Results[]>;

  /**
   * The execute method runs a prepared statement owned by the same Client.
   * @returns A Promise resolving to the statement results.
   * @category Transactions
   * @since v0.0.5
   */
  execute<RowType = Row>(
    statement: PreparedStatement<RowType>,
    params?: JsonValue[],
    options?: QueryOptions,
  ): Promise<Results<RowType>>;

  /**
   * The rollback method explicitly discards the staged transaction.
   * @category Transactions
   * @since v0.0.5
   */
  rollback(): Promise<void>;

  /**
   * The closed property indicates that the transaction is sealed, including
   * after an explicit rollback or after its callback finishes.
   * @category Lifecycle
   * @since v0.0.5
   */
  readonly closed: boolean;
}

/**
 * The Client class represents one open TinyJoin database and its dedicated
 * Worker.
 *
 * Prefer the async create function so initialization failures are reported
 * before the Client is returned.
 * @category Lifecycle
 * @since v0.0.5
 */
export class Client {
  /**
   * The constructor begins opening a Client immediately.
   *
   * Prefer create unless code specifically needs the waitReady lifecycle.
   * @category Lifecycle
   * @since v0.0.5
   */
  constructor(options?: ClientOptions);

  /**
   * The waitReady property resolves when the Worker and database are ready.
   * It settles once for initialization; it does not track later Worker health
   * or temporary OPFS owner handover.
   * @category Lifecycle
   * @since v0.0.5
   */
  readonly waitReady: Promise<void>;

  /**
   * The ready property indicates that initialization finished and closing has
   * not started.
   * It is not a health check: it can remain true after a terminal Worker error
   * while operations reject. Close and reopen the Client and reconcile writes;
   * see the [lifecycle guide](https://tinyjoin.org/guides/storage-and-lifecycle/#terminal-worker-failures).
   * Temporary OPFS owner handover reconnects the same Client automatically.
   * @category Lifecycle
   * @since v0.0.5
   */
  readonly ready: boolean;

  /**
   * The closed property indicates that Client cleanup has completed.
   * A terminal Worker failure does not itself complete Client cleanup. Call
   * close even when subsequent operations reject with WORKER_TERMINATED.
   * @category Lifecycle
   * @since v0.0.5
   */
  readonly closed: boolean;

  /**
   * The query method executes exactly one parameterized SQL statement.
   * @param sql A statement in the documented TinyJoin SQL subset.
   * @param params JSON-compatible values for `$1`, `$2`, and so on.
   * @param options Result-shape options.
   * @returns A Promise resolving to the statement results.
   * @category SQL
   * @essential Using a database
   * @since v0.0.5
   */
  query<RowType = Row>(
    sql: string,
    params?: JsonValue[],
    options?: QueryOptions,
  ): Promise<Results<RowType>>;

  /**
   * The sql method is a parameterizing tagged-template form of query.
   *
   * Interpolated values become `$n` parameters. It does not interpolate raw
   * identifiers or SQL fragments.
   * @returns A Promise resolving to the statement results.
   * @category SQL
   * @essential Using a database
   * @since v0.0.5
   */
  sql<RowType = Row>(
    strings: TemplateStringsArray,
    ...params: JsonValue[]
  ): Promise<Results<RowType>>;

  /**
   * The prepare method parses and retains one reusable read or row-mutation
   * statement in the Worker.
   * @returns A Promise resolving to a reusable PreparedStatement handle.
   * @category SQL
   * @since v0.0.5
   */
  prepare<RowType = Row>(sql: string): Promise<PreparedStatement<RowType>>;

  /**
   * The exec method runs one or more parameter-free statements as one implicit
   * transaction.
   * @returns A Promise resolving to one result per statement, in script order.
   * @category SQL
   * @essential Using a database
   * @since v0.0.5
   */
  exec(sql: string, options?: QueryOptions): Promise<Results[]>;

  /**
   * The transaction method stages row mutations and publishes them together
   * when the callback succeeds, unless it explicitly rolls back.
   *
   * Transaction calls on this Client queue in order. Do not await another transaction on
   * this Client inside the callback; pass its Transaction to helpers instead.
   * Use that object for all SQL in the callback and prepare handles beforehand.
   * Other Clients for the same OPFS name wait for the whole callback to finish.
   * Do not await work on those Clients from inside this callback.
   * An uncaught callback error before commit discards staged work. A caught
   * statement error leaves earlier writes staged unless rollback is called.
   *
   * There is no cancellation or timeout option. Promise.race only stops
   * waiting, and queued work can still commit. Rejection during commit can
   * leave its outcome uncertain; see the
   * [recovery guide](https://tinyjoin.org/guides/storage-and-lifecycle/#recovering-after-an-uncertain-write)
   * before replaying a failed write.
   * @returns A Promise resolving to the callback's result after the transaction
   * commits or explicitly rolls back.
   * @category Transactions
   * @essential Using a database
   * @since v0.0.5
   */
  transaction<Result>(
    callback: (transaction: Transaction) => Result | Promise<Result>,
  ): Promise<Result>;

  /**
   * The subscribe method listens for committed table changes and returns an
   * unsubscribe function. OPFS Clients receive changes from every connected
   * tab. After handover or page restoration, reset events notify every
   * subscriber to re-query even though tables is empty.
   * @returns A function that removes this subscription when called.
   * @category Subscriptions
   * @since v0.0.5
   */
  subscribe(
    options: SubscriptionOptions,
    listener: (event: TablesChangedEvent) => void,
  ): () => void;

  /**
   * The getRevision method returns the newest database revision observed by
   * this Client.
   * @returns The newest database revision observed by this Client.
   * @category Subscriptions
   * @since v0.0.5
   */
  getRevision(): number;

  /**
   * The getSchema method reads the database's tables, with their columns,
   * primary keys, and indexes, as the engine's catalog holds them.
   *
   * Tables and each table's indexes are in name order, and columns and key
   * columns are in the order the table declares them. Call it outside a
   * transaction.
   * @returns A Promise resolving to the Schema.
   * @example
   * ```ts
   * import {create} from 'tinyjoin';
   *
   * const db = await create();
   * await db.exec(`
   *   CREATE TABLE tasks (id TEXT PRIMARY KEY, done BOOLEAN NOT NULL DEFAULT false);
   *   CREATE INDEX tasks_done ON tasks (done);
   * `);
   * const {tables} = await db.getSchema();
   * console.log(tables[0].columns[1]);
   * // -> {name: 'done', type: 'boolean', nullable: false, default: false}
   * await db.close();
   * ```
   * @category Lifecycle
   * @since v0.5.0
   */
  getSchema(): Promise<Schema>;

  /**
   * The setSchema method makes the database's tables, columns, primary keys,
   * and indexes match a Schema, as getSchema returns one, in one change that
   * commits whole or not at all.
   *
   * It creates the tables and columns the schema has and the database lacks,
   * renames a table or column from the name its renamedFrom gives while the
   * database still has that name, and changes a column's default, nullability,
   * or `VARCHAR` length to the schema's. Each table's indexes and foreign keys
   * become exactly the schema's, with keys added once every table they
   * reference exists. Tables and columns the schema leaves out stay, unless the
   * options' drop property is true. Rows keep their values, as `ALTER TABLE`
   * keeps them: a change no statement could make, to a primary key or a
   * column's runtime type, is refused, and so is one that a row prevents, such
   * as `NOT NULL` where a row holds `NULL`.
   *
   * A schema the database already has changes nothing, so every tab of an
   * application can call setSchema as it starts. A schema whose version is
   * lower than the database's is refused with `SCHEMA_OUTDATED`, so that a tab
   * still running an older copy of the application cannot undo a newer one's
   * changes; a higher version is recorded. Call it outside a transaction.
   * @param schema The schema to make the database's.
   * @param options Whether to drop what the schema leaves out.
   * @returns A Promise resolving to true if the database changed, or false if
   * it already had the schema.
   * @example
   * ```ts
   * import {create} from 'tinyjoin';
   *
   * const db = await create();
   * await db.setSchema({
   *   version: 1,
   *   tables: [
   *     {
   *       name: 'tasks',
   *       columns: [
   *         {name: 'id', type: 'text', nullable: false},
   *         {name: 'title', type: 'text', nullable: false},
   *         {name: 'done', type: 'boolean', nullable: false, default: false},
   *       ],
   *       primaryKey: ['id'],
   *       indexes: [{name: 'tasks_done', columns: ['done'], unique: false}],
   *       foreignKeys: [],
   *     },
   *   ],
   * });
   * await db.close();
   * ```
   * @category Lifecycle
   * @since v0.5.0
   */
  setSchema(schema: Schema, options?: SetSchemaOptions): Promise<boolean>;

  /**
   * The check method reads every row and index entry of the committed
   * database, and every page that holds them, and resolves when all of them
   * are sound.
   *
   * Opening a database checks only its catalog, and each page and row is
   * checked as a statement reads it, so rows that are never read, and whether
   * each index still matches its table, are checked only here. The check takes
   * time in proportion to the database, and statements from every Client of
   * the same OPFS name wait while it runs. Call it outside a transaction.
   *
   * TinyJoin cannot yet repair a database that fails its check.
   * @returns A Promise that resolves when the database is sound, or rejects
   * with a ClientError whose code names the first problem found, such as
   * `STORAGE_CORRUPT`.
   * @category Lifecycle
   * @since v0.4.0
   */
  check(): Promise<void>;

  /**
   * The close method detaches this Client and releases its prepared statements
   * and Worker. Other Clients for the same OPFS name stay connected; ownership
   * transfers automatically when necessary.
   * Repeated calls share the same asynchronous cleanup.
   * A closed Client cannot resume; create a new one and recreate its prepared
   * statements and subscriptions. Closing is not a cancellation or rollback
   * guarantee for a write already in flight.
   * @category Lifecycle
   * @since v0.0.5
   */
  close(): Promise<void>;
}

/**
 * The create function opens a TinyJoin database in a dedicated Worker.
 *
 * This overload creates an ephemeral in-memory database using the packaged
 * Worker. Its data is lost when the Client closes or the page reloads.
 * @returns A Promise resolving to a Client whose Worker and database are ready.
 * @example
 * ```ts
 * import {create} from 'tinyjoin';
 *
 * const db = await create();
 * await db.exec(`
 *   CREATE TABLE IF NOT EXISTS tasks (
 *     id INTEGER PRIMARY KEY,
 *     title TEXT NOT NULL
 *   )
 * `);
 * const {rows} = await db.query('SELECT * FROM tasks ORDER BY id');
 * await db.close();
 * ```
 * @category Lifecycle
 * @essential Using a database
 * @since v0.0.5
 */
export function create(): Promise<Client>;

/**
 * This create overload opens a database with ClientOptions.
 *
 * Use the options object to select storage or provide a custom Worker.
 * Omitting its dataDir property selects an ephemeral in-memory database.
 * @param options Storage and Worker configuration.
 * @returns A Promise resolving to a Client whose Worker and database are ready.
 * @example
 * ```ts
 * import {create} from 'tinyjoin';
 *
 * const db = await create({dataDir: 'memory://'});
 * await db.close();
 * ```
 * @category Lifecycle
 * @essential Using a database
 * @since v0.0.5
 */
export function create(options: ClientOptions): Promise<Client>;

/**
 * This create overload opens a database at the supplied storage URL.
 *
 * Use a stable `opfs://name` to persist data in the same browser, or
 * `memory://` for an ephemeral database. When providing the second argument,
 * supply a storage URL and leave options.dataDir unset.
 * @param dataDir The `memory://` URL, a named `opfs://` URL, or `undefined` for
 * the default in-memory database.
 * @param options Optional Worker configuration.
 * @returns A Promise resolving to a Client whose Worker and database are ready.
 * @example
 * ```ts
 * import {create} from 'tinyjoin';
 *
 * const db = await create('opfs://my-app');
 * await db.close();
 * ```
 * @category Lifecycle
 * @essential Using a database
 * @since v0.0.5
 */
export function create(
  dataDir: DataDir | undefined,
  options?: ClientOptions,
): Promise<Client>;

/**
 * The ClientError class extends JavaScript Error with a validated error
 * returned by the TinyJoin Worker.
 *
 * RECOVERY_REQUIRED, STORAGE_COMMIT_OUTCOME_UNKNOWN, and
 * STORAGE_ENGINE_POISONED mean the engine must no longer be used. Stop work,
 * close the Client, reopen the same OPFS name, and reconcile stored state
 * using stable operation identifiers before replaying a write. A rejected
 * operation may already have committed. See the
 * [recovery guide](https://tinyjoin.org/guides/storage-and-lifecycle/#recovering-after-an-uncertain-write).
 * @category Errors
 * @since v0.0.5
 */
export class ClientError extends Error {
  /**
   * The constructor creates an Error from its serialized Worker envelope.
   * @category Error
   * @since v0.0.5
   */
  constructor(error: SerializedError);

  /**
   * The code property identifies the TinyJoin error family.
   * @category Error
   * @since v0.0.5
   */
  readonly code: string;

  /**
   * The details property contains optional JSON-compatible context.
   * @category Error
   * @since v0.0.5
   */
  readonly details: SerializedError['details'];

  /**
   * The retryable property indicates whether reopening or retrying may succeed.
   * It is not a safe-replay guarantee. An uncertain write requires reopening
   * and reconciliation even when application code wants to retry it.
   * @category Error
   * @since v0.0.5
   */
  readonly retryable: boolean;
}
````

# Public API: tinyjoin/node

Source: https://tinyjoin.org/api/node/

````ts
/**
 * The node module opens ephemeral TinyJoin databases in Node.js 22 or later.
 * It constructs a Node Worker thread and loads the packaged WebAssembly engine
 * automatically, with no extra dependencies, bundler, or polyfills.
 *
 * The returned Client has the same SQL, transaction, prepared-statement, and
 * subscription APIs as the browser Client. The module also exports ClientError
 * and the shared Client API types, so Node applications can import their query,
 * result, transaction, and subscription contracts from this entry point.
 * ClientError is the same class exported by the browser entry point.
 * See the [Node guide](https://tinyjoin.org/guides/node/).
 * @packageDocumentation
 * @module node
 * @since v0.1.0
 */

import type {Client} from '../index.js';

/**
 * The node module re-exports the shared ClientError class.
 * @category Shared API
 */
export {ClientError} from '../index.js';

/**
 * The node module re-exports the shared Client API types.
 * @category Shared API
 */
export type {
  Client,
  ColumnSchema,
  ColumnType,
  ForeignKeyAction,
  ForeignKeySchema,
  IndexSchema,
  JsonPrimitive,
  JsonValue,
  PreparedStatement,
  QueryOptions,
  ResultField,
  Results,
  Row,
  RowMode,
  Schema,
  SerializedError,
  SetSchemaOptions,
  SubscriptionOptions,
  TableSchema,
  TablesChangedEvent,
  Transaction,
} from '../index.js';

/**
 * The create function opens a fresh in-memory database in a dedicated Node
 * Worker thread. It resolves when the database is ready.
 *
 * Omit the argument or pass `memory://`. Each call owns an independent database
 * and Worker; close the Client to release the Worker. All data is lost when
 * the Client closes or the process exits. OPFS, filesystem persistence, and
 * remote synchronization are not supported by this entry point.
 * @param dataDir The optional `memory://` URL.
 * @returns A ready Client. Always await its close method when finished.
 * @example
 * ```ts
 * import {type Client, create} from 'tinyjoin/node';
 *
 * const db: Client = await create();
 * try {
 *   await db.exec('CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT)');
 *   await db.query('INSERT INTO notes VALUES ($1, $2)', [1, 'Hello from Node']);
 *   console.log((await db.query('SELECT * FROM notes ORDER BY id')).rows);
 * } finally {
 *   await db.close();
 * }
 * ```
 * @category Lifecycle
 * @since v0.1.0
 */
export function create(dataDir?: 'memory://'): Promise<Client>;
````

# Public API: tinyjoin/worker

Source: https://tinyjoin.org/api/worker/

````ts
/**
 * The worker module lets advanced applications start TinyJoin inside a Worker
 * that they construct and bundle themselves.
 *
 * Most applications should use create from the main tinyjoin module, which
 * constructs the packaged Worker automatically.
 * @packageDocumentation
 * @module worker
 * @since v0.0.5
 */

/**
 * The startWorker function starts the TinyJoin request host in the current
 * dedicated Worker.
 * Persistent databases participate in automatic same-name coordination across
 * tabs, just like the packaged Worker. Memory databases remain independent.
 * @example
 * ```ts
 * import {startWorker} from 'tinyjoin/worker';
 *
 * startWorker();
 * ```
 * @category Workers
 * @since v0.0.5
 */
export function startWorker(): void;
````

# Public API: tinyjoin/vite

Source: https://tinyjoin.org/api/vite/

````ts
/**
 * The vite module provides optional production offline caching for Vite
 * applications, including TinyJoin's lazily loaded Worker, OPFS, and WASM files.
 * This Node-only build integration is separate from the browser runtime.
 * @packageDocumentation
 * @module vite
 * @since v0.1.0
 */

import type {Plugin} from 'vite';

/**
 * The TinyjoinOfflineOptions interface configures production offline caching.
 * @category Offline
 * @since v0.1.0
 */
export interface TinyjoinOfflineOptions {
  /**
 * The mode property selects automatic service-worker registration or integration
 * with an existing service worker. The default is `service-worker`.
 *
 * The `manifest` mode emits `tinyjoin-precache.json` and
 * `tinyjoin-precache.js` without registering or replacing a service worker.
 * @category Offline
 * @since v0.1.0
 */
  mode?: 'service-worker' | 'manifest';
  /**
 * The navigationFallback property names the emitted HTML file served for
 * otherwise unmatched navigation requests within the application scope.
 * It defaults to `index.html`; use `false` to disable SPA navigation fallback.
 * @category Offline
 * @since v0.1.0
 */
  navigationFallback?: string | false;
}

/**
 * The tinyjoinOffline function returns a Vite plugin that precaches the complete
 * emitted production build, including lazy runtime assets, with verified content
 * hashes. It does not register a service worker during development.
 *
 * The first visit requires a network connection. Wait for
 * `navigator.serviceWorker.ready` before expecting a subsequent navigation to
 * work offline. Storage persistence and application-file caching remain separate:
 * OPFS stores the database, while the service worker stores the application.
 *
 * Updates wait until all tabs controlled by the old service worker close. The
 * plugin does not force a new worker onto running tabs. Serve the whole build
 * together at one same-origin root-relative or relative Vite base. External
 * requests and files created after the build are not cached.
 * @param options Optional offline-caching settings.
 * @returns A Vite plugin that configures production offline caching.
 * @example
 * ```ts
 * import {defineConfig} from 'vite';
 * import {tinyjoinOffline} from 'tinyjoin/vite';
 *
 * export default defineConfig({plugins: [tinyjoinOffline()]});
 * ```
 * @category Offline
 * @since v0.1.0
 */
export function tinyjoinOffline(options?: TinyjoinOfflineOptions): Plugin;
````

# Public API: tinyjoin/drizzle

Source: https://tinyjoin.org/api/drizzle/

````ts
/**
 * The drizzle module connects the Drizzle ORM query builder to a TinyJoin
 * Client. It needs `drizzle-orm` 0.45 or later, which the application installs
 * itself: TinyJoin declares it as an optional peer dependency and never bundles
 * it.
 *
 * Drizzle writes PostgreSQL, so its queries run in TinyJoin's
 * [SQL dialect](https://tinyjoin.org/guides/sql-compatibility/), and its schemas use the column
 * types that dialect has. See the [Drizzle guide](https://tinyjoin.org/guides/drizzle/) for what
 * works, and what does not.
 * @packageDocumentation
 * @module drizzle
 * @since v0.5.0
 */

import type {DrizzleConfig} from 'drizzle-orm';
import type {PgRemoteDatabase} from 'drizzle-orm/pg-proxy';
import type {Client} from '../index.js';

/**
 * The TinyJoinDatabase type is the Drizzle database the drizzle function
 * returns: Drizzle's PostgreSQL database with the TinyJoin Client it queries as
 * its `$client`.
 *
 * Drizzle's raw `db.execute` resolves to the rows its statement returns, as
 * objects.
 * @category Drizzle
 * @since v0.5.0
 */
export type TinyJoinDatabase<
  TSchema extends Record<string, unknown> = Record<string, never>,
> = PgRemoteDatabase<TSchema> & {$client: Client};

/**
 * The drizzle function creates a Drizzle database over a TinyJoin Client.
 *
 * Queries run on the Client as array rows, which Drizzle maps to its own
 * result shapes. Drizzle's `db.transaction` runs on the Client's callback
 * transaction, so it holds the database as any TinyJoin transaction does,
 * `tx.rollback` discards its work, and a transaction cannot nest, since there
 * are no savepoints, or take an isolation level.
 *
 * A value Drizzle encodes for a `json` or `jsonb` column is bound as the JSON
 * value it is, rather than as the JSON text Drizzle makes of it, which TinyJoin
 * would store as a string.
 *
 * Close the Client yourself when the database is no longer needed.
 * @param client The TinyJoin Client to query, from create().
 * @param config Drizzle's configuration: its schema, for relational queries,
 * and its logger, casing, and cache.
 * @returns A Drizzle database.
 * @example
 * ```ts
 * import {create} from 'tinyjoin';
 * import {drizzle} from 'tinyjoin/drizzle';
 * import {eq} from 'drizzle-orm';
 * import {boolean, pgTable, text} from 'drizzle-orm/pg-core';
 *
 * const tasks = pgTable('tasks', {
 *   id: text('id').primaryKey(),
 *   title: text('title').notNull(),
 *   done: boolean('done').notNull().default(false),
 * });
 *
 * const client = await create('opfs://my-app-v1');
 * const db = drizzle(client, {schema: {tasks}});
 * await db.insert(tasks).values({id: crypto.randomUUID(), title: 'Try Drizzle'});
 * const open = await db.select().from(tasks).where(eq(tasks.done, false));
 * ```
 * @category Drizzle
 * @since v0.5.0
 */
export function drizzle<
  TSchema extends Record<string, unknown> = Record<string, never>,
>(client: Client, config?: DrizzleConfig<TSchema>): TinyJoinDatabase<TSchema>;

/**
 * The MigrationJournal interface is the journal that `drizzle-kit generate`
 * writes beside its migrations, as `meta/_journal.json`, of which the migrate
 * function reads each entry's tag and time.
 * @category Migrations
 * @since v0.5.0
 */
export interface MigrationJournal {
  /**
   * The entries property lists the migrations in the order to apply them, each
   * with its tag, which names its SQL file, and the time it was generated.
   * @category Migrations
   * @since v0.5.0
   */
  entries: {tag: string; when: number}[];
}

/**
 * The MigrationConfig interface describes the migrations that the migrate
 * function applies.
 * @category Migrations
 * @since v0.5.0
 */
export interface MigrationConfig {
  /**
   * The journal property is the Drizzle Kit journal, which an application can
   * import from `meta/_journal.json`.
   * @category Migrations
   * @since v0.5.0
   */
  journal: MigrationJournal;

  /**
   * The migrations property maps each migration's file to its SQL text. A key
   * may be the migration's tag, its file name, or any path ending in its file
   * name, so the object that a bundler's glob import returns can be passed as
   * it is.
   * @category Migrations
   * @since v0.5.0
   */
  migrations: Record<string, string>;

  /**
   * The migrationsTable property names the table that records the migrations
   * applied, `__drizzle_migrations` unless it is given.
   * @category Migrations
   * @since v0.5.0
   */
  migrationsTable?: string;
}

/**
 * The migrate function applies the migrations that `drizzle-kit generate`
 * wrote, which the database has not yet recorded, in the journal's order.
 *
 * Each migration runs as one script together with the row that records it, so
 * it commits whole or not at all, and a migration that fails leaves the
 * database as the previous one left it, and rejects with its error. A
 * migration another Client of the same database applied first is skipped, so
 * every tab of an application can call migrate as it starts.
 *
 * A migration is recorded by its tag and applied once: a file changed after it
 * was applied is not applied again, and a migration that joins the journal
 * before others already applied is still applied. The migrations need no file
 * system, so a browser application bundles them.
 * @param db The Drizzle database, from drizzle.
 * @param config The journal and the SQL text of each migration.
 * @returns A Promise that resolves when every migration has been applied.
 * @example
 * ```ts
 * import {create} from 'tinyjoin';
 * import {drizzle, migrate} from 'tinyjoin/drizzle';
 * import journal from './drizzle/meta/_journal.json';
 *
 * const db = drizzle(await create('opfs://my-app-v1'));
 * await migrate(db, {
 *   journal,
 *   migrations: import.meta.glob<string>('./drizzle/*.sql', {
 *     query: '?raw',
 *     import: 'default',
 *     eager: true,
 *   }),
 * });
 * ```
 * @category Migrations
 * @since v0.5.0
 */
export function migrate<
  TSchema extends Record<string, unknown> = Record<string, never>,
>(db: TinyJoinDatabase<TSchema>, config: MigrationConfig): Promise<void>;

/**
 * The PushOptions interface configures the push function.
 * @category Push
 * @since v0.5.0
 */
export interface PushOptions {
  /**
   * The version property gives the schema a version, zero unless it is
   * given, which the database records. A schema whose version is lower than
   * the database's is refused with `SCHEMA_OUTDATED`, so that a tab running an
   * older copy of the application cannot undo a newer one's changes.
   * @category Push
   * @since v0.5.0
   */
  version?: number;

  /**
   * The drop property, when true, drops each table, and each column of a
   * table the schema keeps, that the schema leaves out, with their data.
   * Otherwise they stay as they are.
   * @category Push
   * @since v0.5.0
   */
  drop?: boolean;

  /**
   * The renames property maps the new name of each renamed table, as
   * `table`, or column, as `table.column`, to the name it had, so that push
   * renames it, keeping its data, rather than creating it empty. A rename
   * that has already happened changes nothing.
   * @category Push
   * @since v0.5.0
   */
  renames?: Record<string, string>;
}

/**
 * The push function makes the database hold the tables of a Drizzle schema,
 * as Drizzle Kit's `push` would for PostgreSQL, but from the application and
 * as one change that commits whole or not at all.
 *
 * It reads each table's columns, primary key, unique constraints, and indexes
 * from Drizzle, and passes them to the Client's setSchema, which creates what
 * the database lacks and changes what differs. A schema the database already
 * holds changes nothing, so every tab of an application can call push as it
 * starts, in place of migrations.
 *
 * A table that declares what TinyJoin does not have, such as a foreign key, a
 * `timestamp` column, an SQL default, or an index on an expression, is
 * refused before anything changes. A default Drizzle works out in JavaScript,
 * with `$defaultFn`, is Drizzle's, not the database's.
 * @param db The Drizzle database, from drizzle.
 * @param schema The Drizzle schema, whose tables push reads, leaving out its
 * relations.
 * @param options The schema's version, whether to drop what it leaves out, and
 * what it renames.
 * @returns A Promise resolving to true if the database changed, or false if it
 * already held the schema.
 * @example
 * ```ts
 * import {create} from 'tinyjoin';
 * import {drizzle, push} from 'tinyjoin/drizzle';
 * import * as schema from './schema';
 *
 * const db = drizzle(await create('opfs://my-app-v1'), {schema});
 * await push(db, schema, {version: 2, renames: {'tasks.name': 'title'}});
 * ```
 * @category Push
 * @since v0.5.0
 */
export function push<
  TSchema extends Record<string, unknown> = Record<string, never>,
>(
  db: TinyJoinDatabase<TSchema>,
  schema: Record<string, unknown>,
  options?: PushOptions,
): Promise<boolean>;
````

# Public API: tinyjoin/kysely

Source: https://tinyjoin.org/api/kysely/

````ts
/**
 * The kysely module connects the Kysely query builder to a TinyJoin Client. It
 * needs `kysely` 0.28 or later, which the application installs itself:
 * TinyJoin declares it as an optional peer dependency and never bundles it.
 *
 * Kysely writes PostgreSQL, so its queries run in TinyJoin's
 * [SQL dialect](https://tinyjoin.org/guides/sql-compatibility/). See the
 * [Kysely guide](https://tinyjoin.org/guides/kysely/) for what works, and what does not.
 * @packageDocumentation
 * @module kysely
 * @since v0.5.0
 */

import type {
  DatabaseIntrospector,
  Dialect,
  DialectAdapter,
  Driver,
  QueryCompiler,
} from 'kysely';
import type {Client} from '../index.js';

/**
 * The TinyJoinDialectConfig interface configures a TinyJoinDialect.
 * @category Kysely
 * @since v0.5.0
 */
export interface TinyJoinDialectConfig {
  /**
   * The client property is the TinyJoin Client, from create(), on which
   * Kysely runs its queries. The application closes it: Kysely's `destroy`
   * leaves it open.
   * @category Kysely
   * @since v0.5.0
   */
  client: Client;
}

/**
 * The TinyJoinDialect class is a Kysely dialect that runs Kysely's PostgreSQL
 * queries on a TinyJoin Client.
 *
 * Kysely runs one query at a time on the Client, so a query outside a
 * transaction waits until the transaction ends rather than failing. Kysely's
 * `db.transaction` runs on the Client's callback transaction, which holds the
 * database, and every tab sharing it, until it commits or rolls back. It takes
 * no isolation level or access mode, and there are no savepoints.
 *
 * Its introspector reads the Client's getSchema, and Kysely's Migrator runs
 * each migration's statements in turn, since TinyJoin runs DDL outside
 * transactions, with a Web Lock keeping one tab migrating at a time.
 * @example
 * ```ts
 * import {create} from 'tinyjoin';
 * import {TinyJoinDialect} from 'tinyjoin/kysely';
 * import {Kysely} from 'kysely';
 *
 * interface Database {
 *   tasks: {id: string; title: string; done: boolean};
 * }
 *
 * const client = await create('opfs://my-app-v1');
 * const db = new Kysely<Database>({dialect: new TinyJoinDialect({client})});
 * const open = await db
 *   .selectFrom('tasks')
 *   .select(['id', 'title'])
 *   .where('done', '=', false)
 *   .execute();
 * ```
 * @category Kysely
 * @since v0.5.0
 */
export class TinyJoinDialect implements Dialect {
  /**
   * The constructor creates a dialect over a TinyJoin Client.
   * @param config The dialect's configuration, with its Client.
   * @category Kysely
   * @since v0.5.0
   */
  constructor(config: TinyJoinDialectConfig);

  /**
   * The createDriver method creates the driver that runs Kysely's queries on
   * the Client. Kysely calls it.
   * @returns A Kysely driver.
   * @category Kysely
   * @since v0.5.0
   */
  createDriver(): Driver;

  /**
   * The createQueryCompiler method creates Kysely's PostgreSQL query compiler.
   * Kysely calls it.
   * @returns A Kysely query compiler.
   * @category Kysely
   * @since v0.5.0
   */
  createQueryCompiler(): QueryCompiler;

  /**
   * The createAdapter method creates the adapter that tells Kysely how
   * TinyJoin differs from PostgreSQL. Kysely calls it.
   * @returns A Kysely dialect adapter.
   * @category Kysely
   * @since v0.5.0
   */
  createAdapter(): DialectAdapter;

  /**
   * The createIntrospector method creates the introspector that lists the
   * database's tables and columns from the Client's getSchema. Kysely calls it.
   * @returns A Kysely database introspector.
   * @category Kysely
   * @since v0.5.0
   */
  createIntrospector(): DatabaseIntrospector;
}
````
