Applying Schema Migrations

A migration file is a timestamped SQL artifact you commit like code: prisma/migrations/20250304101500_add_order_items/migration.sql. Because every environment replays the same files in the same order, migration files are what make dev, staging, and production converge on the same shape — without them, each database drifts into a private dialect of your schema.

The three commands have non-overlapping jobs:

migrate dev is a development tool: it creates the migration, applies it, may drop and rebuild your local schema when it detects hand-edited drift, and regenerates the client. migrate deploy is a deployment tool: it reads _prisma_migrations, applies anything pending, and stops. It never resets, never regenerates, never prompts — the properties you need when a running system is watching.

Production runs before traffic does:

# GitHub Actions — migrate, then promote the new build
- name: Apply migrations
  run: npx prisma migrate deploy
  env:
    DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }}
- name: Deploy app
  run: ./deploy.sh

Running it in CI rather than at container boot keeps migrations single-writer: twenty instances racing to apply the same DDL is a deadlock you introduce deliberately. A failure lands in _prisma_migrations with P3009: migrate found failed migrations in the migration database; fix the SQL, then record the decision with prisma migrate resolve --rolled-back 20250304101500_add_order_items.

An existing, unmanaged database needs a baseline — tell Prisma which history it has already "run" without running it:

npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script \
  > prisma/migrations/0_init/migration.sql
npx prisma migrate deploy
npx prisma migrate resolve --applied 0_init

Otherwise the first deploy tries to CREATE TABLE "User" and fails with a duplicate-object error.

The sequencing rule that governs all of this: migrations must be backward compatible with the code that is currently live. Deploys are not atomic — old instances keep serving while the new schema lands — so a migration may add, but the old code must not notice. Keep every query from Querying with Prisma Client working against both the pre- and post-migration schema, and the habit starts here: review migration SQL in the pull request with the same skepticism as application code.

What _prisma_migrations actually stores is worth knowing when you debug a stuck pipeline: one row per migration with its started_at, finished_at, logs, and rolled_back_at. finished_at null means it is running or died mid-flight — that is the row P3009 points at. The table is also the contract for "applied exactly once": two concurrent migrate deploy runs race to insert the row, and the loser stops, which is why one writer in CI beats any amount of locking discipline elsewhere.

Seed data follows the same single-source rule. Declare it once:

{ "prisma": { "seed": "tsx prisma/seed.ts" } }   // in package.json

npx prisma db seed          # after migrate deploy / migrate reset

Write seeds to be idempotent (upsert, or deleteMany then create) so re-running them in staging never multiplies rows. One migration per pull request keeps the replay order obvious and the blast radius of a bad file small.

The instinctive change is one deploy: rename User.name to User.fullName, update the model, ship. During the rollout window — new database shape, old code still serving — every request from an old instance fails with:

PrismaClientKnownRequestError:
Invalid `prisma.user.findMany()` invocation
P2022: Column "name" does not exist on table "User"

Thirty seconds of 500s on your profile page, longer if the rollout stalls. The pattern that avoids it is expand and contract, split across at least two releases:

1. Expand — add the new column alongside the old one. fullName String? is nullable, so the ALTER TABLE ... ADD COLUMN is metadata-only in Postgres 11+ and instant even on large tables. Ship code that writes both columns and reads the new one with a fallback to the old.

2. Migrate data — backfill in batches so you never hold a lock for minutes:

   UPDATE "User" SET "fullName" = "name"
   WHERE "fullName" IS NULL AND id IN (SELECT id FROM "User" WHERE "fullName" IS NULL LIMIT 1000);
   -- repeat until 0 rows; verify counts before proceeding
   

3. Switch reads — release the code that reads fullName only, and assert the backfill is complete (a count check in a script, not a hope).

4. Contract — a later release drops name and removes it from the schema. Only now is the change destructive, and only now is old code gone.

