Schema & migrations

Define tables once in TypeScript. The same declaration drives migrations, row types, and every query builder — and migrate() never rewrites stored data.

Schema management lives in @minnowdb/core, not in the typed client. migrate() is an engine capability, and the catalog it produces is what schema tooling of any kind builds on — so it works with or without the query builder. The examples below use the builder where it makes the types easier to see.

Defining a schema

import { column, schema, table, view } from "@minnowdb/core";
import { createMinnow, type InferDatabase } from "@minnowdb/client";

const people = table("people", {
  name: column.string().unique(),
  score: column.number(),
  joined: column.datetime().nullable(),
});
const appSchema = schema([people]);
interface DB extends InferDatabase<typeof appSchema> {}

await database.migrate(appSchema);

// The same declaration drives the query builder's types end to end:
const db = createMinnow<DB>(database, { schema: appSchema });
await db.insertInto("people").values({ name: "Ada", score: 10 }).execute(); // joined pads to null
const rows = await db.selectFrom("people").selectAll().execute();
// Array<{ name: string; score: number; joined: Date | null }>

There's a builder per column type — column.boolean(), column.number(), column.string(), column.datetime(), and column.enum([...]) — and six modifiers:

• .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) / .default(fn) — fills omitted (or null) slots at insert time. A plain value persists in the catalog and fills inside the engine, so every write path gets it — raw batches, SQL statements, other tabs (datetime columns take only "now", which stamps one consistent timestamp per batch). A function is a userland generator — () => ulid(), a custom nanoid, anything: the typed facade (insertInto, typedTable) calls it just before the batch is sent, so it never persists, never crosses the worker boundary, and write paths that skip the facade don't see it. Defaults require non-nullable columns, and returning echoes the written values either way.
• .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.

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"),
});
// Selects return "open" | "closed" | "reopened"; inserts and updates accept nothing else.
await db.insertInto("tickets").values({ status: "open" }).execute();
await db.insertInto("tickets").values({ status: "lost" }).execute(); // 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).

const notes = table("notes", {
  id: column.number().unique().autoIncrement(),
  slug: column.string().default(() => nanoid()), // any userland generator
  status: column.string().default("draft"),
  created: column.datetime().default("now"),
  body: column.string(),
});
// Insert types make generated columns optional:
await db.insertInto("notes").values({ body: "hello" }).returningAll().executeTakeFirstOrThrow();
// { id: 1, slug: "V1StGXR8_Z5jdHi6B-myT", status: "draft", created: Date, body: "hello" }

InferDatabase carries the "engine can fill this" fact into insert types automatically. If you hand-write your DB interface instead, mark those columns with Generated<T> and wrap the row in FromRow, which reads the marker once — at your declaration — and produces the three shapes:

import { type FromRow } from "@minnowdb/client";

interface DB {
  notes: FromRow<{ id: Generated<number>; slug: Generated<string>; body: string }>;
}

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 declared relation is enforced, not decorative. The same is true of checks, the third argument to table():

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

const children = table(
  "children",
  {
    id: column.number().unique(),
    parent_id: column.number().references("parents", "id", { onDelete: "cascade" }),
    qty: column.number(),
  },
  { 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 created — so an expression the engine cannot evaluate fails at migration time rather than on the first write.

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.

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. They join DB like tables, so selectFrom works and the row type is what you declared — but they carry no insert shape, which makes a write a compile error:

await db.selectFrom("active_customers").select(["name"]).execute(); // fine
await db.insertInto("active_customers"); // compile error: not a writable table

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 atomic, and the whole run is idempotent — an interrupted migration completes by re-running.
• Concurrent migrators can't interleave — the loser fails with a typed conflict.
• 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.

Proven rather than assumed

Two changes are earned rather than declared. Both read block headers only — the same checksum-authenticated statistics that drive data skipping — so they cost one header read per block, with nothing decompressed or decoded:

• 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.

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 — the unique key, a FOREIGN KEY, or a CHECK — because dropping it would leave that constraint 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

Both need the stored bytes rewritten, which is compaction's job, not a migration's.

• adding, changing, or dropping a FOREIGN KEY or CHECK on an existing table

That last one is the same rule as the rest: existing rows are not known to satisfy a constraint nobody has verified them against, 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 shapes

InferDatabase<typeof appSchema> maps each name in your schema to its shapes. A table contributes three; a view contributes one.

type DB = {
  people: { select: {...}; insert: {...}; update: {...} };
  active_people: { select: {...} }; // a view: readable only
};

Naming the three explicitly is what makes DB readable by code that did not build it — including your own tooling — instead of requiring it to decode a marker. It is also what makes a write to a view a compile error: a view has no insert.

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.