Diagnosing Prisma Migrate Migration Conflicts in PostgreSQL

Recognizable condition

When you run prisma migrate dev or prisma migrate deploy the command fails with an error similar to:

Migration engine encountered an error: SQL error: duplicate key value violates unique constraint "_prisma_migrations_pkey"

or a generic P2025 error indicating that a migration record is missing or out of order. The useful takeaway is that the migration history stored in the _prisma_migrations table no longer matches the state expected by your Prisma schema, and you need to reconcile the two before proceeding.

Cause / diagnostic table

Possible cause	What to look for
Manual schema changes outside Prisma Migrate	Tables, columns, or indexes added/removed directly in PostgreSQL after the last successful migration.
Stale or corrupted migration history	Missing rows, duplicate names, or out‑of‑order applied_at timestamps in _prisma_migrations.
Failed migration left partial objects	Some objects created by a migration that errored mid‑run remain in the database.
Concurrent migrations on multiple environments	Two CI agents or developers ran migrations at the same time, causing interleaved entries.

Ordered checks

1. Pull the current database schema – run prisma db pull in your project root (you need read access to the database via the DATABASE_URL in .env). This generates a temporary Prisma schema reflecting what is actually in PostgreSQL.
2. Inspect the migration table – connect to PostgreSQL (e.g., with psql $DATABASE_URL) and run:

   SELECT * FROM _prisma_migrations ORDER BY applied_at;

   Look for gaps in the name column, duplicate entries, or missing expected migrations.
3. Compare schemas – diff the output of prisma db pull against your schema.prisma. Any differences indicate manual DDL or a failed migration.
4. Check for leftover objects – if a migration failed, look for tables, columns, or indexes that were partially created (often named with the migration’s prefix). You can list them with:

   SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'migration_%';

   Replace the pattern as needed.

Fixes tied to findings

If manual changes are detected

• Option A: Revert the manual DDL to match schema.prisma, then rerun prisma migrate dev.
• Option B: Import the changes into Prisma by running prisma db pull, reviewing the pulled schema, copying any wanted alterations into schema.prisma, and then running prisma migrate dev --create-only to generate a new migration that captures those changes.

If the migration table is corrupted

• Back up your data (e.g., pg_dump $DATABASE_URL > backup.sql).
• Truncate the history table: TRUNCATE TABLE _prisma_migrations; (requires write access to the database).
• Re‑apply migrations from scratch in a safe environment: prisma migrate reset (this will drop and recreate all tables – only use on a copy or dev database).
• After reset, run prisma migrate dev to replay migrations and verify they apply cleanly.

If a failed migration left partial objects

• Manually drop the incomplete objects (e.g., DROP TABLE IF EXISTS incomplete_table;).
• Remove the corresponding row from _prisma_migrations if it was recorded as applied.
• Run prisma migrate dev again to re‑create the migration cleanly.

If concurrency caused the conflict

• Serialize migration runs: add a file‑based lock or use CI gating (e.g., only allow the main branch to run migrations).
• Ensure each environment has its own database; never point multiple pipelines to the same PostgreSQL instance for migration.
• After resolving the conflict, add a preventive step in your CI config to run prisma migrate deploy only after acquiring a lock.

Escalation criteria

• If after attempting the above steps the error persists and you cannot identify the source of mismatch.
• If the database contains production data and a reset would cause unacceptable data loss.
• If you need to preserve manual SQL changes that cannot be expressed in Prisma schema.

In these cases, open a Prisma GitHub issue with:

• The full error log from prisma migrate dev.
• A snapshot of your schema.prisma.
• The output of prisma db pull (the current database schema).
• The contents of _prisma_migrations (you can export with SELECT * FROM _prisma_migrations;).

As a temporary workaround you can run prisma migrate deploy --skip-seed and apply the missing DDL manually via a SQL script, then re‑sync the migration table with prisma migrate resolve --applied <migration-name>.

Verification

1. Run prisma migrate dev again and confirm it finishes without error.
2. Start Prisma Studio (prisma studio) and verify that the expected tables, columns, and relations are present.
3. Check the migration table: SELECT name FROM _prisma_migrations ORDER BY applied_at; should show a continuous, increasing sequence with no gaps or duplicates.
4. Optionally, run prisma db pull once more and diff the generated schema against schema.prisma; they should match exactly.