With Prisma 7, after someone added a production column by hand, why does `prisma migrate deploy` fail with P3018 and then keep reporting P3009, and how do you recover?
answer
- deploy never looks for drift
- a failed row blocks the queue
- migrate status names the way out
- resolve --applied versus --rolled-back
basics
~20 sThe hand-made column was drift that migrate deploy never checks for, so the migration adding it failed (P3018) and was recorded as failed, which blocks all later deploys (P3009). Mark it with prisma migrate resolve, applied or rolled back, then redeploy.
solid answer
~50 sThe hotfix put production out of line with the migration history, and `prisma migrate deploy` never checks for that: it only compares migration folders with `_prisma_migrations`. When a later migration tried to add the same column, the database rejected it, deploy reported `P3018` and recorded the migration as failed, with the error in its `logs` column. From then on every deploy stops with `P3009`, because Prisma will not apply new migrations past a failed one. Recovery starts with `prisma migrate status`, which names the failed migration. If the hotfix already did everything that migration does, run `prisma migrate resolve --applied <name>`, which records it as applied without running its SQL. If not, finish or revert its steps by hand, then use `--applied` or `--rolled-back` and redeploy. Next time, capture the hotfix as a migration and resolve it as applied before deploying.
code
bash · 6 linesnpx prisma migrate status
# The hand-made column already matches the migration exactly:
npx prisma migrate resolve --applied 20261008093000_add_discount_code
npx prisma migrate deploygo deeper
Know that a failed Prisma migration blocks every later deploy until someone marks it with prisma migrate resolve.
Explain why deploy missed the hand-made column, what P3018 and P3009 each mean, and the difference between resolve --applied and --rolled-back.
Show the diagnosis order (migrate status, compare the SQL with production, decide applied or rolled back) and the hotfix workflow that would have prevented the failure.
Weigh adding a pre-deploy drift check with migrate diff and a shadow database against the cost of a blocked release queue, and decide who may touch production schemas by hand.
## What actually happened 1. During an incident someone ran `ALTER TABLE ... ADD COLUMN` directly on production. The database now has **drift**: it no longer matches what the migration history produces. 2. `prisma migrate deploy` never checks for drift. It compares the migration folders with the rows in `_prisma_migrations` and runs whatever is pending, so the hotfix stayed invisible. 3. Later a developer added the same field to the Prisma schema, and `prisma migrate dev` generated a migration that adds the column. On their database it worked, because the column did not exist there. 4. On production the database rejected the `ADD COLUMN`. Deploy failed with `P3018` ("A migration failed to apply. New migrations cannot be applied before the error is recovered from"), printed the migration name and the database error, and recorded the migration as **failed**, with the error in the row's `logs` column. 5. Every later deploy stops with `P3009`: "migrate found failed migrations in the target database, new migrations will not be applied". Nothing ships until that row is resolved. ## Diagnose before touching anything - Run `prisma migrate status` against production. It lists the failed migration and prints both ways out: `prisma migrate resolve --rolled-back` if you reverted it, `--applied` if you completed it by hand. - Read the error deploy printed: `P3018` includes the migration name and the database's own error code and message, and the failed row's `logs` column keeps the same text for later. - Read the failed migration's SQL and compare it with what production actually has. Does the hotfix match it exactly (type, nullability, default, indexes)? Did any statements before the failing one take effect? Depending on the statements and the database, earlier steps may already be in place. ## The recovery paths | Situation | Steps | What Prisma records | |---|---|---| | The hotfix already did everything the migration does | `prisma migrate resolve --applied <name>` | the failed row is marked rolled back and a new row marks the migration applied; its SQL is not run | | The migration does more, or the hotfix differs | finish the remaining steps by hand so production matches the migration, then `resolve --applied` | as above | | You want the migration to run again | revert what partly ran, adjust the migration so it can succeed and commit it, `resolve --rolled-back`, then `prisma migrate deploy` | the failed row is marked rolled back and deploy applies the migration afresh | Two guard rails: `--applied` on a migration that is already recorded as applied fails with `P3008`, and `--rolled-back` on one that is not in a failed state fails with `P3012`. Do the same on every other database that received the hotfix, such as a staging copy patched during the same incident. Databases without the hotfix need nothing: there the migration simply runs. And resist editing `_prisma_migrations` by hand; `migrate resolve` exists so that the record stays consistent and the failed attempt stays visible as an audit trail. To have Prisma write the SQL that brings production to the schema's state, use `prisma migrate diff --from-config-datasource --to-schema prisma/schema.prisma --script > forward.sql`, run it with `prisma db execute --file forward.sql`, and finish with `resolve --applied`. In Prisma 7 both commands take the connection from the Prisma config file, because `--from-url` and `db execute --url` were removed; a separate config file for production, passed with `--config`, keeps that explicit. ## What should have happened The hotfix workflow keeps history and database in step: 1. Reproduce the hotfix in the Prisma schema. 2. Run `prisma migrate dev --name retroactively_add_discount_code` to generate the matching migration. 3. On **every** database that received the hotfix, run `prisma migrate resolve --applied <that migration>` before the next deploy. 4. Commit the migration and let the pipeline apply it to the databases that were not patched. To catch drift before a deploy rather than during it, a pipeline step can run `prisma migrate diff --from-migrations prisma/migrations --to-config-datasource --exit-code`. Exit code 2 means the database differs from what the history produces. Diffing from a migrations directory needs a shadow database, so this step needs one configured. ## Why the error arrived so late - `migrate deploy` trusts `_prisma_migrations` and never inspects the live schema. - `migrate dev` does detect drift, but only on the developer's database, where there was none. - The first point where history and production met was the failing `ALTER TABLE` itself.
- What exactly does `prisma migrate resolve --applied` do to a migration recorded as failed?It marks the failed row as rolled back, keeping it as an audit trail, and inserts a new row recording the migration as applied. None of the migration's SQL runs, so you must be sure the database already matches what the file would have done.
- Why might the failure only appear on production, even though staging runs the same migrations?Deploy behaves identically everywhere; the databases differ. If the hotfix was applied only to production, staging had no column, so the migration succeeded there. That is why the hotfix workflow resolves the migration as applied on every patched database and nowhere else.
- migrate deploy would have refused the release because of drift
- P3009 clears itself on the next successful deploy
- Run prisma migrate dev on production to reconcile the history
- resolve --rolled-back undoes the migration's SQL on the database
- Delete the failed row from _prisma_migrations by hand
Assumes Prisma 7, where migrate diff reads production through --from-config-datasource and db execute lost --url; the resolve flow itself predates 7.