Locking is why this is not ceremony. ALTER TABLE "User" ADD COLUMN ... NOT NULL without a default rewrites the table under an ACCESS EXCLUSIVE lock, which queues behind every open query and lets a two-minute migration become a ten-minute outage. CREATE INDEX takes a lock that blocks writes for its full build. Prisma runs each migration's SQL inside a transaction, and CREATE INDEX CONCURRENTLY cannot run in a transaction — so on a large table, index creation is a deliberate, monitored step outside the normal migration flow rather than a line you paste into migration.sql.

Destructive changes deserve the explicit warning Prisma gives you: P3001: You are about to drop the column "name" on the table "User". All data in it will be lost. In development migrate dev asks for confirmation (or --accept-data-loss); treat the same message in any shared environment as a signal that the contract phase is scheduled, reviewed, and backed up. The queries you wrote against those columns — the selects and includes from Querying with Prisma Client — are exactly what break the moment the column disappears, so the drop ships only after no released code names it.

Constraints get the same phased treatment. Adding NOT NULL to a column with existing rows requires a full-table verification under a lock; Postgres offers a lock-friendly two-step:

ALTER TABLE "User" ADD COLUMN "fullName" TEXT;              -- instant
-- ...batched backfill...
ALTER TABLE "User" ALTER COLUMN "fullName" SET NOT NULL;     -- scans; do it off-peak

ALTER TABLE "User" ADD CONSTRAINT email_format CHECK (email ~ '@')
  NOT VALID;                                                 -- takes the lock, checks no rows
ALTER TABLE "User" VALIDATE CONSTRAINT email_format;         -- verifies existing rows under a
                                                             -- weaker lock that still allows writes

NOT VALID splits "apply the rule to future writes" from "audit history", which is exactly the expand-then-verify shape of every risky change. Every one of these statements belongs in a migration file that someone reviewed — the pattern only works if the whole sequence is versioned.

Ask how you'd roll back a bad migration and the honest answer starts with: Prisma migrations are forward-only. migrate deploy has no down direction, no migrate rollback. Reversing means writing a new migration that undoes the old one — plus a restore if data was destroyed. Interviews reward candidates who say this plainly instead of describing a tool that does not exist.

Three failure classes, three different responses:

The middle row is the one that matters in practice, and it dictates policy: keep the schema compatible with both the previous and the current release until you are confident — nullable new columns, no dropped columns, dual reads. Then a code rollback is seconds (deploy.sh re-running the old image) while the database stays exactly where it is. The rollback unit is the deploy, not the migration.

Classify every change before you write it:

Data-loss recovery depends on the platform, not on you: Neon, Supabase, and RDS all keep point-in-time history measured in minutes to days, so the drill is "restore to a scratch database, copy the rows back" — worth having run once before an incident, not during one.

A recovery script that also guards against a repeat:

# after fixing a failed migration
npx prisma migrate resolve --rolled-back 20250304101500_add_order_items
npx prisma migrate deploy          # re-applies the corrected SQL
npx prisma migrate status          # confirms: "Database schema is up to date!"

migrate status in a deploy pipeline answers the question your on-call actually asks — what is the shape of production right now? — before anyone SSHes anywhere. And the transport question comes next: none of these guarantees hold when the process applying them is a short-lived function in HTTP Drivers at the Edge.

Because there is no automatic down direction, author the reversal deliberately. Revert schema.prisma to the previous shape and let Prisma write the undo SQL for you:

npx prisma migrate diff \
  --from-migrations prisma/migrations \
  --to-schema-datamodel prisma/schema.prisma \
  --shadow-database-url $SHADOW_DATABASE_URL --script

Save that as migrations/<ts>_revert_add_order_items/migration.sql and your "rollback" is a normal forward migration — reviewable, recorded, and replayable in every environment, including staging, where you will actually test it.

A pre-deploy checklist keeps the reasoning honest:

• Additive only? If not, is the old code proven compatible until it is gone?
• Backfill batched and counted? How long does the slowest statement hold its lock?
• Does someone know the point-in-time recovery window before they need it?
• Is there an owner for the revert decision, not just for the deploy?