How to Fix Prisma Schema Drift Without Losing Production Data

Prisma schema drift means the database's actual schema no longer matches the end state described by the migration history. Fixing it safely is a reconciliation exercise: preserve the data, identify whether schema state or migration history is wrong, rehearse a recovery on a restored copy, and deploy a reviewed forward change.

Do not start with prisma migrate reset. It is a development command that drops the PostgreSQL schema and loses its data. Do not delete _prisma_migrations, drop production tables, or mark migrations applied merely to silence an error.

Author-review TODO: This repository contains a general baselining article but no sanitized evidence of a real production drift repair, restored-copy rehearsal, or final verification. This procedure must remain a draft until an experienced owner/DBA reviews it against the Prisma version and deployment environment where it will be used.

Drift, schema state, and migration history

Three related artifacts can disagree:

• The Prisma schema declares the application-facing data model.
• Migration directories contain ordered SQL changes that should produce the intended database schema.
• The database stores both its current objects and Prisma's record of applied migrations in _prisma_migrations.

Schema state answers "what objects exist now?" Migration history answers "which reviewed steps does the project believe ran?" Two databases can have matching tables yet different histories, or matching histories yet different tables after a manual change.

Prisma's migrate dev detects drift in development by replaying migration history in a shadow database and comparing the result with the development database. migrate deploy applies pending migrations in production but does not detect schema drift.

For the normal command roles that should precede this exceptional recovery path, see Prisma Migrate Dev vs Deploy vs DB Push.

Common causes

• A manual production hotfix changed a column, index, constraint, or enum outside Prisma Migrate.
• An already applied migration file was edited or deleted.
• Different branches or deployment artifacts carried different migration histories.
• A populated, pre-existing database was deployed without a migration baseline, often producing P3005.
• A failed migration was repaired in the database without reconciling its recorded state.
• db push changed one environment while other environments continued using migrations.
• The datasource pointed a command at a different database or schema than expected.

Separate diagnosis from repair

The diagnostic phase must not modify production. Collect read-only evidence, decide which state is authoritative, and write a recovery plan. Only then should a separately approved repair be rehearsed and deployed.

1. Freeze competing schema changes

Pause migration deployments and manual DDL so the target does not change while it is being compared. Record the application release, commit, migration artifact, Prisma CLI version, database endpoint identifier, and expected schema. Do not record credentials.

Author-review TODO: Define who can approve the freeze, where the change record lives, and how emergency DDL is coordinated for the actual deployment platform.

2. Create and verify a recoverable backup

Use the database provider's official backup or snapshot procedure. Verify its completion, retention, encryption, and restoration instructions. A backup is not proven merely because a dashboard says one exists; restore it into an isolated environment and validate the expected schema and representative data.

Do not run examples from this article against a real database. Backup and restore commands are provider-specific and intentionally omitted.

Author-review TODO: Add the provider's official backup/restore link, recovery objectives, named owner, and sanitized evidence of a successful restored-copy rehearsal.

3. Inspect migration history read-only

First run the status command in a controlled diagnostic environment. It reads the configured datasource and reports how local migration files relate to the database history:

# READ-ONLY DIAGNOSTIC. Datasource must be configured in prisma.config.ts.
npx prisma migrate status

Then inspect _prisma_migrations with a read-only database role. This query returns migration names, timestamps, rollback markers, and stored error logs; it does not change the table:

-- READ-ONLY DIAGNOSTIC. Run with a read-only role.
SELECT
  migration_name,
  started_at,
  finished_at,
  rolled_back_at,
  applied_steps_count,
  logs
FROM _prisma_migrations
ORDER BY started_at;

Sanitize any output before sharing it. Migration names and error logs can disclose internal schema, host, or application details.

Compare the rows with the exact migration directories in the deployed commit. Check for missing files, edited checksums, failures, and migrations that exist locally but not in the database.

4. Generate a non-mutating schema diff

In Prisma 7, migrate diff can compare the datasource configured by prisma.config.ts with a Prisma schema. This command prints a human-readable comparison and uses --exit-code so automation can distinguish an empty diff from a non-empty one. It does not apply changes:

# READ-ONLY DIAGNOSTIC. Replace the schema path if the project differs.
npx prisma migrate diff \
  --from-config-datasource \
  --to-schema=./prisma/schema.prisma \
  --exit-code

Exit code 0 means no difference, 2 means a non-empty diff, and 1 means an error. Direction matters: this describes changes needed to transform the configured database schema into the Prisma schema.

