test-database-seeding

Test Database Seeding

Overview

Automated tests need a database that is real (same engine as production), isolated (no shared state), and seeded with known data. The answer is a disposable, per-run database — Testcontainers spins up a real Postgres in a container, migrated and seeded, torn down after.

Core principle: Real engine, disposable instance, known seed. Never point tests at a shared dev or production database, and never use an in-memory substitute whose SQL differs from production.

Why not the alternatives

The Pattern

1. Start a real DB container for the suite (Testcontainers): postgres:<same-major-as-prod>.
2. Run the real migrations against it — the same migration files the app uses, so the schema is production-accurate.
3. Seed known fixtures: insert the exact rows the scenario needs (a project, N tasks, a user with a role). Keep fixtures small and explicit — a test asserting "3 tasks" seeds exactly 3.
4. Isolate per test: each test either runs in a transaction rolled back at the end, or truncates/reseeds between tests. No test sees another's data.
5. Point the app/config at the container's connection string for the run.
6. Tear down automatically when the suite ends.

Worked Example

@Testcontainers
class TaskExportIT {
    @Container static PostgreSQLContainer<?> db =
        new PostgreSQLContainer<>("postgres:16");

    @BeforeAll static void migrate() { Flyway.configure().dataSource(db.getJdbcUrl(),
        db.getUsername(), db.getPassword()).load().migrate(); }   // real migrations

    @BeforeEach void seed() {
        // exactly the rows this scenario needs
        insertProject("p1", "Demo");
        insertTasks("p1", 3);
    }

    @Test void export_returns_all_tasks() {
        var csv = export("p1");
        assertEquals(3, csvRows(csv));   // asserts against the known seed
    }
}