Tables and types

CREATE TABLE

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  employee_id INTEGER,
  status VARCHAR(20) NOT NULL,
  total DOUBLE PRECISION NOT NULL,
  refunded BOOLEAN NOT NULL,
  placed_at TIMESTAMP NOT NULL
)

Columns are nullable unless marked NOT NULL. A scalar or composite PRIMARY KEY is the table's row identity: UPDATE, DELETE, foreign keys, and ON CONFLICT address rows through it. A table may declare several scalar or composite UNIQUE constraints, inline or table-level; each is enforced independently. A table with one inline UNIQUE column and no primary key uses that column as its row identity.

CREATE TABLE order_items (
  order_id INTEGER,
  line_no INTEGER,
  sku TEXT NOT NULL,
  serial_no TEXT UNIQUE,
  PRIMARY KEY (order_id, line_no),
  UNIQUE (order_id, sku)
)

Primary-key components are immutable. Composite identity uses an internal scalar locator, so it does not add tuple comparison work to ordinary scans.

A SERIAL, BIGSERIAL, or SMALLSERIAL column, a column declared GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY, and SQLite's INTEGER PRIMARY KEY AUTOINCREMENT all read as the table's auto-increment key, the same thing the schema DSL's column.integer().autoIncrement() declares: omitted values count up from 1, an explicit value is accepted without advancing the counter (as a PostgreSQL sequence behaves), and identity sequence options in parentheses are accepted and ignored. The counter belongs to the row identity, so the column must be the primary key.

CREATE TABLE events (id SERIAL PRIMARY KEY, kind TEXT NOT NULL, at TIMESTAMP DEFAULT NOW())

CREATE TABLE IF NOT EXISTS leaves an existing table of that name alone. Concurrent callers converge on the one atomically admitted catalog object instead of leaking a duplicate-name race. CREATE TABLE … AS SELECT takes both its columns and its first rows from a query:

CREATE TABLE completed_orders AS
SELECT order_id, customer_id, total FROM orders WHERE status = 'completed'

The target stays invisible while query blocks are staged and publishes with its rows in one atomic commit; a conflict, quota refusal, crash, or query error leaves no empty table behind. Single-scan queries stream into bounded storage batches. Query shapes that require a complete result (for example, a global sort) may spill, but their retained result is capped at 64 MiB (or the database's lower query-memory budget) and fail before the target publishes.

A CHECK constraint is a row condition over the table's own columns, and it runs on every path that writes a row — insert, upsert, and update, which is checked against the row as it will be once the update lands:

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  total DOUBLE PRECISION NOT NULL CHECK (total >= 0),
  status VARCHAR(20) NOT NULL,
  CONSTRAINT settled_orders_have_a_total CHECK (status <> 'completed' OR total > 0)
)

A constraint fails only when it evaluates to false, so SQL's unknown passes: a NULL column satisfies CHECK (total >= 0) unless the column is also NOT NULL. Minnow parses every CHECK and resolves all of its column references before admitting the table; it cannot refer to another relation. Named CHECK, FOREIGN KEY, and UNIQUE constraints share one per-table namespace, so reusing a constraint name across kinds is rejected before the catalog changes.

A FOREIGN KEY references another table's primary row identity. Scalar and composite references use the same ordered columns as the parent key:

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  note_id INTEGER REFERENCES notes(note_id) ON DELETE SET NULL
)

CREATE TABLE order_notes (
  order_id INTEGER,
  line_no INTEGER,
  note TEXT,
  FOREIGN KEY (order_id, line_no) REFERENCES order_items(order_id, line_no)
)

Every write of a referencing column checks that the parent row exists, reading through the writing transaction, so a child inserted beside its parent in one scope sees it. A NULL reference names no parent and is satisfied.

ON DELETE takes RESTRICT (the default), CASCADE, and SET NULL, and the action runs inside the deleting transaction — a parent and its dependents publish together or not at all. ON DELETE SET DEFAULT is rejected with an explicit error; use SET NULL or CASCADE. ON UPDATE has nothing to act on, because primary-key columns cannot change.

A table without a row-addressing PRIMARY KEY or single inline UNIQUE column is append-only. That is a reasonable choice for an event log, and a mistake for anything a user edits, because the key cannot be added later without recreating the table.

Secondary indexes

Use a durable secondary index when a production query repeatedly filters a large table on a non-key column:

CREATE INDEX orders_by_status ON orders (status);
CREATE INDEX IF NOT EXISTS orders_by_total ON orders (total DESC);
CREATE INDEX orders_by_customer_status
  ON orders (customer_id ASC, status ASC, placed_at DESC);
