The Guide to Resolving Prisma Migration Conflicts
Do not run prisma migrate reset on a production database.
If you see a message saying a migration was modified or that you need to reset the schema, stop. Resetting will delete all your data. You can fix these conflicts manually without losing anything.
Common Prisma Errors and How to Fix Them
Error: Migration failed mid-run (P3018) • Fix the SQL file. • Run: npx prisma migrate resolve --rolled-back [migration_name] • Run: npx prisma db execute --file [path_to_sql_file] • Run: npx prisma migrate resolve --applied [migration_name]
Error: Migration modified after it was applied (Checksum mismatch) • Calculate the SHA-256 hash of your current migration file. • Update the checksum in the _prisma_migrations table. • Important: Update all rows for that migration name. Do not filter by rolled_back_at.
Error: Shadow database column missing (P3006) • This happens when a migration references a column that a later migration creates. • Prisma replays migrations in timestamp order. • Fix: Move the SQL code to the migration where the column is first created.
Error: DROP COLUMN blocked by RLS policy • PostgreSQL Row Level Security ties policies to columns. • Fix: Use CASCADE when dropping columns. • Example: ALTER TABLE "my_table" DROP COLUMN IF EXISTS "tenant_id" CASCADE;
Best Practices for Safe Migrations
- Query the database first. Do not guess if a column or index exists. Use direct SQL queries to check your actual database schema before writing migration code.
- Use IF EXISTS and IF NOT EXISTS. This makes your migrations idempotent. An idempotent migration can run multiple times without causing errors.
- Use DO blocks for constraints. Wrap Foreign Key additions in a check to see if the constraint name already exists.
- Always use CASCADE for drops if you use Row Level Security.
Summary Table
• P3018 (Failed mid-run): Fix SQL, then use resolve --rolled-back and resolve --applied. • Checksum mismatch: Compute new hash and update all rows in _prisma_migrations. • Shadow DB error: Check your timestamp order and move SQL logic. • RLS error: Add CASCADE to your DROP statements.