Database migrations tested on staging or test database before production

Applying untested database migrations directly to production risks table corruption, constraint violations, or data loss that cannot be easily undone. NIST CP-9 and SOC 2 A1.2 require verified recovery procedures for data; a migration that drops a column or alters a type without pre-testing can trigger application errors for every user and may require hours of manual data recovery. ISO 25010 reliability.recoverability demands that any data-modifying operation be validated before it reaches the primary store.

Run migrations against a test or staging database as a CI/CD step before the production deployment step. For Prisma:

# .github/workflows/test.yml
- name: Test migrations on staging DB
  run: |
    export DATABASE_URL="postgres://test:test@localhost:5432/test_db"
    npx prisma migrate deploy

Document the rollback path in DEPLOYMENT.md:

## Migration Rollback
1. Check migration status: `npx prisma migrate status`
2. Mark as rolled back: `npx prisma migrate resolve --rolled-back <migration-name>`
3. Fix the migration file, then re-deploy: `npx prisma migrate deploy`

For destructive migrations (column drops, type changes), take a point-in-time snapshot immediately before the production deployment step.

• ID: migration-testing

• Severity: high

• What to look for: Enumerate every relevant item. Look for migration files (migrations/ directory, prisma/migrations/, drizzle/, Alembic), and check if CI/CD pipeline includes a step that tests migrations on a test or staging database before production. Look for evidence of dry-run or rollback testing in workflows.

• Pass criteria: At least 1 of the following conditions is met. Database migrations are tested before production deployment — either on a staging database as part of CI/CD, or on a test database, or both. Rollback plan is documented.

• Fail criteria: No migrations found, or migrations are applied directly to production without pre-testing. No test or staging database is used for migration validation.

• Skip (N/A) when: The project has no database or all data is ephemeral (e.g., cache-only).

• Detail on fail: "No database migrations found in the project." or "Migrations exist but CI/CD pipeline does not include migration testing step. Migrations are applied directly to production."

• Remediation: Add migration testing to your CI/CD pipeline. For Prisma:

  # .github/workflows/test.yml
  - name: Test migrations
    run: |
      export DATABASE_URL="postgres://test:test@localhost:5432/test"
      npx prisma migrate deploy  # Test against test database
  

  Document rollback in your DEPLOYMENT.md:

  ## Migration Rollback
  1. Identify the failed migration: `npx prisma migrate status`
  2. Rollback: `npx prisma migrate resolve --rolled-back <migration-name>`
  3. Fix the migration file
  4. Re-run: `npx prisma migrate deploy`