SQL

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 domainSpellingsJavaScript result
exact whole numberINTEGER, BIGINT, SMALLINT, INT4, INT8, INT2number
approximate numberDOUBLE PRECISION, REAL, FLOAT, FLOAT8, FLOAT4number
exact decimalNUMERIC(p, s), DECIMAL(p, s)decimal string
stringVARCHAR(n), CHARACTER VARYING(n), TEXT, CHAR(n)string
booleanBOOLEAN, BOOLboolean
calendar dateDATEcanonical YYYY-MM-DD string
instantTIMESTAMPTZ, TIMESTAMP WITH TIME ZONEDate
timestampTIMESTAMP, TIMESTAMP WITHOUT TIME ZONE, DATETIMEDate
time of dayTIMEHH:MM:SS[.ffffff] string
JSONJSON, JSONBJSON string
UUIDUUIDlowercase string
intervalINTERVALPostgreSQL text string, 1 day 02:00:00
arrayINTEGER[], TEXT[], and other core arraysJSON string
enuma name declared by CREATE TYPE … AS ENUMmember 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 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. 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_channel

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

On this page