Database Testing Strategies
Database testing validates that data is stored, retrieved, and manipulated correctly — and that database operations perform adequately under load. It is often underemphasised in application testing but is critical: data corruption or loss is often irreversible, and database performance problems are a common production failure mode.
What to Test
- CRUD operations: Create, read, update, and delete operations produce correct results
- Constraints and validation: NOT NULL, UNIQUE, FOREIGN KEY constraints function correctly and are enforced
- Transactions: Operations that should be atomic are — partial failures roll back completely
- Migrations: Schema migrations apply correctly and don't lose data
- Query performance: Critical queries execute within acceptable time under representative data volumes
Test Database Strategies
- In-memory databases: SQLite in-memory (or H2 for Java) for fast unit-style database tests. Caveat: behaviour may differ from production database.
- Docker containers: Run the same database engine as production in Docker for integration tests — Testcontainers library manages this automatically
- Migration testing: Apply migrations to a copy of the production schema structure and verify they succeed before applying to production
Data Integrity Checks
Beyond unit tests, periodic automated data integrity checks in production catch issues that application logic didn't prevent: orphaned records, constraint violations, unexpected nulls, referential integrity failures. These are defensive checks rather than tests but are valuable for production data health.