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:

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, tables, and keys, the changed keys of a write. 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:

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:

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

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

Either side of a comparison may be an expression, 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.

A predicate is one of the forms above, so a boolean column or literal is not one by itself. Write WHERE done = true or WHERE done = false rather than WHERE done or WHERE NOT done.

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:

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.

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:

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:

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

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 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:

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:

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:

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.

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

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 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. 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. 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:

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:

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

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

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:

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

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:

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

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.

Requests are serialized through one database-owning Worker. Clients that open the same OPFS name, in any tab, share that owner rather than opening the database again, so 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.

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.