Schema & migrations

Define tables once in TypeScript. The same declaration drives migrations, inferred core and Kysely types, runtime validation, and schema tooling — and migrate() never rewrites stored data.

Schema management lives in @minnowdb/core. The catalog migrate() produces is also the public surface that client adapters and other tooling build on.

Defining a schema

import { column, schema, table, view } from "@minnowdb/core";
import { typedTable } from "@minnowdb/core/schema";

const people = table("people", {
  name: column.string().unique(),
  score: column.number(),
  birthday: column.date().nullable(),
  joined: column.datetime().nullable(),
});
const appSchema = schema([people]);

await database.migrate(appSchema);

// The optional core handle carries the table's inferred row shapes:
const peopleRows = typedTable(database, people);
await peopleRows.insert([{ name: "Ada", score: 10 }]); // joined pads to null
const rows = await peopleRows.rows();
// Array<{ name: string; score: number; birthday: string | null; joined: Date | null }>

The four physical storage builders are column.boolean(), column.number(), column.string(), and column.datetime(). The DSL also retains the SQL domain layered over that storage:

NUMERIC selects as a decimal string so no precision is lost, while writes may use a decimal string or a finite number. JSON/JSONB and arrays use their JSON text representation at the JavaScript boundary, matching direct SQL and the Kysely adapter. A JSON column may also declare its document's shape — column.jsonb<{ name: string }>() — a compile-time promise that leaves reads and writes JSON text but gives the Kysely adapter typed ->/->> traversal. column.date() is a zoneless calendar date: it validates canonical YYYY-MM-DD text and never adds a time or UTC offset.

The column modifiers are:

• .unique() — marks the table's unique key (one non-null column).
• .nullable() — permits NULL and widens the inferred type.
• .autoIncrement() — generates monotonically increasing integers for omitted values from a persistent per-table counter that stays atomic across tabs. Number unique-key columns only. Explicit values are accepted and bump the counter past their maximum, so imports keep stable ids.
• .default(value) / .defaultSql(expression) — stores a literal or a variable-free SQL expression in the catalog. Omitted columns and explicit SQL DEFAULT slots evaluate it inside the engine, once per inserted row, through every write path. Explicit NULL stays NULL; a nullable column may therefore also have a default. defaultSql accepts the SQL functions and operators Minnow implements, including CURRENT_TIMESTAMP, random(), gen_random_uuid(), and nextval(...). It rejects column references, parameters, aggregates, windows, subqueries, and a result type that does not match the column. RETURNING echoes the generated values.
• .generatedSql(expression) — stores an immutable expression over ordinary sibling columns and recomputes it on every insert, upsert, and update. Generated columns are omitted from inferred insert/update inputs and cannot be row-addressing keys. The expression cannot use parameters, aggregates, windows, subqueries, volatile functions, or another generated column.
• .renamedFrom() — renames through the column's stable ID, so a rename is a metadata step rather than a drop-and-add.
• .backfill(value) — what rows written before this column existed read as, instead of NULL. Giving one is what makes adding a non-nullable column possible.
• .references(table, column, { onDelete }) — declares a FOREIGN KEY onto another table's unique key. migrate() creates it as a real constraint, so a write naming a parent row that does not exist is rejected. onDelete is "restrict" (the default), "cascade", or "set null"; the last requires a nullable column.
• .references(table, column, { enforced: false }) — records the relationship in catalog introspection without validating child writes or acting on parent deletes. Informational keys cannot specify onDelete because they never perform a referential action.

The complete select, insert, and update types are available as InferRow<T>, InferInsertRow<T>, and InferUpdateChanges<T>. Defaults and nullable columns are optional on insert; primary-key columns are excluded from updates. The Kysely adapter converts the same metadata to Kysely's ColumnType<Select, Insert, Update> automatically.

Enum columns

column.enum([...]) is a string column restricted to a closed set of values, typed as their literal union:

const tickets = table("tickets", {
  id: column.number().unique().autoIncrement(),
  status: column.enum(["open", "closed", "reopened"]).default("open"),
});
await database.migrate(schema([tickets]));
// Selects return "open" | "closed" | "reopened"; inserts and updates accept nothing else.
const ticketRows = typedTable(database, tickets);
await ticketRows.insert([{ status: "open" }]);
await ticketRows.insert([{ status: "lost" }]); // compile error

The set is enforced twice: at compile time through the inferred union, and at runtime on every write path (batch inserts, upserts, keyed updates, and SQL statements), so an untyped caller can't sneak an outside value into storage. Physically the column stays a plain string column — the value set is catalog metadata, which keeps its migrations metadata-only: adding values or relaxing the column to column.string() is safe, while removing values or tightening an existing string column into an enum is rejected (existing rows could already violate the set).