CREATE UNIQUE INDEX orders_customer_time ON orders (customer_id, placed_at);

Composite planning follows the leftmost-prefix rule: equality or IN can constrain the leading columns, followed by one range (<, <=, >, >=) on the next column. ASC and DESC are part of the physical tuple order. On a keyed table the postings name the matching rows exactly: an equality or IN lookup visits those rows and no others, and when the indexed table is the other side of a join, only those rows are read before the join. Minnow still evaluates the SQL predicate on every candidate row, so a stale posting or an internal locator collision can cost an extra read but cannot change an answer.

A nullable indexed column does not cost you the index. A row with a NULL in a trailing indexed column is named under its non-null leading columns, so WHERE customer_id = ? still prunes through orders (customer_id, status) when status is nullable, and the NULL rows come back with the rest. The NULL marker sorts outside every real value, so an equality, IN, or range on that column never matches it — which is what SQL means by a comparison with NULL. A NULL in the leading column is deliberately not indexed: no such predicate could match it. Indexes created before 0.10.2 keep serving equality and range lookups; rebuild one (DROP INDEX, then CREATE INDEX) to get prefix pruning past its nullable trailing columns.

Exact equality remains indexable for SQL-domain columns such as NUMERIC and enum. Their lossless physical strings are not bytewise SQL order, so range pruning and index-supplied ORDER BY fall back to the ordinary scan/sort path for correctness.

UNIQUE is not an optimizer hint. It owns an independent membership set that commits atomically with every insert, update, delete, upsert, trigger-derived write, and multi-statement write scope. Concurrent tabs cannot both publish the same key. A key with any NULL component does not conflict, matching SQL unique-constraint semantics. Failed builds leave no catalog entry, and snapshot restore keeps enforcement active even when the accelerator postings need rebuilding.

Indexes are maintained across inserts, updates, deletes, and upserts. Their base is built a row group at a time, in chunks that grow only as far as a large table needs to stay within the store's chunk limit, so an index can be built over any table. New commits append bounded deltas, and background folding prevents a long-running write workload from retaining one index object per transaction. A UNIQUE index stages its key set in bounded, ordered chunks and publishes it together with the ready index. A build is published with one atomic pointer change; a crash can leave reclaimable staging bytes but cannot expose a partial index. Multi-tab writers that cannot prove they updated an index invalidate the accelerator, and the next relevant query rebuilds it while answering correctly from a scan.

DROP INDEX IF EXISTS orders_by_status;

Index names are global within the database. On a keyless append-only table, an exact index whose leading columns match the complete ORDER BY can deliver forward or reverse order without a sort. If every referenced column is in that index, the scan is covering and reads no table blocks. All indexed columns must be NOT NULL for that optimization; nullable, keyed, mutation-history, join, grouping, and unsupported order shapes keep the ordinary correct scan and bounded sort. Those restrictions affect speed only, never the accepted SQL or its result.

Column types

Blocks retain four compact physical kinds—number, string, boolean, and datetime—but SQL domains add validation and semantics without widening hot scan vectors:

Widths in VARCHAR(80) are accepted and ignored — they document intent, and nothing truncates. So are CREATE TEMP, TEMPORARY, and UNLOGGED TABLE: every table lives in the one database with one durability. A column constraint may be named (n INTEGER CONSTRAINT n_positive CHECK (n > 0)), as a table constraint may. An integer write or cast must be inside JavaScript's exact safe-integer range, −9,007,199,254,740,991 through 9,007,199,254,740,991. Minnow rejects a value outside that range instead of rounding an ID or quantity before it reaches disk. SQL declarations set this guard automatically; the low-level createTable API can request the same domain with integer: true on a number column. An integer constant beyond that range is not an error: it stays exact, like PostgreSQL's int8 and NUMERIC typing of large constants, and writing one to an integer column is rejected rather than rounded.

Integer division is PostgreSQL's. When both operands of / are integers — integer columns, integer constants, COUNT, an integer CAST, or integer arithmetic and aggregates over them — the quotient truncates toward zero: 7 / 2 is 3, -7 / 2 is -3, and SUM(qty) / COUNT(*) is a whole number. Write 7 / 2.0, divide by a DOUBLE PRECISION or NUMERIC column, or cast an operand when a fractional quotient is wanted. A bound parameter takes the type of its integer partner, so qty / $1 truncates when $1 is bound to an integer.

