Prisma Migration Strategies

Prisma’s migration engine is split into two commands that behave nothing alike, and confusing them is the most common way teams corrupt a production migration history. prisma migrate dev is an authoring tool: it diffs your schema.prisma against a throwaway shadow database, generates SQL, applies it, and can reset your local database without asking. prisma migrate deploy is a delivery tool: it applies already-committed migration files in order, never generates anything, never touches a shadow database, and never resets. This guide is the operational runbook for running both correctly — for the backend engineers who author migrations, the DevOps engineers who wire migrate deploy into a release, and the on-call who has to read _prisma_migrations at 3 a.m. when a deploy half-applies.

Everything here assumes the migration is the deploy step that runs before the new application image, the discipline the broader ORM & Framework Migration Workflows guide establishes for every framework. Two failure surfaces are common enough to have their own runbooks: the shadow database that Prisma cannot create on managed Postgres, covered in fixing Prisma shadow database failures, and the P2024 pool timeouts that strike when a migration and a rolling fleet contend for the same connections, covered in resolving Prisma connection pool timeouts.

Concept & Mechanism

Prisma keeps a desired state (schema.prisma) and a history (the timestamped SQL files in prisma/migrations/). The job of prisma migrate dev is to make those two agree. It does so by replaying every existing migration onto a shadow database — a temporary, structurally identical database that Prisma creates, migrates, drops, and recreates on every invocation — to compute the current state of the history, then diffing that against schema.prisma. The difference becomes a new migration file. The shadow database exists precisely so Prisma never has to introspect your real development database to figure out what the next migration should be, which is why it needs CREATE DATABASE rights and why it is the first thing to break on managed Postgres; that breakage has its own runbook in fixing Prisma shadow database failures.

prisma migrate deploy does none of that. It reads the committed migration files, compares them against the rows in the _prisma_migrations ledger table on the target database, and applies — in filename order — every migration that has not yet been recorded. It never creates a shadow database, never generates SQL, never prompts, and never resets. On PostgreSQL each migration’s statements run inside a single transaction, so a mid-migration failure rolls the whole file back cleanly. On MySQL 8.0 there is no transactional DDL — each ALTER TABLE implicitly commits — so a failed deploy can leave a migration half-applied, a difference the Transactional vs Non-Transactional Databases guide treats in depth.

Three lower-level tools back these commands. prisma migrate diff is the pure diff engine: given any two states (a live URL, the datamodel, a migrations directory, or an empty schema), it prints the SQL — or, with --exit-code, exits non-zero when they differ — which makes it the building block for drift detection in a pipeline. Drift is what Prisma calls the situation where the live database no longer matches what its applied migrations would produce; migrate dev and migrate deploy both detect it and refuse to proceed rather than guess. When drift is intentional or already resolved out of band, prisma migrate resolve rewrites the ledger without touching the schema: --applied <id> marks a never-applied migration as done (used to baseline an existing database), and --rolled-back <id> clears a failed migration’s record so it can be retried. resolve edits only the ledger — it never runs or reverses DDL.

Prerequisites & Decision Criteria

Decide which command you are running before you touch a terminal, because the wrong one on the wrong database is destructive.

• You are on Prisma CLI 5.0 or newer — earlier versions have unstable migrate diff state handling.
• The target is PostgreSQL 13+ or MySQL 8.0+, so information_schema introspection and online DDL behave predictably.
• On any shared, staging, or production database you will run only prisma migrate deploy — never migrate dev, which can reset the database and create new files.
• A shadow database is reachable for local development, either auto-created (local Postgres with CREATE DATABASE rights) or pinned via shadowDatabaseUrl.
• The migration is the deploy step that runs before the new application image, and the new code tolerates the old schema for the duration of the rollout.
• You have an explicit inverse SQL script for this migration — Prisma does not generate down migrations, so rollback is something you author, not something the tool provides.

The decision is mechanical: authoring a schema change locally is migrate dev; shipping committed migrations anywhere else is migrate deploy; checking whether two states agree without changing anything is migrate diff; fixing a ledger that is out of sync with reality is migrate resolve. If you find yourself reaching for migrate dev against a database you cannot afford to lose, stop — that is always the wrong tool.

Step-by-Step Procedure

This is the path from a local schema edit to a verified production deploy.

1. Author the migration locally. Edit schema.prisma, then let migrate dev diff against the shadow database and write the SQL.

