Schema migrations

You need: a project with a database.

Forward

Migrations is what one is, pithy migrate is what runs it, and the data layer is where the schema it changes is defined.

pithy migrate --env dev
pithy migrate --env staging
pithy migrate --env prod

Same registry, same ordering, same per-database runs. Only the driver differs: dev goes through the local emulator against the same state your dev server reads; a deployed environment executes over the REST API against the database that stanza names.

You pass no ids.

Every run is idempotent — a second one with nothing pending prints Nothing to migrate.

Back

pithy migrate --env staging --rollback

One step. That is what the tested down is for, and testing it is the kit’s own standard rather than a suggestion.

Test the rollback before you need it. It is the one piece of code you reach for under pressure and have never run.

Writing the migration

export const app_0002_add_tags: Migration = {
  up: async (db) => {
    await db.schema.alterTable("notes").addColumn("tags", "text").execute();
  },
  down: async (db) => {
    await db.schema.alterTable("notes").dropColumn("tags").execute();
  },
};

Identifiers in camelCase; the runner emits snake_case.

Every drop is guarded with an existence check. D1 has no transactional DDL, and the ledger row is deleted only after the rollback resolves — so a down that dies halfway leaves what it already dropped gone and the migration still recorded as applied. Nothing is pending, so re-running is the only way out, and an unguarded drop would throw on its first statement forever.

sequenceDiagram
    actor You
    participant CLI as The runner
    participant DB as Your database
    participant L as The ledger
    You->>CLI: Roll 0003 back
    CLI->>DB: First drop
    DB-->>CLI: Gone, and not undoable
    CLI->>DB: Second drop
    Note over CLI,DB: The run dies here
    CLI--xL: The row is deleted only once the<br/>rollback resolves, so it stays
    Note over DB,L: Half dropped, and still recorded as applied.<br/>Nothing is pending, so re-running is the way out.
    You->>CLI: Roll 0003 back, again
    CLI->>DB: First drop
    DB-->>CLI: The guard makes it a no-op
    Note over CLI,DB: Unguarded, this throws on its<br/>first statement forever
    CLI->>DB: Second drop
    CLI->>L: Now the row goes

Adding a column to a table with rows

SQLite refuses a non-null column with no constant default on a table that has rows. So a column added later is nullable, whatever you would prefer.

The kit hits this itself — a plugin’s added column is nullable regardless of what the plugin declares — and the resolution is the same one you want: the write path enforces the constraint, since it is the thing that knows.

Order is stable forever

migrationOrder sorts your migrations against every capability’s within one database.

Unique within its database, and stable forever: renumbering renames the composed keys, and the migrator then reads applied migrations as unapplied and runs them again — against a database that already has those tables.

Pick a high number for your own app so a capability added next year still lands before your tables. The ceiling is 9999 per database.

Once something has shipped, the chain is append-only

While nothing is deployed, editing the initial migration is fine — no database holds a row a second migration would have to carry across.

The day a version is cut, that inverts. A migration that has run somewhere real is history and is never edited.

The kit’s own rule is worth copying, including the part people skip: re-check the condition rather than assuming it. Is there a deployed database holding a row is a question you answer by looking at the account the project actually pins — not whatever your tool happens to be logged in to.

The corrupted-chain state, and how to get out

A migration the database has applied that nothing declares any more is refused by name.

sequenceDiagram
    actor You
    participant CLI as pithy migrate
    participant L as The ledger
    participant DB as Your database
    You->>CLI: pithy migrate
    CLI->>DB: 0003 runs
    CLI->>L: 0003 recorded as applied
    You->>You: Delete the 0003 file
    Note over You,L: The file is gone. The row is not.
    You->>CLI: pithy migrate
    CLI->>CLI: Read what the project declares
    CLI->>L: Read what the database has applied
    L-->>CLI: 0003
    CLI--xYou: Refused, naming 0003
    Note over CLI: No migration is broken, so the<br/>remedy is not "fix the migration"

That is what deleting a migration file leaves behind. No migration is broken, so the remedy is not fix the migration:

On dev, delete the local state and run again.

On a deployed environment there are real rows, so restore the migration or remove its ledger row deliberately.

pithy doctor reports the same state before you reach for migrate, and it asks the question in both directions — a pending count is blind to this one.

Roll back before you remove a capability, not after. Its migration disappears from the set while its row is still in the ledger, which is exactly this state. pithy remove --drop does the two in the right order.

The database has an owner

Every database in a run is claimed before any of them is written to — a row beside the ledger — and a database another project owns aborts the whole run rather than being discovered halfway through.

That is why migrate needs your project name and refuses to guess one: a guessed name would stamp one value and check a different one next run, locking a project out of its own database.

Nothing clears the stamp. Handing a database to another project deliberately means dropping that table by hand.

A shared database migrates once

Workers whose bindings resolve to the same physical database are grouped, their sets merged, and that provider runs a single time — then each result is credited back to the Worker whose capability declared it.

--worker narrows what is reported, never what a visited database runs: a shared ledger holds both Workers’ migrations, and a partial provider reads as corrupted state.

When a run dies partway

A fan-out has no transaction across databases. The third one throws and the first two are already ahead of it.

sequenceDiagram
    participant CLI as pithy migrate
    participant A as Database one
    participant B as Database two
    participant C as Database three
    participant D as Database four
    CLI->>A: Its migrations run
    A-->>CLI: Applied
    CLI->>B: Its migrations run
    B-->>CLI: Applied
    CLI->>C: Its migrations run
    C--xCLI: Throws
    Note over A,B: Already ahead, and staying there
    Note over D: Never opened
    Note over CLI,D: stderr carries the failure and the exit is non-zero.<br/>stdout still carries a truncated report: what changed,<br/>the database it died on, and every one it never opened.

So the failure goes to stderr, the exit is non-zero, and stdout still carries what the run changed — marked as a truncated report, naming the database it died on and every one it never opened.

Within a single migration there is a transaction: its statements go as one batch, so a migration that fails partway applies none of itself and records nothing. Nothing is ever batched across a migration boundary, because a partial chain has to stay representable in the ledger.

Deploy never migrates

It warns when the schema is behind and ships anyway. Promote first, then ship.

Check it worked

  • Forward, then back, then forward again on dev
  • The rollback actually reverses it
  • A second run prints Nothing to migrate.
  • pithy doctor reports no undeclared migrations
  • The order number is unique within its database
ESC