How to fix P3009: migrate found failed migrations in the target database in Prisma

PrismaINTERMEDIATEHIGHUpdated Dec 22, 2025· 196 views

The Prisma P3009 error occurs when Prisma detects previously failed migration attempts in your database. This prevents new migrations from running until the failed migrations are resolved. The fix involves identifying and resolving the failed migration state, either by fixing the migration or resetting the migration history.

Quick fix

Fixes P3009: migrate found failed migrations in the target database

# Check migration status
npx prisma migrate status

# Or query the migrations table directly
npx prisma db execute --stdin --schema=./prisma/schema.prisma << 'EOF'
SELECT * FROM _prisma_migrations WHERE "finished_at" IS NULL OR "rolled_back_at" IS NOT NULL;
EOF
  1. Check the migration failure details
  2. Review the failed migration SQL
  3. Fix the migration SQL if possible
  4. Reset migration history if needed
prisma — error
# Check migration status
npx prisma migrate status
# Or query the migrations table directly
npx prisma db execute --stdin --schema=./prisma/schema.prisma << 'EOF'
SELECT * FROM _prisma_migrations WHERE "finished_at" IS NULL OR "rolled_back_at" IS NOT NULL;
EOF
P3009: migrate found failed migrations in the target database

What this error means

The P3009 error in Prisma indicates that the migration engine has detected one or more failed migration attempts in your target database. This is a safety mechanism that prevents potentially destructive operations when there are unresolved migration failures. When Prisma runs migrations, it tracks their execution in a special `_prisma_migrations` table. Each migration has a status: "Success", "Failed", "RolledBack", or "Applied". The P3009 error occurs when Prisma finds migrations with "Failed" status in this table. This error is particularly important because: 1. **Data Integrity**: Failed migrations might have partially applied changes that could corrupt your database schema 2. **Safety First**: Prisma prevents new migrations from running until you explicitly address the failed state 3. **Manual Intervention Required**: You need to investigate what caused the migration to fail and decide how to proceed The error typically appears when running commands like `prisma migrate dev`, `prisma migrate deploy`, or `prisma db push`. It's part of the P3000-P3099 range of Prisma errors that relate to migration-specific issues.

Typical symptoms

  • Prisma migrate commands fail with P3009 error message
  • Database schema changes are not applied
  • Migration history shows "Failed" status for recent migrations
  • Application cannot start due to schema mismatch
  • CI/CD pipeline fails during database migration step

Common causes

  1. A previous migration failed due to SQL syntax errors or invalid operations
  2. Database constraints or permissions prevented migration execution
  3. Network timeout or connection loss during migration execution
  4. Conflicting schema changes between multiple migration files
  5. Manual intervention in the _prisma_migrations table
  6. Database server was restarted during migration execution

How to fix "P3009: migrate found failed migrations in the target database"

1Check the migration failure details

First, examine what migrations failed and why:

bash
# Check migration status
npx prisma migrate status

# Or query the migrations table directly
npx prisma db execute --stdin --schema=./prisma/schema.prisma << 'EOF'
SELECT * FROM _prisma_migrations WHERE "finished_at" IS NULL OR "rolled_back_at" IS NOT NULL;
EOF

Look for:
- Which migration(s) have "Failed" status
- The error messages in the "logs" column
- When the failure occurred

2Review the failed migration SQL

Examine the SQL that failed to execute:

bash
# Find the migration file that failed
# Look in prisma/migrations/ directory for the timestamp matching the failed migration

# View the migration SQL
cat prisma/migrations/[TIMESTAMP]_migration_name/migration.sql

Common issues to look for:
- Syntax errors in SQL
- References to non-existent tables or columns
- Duplicate constraint names
- Invalid data type conversions
- Missing required columns in INSERT/UPDATE statements

3Fix the migration SQL if possible

If the migration failure was due to a fixable SQL error:

1. Create a new migration with the fix:

bash
# Make your schema changes in schema.prisma
# Then create a new migration
npx prisma migrate dev --name fix_failed_migration

2. Or manually fix the failed migration:

