Fix Prisma Migration Failures with Claude Code

The Problem

You run npx prisma migrate dev and it fails:

Error: P3006
Migration `20260415_add_user_roles` failed to apply cleanly to the shadow database.
Error:
  Step 1 Added the required column `role` to the `User` table without a default value.
  There are 247 rows in this table, it is not possible to execute this step.

Or you see drift detection failures:

Error: P3005
The database schema is not empty.

Quick Fix

For the "required column without default" error, add a default value:

model User {
  id    String @id @default(cuid())
  email String @unique
  role  String @default("user") // Add default for existing rows
}

Then regenerate the migration:

npx prisma migrate dev --name add_user_roles

What Is Happening

Prisma Migrate uses a shadow database to verify migrations. It creates a temporary database, applies all migrations from scratch, and compares the result with your schema. Common failure scenarios:

1. Adding a required column: Existing rows have no value for the new column
2. Schema drift: The database has been modified outside of Prisma migrations
3. Failed migration stuck in history: A previous migration partially applied
4. Data type changes: Converting a column type with incompatible data
5. Unique constraint violations: Adding a unique constraint to a column with duplicates

Step-by-Step Fix

Fix required column errors (3 options)

Option A: Add a default value in the schema.

Option B: Make the column optional first, backfill, then make required:

-- Step 1: Add column as optional
ALTER TABLE "User" ADD COLUMN "role" TEXT;

-- Step 2: Backfill
UPDATE "User" SET "role" = 'user' WHERE "role" IS NULL;

-- Step 3: Make required
ALTER TABLE "User" ALTER COLUMN "role" SET NOT NULL;
ALTER TABLE "User" ALTER COLUMN "role" SET DEFAULT 'user';

Fix schema drift

npx prisma migrate diff \
  --from-migrations ./prisma/migrations \
  --to-schema-datamodel ./prisma/schema.prisma \
  --script

If the database matches your desired schema but has no migration history:

npx prisma migrate resolve --applied "20260415_init"

Fix a stuck failed migration

npx prisma migrate status
npx prisma migrate resolve --rolled-back "20260415_add_user_roles"

Prevention

Always test migrations against a copy of production data before deploying. Never add a required column without a default value. Always use --create-only for complex migrations and review the SQL. Never use prisma migrate dev in production (use prisma migrate deploy).