Exact decimals never pass through Float64. Precision and scale are enforced on writes, and exact comparison, arithmetic, aggregates, windows, grouping, and sorting retain decimal semantics. Division picks its result scale the way PostgreSQL does — roughly sixteen significant digits, never fewer fractional digits than either operand, rounded half away from zero. Use an integer minor unit when that simpler boundary fits; use DOUBLE PRECISION only when approximation is intended.

Decimal constants in SQL are exact too, because PostgreSQL types every decimal constant NUMERIC: arithmetic among constants folds in exact decimal space, so SELECT 0.1 + 0.2 is 0.3 and 1.000000000000000000000000 / 3 keeps twenty-four true digits, the quotient scale following the written scales. A result that reads back identically from a JavaScript number is returned as an ordinary number — the same cast PostgreSQL applies when a numeric constant meets a float column — so price * 1.1 still computes and returns binary floats. A result the number boundary would visibly round is returned as an exact decimal string instead. That exactness also holds beside a float column: a constant Float64 cannot represent keeps exact semantics in arithmetic and comparisons where PostgreSQL would cast it to the float and round — a deliberate difference recorded in the feature profile.

A column with a declared scale renders at exactly that scale, matching PostgreSQL: 1.5 in a NUMERIC(10, 2) column reads back as '1.50', and so do aggregates, window aggregates, and COALESCE over it, plus CAST(… AS NUMERIC(p, s)) results. Two shapes render canonically instead (trailing fractional zeros stripped), where PostgreSQL would keep each value's own scale: a bare NUMERIC column with no declared scale, and derived arithmetic such as price * 2, whose result type carries no scale.

JSON validates a document and preserves object key order; JSONB canonicalizes object keys. Numbers read from JSON text retain every decimal digit in storage, extraction, constructors, and JSONB comparisons. They never pass through JavaScript's floating-point parser. JSON numbers share the exact numeric digit limit; documents also have the scalar size, nesting, and item limits described in the SQL reference. Pass JSON text when precision matters: digits already rounded in a JavaScript number cannot be recovered. Native JSON.parse on a returned document can round its numbers again. This fix cannot restore digits already rounded in documents stored by an older build. UUID values validate and normalize. Arrays use canonical JSON at the JavaScript boundary. An unquoted number in ARRAY input must retain its decimal value when converted to a JavaScript number; otherwise insertion is refused. Encode exact values as JSON strings to retain their digits, as the ARRAY constructor does for exact NUMERIC members. Minnow supports one-based scalar array subscripts, ARRAY_AGG with ordering and DISTINCT, and typed UNNEST(ARRAY[...]) constructor sources with optional WITH ORDINALITY. Array slices, multidimensional access, correlated UNNEST inputs, and PostgreSQL array operators such as || remain unsupported. Arrays compare their represented scalar elements lexicographically, with NULL elements last; JSONB compares structural values, using numeric comparison for JSON numbers. JSON continues to compare its text representation. || remains text concatenation and is refused on two array or JSONB operands. Intervals retain separate month, day, and microsecond fields, and a date or datetime accepts + INTERVAL and - INTERVAL; interval-valued arithmetic such as interval-plus-interval is unsupported.

Declare an enum before using it as a column type:

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped');
CREATE TABLE order_state (
  order_id INTEGER PRIMARY KEY,
  status order_status NOT NULL
);

Enum members validate on write and sort in declaration order. ALTER TYPE is not supported.

Sequences are durable, non-transactional counters:

CREATE SEQUENCE order_ids;
SELECT NEXTVAL('order_ids') AS order_id;
SELECT CURRVAL('order_ids') AS current_order_id;

CURRVAL is local to the database session and fails before its first NEXTVAL. Minnow does not support sequence options, ALTER SEQUENCE, or sequence calls anywhere but the select list of a SELECT without FROM — not in a table-reading query, a subquery, or a derived table. A statement that calls a sequence is never answered from the result memo. nextval('name') is valid in a column default. Use the key column's autoincrement default for ordinary table IDs.

Defaults

A column default is a variable-free SQL expression:

CREATE TABLE events (
  event_id INTEGER PRIMARY KEY,
  kind TEXT NOT NULL,
  source TEXT DEFAULT 'app',
  noted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  request_id UUID DEFAULT gen_random_uuid()
)

The expression may use the operators and scalar functions Minnow supports, including CURRENT_TIMESTAMP, random(), gen_random_uuid(), and nextval(...). It cannot reference a row column, take a parameter, aggregate rows, use a window, or contain a subquery. The engine parses and type-checks it before changing the catalog. CURRENT_* is stable for the statement; volatile functions run once for each omitted row.

