The Only Guide You Need for Prisma Migration Conflicts (Without Losing Data)

If you've ever seen this message:

The migration `20240101_something` was modified after it was applied.
We need to reset the "public" schema.
All data will be lost.

Stop. Do not run prisma migrate reset. This guide will walk you through resolving every Prisma migration conflict without touching your data.

The Golden Rule

Never use prisma migrate reset on a real database.
Every conflict has a manual resolution. Here's the playbook.

The pattern is always the same:

Fix or create the SQL file
Apply it manually with prisma db execute
Tell Prisma it's done with prisma migrate resolve --applied

Before You Write Any SQL — Query the Actual DB First

Do not guess. Before writing migration SQL, check what actually exists in your database. This prevents the back-and-forth ping-pong of "column doesn't exist" errors.

docker exec <container> sh -c 'node -e "
const { Client } = require(\"pg\");
const client = new Client({ connectionString: process.env.DATABASE_URL });
client.connect()
  .then(() => client.query(\"SELECT indexname, tablename FROM pg_indexes WHERE tablename = \$1\", [\"your_table\"]))
  .then(r => { console.log(JSON.stringify(r.rows, null, 2)); client.end(); })
  .catch(e => { console.error(e.message); client.end(); });
"'

Check columns:

docker exec <container> sh -c 'node -e "
const { Client } = require(\"pg\");
const client = new Client({ connectionString: process.env.DATABASE_URL });
client.connect()
  .then(() => client.query(\"SELECT column_name, is_nullable, data_type, column_default FROM information_schema.columns WHERE table_name = \$1\", [\"your_table\"]))
  .then(r => { console.log(JSON.stringify(r.rows, null, 2)); client.end(); })
  .catch(e => { console.error(e.message); client.end(); });
"'

Check constraints:

docker exec <container> sh -c 'node -e "
const { Client } = require(\"pg\");
const client = new Client({ connectionString: process.env.DATABASE_URL });
client.connect()
  .then(() => client.query(\"SELECT conname, contype, conrelid::regclass AS table_name FROM pg_constraint WHERE conrelid::regclass::text = \$1\", [\"your_table\"]))
  .then(r => { console.log(JSON.stringify(r.rows, null, 2)); client.end(); })
  .catch(e => { console.error(e.message); client.end(); });
"'

Case 1 — Migration Failed Mid-Run (P3018)

Error:

A migration failed to apply. New migrations cannot be applied before the error is recovered from.
Migration name: 20240101_something
Database error: ERROR: column "xyz" does not exist

Fix:

Read the error. Fix the SQL file.
Roll it back (only works if migration is in failed state):

npx prisma migrate resolve --rolled-back 20240101_something

Apply the fixed SQL manually:

npx prisma db execute --file prisma/migrations/20240101_something/migration.sql

Mark it as applied:

npx prisma migrate resolve --applied 20240101_something

Case 2 — Checksum Mismatch (Migration Modified After Being Applied)

Error:

The migration `20240101_something` was modified after it was applied.
We need to reset the "public" schema.
All data will be lost.

Fix:

Compute the SHA-256 of your current migration file:

# PowerShell
$hash = (Get-FileHash "prisma/migrations/20240101_something/migration.sql" -Algorithm SHA256).Hash.ToLower()
Write-Output $hash

# bash/Linux
sha256sum prisma/migrations/20240101_something/migration.sql

Update ALL rows for that migration in _prisma_migrations — Prisma keeps rolled-back history rows and checks those too. Filtering by rolled_back_at IS NULL is NOT enough:

docker exec <container> sh -c 'node -e "
const { Client } = require(\"pg\");
const client = new Client({ connectionString: process.env.DATABASE_URL });
client.connect().then(async () => {
  const r = await client.query(
    \"UPDATE _prisma_migrations SET checksum = \$1 WHERE migration_name = \$2\",
    [\"<your_hash_here>\", \"20240101_something\"]
  );
  console.log(\"rows updated:\", r.rowCount);
  await client.end();
}).catch(e => { console.error(e.message); client.end(); });
"'

Critical: Always update ALL rows (no rolled_back_at IS NULL filter). Prisma checks rolled-back rows too and will keep complaining if they have old checksums.

Case 3 — Already Applied (P3008)

Error:

The migration `20240101_something` is already recorded as applied in the database.

Nothing to do — it's applied. If there's also a checksum mismatch, go to Case 2.

Case 4 — Cannot Roll Back, Not in Failed State (P3012)

Error:

Migration `20240101_something` cannot be rolled back because it is not in a failed state.

The migration is recorded as applied/success. Use Case 2 (checksum update) to reconcile the file change.

Case 5 — Shadow DB Error on Column That Doesn't Exist Yet (P3006)

Error:

Migration `20240101_fix` failed to apply cleanly to the shadow database.
ERROR: column "my_column" of relation "my_table" does not exist

What's happening: Prisma replays all migrations in timestamp order in the shadow DB. If migration A (timestamp 20240101) references a column that migration B (timestamp 20240115) creates, the shadow DB fails because A runs before B.

Fix:

Move the offending SQL line from migration A into migration B (where the column actually gets created)
Update checksums for both migration files using Case 2

SQL Rules for All Manual Migrations

Always use `IF EXISTS` / `IF NOT EXISTS` — make every migration idempotent

-- Indexes
DROP INDEX IF EXISTS "my_index";
CREATE INDEX IF NOT EXISTS "my_index" ON "my_table"("my_column");

-- Columns
ALTER TABLE "my_table" ADD COLUMN IF NOT EXISTS "my_column" TEXT;

Always use `CASCADE` when dropping columns that have RLS policies

PostgreSQL Row Level Security policies are tied to columns. Dropping the column without CASCADE will fail:

-- WRONG — will fail if RLS policies depend on tenant_id
ALTER TABLE "my_table" DROP COLUMN "tenant_id";

-- CORRECT — drops dependent RLS policies automatically
ALTER TABLE "my_table" DROP COLUMN IF EXISTS "tenant_id" CASCADE;

Always wrap FK additions in a DO block

DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'my_table_col_fkey'
  ) THEN
    ALTER TABLE "my_table"
      ADD CONSTRAINT "my_table_col_fkey"
      FOREIGN KEY ("col") REFERENCES "other_table"("id")
      ON DELETE CASCADE ON UPDATE CASCADE;
  END IF;
END $$;

Full Example: A Real Conflict Resolution

Here's a real sequence we used to resolve a failed migration with RLS dependencies:

-- migration.sql (fixed version)

-- DropIndex (already gone in DB — IF EXISTS makes this a no-op)
DROP INDEX IF EXISTS "candidate_cv_tenant_id_candidate_id_idx";
DROP INDEX IF EXISTS "candidate_skills_tenant_id_candidate_id_skill_uri_idx";

-- Drop tenant_id (CASCADE removes dependent RLS policies automatically)
ALTER TABLE "candidate_certifications" DROP COLUMN IF EXISTS "tenant_id" CASCADE;
ALTER TABLE "candidate_cv"             DROP COLUMN IF EXISTS "tenant_id" CASCADE;
ALTER TABLE "candidate_educations"     DROP COLUMN IF EXISTS "tenant_id" CASCADE;
ALTER TABLE "candidate_languages"      DROP COLUMN IF EXISTS "tenant_id" CASCADE;
ALTER TABLE "candidate_skills"         DROP COLUMN IF EXISTS "tenant_id" CASCADE;

-- Change PK (idempotent: IF EXISTS on drop, unconditional on add)
ALTER TABLE "candidate_embeddings" DROP CONSTRAINT IF EXISTS "candidate_embeddings_pkey";
ALTER TABLE "candidate_embeddings" ALTER COLUMN "tenant_id" DROP NOT NULL;
ALTER TABLE "candidate_embeddings" ADD CONSTRAINT "candidate_embeddings_pkey" PRIMARY KEY ("cand_id");

-- CreateIndex
CREATE INDEX IF NOT EXISTS "candidate_cv_candidate_id_idx" ON "candidate_cv"("candidate_id");
CREATE UNIQUE INDEX IF NOT EXISTS "job_skills_unique_key" ON "job_skills"("job_id", "skill_id", "tenant_id");

-- AddForeignKey (guarded)
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'job_skills_tenant_id_fkey') THEN
    ALTER TABLE "job_skills" ADD CONSTRAINT "job_skills_tenant_id_fkey"
      FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE CASCADE ON UPDATE CASCADE;
  END IF;
END $$;

Then:

npx prisma migrate resolve --rolled-back 20240101_fix_migration
npx prisma db execute --file prisma/migrations/20240101_fix_migration/migration.sql
npx prisma migrate resolve --applied 20240101_fix_migration

Quick Reference Cheat Sheet

Error	Cause	Fix
P3018	Migration failed mid-run	Fix SQL → `resolve --rolled-back` → `db execute` → `resolve --applied`
"modified after applied"	Checksum mismatch	Compute SHA-256 → update ALL rows in `_prisma_migrations`
P3008	Already applied	No-op (check for checksum mismatch too)
P3012	Not in failed state	Use checksum update approach
P3006 shadow DB column missing	Wrong timestamp order	Move SQL to the migration that creates the column
RLS policy dependency error	`DROP COLUMN` blocked by policy	Add `CASCADE` to `DROP COLUMN IF EXISTS`
FK constraint already exists	Migration re-run	Wrap FK in `DO $$ IF NOT EXISTS` block

TL;DR

Never prisma migrate reset — there's always a manual fix
Query the real DB first before writing any SQL
IF EXISTS / IF NOT EXISTS everywhere — idempotent migrations don't cause pain
CASCADE on DROP COLUMN if you use PostgreSQL RLS
Wrap FKs in DO $$ IF NOT EXISTS blocks
Checksum fix: update ALL rows, not just the non-rolled-back one
Shadow DB timestamp ordering: move SQL to the migration where the column is born

Battle-tested on a production PostgreSQL database with RLS, pgbouncer, and Prisma Migrate.

The Only Guide You Need for Prisma Migration Conflicts (Without Losing Data)