Fix: Prisma Migration Failed (migrate dev / migrate deploy Errors)

The Error

Running prisma migrate dev fails with:

Error: P3006
Migration `20240315120000_add_users` failed to apply cleanly to the shadow database.
Error: column "email" of relation "User" already exists

Or in production with prisma migrate deploy:

Error: P3009
migrate found failed migrations in the target database, new migrations will not be applied.
The `20240315120000_add_users` migration failed.

Or after pulling a teammate’s changes:

Error: P3006
The migration `20240312_init` failed to apply cleanly to the shadow database:
db error: ERROR: relation "User" already exists

Or the database schema is out of sync with Prisma’s migration history:

⚠️  There are 2 unapplied migrations.

Why This Happens

Prisma tracks applied migrations in a _prisma_migrations table. Each row records the migration name, when it started, when it finished, the SQL checksum, and how many steps were applied. Failures occur when:

• Manual database changes — a column or table was created directly in the database (via a SQL client or another migration tool), and now Prisma’s migration conflicts with existing schema.
• Failed migration partially applied — a migration ran partway through and left the database in an inconsistent state. Prisma marks it as failed (finished_at is NULL, rolled_back_at is NULL) and refuses to proceed.
• Development database out of sync — the local database diverged from the migration history (e.g., from running prisma db push in dev, then switching to migrate dev).
• Shadow database access issues — migrate dev uses a temporary shadow database to verify migrations. If it cannot create or access it, migrations fail.
• Production drift — someone applied a manual SQL change to production that conflicts with an upcoming migration.
• Non-transactional DDL — MySQL (and some MySQL forks) cannot run multiple DDL statements inside a single transaction. A failure halfway through leaves the schema half-applied with no automatic rollback.
• Migration checksum mismatch — the file content of a migration in prisma/migrations/ was edited after it was applied. Prisma detects the SHA256 checksum no longer matches what _prisma_migrations recorded and refuses to proceed.
• Conflicting concurrent migrations — two developers create migrations on separate branches with overlapping changes. After merge, Prisma sees two migrations both trying to add the same column.

In Production: Incident Lens

How the incident surfaces. A failed prisma migrate deploy is a deploy-time blocker: the new app version cannot start because the schema migration step in the deploy pipeline fails with P3009. The application currently in production keeps serving on the old schema, so users see no symptoms, but the new code that depends on the new schema is held back. The worst variant is a partially-applied migration on a database that does not support transactional DDL (MySQL, MariaDB): a multi-step migration crashes after creating the first table but before creating the second, leaving the database in a half-applied state that Prisma refuses to either retry or roll back automatically.

Blast radius. Bounded but high-attention. No user-visible impact in the no-rollback case, but the deploy pipeline is frozen until the failed migration is resolved. The blast widens dramatically if the partially-applied migration broke an existing query — for example, dropping a column the running app still reads from. Then existing pods start logging “column does not exist” errors and the on-call decides whether to roll back the app (back to the old schema) or push forward (manually finish the migration). Both paths carry risk.

The monitoring signal that catches it. Deploy job exit code is the primary signal — alert on consecutive prisma migrate deploy failures via your CI/CD platform. Database query error rate filtered on 42703 (column does not exist) and 42P01 (relation does not exist) Postgres SQLSTATEs catches the schema-mismatch case at the application layer. For MySQL, 1054 (Unknown column) and 1146 (Table doesn’t exist) are the equivalent codes. Layer in: the count of rows in _prisma_migrations where finished_at IS NULL AND rolled_back_at IS NULL (the “failed migration” sentinel), graphed over time.

Recovery sequence. First, do not panic-rollback. Run prisma migrate status against production to see exactly which migration failed and how far it got. Second, decide between three paths: (a) prisma migrate resolve --rolled-back <name> if the failed migration produced no schema changes (rare for DDL-only migrations), then redeploy; (b) prisma migrate resolve --applied <name> if you manually completed the remaining DDL by hand and want Prisma to treat the migration as done; (c) shadow-database replay if the migration was complex — copy the production schema to a staging DB, replay the failed migration carefully, capture the working SQL, then apply by hand in prod. Never edit _prisma_migrations table directly to “unblock” — that leaves an inconsistent state that bites the next migration.

