How to fix P3018: A migration failed to apply. New migrations cannot be applied before the error is recovered from. in Prisma

First, query the _prisma_migrations table to identify which migration failed and read the error details:

SELECT migration_name, logs, finished_at, rolled_back_at
FROM _prisma_migrations
WHERE finished_at IS NULL OR rolled_back_at IS NOT NULL
ORDER BY started_at DESC;

The logs column contains the actual database error that caused the failure. Read this carefully to understand what went wrong. Common errors include syntax issues, constraint violations, or missing database objects.

You can also check your Prisma CLI output - it typically displays the error message when the migration fails.

Locate the failed migration in your prisma/migrations/ directory. The migration name from the previous step corresponds to a timestamped folder.

Open the migration.sql file and review it for:
- SQL syntax errors
- Operations that conflict with existing data (e.g., adding NOT NULL without a default)
- References to non-existent tables or columns
- Constraint definitions that existing data violates

If you find an error in the migration file itself, fix it directly in the file. Note that you should only edit migrations that haven't been successfully applied to any environment yet.

Based on the error in the logs, address the underlying issue:

For data constraint violations:

-- Example: Clean up duplicate data before making field unique
UPDATE users SET email = CONCAT(email, '-', id)
WHERE id NOT IN (
  SELECT MIN(id) FROM users GROUP BY email
);

For missing database objects:

-- Example: Create missing extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

For NOT NULL violations:

-- Example: Populate NULL values before adding constraint
UPDATE products SET category = 'uncategorized' WHERE category IS NULL;

The goal is to make your database compatible with the migration's requirements.

If the migration partially executed before failing, you need to manually complete the remaining steps. Carefully review the migration file and identify which statements succeeded and which failed.

Execute the remaining SQL statements manually against your database:

# Connect to your database
psql $DATABASE_URL

# Or for MySQL
mysql -h host -u user -p database

Then run the SQL statements that weren't executed. Be extremely careful to match the migration file exactly - any deviation can cause discrepancies between environments.

For production environments:

If you manually completed the migration, mark it as applied:

npx prisma migrate resolve --applied "migration_name"

This updates the _prisma_migrations table to record the migration as successful without re-executing the SQL.

For development environments:

The easier approach is to reset and reapply:

# Reset database (WARNING: deletes all data)
npx prisma migrate reset

# Or mark as rolled back and fix
npx prisma migrate resolve --rolled-back "migration_name"

After marking as rolled back, fix the migration file and run:

npx prisma migrate dev

After resolving, confirm all migrations are recorded correctly:

npx prisma migrate status

This should show all migrations as applied with no failures. You can also query the _prisma_migrations table again:

SELECT migration_name, finished_at, rolled_back_at
FROM _prisma_migrations
ORDER BY started_at DESC
LIMIT 10;

All migrations should have a finished_at timestamp and NULL for rolled_back_at. If everything looks correct, you can now apply new migrations.