# Schema & migrations

> Define tables once in TypeScript, and evolve them without rewriting stored data.

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](/docs/reference/extending.md) builds on — so it works with or without
[the query builder](/docs/client.md). The examples below use the builder where it makes the types
easier to see.

## Defining a schema

```ts
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:

```ts
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).

```ts
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:

```ts
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()`:

```ts
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:

```sql
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:

```ts
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:

```ts
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:

```ts
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:

```ts
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:

| Step                         | What it does                                                               |
| ---------------------------- | -------------------------------------------------------------------------- |
| create table                 | Including its constraints, and after any table it references.              |
| add column                   | Nullable, or non-nullable [with a backfill](#backfilling-an-added-column). |
| rename column                | Through the column's stable ID, so it is not a drop plus an add.           |
| widen nullability            | NOT NULL to NULL.                                                          |
| tighten nullability          | NULL to NOT NULL, [proven first](#proven-rather-than-assumed).             |
| widen an enum                | Add values, or drop the restriction to a plain string.                     |
| alter a default              | Defaults are write-time only, so changing one never touches stored rows.   |
| adopt or drop auto-increment | [Proven first](#proven-rather-than-assumed) when adopting.                 |
| replace a view               | Nothing is stored under a view, so a body change needs no proof.           |
| drop a column or table       | Only [with your say-so](#dropping-things).                                 |

- **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](/docs/storage/maintenance.md#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:

```ts
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:

```ts
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](#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.

```ts
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`.

| Type                 | Meaning                                             |
| -------------------- | --------------------------------------------------- |
| `InferRow`           | The select shape; nullable columns are `\| null`.   |
| `InferInsertRow`     | The insert shape; nullable columns may be omitted.  |
| `InferUpdateChanges` | The partial-update shape accepted by keyed updates. |
| `SelectRowOf<S>`     | Pulls the select row back out of a `DB` entry.      |
| `InsertRowOf<S>`     | Likewise for inserts.                               |
| `UpdateRowOf<S>`     | Likewise for updates.                               |
| `WritableTable<DB>`  | The names that accept writes — views excluded.      |

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](/docs/reference/extending.md#introspecting-the-catalog) — useful for previewing
what a migration would do, or for building schema tooling of your own.

> Raw `createTable` (see [Writes](/docs/sql/dml.md)) remains available when compile-time types aren't
> needed.

---

Minnow 0.1.1 · this page on the site: /docs/schema/