Postmortem-style preventive. The durable controls: (1) every migration runs in a staging environment with prod-shape data before reaching production — catches the “this works locally with 10 rows but locks for an hour on 10M rows” class of failures; (2) migrations are split into small, individually-reversible pieces (add column nullable → backfill → set NOT NULL is three migrations, not one); (3) review checklist on the PR template asks “does this migration take a lock? for how long?” and “is it backwards-compatible with the previous app version?”; (4) the deploy pipeline runs prisma migrate diff --from-empty --to-schema-datamodel schema.prisma --script as a dry-run that captures the actual DDL Prisma will execute; (5) blue-green deploys ensure the old app version stays up while migrations run, so a long migration does not equal downtime.

Fix 1: Resolve a Failed Migration in Development

If a migration failed during development and left the database dirty, reset and re-apply:

Check the current migration status:

npx prisma migrate status

This shows which migrations are applied, failed, or pending.

Option A — reset the development database (safest in dev):

# Drops and recreates the database, applies all migrations from scratch
npx prisma migrate reset

Warning: migrate reset deletes all data in the development database. Only use it in development — never in production.

After reset, run:

npx prisma migrate dev

Option B — mark the failed migration as rolled back and fix it:

If you cannot reset (e.g., there is data you need), manually fix the migration:

# 1. Fix the SQL in the failed migration file
# prisma/migrations/20240315120000_add_users/migration.sql

# 2. Mark it as rolled back in the migrations table
npx prisma migrate resolve --rolled-back "20240315120000_add_users"

# 3. Re-apply
npx prisma migrate dev

Fix 2: Fix Production Migration Failures

In production, migrate deploy applies pending migrations. If one fails:

Check what failed:

npx prisma migrate status
# or connect to the DB and inspect:
SELECT * FROM _prisma_migrations ORDER BY started_at DESC LIMIT 10;

Mark a failed migration as rolled back after manually fixing the database:

# After manually reverting the partial changes in the database:
npx prisma migrate resolve --rolled-back "20240315120000_add_users"

# Then re-deploy
npx prisma migrate deploy

Mark a migration as applied if you applied it manually:

# If you ran the SQL manually in production and want Prisma to acknowledge it:
npx prisma migrate resolve --applied "20240315120000_add_users"

Pro Tip: Always run prisma migrate deploy in a transaction-aware context. Wrap destructive migrations in explicit transactions in the SQL file to allow automatic rollback if any statement fails:

BEGIN;
ALTER TABLE "User" ADD COLUMN "emailVerified" BOOLEAN NOT NULL DEFAULT false;
UPDATE "User" SET "emailVerified" = true WHERE "createdAt" < '2024-01-01';
COMMIT;

Fix 3: Fix Schema Drift (Database Out of Sync with Migrations)

Schema drift occurs when the database schema differs from what Prisma’s migrations would produce — usually from manual SQL changes or prisma db push:

Detect drift:

npx prisma migrate dev
# Prisma detects drift and warns:
# ⚠️  Drift detected: Your database schema is not in sync with your migration history.

Option A — generate a new migration that captures the drift:

# Prisma compares the current DB state to the migration history and generates a diff
npx prisma migrate dev --name fix_drift

Review the generated migration SQL carefully before committing it.

Option B — use db pull to sync the schema, then create a migration:

# Pull the current database schema into schema.prisma
npx prisma db pull

# Review changes to schema.prisma, then create a migration
npx prisma migrate dev --name sync_schema

Option C — for a clean slate in development:

npx prisma migrate reset
npx prisma migrate dev

Fix 4: Fix Shadow Database Errors

migrate dev requires a shadow database (a temporary copy of your DB) to safely test migrations. Errors here are common with cloud databases or restricted permissions:

