Migrations

One ordered registry per database, contributed by every capability composed into a Worker, run by pithy migrate against one environment at a time.

They are Kysely migrations, not SQL files

The data layer is where the schemas come from, pithy migrate is what runs them, and writing one is the walkthrough.

TypeScript up and down, with a down that is tested.

Not raw .sql files, and — worth saying plainly — not wrangler’s D1 migrations. The kit runs its own, because a migration set assembled from several packages plus your app is not something a per-directory file convention can express.

export const app_0001_init: Migration = {
  up: async (db) => {
    await db.schema.createTable("notes")
      .addColumn("id", "text", (c) => c.primaryKey())
      .addColumn("createdAt", "integer", (c) => c.notNull())
      .execute();
  },
  down: async (db) => {
    await db.schema.dropTable("notes").ifExists().execute();
  },
};

Identifiers are camelCase and the runner emits snake_case SQL — the plugin that does that is mandatory on every Kysely instance the kit builds, so your query code never types an underscore.

Every drop is ifExists(). D1 has no transactional DDL, and the ledger row is deleted only after down 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 down is the only way out, and a bare drop would throw on its first statement forever.

migrationOrder sorts a whole database’s registry

Each capability declares one number per database. The composed key is that number plus the capability’s own namespace:

0300_auth_0001_init
2000_app_0001_init

Two properties, both non-negotiable.

Unique within its database. A colliding pair throws at migrate time for any project composing both.

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.

Order across capabilities is otherwise arbitrary, because no capability’s tables reference another’s. The single foreign key in the kit is internal to one capability.

For your own app, pick a high number — 2000 is what the Pithy dashboard uses — so a capability you add next year still lands before your tables rather than after them. The ceiling is 9999 per database.

The database has an owner

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

An unstamped database is adopted on first migrate. Your own is a no-op.

That is why migrate needs name in the root config 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.

Every command that can write to a database passes the same claim, not just migrate: adding a capability, dropping one, upgrading with migrations, reseeding, and each feature step. They share one code path, and it refuses to run at all without a project name.

The ledger has to match the declaration, in both directions

Before anything is written, every database is read back.

A migration you declare that the database has not applied is what the run applies. Ordinary.

A migration the database has applied that nothing declares any more is refused by name. That is what deleting a migration file leaves behind, and it reads as a corrupted chain — so nothing can migrate until the two agree.

No migration is broken there, so the remedy is not fix the migration. On dev it is to delete the local state and run again. On a deployed environment there are real rows, so it is to restore the migration or remove its ledger row deliberately. pithy doctor reports the same state before you reach for migrate.

A pending count is blind to that second direction, which is why doctor asks the question both ways.

One driver per environment, one registry everywhere

The registry, the ordering and the per-database runs are identical in every environment. Only the driver differs: dev goes through Miniflare against the same local state wrangler dev reads, and a deployed environment executes over the D1 REST API against the database that stanza names.

You pass no ids.

A shared database migrates once

Workers whose bindings resolve to the same physical D1 are grouped, their sets merged into one ordered provider, 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. It never narrows the registry a visited database runs, because a shared ledger holds both Workers’ migrations and a partial provider reads as corrupted state.

Idempotent, and honest when it dies

A second run with nothing pending prints Nothing to migrate.

A fan-out has no transaction across databases: the third one throws and the first two are already ahead of it. So a failed run still writes to stdout what it changed on the way, marked as a truncated report, naming the database it died on and every database it never opened. That record is worth more on the run that failed than on the one that worked.

Within a single migration there is a transaction: its statements go to D1 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.

Rolling 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 — every migration’s up and down are exercised.

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 the corrupted-chain state above. pithy remove --drop does the two in the right order for you.

Deploy never migrates

pithy deploy warns when the target environment’s schema is behind and never runs anything. Promote first, then ship:

pithy migrate --env prod --json
pithy deploy --env prod --json

Two gated steps rather than one convenient one, because the failure modes are different and the remedies are different.