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. 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 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.

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 and Fix: PostgreSQL Relation Does Not Exist.