# Local workstation only · requires a reachable shadow DB · NEVER run against staging or prod.
# migrate dev may reset the local database; that is expected here and catastrophic anywhere else.
npx prisma migrate dev --name add_user_status --schema=./prisma/schema.prisma

Verify before proceeding: open the generated file under prisma/migrations/<timestamp>_add_user_status/migration.sql and read the SQL by hand. If it contains an unexpected DROP or a column rename rendered as drop-plus-add, fix the schema and regenerate before committing.

2. Confirm the committed history reproduces the schema. Use migrate diff from the migrations directory to the datamodel; an empty diff proves the history is complete.

# CI or local · read-only · exits non-zero if the committed migrations do not reproduce schema.prisma.
npx prisma migrate diff \
  --from-migrations ./prisma/migrations \
  --to-schema-datamodel ./prisma/schema.prisma \
  --exit-code

Verify before proceeding: a zero exit code means the history is self-consistent. A non-zero exit means a migration is missing — do not deploy.

3. Detect drift against the live target. Before applying, diff the production schema against the migrations to confirm nothing was changed by hand.

# CI pre-deploy gate · read-only role is sufficient · non-zero exit means production has drifted.
npx prisma migrate diff \
  --from-url "$PROD_READONLY_URL" \
  --to-migrations ./prisma/migrations \
  --exit-code

Verify before proceeding: a clean exit means production matches the recorded history. Drift here must be reconciled (see Rollback Path) before any deploy.

4. Apply in production with migrate deploy. Run the migration as a discrete, gated step ahead of the application rollout.

# Production deploy step · migration DB role · runs BEFORE the new app image · idempotent (already-applied migrations are skipped).
npx prisma migrate deploy --schema=./prisma/schema.prisma

Verify before proceeding: exit 0 with every pending migration reported applied. Reserve a dedicated connection budget for this step so it does not contend with the live fleet — pool starvation here surfaces as the P2024 timeout covered in resolving Prisma connection pool timeouts.

5. Regenerate the client and roll out the app. Only after the schema is live do you promote code that depends on it.

# Deploy pipeline · no DB access · run after migrate deploy succeeds, before app rollout.
npx prisma generate

Verification & Observability

A migration is not done when deploy exits 0; it is done when the database confirms the expected state and no session is stuck waiting on a lock.

Check the ledger directly to see exactly which migrations are recorded and whether any failed:

-- PostgreSQL · read-only · safe to run any time against the production database.
SELECT migration_name, started_at, finished_at, applied_steps_count, rolled_back_at
FROM "_prisma_migrations"
ORDER BY started_at DESC
LIMIT 10;

A row with finished_at NULL and rolled_back_at NULL is a migration that started and never completed — the classic half-applied state that blocks the next deploy until you resolve it. Confirm the same from Prisma’s own view:

# Any environment · read-only · reports pending and failed migrations from the ledger.
npx prisma migrate status --schema=./prisma/schema.prisma

While a migration is applying, watch for blocking on the engine itself. On PostgreSQL, a migration that appears hung is almost always waiting on an ACCESS EXCLUSIVE lock held by a long query:

-- PostgreSQL · read-only · run in a second session to see what is blocking the migration.
SELECT pid, wait_event_type, wait_event, state, left(query, 80) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- MySQL 8.0 · read-only · shows DDL waiting on metadata or row locks during a migration.
SELECT * FROM performance_schema.data_lock_waits;
SHOW ENGINE INNODB STATUS\G

Rollback Path

Prisma has no automatic down migration, so reversal is a deliberate, authored procedure with two distinct cases.

Case A — the migration failed mid-flight. On PostgreSQL the transaction already rolled the DDL back, so the schema is untouched but the ledger holds a failed row that blocks the next deploy. Clear it, fix the migration, and re-run:

# Production · migration DB role · run only after confirming the schema is back to its pre-migration state.
# resolve edits ONLY the _prisma_migrations ledger — it does not run or reverse any DDL.
npx prisma migrate resolve --rolled-back "<timestamp>_failed_migration"
npx prisma migrate deploy

On MySQL the DDL may have partially applied because each statement implicitly commits; confirm the actual table state with SHOW CREATE TABLE before marking anything rolled back, and hand-revert any partial change first.