column.sqlEnum("mood", values) additionally retains a PostgreSQL-style type name for generated SQL and introspection. The name and values belong to that column declaration; the schema DSL does not create or manage a reusable CREATE TYPE mood catalog object. Use SQL or Kysely migrations when several independently managed tables must share one named enum type.

const notes = table("notes", {
  id: column.number().unique().autoIncrement(),
  slug: column.uuid().defaultSql("gen_random_uuid()"),
  status: column.string().default("draft"),
  created: column.datetime().defaultSql("CURRENT_TIMESTAMP"),
  body: column.string(),
});
await database.migrate(schema([notes]));
// Inferred insert types make defaulted columns optional:
const noteRows = typedTable(database, notes);
await noteRows.insert([{ body: "hello" }]);

InferInsertRow<typeof notes> carries the "engine can fill this" fact into the insert type. The SQL text remains catalog metadata and crosses the worker boundary unchanged. CURRENT_* uses one statement timestamp; volatile functions such as gen_random_uuid() run separately for each omitted row.

For a composite value maintained from sibling columns, declare a stored generated column instead of repeating the calculation at every write site:

const rows = table("offline_rows", {
  id: column.integer().unique(),
  field_id: column.string(),
  ticket_number: column.integer(),
  version: column.integer(),
  offline_key: column
    .string()
    .generatedSql("field_id || ':' || CAST(ticket_number AS TEXT) || ':' || CAST(version AS TEXT)"),
});

offline_key appears in InferRow, but not in InferInsertRow or InferUpdateChanges. Creating the table stores the expression immediately. An existing application-maintained column can adopt the expression only after migrate() scans and verifies every value. Adding a brand-new generated column to an existing table is refused because that would require rewriting its rows.

Declaration order does not matter: migrate() creates a table after the tables it references, so a child may be listed before its parent.

Relations and row conditions

A relation is enforced by default. The same is true of checks, the third argument to table():

const parents = table("parents", {
  id: column.integer().unique(),
  label: column.string(),
});

const children = table(
  "children",
  {
    id: column.integer().unique(),
    parent_id: column.integer().references("parents", "id", { onDelete: "cascade" }),
    qty: column.integer(),
  },
  { checks: [{ name: "positive_qty", sql: "qty > 0" }] },
);

await database.insertBatch("children", [{ id: 1, parent_id: 999, qty: 1 }]);
// throws: FOREIGN KEY children_parent_id_fkey has no parents row with 999
await database.insertBatch("children", [{ id: 1, parent_id: 1, qty: 0 }]);
// throws: CHECK positive_qty failed for row 0 of children

This is exactly what the equivalent SQL DDL produces — same catalog, same constraint names, same rejections:

CREATE TABLE children (
  id INTEGER PRIMARY KEY,
  parent_id INTEGER NOT NULL REFERENCES parents(id) ON DELETE CASCADE,
  qty INTEGER NOT NULL,
  CONSTRAINT positive_qty CHECK (qty > 0)
);

Each check is a boolean SQL expression over the table's own columns, compiled when the table is declared — so invalid SQL, an unknown column, or a reference to another relation fails before migrate() can change the catalog. CHECK and FOREIGN KEY names share the table's constraint namespace; a duplicate name across kinds is rejected at declaration time.

Informational relationships

Use an informational foreign key for a display relationship that may legitimately dangle—for example, a ticket whose customer is outside the current authorization or synchronization window:

const tickets = table("tickets", {
  id: column.integer().unique(),
  customer_id: column.integer().references("customers", "id", { enforced: false }),
});

Minnow still validates that the child and parent key domains line up, and introspect() returns the relationship with enforced: false. It does not reject a missing customer, cascade a delete, or set the child to NULL. Informational relationships may be added, removed, or remapped on an existing table as metadata-only migration steps; changing between enforced and informational is rejected. A composite entry in table(..., { foreignKeys }) accepts the same enforced: false flag.

Composite primary and foreign keys

Use the table options for ordered, multi-column identity and relations:

const accounts = table(
  "accounts",
  {
    tenant_id: column.uuid(),
    account_no: column.integer(),
    balance: column.numeric({ precision: 12, scale: 2 }).default("0"),
  },
  { primaryKey: ["tenant_id", "account_no"] },
);

