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. Exactly one column may be PRIMARY KEY — written on the column or as a table-level PRIMARY KEY (order_id) — and it is the table's unique key: the column that UPDATE, DELETE, and ON CONFLICT address rows through, and the only uniqueness the engine enforces.

CREATE TABLE IF NOT EXISTS leaves an existing table of that name alone, and 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'

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.

A FOREIGN KEY references another table's unique key — the column the engine can probe for existence, and the one its keyed writes address rows by:

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
)

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 UPDATE has nothing to act on, because a unique key cannot change.

A table without a PRIMARY KEY is append-only. That is a reasonable choice for an event log, and a mistake for anything a user edits, because it cannot be changed later without recreating the table.

Column types

Four logical types, chosen because they are what a browser can store and compare without ambiguity. The usual SQL spellings map onto them:

Widths in VARCHAR(80) are accepted and ignored — they document intent, and nothing truncates. NUMERIC is IEEE-754 double precision, not arbitrary precision: money is safe to the cent in the ranges an application deals with, but this is not the engine to settle accounts in.

Types the engine deliberately does not have — JSON/JSONB, arrays, UUID, INTERVAL as a stored type, enums as a database type — are rejected at CREATE TABLE rather than silently stored as text.

Defaults

A column can declare a default in SQL, either a constant or CURRENT_TIMESTAMP:

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

A column with a default is NOT NULL unless declared otherwise, because the default is what an absent value means — NULL and the default cannot both claim the slot.

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: "now" } },
    { name: "source", type: "string", defaultValue: { kind: "literal", value: "app" } },
  ],
});

Three kinds: now, a literal, and autoincrement on the key column. They fill null-or-absent slots at insert time and are never applied at read time, so adding a default later does not rewrite the rows already stored. Arbitrary expressions are deliberately not representable — the spec is stored in the catalog and crosses the worker boundary, so it has to be plain data.

Evolving a table

ALTER TABLE orders ADD COLUMN channel TEXT

Tables can gain nullable 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 column added to a table with rows in it is always nullable — the rows already stored have no value for it — though a DEFAULT fills the rows written afterwards.

DROP TABLE takes the table's rows, its catalog record, its full-text index, 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, dropping it, or adding a NOT NULL column to a table with rows in it are all refused — each would need a rewrite of every block, and doing that silently behind a DDL statement is how a browser tab freezes.

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", nullable: false }, …] }]

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