migrate diff compares features Prisma supports. It can omit unsupported database objects such as some views or triggers, so supplement it with provider/PostgreSQL catalog inspection when those objects matter.

Older Prisma versions use different flags. Do not copy a version 7 command into a version 6 project without checking that version's CLI reference.

Choose a recovery path

The correct path depends on whether the database change was intended and whether migration history is trustworthy.

Intended production hotfix

If emergency DDL is correct and must remain:

1. Reproduce the change in the Prisma schema and a development environment.
2. Create a migration that represents the intended change.
3. Review its SQL against what production already contains.
4. Rehearse the reconciliation on the restored copy.
5. Use Prisma's documented hotfix workflow to align history without reapplying completed SQL.
6. Deploy later pending migrations normally with migrate deploy.

migrate resolve --applied changes migration history; it does not execute the migration SQL. Marking a migration applied is truthful only when the target database already has the effects of that exact reviewed migration.

# REPAIR COMMAND — RESTORED COPY FIRST.
# Replace 20260808000000_reconcile_example with an exact reviewed migration directory.
npx prisma migrate resolve --applied 20260808000000_reconcile_example

Expected effect: Prisma records that named migration as applied in _prisma_migrations. It does not make the database schema match the migration. Run it in production only after restored-copy rehearsal, peer approval, and verification that the SQL effects already exist.

Unintended manual change

If the out-of-band change is wrong, create a new forward migration that restores the intended state without discarding required data. For a column type or constraint change, that may require staged columns, backfills, validation, and a later cleanup migration rather than one generated statement.

Do not edit a migration that has already been applied. That changes history without changing the existing database and creates a new disagreement.

Pre-existing populated database with no history

This is a baselining problem, not ordinary drift. A baseline migration represents the schema that already exists, and migrate resolve --applied records it so future deployments do not try to recreate existing objects.

Follow Prisma Migration Baseline for an Existing Database, but update its commands for the installed Prisma version and rehearse on a restored copy. P3005 is not permission to reset a populated database.

Failed migration

Use the migration's logs field and the actual database state to determine how far it ran. Prisma's production troubleshooting guidance supports either rolling back completed partial changes and marking the migration rolled back, or completing the migration and marking it applied. Both paths require migration-specific SQL and review; neither can be safely automated from a generic article.

Test the recovery on a restored copy

The rehearsal should use the same migration artifact and Prisma version intended for production. At minimum:

1. Restore the verified backup into an isolated database.
2. Re-run the read-only status and diff checks.
3. Apply only the approved repair steps.
4. Run npx prisma migrate deploy to prove later pending migrations apply normally.
5. Re-run migrate status and migrate diff.
6. Verify important constraints, indexes, row counts, application reads/writes, and provider monitoring.
7. Record duration, locks, errors, rollback criteria, and the exact commands used—with credentials removed.

Author-review TODO: Add sanitized rehearsal results, including the before/after diff, migration status, database validation queries, duration, and rollback decision points.

Deploy and verify

Schedule the approved change through the normal deployment mechanism. Avoid placing a production URL in a local shell command; use the deployment platform's secret injection and access controls.

Afterward, verify:

• prisma migrate status reports the expected history;
• the reviewed diff is empty or contains only documented, intentionally unmanaged objects;
• _prisma_migrations has the expected new state and no unexplained failures;
• application health, error rates, query latency, connection use, locks, and replication are normal;
• the migration artifact in source control matches what was deployed;
• the backup remains available through the agreed recovery window.

When to involve a DBA

Seek expert PostgreSQL and provider review when a repair changes large tables, column types, primary or foreign keys, uniqueness, partitions, extensions, replication, row-level security, triggers, or data meaning. Also escalate when the database cannot tolerate blocking DDL, the diff contains unsupported Prisma features, recovery objectives are strict, or migration history is incomplete.

Publication blockers

This draft cannot be published until:

• The owner identifies the actual Prisma version and reviews every CLI flag.
• The database provider's official backup, restore, TLS, and connection guidance is linked.
• A DBA or experienced migration owner reviews the decision paths.
• A sanitized restored-copy rehearsal demonstrates backup recovery and the proposed reconciliation.
• The read-only before/after status and diff are captured without secrets.
• All author-review TODOs are resolved.
• No published article links readers to this draft.

References

Documentation checked on 2026-08-08:

• Prisma: production migration troubleshooting
• Prisma: migrate resolve
• Prisma: migrate diff
• Prisma: baselining an existing database
• Prisma: development and production migration behavior
• Prisma: migration history concepts