Error: P3014
Prisma Migrate could not create the shadow database. Please make sure the database user has permission to create databases.

Fix — grant CREATE DATABASE permission:

-- PostgreSQL
ALTER USER myuser CREATEDB;

-- MySQL
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Or specify a shadow database URL explicitly:

# .env
DATABASE_URL="postgresql://user:pass@localhost:5432/mydb"
SHADOW_DATABASE_URL="postgresql://user:pass@localhost:5432/mydb_shadow"

// schema.prisma
datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

Create the shadow database manually first:

CREATE DATABASE mydb_shadow;

For cloud databases that do not allow shadow databases (PlanetScale, Supabase, etc.):

Use prisma db push during development instead of migrate dev, and only use migrate deploy in production:

# Development — push schema changes directly without migration history
npx prisma db push

# Production — apply pre-generated migrations
npx prisma migrate deploy

Fix 5: Fix “Already Exists” Errors in Migrations

A migration fails because the column or table already exists — usually from a previous partial run or a manual change:

Option A — make the migration idempotent with IF NOT EXISTS:

Edit the migration SQL file:

-- Before (fails if column exists):
ALTER TABLE "User" ADD COLUMN "role" TEXT NOT NULL DEFAULT 'user';

-- After (safe to run multiple times):
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "role" TEXT NOT NULL DEFAULT 'user';

Note: IF NOT EXISTS for ALTER TABLE ADD COLUMN is supported in PostgreSQL 9.6+ and MySQL 8.0+. For older databases, use a conditional check.

Option B — drop the conflicting object first in the migration:

-- Drop the manually-created column first, then recreate with correct definition
ALTER TABLE "User" DROP COLUMN IF EXISTS "role";
ALTER TABLE "User" ADD COLUMN "role" TEXT NOT NULL DEFAULT 'user';

Option C — skip the conflicting step and mark migration applied:

If the database already has the correct schema and you just need Prisma to acknowledge the migration:

npx prisma migrate resolve --applied "20240315120000_add_role"

Fix 6: Handle Multi-Environment Migration Workflow

A reliable migration workflow for teams:

# Developer A — make schema change
# 1. Edit prisma/schema.prisma
# 2. Generate migration (do not apply yet)
npx prisma migrate dev --name add_user_role --create-only

# 3. Review the generated SQL in prisma/migrations/
# 4. Commit both schema.prisma and the migration directory

# Developer B — pull the change
git pull
npx prisma migrate dev  # Applies the new migration

# CI/CD Pipeline — production deploy
npx prisma migrate deploy  # Only applies, never creates

Ensure migrations run before your app starts in production:

// server.js — run migrations on startup
import { PrismaClient } from '@prisma/client';
import { execSync } from 'child_process';

// Run migrations before starting the server
execSync('npx prisma migrate deploy', { stdio: 'inherit' });

const prisma = new PrismaClient();

// Start app...

Or in a Dockerfile:

CMD ["sh", "-c", "npx prisma migrate deploy && node dist/server.js"]

Still Not Working?

Check the _prisma_migrations table directly. This table records every migration attempt — look for rows with finished_at as NULL (failed) or applied_steps_count less than expected:

SELECT migration_name, started_at, finished_at, applied_steps_count, logs
FROM _prisma_migrations
ORDER BY started_at DESC
LIMIT 20;

Verify the DATABASE_URL is correct. A wrong connection string causes misleading errors. Test the connection:

npx prisma db pull  # If this succeeds, the connection string is correct

Check for transaction support. Some databases (PlanetScale, Vitess) do not support DDL transactions. Enable referentialIntegrity mode in your schema:

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"  // Use Prisma-level relation enforcement instead of DB constraints
}

For related database issues, see Fix: MySQL Deadlock Detected, Fix: PostgreSQL Relation Does Not Exist, Fix: Django Migration Conflict, and Fix: PostgreSQL FATAL Role Does Not Exist.