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

# 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

# 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

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

# 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

# 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

# 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"

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

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

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

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