# Database migrations tested on staging or test database before production

- **Pattern:** `ab-000975` (`deployment-readiness.ci-cd-pipeline.migration-testing`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000975
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000975)

## Why it matters

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.

## Severity rationale

High because a failed migration in production can corrupt live data or take down the application for all users, with recovery time measured in hours if no pre-tested rollback path exists.

## Remediation

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

```yaml
# .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`:

```markdown
## 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.

## Detection

- **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:

  ```yaml
  # .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:

  ```markdown
  ## 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`
  ```

---

## External references

- iso-25010 reliability.recoverability
- nist CP-9
- soc2 A1.2

Taxons: operational-readiness, data-integrity

HTML version: https://auditbuffet.com/patterns/ab-000975