Migrations

Sadie’s schema is Postgres, defined in packages/db/src/schema.ts, with migrations in packages/db/drizzle/ and a journal at packages/db/drizzle/meta/_journal.json.

Two commands, different semantics

pnpm db:migrate

Replays the migrations in order against DATABASE_URL. This is the production path.

pnpm db:migrate

Under the hood: drizzle-kit migrate reads packages/db/drizzle/*.sql one by one, records each in __drizzle_migrations, and stops cleanly when caught up. Idempotent. Re-running a fully-migrated database is a no-op.

pnpm db:push

Diffs schema.ts against the live database and applies the changes directly. No migration file, no journal entry.

pnpm db:push

This is destructive for renames. Drizzle cannot tell whether you renamed mind_state to soul_state or dropped one and added the other, so it picks the second interpretation and drops data. Use db:push on empty databases only. Hand-craft a migration when renames or data rewrites are involved.

When each is right

Situation	Use
Fresh database, first-ever boot	db:push (fast) or db:migrate (equivalent result, slower).
Production already has data	db:migrate always.
Added a new column with a default	db:push or generate a migration; both safe.
Renamed a table or column	Hand-craft a migration. See below.
Rewrote enum values	Hand-craft a migration with UPDATE statements.

Hand-crafted rename example

packages/db/drizzle/0002_mind_to_soul.sql is a reference for how to do a non-destructive rename. The “Mind” concept became “Soul” across multiple tables, columns, indexes, and enum values. The migration:

• ALTER TABLE ... RENAME TO for the two tables.
• ALTER INDEX ... RENAME for the one index that referenced the old name.
• ALTER TABLE ... RENAME CONSTRAINT for the unique constraint Drizzle had named after the old table.
• ALTER TABLE ... RENAME COLUMN for a foreign-key column.
• UPDATE ... SET ... WHERE ... for every row that carried the old enum value as plain text.

The whole thing is idempotent. Every statement uses IF EXISTS or matches on the old value, so re-running is safe.

A mechanical drizzle-kit generate for the same schema change would have produced a drop-then-create sequence and lost data. The hand-crafted version preserves everything.

Generating mechanical diffs

For safe, add-only changes (new table, new column, new index), let drizzle-kit do the work:

pnpm --filter @repo/db exec drizzle-kit generate

Review the generated SQL in packages/db/drizzle/NNNN_*.sql. Edit if needed. Commit both the SQL and the updated meta/_journal.json.

Journal format

packages/db/drizzle/meta/_journal.json records which migrations exist and their idx. It looks like:

{
  "version": "7",
  "dialect": "postgresql",
  "entries": [
    { "idx": 0, "version": "7", "when": 1776297349222, "tag": "0000_init", "breakpoints": true },
    { "idx": 1, "version": "7", "when": 1776470328188, "tag": "0001_wild_excalibur", "breakpoints": true },
    { "idx": 2, "version": "7", "when": 1776700000000, "tag": "0002_mind_to_soul", "breakpoints": true }
  ]
}

If you hand-craft a migration, append a matching entry here. The tag must match the filename without the .sql extension. breakpoints: true enables --> statement-breakpoint markers so drizzle-kit splits each statement into its own transaction.

Keeping schema.ts and migrations in sync

One invariant to hold: every change to schema.ts lands in a migration. If you edit the schema without generating or writing a migration, db:migrate on a fresh database will not produce the same result as db:push on the dev database, and the two environments drift.

See also: Environment variables for the DATABASE_URL that migrations target.