Resolving Prisma Migrate P3009 Failed Migrations

Yesterday’s deploy failed halfway through a migration — a lock timeout, a unique violation during an index build, a network blip. Today every deploy fails before doing anything: Error: P3009 — migrate found failed migrations in the target database, new migrations will not be applied. Prisma Migrate recorded the failure in _prisma_migrations and now refuses to continue until someone decides what that failed migration left behind. The fix is two commands, prisma migrate resolve --rolled-back or --applied, but choosing the wrong one either re-runs statements that already took effect or skips statements that never did. This guide shows how to establish what actually happened in the database, bring it to a consistent state, and resolve the record correctly — without guessing. It is the recovery runbook for Prisma Migration Strategies.

Symptom / Error Signatures

The blocking error on every subsequent deploy:

Error: P3009
migrate found failed migrations in the target database, new migrations will not be applied.
The `20260918101500_add_order_region_index` migration started at 2026-09-18 10:15:03 UTC failed

The original failure, from the deploy that broke, is usually P3018:

Error: P3018
A migration failed to apply. New migrations cannot be applied before the error is recovered from.
Migration name: 20260918101500_add_order_region_index
Database error code: 55P03
Database error: ERROR: canceling statement due to lock timeout

In _prisma_migrations, the row for that migration has finished_at NULL, rolled_back_at NULL, and a non-zero applied_steps_count or a logs column containing the error.

Root Cause Analysis

Prisma records each migration attempt in _prisma_migrations before running it and marks it finished afterwards. A migration whose row is started but neither finished nor rolled back is “failed”, and migrate deploy will not apply anything after it, because the database may be in an intermediate state that later migrations do not expect.

Whether an intermediate state is possible depends on the database and the statements. On PostgreSQL, most DDL is transactional, and when Prisma runs the migration in a transaction a failure rolls everything back cleanly; but statements that cannot run in a transaction (CREATE INDEX CONCURRENTLY, some ALTER TYPE forms on older versions) can leave partial results such as an invalid index. On MySQL, DDL commits statement by statement, so a migration with three statements that failed on the third has applied the first two. The table below summarises what to expect.

Immediate Mitigation

1. Read the failure record. Find the failed migration and its error.

-- PostgreSQL · read-only
SELECT migration_name, started_at, finished_at, rolled_back_at, applied_steps_count, left(logs, 300) AS logs
FROM _prisma_migrations
WHERE finished_at IS NULL AND rolled_back_at IS NULL;

2. Check which of its changes exist. Read the migration’s migration.sql and verify each object. For an index migration:

-- PostgreSQL · read-only · does the index exist, and is it valid?
SELECT c.relname, i.indisvalid FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'Order_region_idx';

3. Make the database consistent. Either undo partial changes (so the migration can run again) or finish them (so it can be marked applied). For a failed concurrent build, undoing is simplest:

-- PostgreSQL · migration role · must run outside a transaction
-- WARNING: drop only the INVALID leftover from the failed attempt.
DROP INDEX CONCURRENTLY IF EXISTS "Order_region_idx";

4. Resolve the record, then redeploy. If the database now contains none of the migration’s changes, mark it rolled back — migrate deploy will run it again. If it contains all of them, mark it applied.

# Shell · migration credentials · edits _prisma_migrations only
# WARNING: the choice must match the actual database state established in steps 2–3.
npx prisma migrate resolve --rolled-back 20260918101500_add_order_region_index
npx prisma migrate deploy
# or, if every change is present:
# npx prisma migrate resolve --applied 20260918101500_add_order_region_index

Before redeploying, fix what caused the failure: a lock timeout means a blocker on the table (see finding the blocking session with pg_blocking_pids); a unique violation means duplicate data to clean up first.

Permanent Fix / Long-Term Pattern

Design migrations so a failure leaves nothing to untangle. Keep each migration to one logical change, and on MySQL to one DDL statement, so its state after a failure is either “all” or “nothing”. Write statements idempotently where the engine allows it — IF NOT EXISTS on indexes and columns, guarded constraint additions — so a migration marked rolled back can simply run again even if some of it took effect. Put non-transactional statements such as concurrent index builds alone in their own migration, as described in customizing Prisma migrations for zero downtime.

Reduce the causes, too. Give migrate deploy a direct connection with a short lock timeout and a retry wrapper that only retries lock errors, per setting lock_timeout and retrying DDL safely, and rehearse migrations against a production-like snapshot to catch unique violations and long builds before production does, as in testing migrations against production-like snapshots. Finally, never edit a migration file after it has been applied or failed anywhere shared: Prisma stores its checksum, and a changed file produces a separate warning that the migration was modified after it was applied.

Verification Checklist

• The failed migration’s intended changes were checked object by object before resolving.
• Partial changes were finished or undone so the database matches the chosen resolution.
• The cause of the original failure (blocker, duplicates, timeout) was fixed before redeploying.
• prisma migrate status reports no failed migrations after the redeploy.
• Migrations are small, single-purpose and idempotent where the engine allows.

Frequently Asked Questions

What is the difference between P3009 and P3018? P3018 is the error raised when a migration fails while being applied. P3009 is raised on later runs, when Prisma finds that failed migration recorded in _prisma_migrations and refuses to apply anything newer until it is resolved.

Does migrate resolve --rolled-back undo database changes? No. It only marks the migration as rolled back in _prisma_migrations, so migrate deploy will try it again. Any partial changes must be undone by hand first, or the rerun may fail on objects that already exist.

When should I use --applied instead? When every change in the migration is present in the database — for example because you finished the remaining statements by hand. Prisma will then treat it as complete and move on to newer migrations.

Can I delete the row from _prisma_migrations instead? It has a similar effect to marking it rolled back, but migrate resolve is the supported way and keeps an audit trail of the failure. Prefer the command.