const entries = table(
  "entries",
  {
    tenant_id: column.uuid(),
    account_no: column.integer(),
    entry_no: column.integer(),
    memo: column.string(),
  },
  {
    primaryKey: ["tenant_id", "account_no", "entry_no"],
    foreignKeys: [
      {
        name: "entries_account_fkey",
        columns: ["tenant_id", "account_no"],
        references: { table: "accounts", columns: ["tenant_id", "account_no"] },
        onDelete: "cascade",
      },
    ],
  },
);

The child and parent lists must have the same arity, their domains must match, and the parent list must match the declared primary-key order. .unique() remains the shorter scalar-key form.

Backfilling an added column

A column added by a migration has no data in older segments, so its rows would read NULL forever — which is why an added column had to be nullable. A backfill says what those rows read instead:

const notes = table("notes", {
  id: column.number().unique(),
  body: column.string(),
  status: column.string().backfill("archived"), // added later; old rows read "archived"
});

Nothing is rewritten. The stored segments are untouched, and the value is substituted at read time — so adding a backfilled column to a table of ten million rows costs a catalog write, not a scan. Compaction folds the value into the blocks whenever it next rewrites them.

The value is real to the engine, not patched onto output rows: you can filter, group, and join on it exactly as if it had always been stored.

A function runs once, when the migration adds the column, and its result is frozen into the catalog:

column.datetime().backfill(() => new Date()); // one timestamp, shared by every pre-existing row

That is what "derived" means here — derived at migration time, not per row. A value that depends on other columns would need one value per row, which is a rewrite rather than a metadata step, and is not supported.

Backfills apply to non-nullable columns only. A nullable column already has an answer for rows that never had a value, and the declaration is rejected rather than quietly ignored.

Keep .backfill(...) in the declaration after it has been applied. Its frozen value is part of the column's meaning for old segments: adding, removing, or changing it later is rejected rather than silently changing what existing rows read. A default is separate — it fills future inserts, while the backfill answers rows written before the column existed.

Views

A view is a named query. Declare it with the columns you expect it to produce:

const activeCustomers = view("active_customers", {
  sql: `SELECT customer_id, name FROM customers WHERE status = 'active'`,
  columns: { customer_id: column.number(), name: column.string() },
});

const appSchema = schema([customers, orders], { views: [activeCustomers] });

The engine infers the query's real output schema when it creates the view and compares it to what you declared, so a body that drifts from its declaration fails the migration instead of surprising a reader later.

Views are readable, not writable. Query them like tables; an insert is rejected because the catalog records the object as a view:

await database.query("SELECT name FROM active_customers");
await database.execute("INSERT INTO active_customers (name) VALUES ($1)", ["Ada"]); // rejected

Because nothing is stored under a view, replacing one is always safe: change the sql and the next migrate() redefines it in place. Removing the declaration drops the view — within a schema, the declaration is the source of truth.

That authority stops at the views the schema created. A view made with CREATE VIEW, or one written before Minnow recorded ownership, belongs to no schema and no migration removes it — "the schema never mentioned it" is not proof that it should go. introspect() reports which is which as managed, and database.dropView(name) removes either.

What migrate() does

migrate() compares the live catalog with your declaration and applies the difference as deterministic steps:

• Each step is all or nothing, and running the same migration again is safe. If a migration is interrupted, the next run completes what remains.
• Concurrent migrators can't interleave — the loser fails with a typed conflict.
• DDL shares the statement executor. Creating, replacing, and dropping schema objects uses the same engine statements as SQL DDL. A table's metadata-only column edits stay grouped into one catalog compare-and-swap, preserving one write per changed table instead of parsing and committing a separate ALTER TABLE for every column.
• Nothing is rewritten. Not one stored byte changes: a column added later is answered at read time, and folding it into the blocks is compaction's job, on its own schedule.

Column storage types, SQL domains, primary-key membership/order, enforced foreign keys, checks, and frozen backfills cannot be changed in place. migrate() rejects those changes because a catalog-only edit cannot prove or preserve their meaning. Informational foreign keys are metadata and may change; enum restrictions are the other exception, with sets that may widen but never narrow. A matching ordinary column may also adopt or drop a stored generated expression under the proof below. Secondary indexes, triggers, named enum-type objects, and sequences remain SQL/Kysely migration concerns; the schema DSL does not claim ownership of them.

Proven rather than assumed

Three changes are earned rather than declared. The first two read only the same checksum-verified block statistics that drive data skipping:

