Database Testing

Test against the same database engine and major version that production runs. In-memory substitutes (SQLite standing in for PostgreSQL, H2 for MySQL, ORM in-memory providers) accept queries production rejects and reject ones it accepts, and they don't enforce the same constraints, types or locking. Tests that pass on them prove little about the real system.

When to use

• Writing integration tests for repositories, queries, ORM mappings or database logic.
• Moving off an in-memory or SQLite test database.
• Database tests are slow, flaky, or fail when run in a different order or in parallel.
• Adding a safety net for schema migrations.

Not for: unit tests of pure logic (no database at all; see test-strategy), load testing, or tuning production queries (sql-optimization).

Workflow

1. Match production: same engine, same major version, same extensions. Pin the image tag (for example postgres:16-alpine if production is PostgreSQL 16).
2. Provide the database with Testcontainers in the test code, or a service container in CI. Examples per stack: references/setup-examples.md.
3. Build the schema with the real migrations, not the ORM's "create all tables" shortcut, so the tests also prove the migrations work.
4. Choose an isolation strategy (table below) so every test starts from a known state and tests can run in any order.
5. Create data per test with factories, only what the test needs.
6. Add migration tests and query-count assertions for risky paths.
7. Add a guard so the test suite refuses to run against anything but a local or test database.

Providing a real database

Speed settings for throwaway test PostgreSQL only (they trade crash safety for speed): start the server with -c fsync=off -c synchronous_commit=off -c full_page_writes=off, and optionally keep the data directory on tmpfs. Never use these settings on any database that holds real data.

Isolation strategies

Default: rollback per test for repository-level tests, truncate or template for tests that exercise code which commits, plus one database per worker when running in parallel.

Test data

• Factories or builders per test (for example factory_boy, FactoryBot, Bogus, or plain builder functions). Each test creates exactly the rows it asserts on, with defaults for everything else.
• Small, named reference data (countries, plans) can be seeded once by a migration or a seed script that is part of the test setup.
• No production dumps. They contain personal data (see data-privacy-retention), are slow to load, and make tests depend on data nobody understands.
• Don't depend on generated IDs, row order without ORDER BY, or the current time. Use the IDs returned by the insert, add explicit ordering, and inject a fixed clock.

Migration tests

1. From empty: apply every migration to an empty database and run the suite. Then check that the ORM's model and the migrated schema agree, using the tool's check command where it exists (for example python manage.py makemigrations --check --dry-run, alembic check, prisma migrate diff; check the docs for your tool).
2. Upgrade from the last release: restore the previous release's schema (and a little representative data), apply only the new migrations, and run smoke tests. This catches migrations that only work on an empty database.
3. Down migrations, if the project uses them: run up, down, up again.
4. Data migrations and backfills: test them with edge-case rows (NULLs, empty strings, duplicates, very long values, legacy formats), and assert the result, not only "no error".
5. Slow migrations: test duration and locking on a production-sized copy outside the unit test suite (see the database-migrations skill).

What to test in the database itself

• Constraints: insert a duplicate and assert the unique violation; insert an orphan and assert the foreign key error; check CHECK constraints at their edges.
• Triggers, functions, row-level security: test as the role the application uses, with tenant settings applied exactly as the application sets them (for example SET LOCAL app.tenant_id = '<tenant uuid>' inside the test transaction, matching the policy in the database-schema-design skill), and assert that other tenants' rows are invisible.
• Tricky queries: time zones and daylight saving boundaries, NULL handling, pagination stability with ties, case sensitivity and collation.
• Concurrency: open two connections and assert that the lock or optimistic version check behaves as designed (two transactions decrementing the same stock must not both succeed).
• Query counts: assert the number of statements a code path runs, to stop N+1 regressions (see orm-patterns).

Safety guard

Add a check that runs before any test touches the database and fails the run unless the target is clearly a test database, for example:

• the host is localhost, a container host, or a CI service name, and
• the database name ends in _test, and
• an explicit environment variable such as APP_ENV=test is set.

Tests truncate and drop tables. Never point a test suite at a shared, staging or production database, and stop and ask the user if the configured database looks like one.

Anti-patterns

• In-memory or SQLite substitutes for a different production engine.
• Schema built by the ORM's "create all" instead of the migrations.
• One giant shared fixture that every test depends on and nobody can change.
• Tests that depend on each other or on run order (test B relies on rows from test A).
• Asserting on auto-increment IDs or unordered result order.
• Restarting the database container for every test.
• Sleeping to wait for the database instead of a readiness check (Testcontainers wait strategies, pg_isready, CI health checks).

Done when

• Tests run against the production engine and major version, with the schema built from migrations.
• Each test creates its own data and passes in any order, alone, and in parallel.
• Migrations are tested from empty and as an upgrade from the previous release.
• Constraints, permissions and critical queries have direct tests; key paths have query-count assertions.
• The suite refuses to run against non-test databases.
• The database portion of the suite runs fast enough that developers run it before pushing.

References

• references/setup-examples.md — read when wiring up the database for tests: Testcontainers in Java/Spring, Python, Node, .NET and Go, a GitHub Actions service container, and template-database and truncate helpers.