Tables and types
CREATE TABLE, the column types, and what a unique key buys you.
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 TIMESTAMPTZ 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)
)A primary key can change: UPDATE ... SET on a key column rekeys the row, and every reference to
it follows. See Offline sync and rekeying. 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 row may also reference itself, or another row the same statement writes. 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.
A reference always follows its parent's rekey, in the same commit. ON UPDATE CASCADE is accepted
because it says what happens anyway; ON UPDATE RESTRICT, NO ACTION, SET NULL, and
SET DEFAULT are rejected. Unlike PostgreSQL, a reference with no ON UPDATE clause follows too,
instead of refusing the key change.
At the 1.0 cutover, omitting ON UPDATE still follows the parent row. Explicit clauses
retain their specified action instead: CASCADE, RESTRICT, NO ACTION, SET NULL, or
SET DEFAULT. The rebuild catalog distinguishes the implicit follow action from explicit
CASCADE, so a future format change cannot silently replace this product default.
NOT ENFORCED keeps the relationship without requiring the parent row to exist, for a table that
holds only part of its parent's rows. Such a reference takes no ON DELETE action but still
follows a rekey:
CREATE TABLE revisions (
id INTEGER PRIMARY KEY,
ticket_id TEXT REFERENCES tickets (id) NOT ENFORCED
)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. A case-sensitive LIKE
whose pattern starts with literal text (email LIKE 'ann%') reads that prefix's range of an
index on a text column, and an OR whose every branch an index serves reads the union of their
candidates. 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.
CREATE INDEX returns once the index is ready, and CREATE UNIQUE INDEX once it enforces. The
build reads a snapshot and lets writes continue meanwhile. A UNIQUE index can publish its key
set only if nothing was committed after that snapshot. When writes did land — in this tab or
another — it builds again while holding the write turn. Any index does the same when other tabs'
writes outgrow its delta tail before its build finishes. Writes wait for that second build, as
they would for any schema change; reads never wait. A row committed before the index enforces is
an existing row: if it repeats a key, CREATE UNIQUE INDEX fails with UniqueConstraintError —
the same error a write that repeats a key gets — and leaves no index. Once the index exists, folding its deltas rebuilds only its postings, never its key set, so a busy table
keeps the index ready.
CREATE INDEX IF NOT EXISTS succeeds on the same terms: once an index of that name is ready, and
a UNIQUE one enforces. So every tab can run the same statement at start-up. If another tab is
still creating the index, the statement waits for that tab's CREATE INDEX to finish. If that
build fails — say the table holds a duplicate — this statement creates the index itself and reports
its own result, which for a duplicate is the same failure. It never succeeds while nothing
enforces the index. As with PostgreSQL, the name alone decides: an existing index of that name on
other columns counts as existing.
A tab that closes or crashes in the middle of a build does not hold the index up. Each connection
holds a Web Lock for as long as it is open, and the browser releases it when the tab goes away.
Another tab's CREATE INDEX IF NOT EXISTS, or a query that needs the index, then takes the build
over within moments, while a tab that is still building is never interrupted. When a
CREATE UNIQUE INDEX IF NOT EXISTS takes over a build that then finds a duplicate, it fails and
removes the index, as the closed tab's statement would have. Without Web Locks (older Node
releases, or a browser too old to have them), a build abandoned by another context holds the
index for up to an hour, until its lease runs out.
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:
| SQL domain | Spellings | JavaScript result |
|---|---|---|
| exact whole number | INTEGER, BIGINT, SMALLINT, INT4, INT8, INT2 | number |
| approximate number | DOUBLE PRECISION, REAL, FLOAT, FLOAT8, FLOAT4 | number |
| exact decimal | NUMERIC(p, s), DECIMAL(p, s) | decimal string |
| string | VARCHAR(n), CHARACTER VARYING(n), TEXT, CHAR(n) | string |
| boolean | BOOLEAN, BOOL | boolean |
| calendar date | DATE | canonical YYYY-MM-DD string |
| instant | TIMESTAMPTZ, TIMESTAMP WITH TIME ZONE | Date |
| timestamp | TIMESTAMP, TIMESTAMP WITHOUT TIME ZONE, DATETIME | Date |
| time of day | TIME | HH:MM:SS[.ffffff] string |
| JSON | JSON, JSONB | JSON string |
| UUID | UUID | lowercase string |
| interval | INTERVAL | PostgreSQL text string, 1 day 02:00:00 |
| array | INTEGER[], TEXT[], and other core arrays | JSON string |
| enum | a name declared by CREATE TYPE … AS ENUM | member string |
TIMESTAMP and TIMESTAMPTZ values, including timestamp literals, keep millisecond precision
in the current engine. Fractional digits beyond the first three are discarded before
comparisons, arithmetic, or text output. For example,
TIMESTAMP '2000-02-29 12:34:56.123400'::text returns 2000-02-29 12:34:56.123;
PostgreSQL returns 2000-02-29 12:34:56.1234.
Both datetime kinds store the same UTC instant, compare and sort alike, do the same arithmetic,
and come back to JavaScript as a Date. They differ only in text, which follows PostgreSQL in a
UTC session — through CAST(x AS TEXT) or x::text, ||, CONCAT, FORMAT, TO_CHAR, and the
JSON constructors. A TIMESTAMPTZ column is an instant, so CAST(placed_at AS TEXT) is
2025-03-14 09:30:00+00, with fractional seconds only when they are nonzero
(2025-03-14 09:30:00.25+00), and TO_JSON(placed_at) is "2025-03-14T09:30:00+00:00". A
TIMESTAMP column, a TIMESTAMP '…' literal, a cast to TIMESTAMP, MAKE_TIMESTAMP,
LOCALTIMESTAMP, and a date plus an interval are timestamps without time zone, and their text
carries no zone: 2025-03-14 09:30:00, and "2025-03-14T09:30:00" in JSON. Views, CTEs, derived
tables, and CREATE TABLE … AS keep each column's kind; mixing the two in CASE, COALESCE, or a
UNION gives TIMESTAMPTZ, as PostgreSQL does. A precision such as TIMESTAMP(3) is accepted and
ignored; it does not change millisecond precision. Every text form reads back as the same instant. Text
written into either kind is read as an instant, so a TIMESTAMP column stores
'2025-03-14 09:30:00+02' as 07:30 UTC, where PostgreSQL would drop the offset and keep
09:30. A datetime column written by core 0.16.0 or earlier is TIMESTAMPTZ, whatever its DDL
said, and stays so after the automatic upgrade; listTables() and
introspect() report a TIMESTAMP column with withoutTimeZone: true, which the low-level
createTable API accepts on a datetime column too. An interval renders as 1 year 2 mons 3 days 04:05:06.5,
-00:00:01, or -3 days +04:00:00, and a time of day as 09:30:00.5. A datetime returned to
JavaScript is still a Date, not text.
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.
Non-constant integer addition, subtraction, and multiplication throw RangeError if the
result leaves the safe range; they never return a rounded integer. Unary minus, ABS,
remainder, and integer division preserve safe integer inputs. DIV and WIDTH_BUCKET
refuse a whole-number result outside the safe range with RangeError. SUM and AVG retain their
number result contract: an integer-typed argument throws RangeError as soon as a
partial sum leaves the safe range, even if later cancellation or division would bring it back.
This also applies to grouped, DISTINCT, live, and window aggregates, including running sums.
DOUBLE PRECISION arguments retain approximate floating-point accumulation even when every
value happens to be a whole number. A numeric input of unknown SQL type uses
floating-point accumulation. A bound JavaScript number follows literal typing: a safe whole
number is integer, while a fractional or unsafe whole number is floating point. Use an explicit
CAST(value AS DOUBLE PRECISION) to request floating-point aggregation regardless of value.
Integer intermediates in UPDATE, ON CONFLICT, MERGE, and trigger assignments have the same
range checks, even when a later operation would return to the safe range. Sequence expressions
(NEXTVAL and CURRVAL) and SQL defaults use these checks too. Untyped text operands in
integer arithmetic follow the integer CAST rules before conversion: out-of-range or fractional
integer text is refused.
Use CAST(value AS NUMERIC) before arithmetic or aggregation for exact decimal strings and
larger results. Constant arithmetic beyond the safe range returns an exact decimal string.
Integer division is PostgreSQL's. When both operands of / are integers — integer columns,
integer constants, COUNT, an integer CAST, a date - date difference, or integer arithmetic
and aggregates over them, including a CASE, COALESCE, or NULLIF whose non-NULL values are
all integers —
the quotient truncates toward zero: 7 / 2 is 3, -7 / 2 is -3, SUM(qty) / COUNT(*)
is a whole number, and (shipped - placed) / 7 over two DATE columns counts whole weeks.
Generated columns and CHECK constraints divide the same way. The same holds for functions that return an integer (LENGTH, POSITION,
STRPOS, ASCII, OCTET_LENGTH), for ROW_NUMBER, RANK, DENSE_RANK, NTILE, and COUNT
windows, for SUM, MIN, MAX, LAG, and FIRST_VALUE windows over an integer, and for the
columns of a recursive CTE whose first member selects integers. Write 7 / 2.0, divide by a
DOUBLE PRECISION or NUMERIC column, or cast an operand (qty / 2::float) 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.
A remainder is never JavaScript's negative zero: -4 % 4 and MOD(-4, 4) are 0, as in
PostgreSQL, whose %, MOD, and DIV work on integers and NUMERIC, which have no negative
zero. DIV and SIGN never return it either. An integer column stores a written -0 as 0.
DOUBLE PRECISION keeps -0 as PostgreSQL's float8 does: -amount for a zero amount is -0.
Integer multiplication and negation can still return -0 (-qty for a zero qty), where
PostgreSQL returns 0; SQL compares the two as equal.
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, typed
UNNEST(ARRAY[...]) constructor sources with optional WITH ORDINALITY, and x = ANY(array) /
x <> ALL(array) over an array column, constructor, or parameter. 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 read PostgreSQL's input spellings, including its own
output (1 year 2 mons 3 days 04:05:06.5) and fractional units (1.5 days is
1 day 12:00:00). A date or datetime accepts + INTERVAL and - INTERVAL,
timestamp - timestamp returns an interval, date - date returns whole days as an integer, and
date ± integer returns a date; arithmetic between intervals, 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 TEXTTables 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. ADD COLUMN IF NOT EXISTS leaves an existing column of that name as it is.
When compaction next folds the table, it writes those
rows with exactly the value they read.
ALTER TABLE orders RENAME COLUMN channel TO sales_channelRenaming a column is a catalog-only step too: stored rows, indexes, keys, and UNIQUE
constraints refer to the column by a stable id and follow it. PostgreSQL also rewrites a CHECK,
generated column, foreign key, trigger, or view that names the column; Minnow refuses the rename
instead, naming the dependent. Renaming a table (ALTER TABLE … RENAME TO) is not supported.
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_ordersWhat 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.