Database schema drift recovery after partial migration failure in Prisma

Answer to the Exact Questions

1. Reset vs. Replay in prisma migrate dev

Prisma compares the current database schema with the expected schema derived from all migrations that have a non-null finished_at and a null rolled_back_at. The comparison is performed by the prisma migrate status logic, which produces three categories:

• Applied – migrations whose records are marked finished and whose DDL is present in the database.
• Pending – migrations that exist in the migrations folder but have no finished record.
• Failed / Drift – the database schema differs from the expected schema for an applied migration (e.g., missing tables, altered columns, or a checksum mismatch).

When prisma migrate dev runs, it follows these rules:

1. If drift exists but all applied migrations are still marked finished and the database can be reconciled by applying the pending migrations, Prisma replays the pending migrations.
2. If drift is detected in an applied migration (the migration record is finished but the DDL does not match) or if the checksum of an applied migration no longer matches the file, Prisma cannot automatically patch the schema. In this case it offers a prisma migrate reset, which drops all user tables and rebuilds the database from scratch.
3. When the --create-only flag is used, Prisma will only create the migration history table and will never perform a reset, even if drift is present.

2. Effect of prisma migrate resolve --rolled-back

Running prisma migrate resolve --rolled-back --migration-name <name> changes the migration record as follows:

• Sets rolled_back_at to the current timestamp.
• Leaves finished_at as NULL.
• Does not touch the database schema.

Because the command does not revert the DDL, you must manually ensure the database matches the state that would exist if the migration had never run. Typical manual steps are:

1. Inspect the database to identify which parts of the migration were applied.
2. Run the corresponding down statements manually (or drop the affected tables/columns).
3. Verify that the schema now matches the schema.prisma and any earlier migrations.
4. Execute the resolve command to mark the migration as rolled back.
5. Run prisma migrate dev again to apply any remaining pending migrations.

Failure to fully revert the schema before resolving will cause a drift that Prisma will detect on the next dev run, potentially triggering another reset.

3. Manually Adjusting _prisma_migrations

Official Prisma documentation does not provide a step-by-step guide for editing _prisma_migrations directly. The supported workflow is:

1. Repair the database schema to the desired state (either by completing the migration manually or by rolling it back).
2. Use prisma migrate resolve --applied if the migration's DDL is now present but the record is missing, or --rolled-back if you have undone the changes.
3. Run prisma migrate status to confirm that the history matches the database.
4. Proceed with prisma migrate dev to apply the rest of the migrations.

Direct SQL edits (e.g., changing finished_at or checksum) are possible but bypass Prisma's integrity checks and can lead to checksum mismatches or future drift detection. Use them only as a last resort, and always backup the _prisma_migrations table first.

Assumptions & Uncertainty

These recommendations assume:

• The database engine supports transactional DDL (PostgreSQL, MySQL 8+, SQL Server). If you are on SQLite or an older MySQL version, DDL is non-transactional and partial failures are inevitable.
• No custom migration logic (e.g., raw SQL with side effects) is present that could alter data beyond the schema.
• You have a recent backup of the database before performing any manual edits.

Because the behavior can differ between engines, please confirm your database type before proceeding. If you are on a non-transactional engine, the steps below are the safest path.

Quick Checklist for Recovery

1. Run prisma migrate status to see the drift report.
2. Inspect the database schema (e.g., \dt in psql, SHOW TABLES in MySQL).
3. Determine which DDL statements from the failed migration actually ran.
4. Manually revert those changes or apply the missing ones to bring the schema in line with the migration history.
5. Execute prisma migrate resolve --rolled-back --migration-name <name> (or --applied if you completed the migration).
6. Run prisma migrate dev to apply any remaining migrations.
7. Verify prisma migrate status shows no pending or failed migrations.

Follow this workflow and you should avoid a destructive reset while keeping the database and migration history in sync.