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 and tables. fields contains ordered {name, dataTypeID} entries, including for empty typed results. rowMode: "array" returns values in that field 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

The right side of an ordinary predicate is a literal or parameter, not another column or subquery. Column-to-column comparison exists only in a join's ON equality terms.

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.

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 such as VARCHAR(100) 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.
• 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.

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; and
• separate unique indexes.

It does not implement foreign keys, CHECK, exclusion constraints, generated columns, sequences, triggers, or dependency cascades.

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. Complete primary-key equality uses direct lookup; complete equality for every column of a secondary index can use its postings. Partial composite matches, ranges, OR, and NOT scan. UPDATE and DELETE currently scan even for a primary-key predicate.

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 JSON object field 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 chains are evaluated as written, from left to right, by a bounded nested loop; TinyJoin does not reorder or optimize them. Each ON equality must connect its newly introduced source to one of the sources already in scope. 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. The engine counts actual candidate comparisons while executing; it does not reject a join merely because the full Cartesian product is large. Source row counts still enforce the scan and retained-build-row limits before execution.

For example, three tables of 100 rows joined on unique matching identifiers need about 20,000 candidate comparisons and return 100 rows. Such a selective chain fits. Two tables of 1,001 rows whose join keys all match can exceed the 1,000,000-comparison budget even when a later WHERE removes every result. Order and join shape therefore matter. An unordered LIMIT can stop early; an ordered join must first collect its matches. Standalone queries and exec() also charge scans and comparisons to their shared script-work budget, which can be reached before the comparison-only 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

Foreign keys are not implemented, so TinyJoin does not enforce the bridge table's references.

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

An ordered query can reach its materialization limit before applying a small LIMIT. 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, including comparisons that fail the join condition. The 1,024-byte secondary-index key limit covers the complete encoded indexed tuple, separator, and primary-key tuple together, not each component independently. 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.