A default does not imply NOT NULL. Omission or an explicit SQL DEFAULT invokes it. An explicit NULL remains NULL and is accepted or rejected by the column's own nullability.

The same defaults, and the autoincrement kind that SQL has no spelling for, are available through createTable, which is also the form the schema DSL compiles to:

await db.createTable({
  name: "events",
  uniqueKey: "event_id",
  columns: [
    { name: "event_id", type: "number", defaultValue: { kind: "autoincrement" } },
    { name: "kind", type: "string" },
    {
      name: "noted_at",
      type: "datetime",
      defaultValue: { kind: "expression", sql: "CURRENT_TIMESTAMP" },
    },
    { name: "source", type: "string", defaultValue: { kind: "literal", value: "app" } },
  ],
});

The catalog uses literal and expression specs, plus autoincrement on the key column. The SQL expression itself is plain structured-clone-safe data, so it persists and crosses the worker boundary. Defaults fill only omitted or SQL DEFAULT slots at write time; adding or changing one never rewrites rows already stored.

Stored generated columns

A stored generated column derives from other columns in the same row and is recomputed on every insert, upsert, and update:

CREATE TABLE offline_rows (
  id INTEGER PRIMARY KEY,
  field_id TEXT NOT NULL,
  ticket_number INTEGER NOT NULL,
  version INTEGER NOT NULL,
  offline_key TEXT GENERATED ALWAYS AS (
    field_id || ':' || CAST(ticket_number AS TEXT) || ':' || CAST(version AS TEXT)
  ) STORED NOT NULL
)

Callers omit offline_key, or use DEFAULT for it in an INSERT. Assigning an explicit value in an INSERT, UPDATE, trigger body, or conflict update is rejected. The expression may use immutable scalar operators and functions over ordinary sibling columns. It cannot use parameters, aggregates, windows, subqueries, volatile functions, another generated column, or a column from another table. Generated columns may be indexed and returned normally, but cannot be a primary key or the single unique row-addressing key.

The schema DSL spells the same declaration .generatedSql(expression). A new table stores the expression immediately. Adding a new generated column to an existing table is refused because it would require rewriting old rows. To move from an application-maintained column, keep the column name and type and add .generatedSql(...); migrate() scans the existing rows and adopts the expression only when every stored value already matches it.

Evolving a table

ALTER TABLE orders ADD COLUMN channel TEXT

Tables can gain columns and widen a NOT NULL column to nullable. Both are catalog-only changes: no blocks are rewritten, so they are effectively instant however large the table is. A constant DEFAULT fills the rows already stored, as PostgreSQL does, which is what lets the added column be NOT NULL:

ALTER TABLE orders ADD COLUMN channel TEXT NOT NULL DEFAULT 'web'

An expression default such as NOW() is evaluated per write, so it fills only the rows written afterwards; a column with one, or with no default at all, is added nullable and the rows already stored read NULL.

ALTER TABLE orders DROP COLUMN channel performs the same guarded metadata step as an explicitly authorized schema migration. It refuses the unique key, the last column, and anything a check, foreign key, trigger, view, or secondary index still reads. Drop the index first. Old column blocks remain until compaction rewrites their segments, while persisted full-text data for the column is removed with the catalog change. RESTRICT is the default; CASCADE is deliberately rejected so a typo cannot silently remove dependent objects.

DROP TABLE takes the table's rows, its catalog record, its secondary and full-text indexes, and its triggers. The blocks are retired through the commit rather than deleted, so a reader pinned to an older version keeps resolving the bytes it already holds and the collector reclaims them once nobody can reach them:

DROP TABLE IF EXISTS old_orders

What a pinned reader does lose is the table itself — the catalog has one present tense, so a snapshot open across a drop sees the table disappear rather than a frozen copy of it. A table another table's trigger writes to cannot be dropped; the trigger would fail at every firing.

Narrowing a column, changing its type, or adding a NOT NULL column to a table with rows in it are refused — each would need a rewrite of every block, and doing that silently behind a DDL statement is how a browser tab freezes. Dropping an independent column is the guarded catalog-only operation described above.

The schema DSL plans those changes for you from a declared schema, with migrate() applying only the steps that are safe.

Reading the catalog

const tables = await db.listTables();
// [{ name: "orders", columns: [{ name: "order_id", type: "number", integer: true, nullable: false }, …] }]

This is what the devtools schema rail and the SQL editor's autocompletion are built on.