• Tightening a column to NOT NULL. Every block records its own null count. If any visible row holds NULL the migration is refused and nothing is applied; otherwise the column tightens with no scan and no rewrite. Rows written before the column existed count as NULL unless it carries a backfill.
• Adopting .autoIncrement(). The counter is seeded past the largest key already stored, taken from each block's numeric zone map, so a generated id can never collide with one already written. Dropping the generator is free — writes simply stop being filled.
• Adopting .generatedSql(). Every existing value is compared with the declared expression. Unlike the two header-only proofs above, this scans and decodes the table. A mismatch refuses the migration without changing the catalog. Dropping generation is metadata-only; the stored values remain ordinary values and future writes stop recomputing them.

Retrying a busy OPFS store

migrate() reads the catalog through the current OPFS leader. When leadership is temporarily unreachable or a request queue is full, it can reject with OpfsCoordinationError, exported from @minnowdb/core. The class and its reason survive a worker boundary, so boot code can classify it with instanceof and retry with bounded backoff. Two clients can migrate the same schema; a later call reads the already-applied catalog and has no remaining steps.

Do not drop and rebuild a database simply because migrate() rejected. A schema refusal requires reviewing the intended schema change; a temporary coordination error requires retrying. An OpfsUncertainOutcomeError means a mutation may have committed and needs reconciliation before retry. Replacing a disposable cache is an explicit application policy, and all clients must close before deleting its OPFS store.

Dropping things

Removing a column from a table you declare, or a table from a schema that speaks for the whole database, is a metadata step — the column stops being projected, the table record goes, and compaction reclaims the bytes when it next rewrites those segments. Nothing is scanned.

It is also the only kind of migration that destroys data, and a migration runs when an application opens, with nobody to review it. A schema file that drifted — a rename typed wrong, a branch checked out — would otherwise delete rows on launch. So destroying anything is a decision you make:

await database.migrate(appSchema); // throws, naming exactly what it would have destroyed
await database.migrate(appSchema, { allowDestructive: true }); // applies it

Tables need a second word. A schema is not necessarily the whole database — an application may migrate feature by feature, each call declaring only its own tables — so a table you no longer declare is left alone unless you say the schema speaks for everything:

await database.migrate(appSchema, { allowDestructive: true, schemaOwnsDatabase: true });

Even then, only tables a migration created are dropped. One made with CREATE TABLE belongs to no schema, exactly as with views.

A drop is refused outright when something in the catalog still points at the column — its primary or unique key, a FOREIGN KEY, a CHECK, or a secondary index — because dropping it would leave that catalog object naming a column that is not there.

Rejected outright, rather than attempted:

• type changes
• unique-key changes
• non-nullable column additions without a backfill
• removing enum values, or tightening a string column into an enum
• adding, changing, or dropping an enforced FOREIGN KEY or CHECK on an existing table

Type and identity changes need a deliberate table replacement. A new or changed constraint also needs proof about existing rows, and there is no validation scan. Declare constraints when you create the table, or create a new table and copy deliberately. Views are the exception — they hold no rows, so replacing one needs no proof.

If you need one of those, that's a new table plus a deliberate application-level copy.

Inferred table shapes

Every table declaration carries select, insert, and update types directly:

type Person = InferRow<typeof people>;
type NewPerson = InferInsertRow<typeof people>;
type PersonChanges = InferUpdateChanges<typeof people>;

Typed batch writes

Hand the same declaration to the engine — or to MinnowDatabaseClient — and its batch API is typed by it. Every method is keyed by table name, and migrate() needs no argument:

const database = new MinnowDatabase(store, { schema: appSchema });
await database.migrate();

// `joined` and `birthday` are nullable, so they may be omitted; `name` and `score` may not.
await database.insertBatch("people", [{ name: "Ada", score: 10 }]);
// The key has the `.unique()` column's type; an `undefined` change leaves that column alone.
await database.update("people", "Ada", { score: 11, birthday: undefined });
// Array<{ name: string; score: number }>
const scores = await database.readTable("people", { columns: ["name", "score"] });
// The guard's `value` follows the column it names.
await database.upsert(
  "people",
  { name: "Ada", score: 12 },
  { conflictWhere: { column: "score", operator: "<", value: 12 } },
);

A misspelled table or column, a missing required column, a key of the wrong type, or a guard whose value does not match its column is a compile error — inside write() scopes and on a buffered writer's add as well. Without schema the batch methods take plain strings, as they always have, and a declared database is still assignable wherever an undeclared MinnowDatabase or MinnowDatabaseClient is expected.

Each table definition also carries a Standard Schema-compatible ~standard validator, so any library that speaks that interface can validate rows at runtime with your definitions.

planMigration(catalog, schema) is the same diff migrate() runs, exposed as a pure function over the published catalog — useful for previewing what a migration would do, or for building schema tooling of your own.

Raw createTable (see Writes) remains available when compile-time types aren't needed.