db-migration-testing

name

db-migration-testing

description

Use this skill when writing, reviewing, or planning database migration tests and data integrity checks. Covers migration safety, rollback testing, referential integrity, constraint validation, production safety checklists, destructive operation safeguards, seed data patterns, and cleanup strategies. Trigger when the user mentions database migrations, schema changes, data integrity, rollback testing, or migration safety.

Database & Migration Testing

A reference for GitHub Copilot to generate database migration tests and data integrity checks.

---

Migration Safety

Every schema migration should be tested for both application and rollback safety.

What to check:

• Migration runs successfully on an empty database
• Migration runs successfully on a database with existing data
• Rollback (down migration) restores the previous schema without data loss
• Migration is idempotent — running it twice doesn't fail or corrupt data
• Migration works within a transaction (if the database supports transactional DDL)

Example test pattern:

describe('migration: add-user-preferences', () => {
  test('applies cleanly to existing database', async () => {
    await seedDatabase(existingData);
    await runMigration('add-user-preferences');
    // Verify new columns exist
    // Verify existing data is preserved
    // Verify default values are applied
  });

  test('rollback restores previous schema', async () => {
    await runMigration('add-user-preferences');
    await rollbackMigration('add-user-preferences');
    // Verify removed columns are gone
    // Verify existing data is intact
  });
});

---

Data Integrity

Referential Integrity

• Foreign key constraints are enforced (cascading deletes, restrict, set null)
• Orphaned records cannot be created
• Deleting a parent record handles children correctly
• Circular references are handled or prevented

Constraint Validation

• NOT NULL constraints on required fields
• UNIQUE constraints on fields that must be unique (email, username, slug)
• CHECK constraints for domain validation (positive numbers, valid enums, date ranges)
• Default values are applied correctly for new and existing rows

Data Type Safety

• Column types match the application's data model
• Numeric precision is sufficient (e.g., DECIMAL for currency, not FLOAT)
• String length limits align with application validation
• Date/timestamp columns use appropriate timezone handling (UTC storage)
• JSON/JSONB columns have consistent structure

---

Migration Patterns to Test

Adding a Column

• New column has a sensible default for existing rows
• Existing queries still work without specifying the new column
• NOT NULL with a default doesn't lock large tables (use backfill strategy on large datasets)

Removing a Column

• Application code no longer references the column
• Column removal doesn't break views, triggers, or stored procedures
• Data backup exists if the column contained important data

Renaming a Column or Table

• All application queries, ORM mappings, and API references are updated
• Consider a two-phase migration: add new → copy data → remove old

Changing a Column Type

• Existing data can be cast to the new type without loss
• Edge cases handled: null values, empty strings, boundary values
• Indexes on the column are rebuilt if needed

Adding an Index

• Index creation doesn't lock the table (use CREATE INDEX CONCURRENTLY for Postgres)
• Query performance improves as expected (check EXPLAIN plan)
• Index doesn't significantly impact write performance

---

⚠️ Production Safety

Migrations that modify or delete production data require extra safeguards. Treat any destructive operation as high-risk.

Destructive Operations Checklist

Before deploying a migration that runs DROP, DELETE, TRUNCATE, removes a column, or changes a column type:

• Backup verified — automated backup ran successfully before migration
• Backup tested — restore from backup has been tested recently (not just "backups are configured")
• Dry run completed — migration tested against a copy of production data, not just an empty or seed database
• Rollback plan documented — step-by-step recovery if the migration fails mid-execution
• Data export — affected data exported or archived before destructive changes
• Staged rollout — migration applied to staging with production-like data before deploying to production
• Downtime window — if the migration locks tables, schedule during low-traffic period
• Monitoring in place — alerts configured for error rate, response time, and database health during deploy

Dangerous Patterns to Flag

Operation	Risk	Safeguard
DROP TABLE	Permanent data loss	Export data first, keep backup for 30+ days
DROP COLUMN	Data removed from all rows	Add a data migration to archive values before removing
ALTER COLUMN TYPE	Data truncation or cast failure	Test with production data — what happens to edge case values?
DELETE FROM without WHERE	Entire table wiped	Always require a WHERE clause in migration scripts
TRUNCATE	All rows removed, no rollback	Use DELETE with transaction if rollback is needed
Renaming a column	Application breaks if code still references old name	Two-phase: add new column → migrate data → drop old column
NOT NULL on existing column	Fails if any row has NULL	Backfill NULL values before adding constraint

Production Data Validation

After a migration runs in production:

• Verify row counts haven't changed unexpectedly
• Spot-check data in affected tables (sample 10–20 rows)
• Verify application behavior with the new schema (smoke tests)
• Monitor error logs for the first 30 minutes after deploy
• Confirm rollback migration still works against the new state

---

Seed Data & Test Fixtures

Best practices:

• Use factories to generate test data with realistic values
• Seed data should cover: typical rows, edge cases (max length, special characters), and minimal data
• Each test should set up its own data — don't rely on shared seed data across tests
• Clean up test data after each run (see cleanup strategies below)
• Test with realistic data volumes for performance-sensitive queries

---

Cleanup Strategies

Test data must be cleaned up reliably to prevent leakage between tests and avoid polluting shared databases.

Transaction Rollback (Recommended)

Wrap each test in a transaction and roll it back at the end. Fastest and most reliable.

beforeEach(async () => {
  await db.query('BEGIN');
});

afterEach(async () => {
  await db.query('ROLLBACK');
});

Pros: Zero residual data, fast, no cleanup logic needed Cons: Doesn't work if the test itself needs to commit (e.g., testing transaction behavior)

Truncation

Truncate affected tables after each test or test suite.

afterEach(async () => {
  await db.query('TRUNCATE users, orders, sessions CASCADE');
});

Pros: Simple, works even when tests commit Cons: Slower than rollback, must list all tables, can break foreign key constraints without CASCADE

Isolated Test Database

Spin up a fresh database per test run using Docker.

# docker-compose.test.yml
services:
  test-db:
    image: postgres:16
    environment:
      POSTGRES_DB: test
    tmpfs: /var/lib/postgresql/data  # RAM-backed for speed

Pros: Complete isolation, no cleanup needed, mirrors production database engine Cons: Slower startup, requires Docker in CI

Tagged Test Data

Prefix test data with a unique identifier and delete by tag after the run.

const testId = `test-${randomUUID()}`;
// Create data with testId prefix
// Cleanup: DELETE FROM users WHERE email LIKE '${testId}%'

Pros: Works in shared databases where you can't truncate Cons: Requires discipline, cleanup can miss records if test crashes

---

Common Anti-Patterns

Anti-Pattern	Why It's a Problem	Fix
No rollback migration	Can't recover from a bad deploy	Always write down migrations
Destructive migration without backfill	Data loss on deploy	Add data migration step before schema change
Testing only against empty database	Misses data-dependent failures	Seed realistic data before running migrations
Migration depends on application code	Breaks when app code changes later	Migrations should be self-contained SQL
Hardcoded IDs in seed data	Conflicts in parallel test runs	Use UUIDs or sequences
No migration ordering test	Migrations may run out of order	Test that all migrations apply sequentially from scratch

---

Best Practices

• Test migrations in CI — run all migrations from scratch on every build
• Test against a real database — SQLite behaves differently from Postgres/MySQL; test against what production uses
• Version your schema — use a migration tool that tracks applied migrations (Prisma Migrate, Flyway, Knex, etc.)
• Monitor migration duration — flag migrations that lock tables or take longer than a threshold
• Back up before deploying — ensure automated backups run before migration in production pipelines