bash
# First, mark the failed migration as rolled back
npx prisma db execute --stdin --schema=./prisma/schema.prisma << 'EOF'
UPDATE _prisma_migrations
SET "rolled_back_at" = NOW(), "logs" = 'Manually rolled back due to P3009'
WHERE "migration_name" = '[failed_migration_name]' AND "finished_at" IS NULL;
EOF

# Then fix your schema and create a new migration

Warning: Only do this if you understand the implications and have database backups.

4Reset migration history if needed

If the failed migration cannot be fixed or you want to start fresh:

bash
# Option 1: Soft reset (keeps data, resets migration history)
npx prisma migrate reset

# Option 2: Manual reset (more control)
# First backup your data
pg_dump your_database > backup.sql

# Then reset the migrations table
npx prisma db execute --stdin --schema=./prisma/schema.prisma << 'EOF'
-- Clear failed migrations
DELETE FROM _prisma_migrations WHERE "finished_at" IS NULL;

-- Or reset entire migration history (more drastic)
-- TRUNCATE TABLE _prisma_migrations;
EOF

# Then re-apply migrations
npx prisma migrate deploy

Important: Always backup your database before resetting migration history.

5Test the fix with a new migration

After resolving the failed migration state, test that migrations work correctly:

bash
# Create a test migration
npx prisma migrate dev --name test_migration_after_fix

# Verify migration status
npx prisma migrate status

# Should show all migrations as "Success"

If this works, your P3009 error should be resolved.

6Implement preventive measures

To avoid P3009 errors in the future:

1. Test migrations locally first:

bash
npx prisma migrate dev --create-only
npx prisma db execute --file prisma/migrations/[migration_file].sql --dry-run

2. Use transactions in migrations:

sql
-- In your migration.sql files
BEGIN;
-- Your migration SQL here
COMMIT;

3. Implement CI/CD checks:

yaml
# In your CI pipeline
- name: Test migrations
  run: npx prisma migrate dev --create-only && npx prisma migrate status

4. Regularly backup migration state:

bash
# Export migration state
npx prisma migrate status --json > migration_state.json

Advanced notes

Production Considerations: In production environments, P3009 errors are particularly critical because they can prevent application updates. Consider:
- Using blue-green deployments with separate databases
- Implementing migration rollback strategies
- Having database snapshots before migrations
- Using Prisma Migrate's shadow database feature for testing

Team Collaboration: When working in teams:
- Coordinate migration execution to avoid conflicts
- Use migration locking mechanisms
- Document migration dependencies and requirements
- Consider using a migration management tool or service

Database-Specific Notes:
- PostgreSQL: Failed migrations might leave behind locked tables or incomplete transactions. Use SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction'; to check for hanging transactions.
- MySQL: Check for table locks with SHOW OPEN TABLES WHERE In_use > 0;
- SQL Server: Failed migrations might leave transaction log growth. Monitor with DBCC OPENTRAN;

Alternative Approaches: If migrations consistently fail, consider:
1. Using Prisma's db push for development (though not recommended for production)
2. Implementing custom migration scripts with better error handling
3. Using database-native migration tools alongside Prisma

Monitoring: Set up alerts for failed migrations in your monitoring system to catch P3009 errors early.

Frequently asked questions

What does "P3009: migrate found failed migrations in the target database" mean?
The P3009 error in Prisma indicates that the migration engine has detected one or more failed migration attempts in your target database. This is a safety mechanism that prevents potentially destructive operations when there are unresolved migration failures. When Prisma runs migrations, it tracks their execution in a special _prisma_migrations table.
How do I fix "P3009: migrate found failed migrations in the target database"?
The Prisma P3009 error occurs when Prisma detects previously failed migration attempts in your database. This prevents new migrations from running until the failed migrations are resolved. The fix involves identifying and resolving the failed migration state, either by fixing the migration or resetting the migration history.
What causes "P3009: migrate found failed migrations in the target database"?
A previous migration failed due to SQL syntax errors or invalid operations Database constraints or permissions prevented migration execution Network timeout or connection loss during migration execution
Is the "P3009: migrate found failed migrations in the target database" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading