How to fix P3012: Migration {migration_name} cannot be rolled back because it is not in a failed state. in Prisma

First, verify the state of your migrations in the database:

npx prisma migrate status

Look for the migration in question and note its status. The --rolled-back flag only works with migrations showing a "failed" status.

You can also directly query the migration history table:

SELECT migration_name, finished_at, rolled_back_at, applied_steps_count
FROM _prisma_migrations
ORDER BY finished_at DESC;

If the migration shows as successfully applied (has a finished_at timestamp and no errors), you cannot use --rolled-back.

If the migration was successfully applied but you need to undo its changes, create a new migration that reverses the changes:

# Make changes to your schema.prisma to undo the unwanted changes
# Then create a new migration
npx prisma migrate dev --name revert_previous_changes

This is the recommended approach for production databases. Never manually modify successfully applied migrations.

Alternatively, for development environments, you can use prisma migrate diff to generate a down migration:

# Generate SQL to revert changes
npx prisma migrate diff \
  --from-schema-datamodel prisma/schema.prisma \
  --to-schema-datasource prisma/schema.prisma \
  --script > down.sql

If you have a migration that genuinely failed during application (showing failed status), first manually roll back any partial changes it made to the database, then mark it as rolled back:

# Manually revert any partial database changes from the failed migration
# Then mark it as rolled back
npx prisma migrate resolve --rolled-back "20231215_migration_name"

After marking as rolled back, you can fix the migration file and reapply:

npx prisma migrate deploy

For local development databases where data loss is acceptable:

# Reset database and reapply all migrations from scratch
npx prisma migrate reset

This will:
1. Drop the database
2. Create a new database
3. Apply all migrations from the migrations folder
4. Run seed scripts if configured

Warning: This deletes all data. Never use this in production or staging environments.

If you previously used --applied to mark a migration that shouldn't have been marked as applied:

# Manually update the _prisma_migrations table to remove the migration record
# Then you can reapply it properly

Direct SQL approach (use with caution):

DELETE FROM _prisma_migrations
WHERE migration_name = '20231215_migration_name';

Then run:

npx prisma migrate deploy

This approach should only be used when you're certain the migration was never actually applied to the database despite being marked as such.