Case B — the migration succeeded but the deploy must be reversed. Run your pre-authored inverse script, then redeploy the previous application image. The safe condition is strict: the reversal must be non-destructive — it disables the new path and drops only objects added in this same migration that nothing now reads. Never DROP COLUMN on a column a backfill has populated; expand-and-contract reversal restores the previous code and leaves the expanded schema in place, the contract the Zero-Downtime Schema Evolution Patterns guide builds in detail.

-- PostgreSQL · migration DB role · pre-authored inverse · safe ONLY for objects this migration added and nothing reads.
BEGIN;
DROP INDEX IF EXISTS idx_user_status;
ALTER TABLE "User" DROP COLUMN IF EXISTS "status";
COMMIT;

After a manual reversal, record it so the history stays honest:

# Production · migration DB role · marks the migration rolled back in the ledger after you reversed it by hand.
npx prisma migrate resolve --rolled-back "<timestamp>_add_user_status"

Common Errors & Fixes

• P3014 — Prisma could not create the shadow database. The migration database user lacks CREATE DATABASE, which managed Postgres providers routinely withhold. Fix by provisioning a dedicated empty shadow database and pointing shadowDatabaseUrl at it; the full procedure, including the related P3006, is the subject of fixing Prisma shadow database failures.
• P3009 — migrate found failed migrations in the target database. A previous deploy left a migration with no finished_at. Root cause: a migration crashed or timed out mid-apply. Fix by verifying the real schema state, then prisma migrate resolve --rolled-back <id> (or --applied <id> if it actually completed) before re-running deploy.
• P3018 — a migration failed to apply. A statement in the migration errored against the live data — often a NOT NULL constraint added before the backfill finished, or a unique index over duplicate rows. Fix the data or split the migration into expand and contract steps, mark the failed one rolled back, and redeploy.
• P2024 — timed out fetching a connection from the pool. The migration competes with the running fleet for connections, or a pooler in transaction mode starves the engine. This is common enough on serverless and PgBouncer setups to have its own runbook in resolving Prisma connection pool timeouts.
• Drift detected: “the database schema is not in sync with the migration history.” Someone changed production by hand. Fix by migrate diff from the live URL to the migrations to capture the delta, fold it into a proper migration, and resolve the ledger — never let migrate dev “fix” it by resetting.

Child Page Index

This section drills into the two failure surfaces that most often stall a Prisma deploy. The guide on fixing Prisma shadow database failures covers P3014 and P3006 on managed Postgres — why the shadow database cannot be auto-created without CREATE DATABASE rights, and how to wire shadowDatabaseUrl to a dedicated database with the right grants. The guide on resolving Prisma connection pool timeouts covers P2024 — sizing connection_limit, tuning pool_timeout, surviving PgBouncer transaction mode with pgbouncer=true, and weathering the connection storms a serverless fleet creates during a migration. For the parallel mechanics in another framework, the Drizzle ORM Type Sync guide is the sibling reference.

Frequently Asked Questions

What is the difference between prisma migrate dev and prisma migrate deploy? migrate dev is for authoring on your local machine: it diffs schema.prisma against a shadow database, generates a new migration file, applies it, and may reset the database. migrate deploy is for delivery: it applies already-committed migration files in order, generates nothing, uses no shadow database, and never resets. Run migrate deploy — and only migrate deploy — against staging and production.

Why does Prisma need a shadow database, and can I disable it? The shadow database is how migrate dev replays your history to compute the next migration without introspecting your working database. It is only used by development-time commands, so production migrate deploy never needs one. You cannot turn it off for migrate dev, but on managed Postgres you point shadowDatabaseUrl at a dedicated empty database instead of letting Prisma create one.

A migration failed halfway and now deploy refuses to run — what do I do? Prisma recorded the failed migration in _prisma_migrations (error P3009). First confirm the real schema state: on PostgreSQL the transaction rolled the DDL back, but on MySQL it may be partially applied. Reconcile the schema by hand if needed, then run prisma migrate resolve --rolled-back <id> to clear the ledger row and re-run prisma migrate deploy.

How do I roll back a Prisma migration? Prisma does not generate down migrations, so you author an inverse SQL script for each forward migration and store it beside the migration directory. To reverse, run that script, redeploy the previous application image, then record it with prisma migrate resolve --rolled-back <id>. Keep the reversal non-destructive — disable the new path rather than dropping columns a backfill has filled.