Integration Tests with Testcontainers

Why the Database Layer Needed Real Postgres

Unit tests were enough for validation rules and orchestration, but they were the wrong tool for the repository layer. Fieldwork relies on Postgres features that matter in production: tenant-scoped uniqueness, transactional updates, cursor pagination queries, and the exact behavior of constraints under concurrent writes. Mocking pgx calls would only prove that my mock expected the SQL I already wrote. It would not prove the SQL actually behaves correctly.

That is why repository tests run against a real disposable Postgres instance. I considered a long-lived shared developer database because it would be simpler to point tests at one host and go. I rejected it because shared state turns test failure into folklore: maybe someone else changed the schema, maybe yesterday's rows are still there, maybe this machine has a different extension installed. Reproducibility matters more than shaving a few container startup seconds.

What Testcontainers Buys over Shared Dev Databases

Testcontainers gives us isolation without maintaining a separate testing environment. Each run starts a real Postgres container, waits until it is ready, applies migrations, and hands the test suite a fresh DSN. That means the test process controls the whole lifecycle: schema version, credentials, cleanup, and startup order. No hidden prerequisites. No wiki page that says run these three commands before the tests are trustworthy.

I chose Testcontainers over sqlite-for-tests for a more concrete reason than purity. Fieldwork already depends on Postgres behavior, including UUID handling, timestamp comparisons, transactional semantics, and index behavior. A substitute engine would create a false sense of safety by passing queries that only look portable. If production is Postgres, the integration harness should be Postgres too.

Building a Repeatable Postgres Test Harness

The harness shape in Fieldwork is intentionally centralized. I do not want every repository package re-teaching itself how to start a container, wait for readiness, and run migrations. A shared test helper starts Postgres 16, returns a pgx pool, and tears the container down through t.Cleanup. The migrations run exactly the same way they do in development, because schema drift between environments is one of the easiest ways to write tests that lie.

I also prefer creating seed data through helper functions rather than raw fixture dumps. Fieldwork's schema is multi-tenant, so rows usually need tenants, workspaces, projects, and memberships in the right order. A small builder API in tests keeps those relationships explicit. The test should still show which tenant owns which records instead of hiding everything inside one giant SQL file.

Testing Real Repository Behavior

Once the harness exists, the repository tests should focus on behaviors the database actually controls. For the tasks repository, that means tenant scoping, pagination order, transactional updates, and conflict handling. A good repository integration test is not just create row, get row. It asserts the invariants that would be painful to debug after release: a tenant cannot see another tenant's tasks, an archived task no longer appears in active listings, and a unique constraint fails in the way the service layer expects.

Because Fieldwork is multi-tenant, I also seed at least two tenants in many repository tests. That catches the subtle class of bugs where a WHERE tenant_id = $1 predicate is missing from one branch of a query. Those bugs are easy to miss with single-tenant fixtures and expensive to explain once customer data crosses boundaries.

Where Integration Tests Stop

I still resist the urge to move every test into the integration bucket. Testcontainers are worth their startup cost when SQL, constraints, or migrations are the thing being verified. They are overkill for pure validation branches or for testing whether a handler writes the right JSON error envelope. The harness should prove the risky storage behavior, not become a mandatory tax on every package.

So Fieldwork uses both layers on purpose. Unit tests keep the inner loop fast. Integration tests with Testcontainers validate the places where mocks would lie, especially the repository layer and transaction-sensitive auth flows like refresh token rotation. That split is not academic. It is the only way to get both trust and iteration speed without turning the test suite into either theater or sludge.