How to fix P3006: Migration failed to apply cleanly to the shadow database in Prisma

Run the migration command with verbose output to see exactly what failed:

npx prisma migrate dev

Read the full error message—it will tell you which table, column, or constraint is causing the issue. For example:

ERROR: relation "PageContents" already exists

This tells you the specific conflict blocking the migration.

Verify that your development database matches your migration history. Use a database client (psql, MySQL CLI, or Prisma Studio) to inspect the current schema:

npx prisma studio

Or connect directly:

psql $DATABASE_URL -c "\dt"  # PostgreSQL: list tables
mysql -u user -p < "SHOW TABLES;"  # MySQL

If you see tables or columns that shouldn't exist (created manually or by other tools), you need to remove them or document them in your migrations.

Compare your prisma/schema.prisma with your migration files:

ls -la prisma/migrations/
cat prisma/migrations/*/migration.sql

Your schema.prisma should represent the END state of all your migrations. If it doesn't, edit schema.prisma to match, then generate a new migration:

npx prisma migrate dev --name fix_schema_sync

If your database and schema are out of sync, pull the current database state into your Prisma schema:

npx prisma db pull

This introspects your actual database and updates schema.prisma. Review the changes carefully, then create a migration to document this state:

npx prisma migrate dev --name baseline_from_db

If you're in active development and the data in your dev database doesn't matter, reset the database to a clean state:

npx prisma migrate reset

This will:
1. Drop the database
2. Recreate it
3. Run all migrations from scratch
4. Re-seed with your seed data

Warning: Only use this in development. Never run this in production.

If the error mentions "permission denied" or similar, ensure your database user has sufficient privileges:

For PostgreSQL:

ALTER USER your_user CREATEDB;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_user;
GRANT USAGE ON SCHEMA public TO your_user;

For MySQL:

GRANT ALL PRIVILEGES ON *.* TO 'your_user'@'localhost';
FLUSH PRIVILEGES;

Alternatively, configure a separate shadow database user with full permissions.

If your hosting provider (e.g., Azure, some managed databases) doesn't allow automatic shadow database creation, specify one manually in schema.prisma:

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

Then in your .env.local:

DATABASE_URL=postgresql://user:password@host/production_db
SHADOW_DATABASE_URL=postgresql://user:password@host/shadow_db

Create the shadow database manually and ensure it's empty before running migrations.

If you switched git branches and have conflicting migrations in your history, use prisma migrate resolve to mark migrations as rolled back:

npx prisma migrate resolve --rolled-back migration_name_here

Then run:

npx prisma migrate dev

This reconciles your migration history without forcing a full reset.

If the error persists, check for syntax issues in your latest migration file:

cat prisma/migrations/*/migration.sql | tail -20

Ensure:
- File is saved as UTF-8 (not UTF-8 with BOM)
- SQL syntax is valid for your database
- Table/column names are properly quoted if needed

If you find an issue, edit the migration SQL file directly (before applying it) or delete the unapplied migration and recreate it.