DEV Community

Cover image for The Only Guide You Need for Prisma Migration Conflicts (Without Losing Data)
Aswindanu Anwar
Aswindanu Anwar

Posted on

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

#prisma #webdev #database #postgres

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.
Enter fullscreen mode Exit fullscreen mode

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:

  1. Fix or create the SQL file
  2. Apply it manually with prisma db execute
  3. 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(); });
"'
Enter fullscreen mode Exit fullscreen mode

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(); });
"'
Enter fullscreen mode Exit fullscreen mode

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(); });
"'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Fix:

  1. Read the error. Fix the SQL file.
  2. Roll it back (only works if migration is in failed state): 
npx prisma migrate resolve --rolled-back 20240101_something
Enter fullscreen mode Exit fullscreen mode
  1. Apply the fixed SQL manually: 
npx prisma db execute --file prisma/migrations/20240101_something/migration.sql
Enter fullscreen mode Exit fullscreen mode
  1. Mark it as applied: 
npx prisma migrate resolve --applied 20240101_something
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

Fix:

  1. 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
Enter fullscreen mode Exit fullscreen mode 
# bash/Linux
sha256sum prisma/migrations/20240101_something/migration.sql
Enter fullscreen mode Exit fullscreen mode
  1. 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(); });
"'
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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:

  1. Move the offending SQL line from migration A into migration B (where the column actually gets created)
  2. 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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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 $$;
Enter fullscreen mode Exit fullscreen mode

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 $$;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

  1. Never prisma migrate reset — there's always a manual fix
  2. Query the real DB first before writing any SQL
  3. IF EXISTS / IF NOT EXISTS everywhere — idempotent migrations don't cause pain
  4. CASCADE on DROP COLUMN if you use PostgreSQL RLS
  5. Wrap FKs in DO $$ IF NOT EXISTS blocks
  6. Checksum fix: update ALL rows, not just the non-rolled-back one
  7. 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.

Top comments (0)