Ephemeral databases in containers for integration tests

Goal

Run data-access and integration tests against the real database engine, started fresh for the run, so that tests catch dialect, constraint and transaction behaviour that an in-memory substitute or a mocked repository hides.

Prerequisites

A container runtime on developer machines and CI; migrations that can be applied from scratch by one command; tests that read connection settings from configuration rather than assuming a fixed port or an existing database.

Steps

1. Start the engine once per test run, not per test. Testcontainers describes itself as providing lightweight, throwaway instances of databases in Docker containers, started from test code and removed afterwards; the plain alternative is docker run with a random published port read back from the runtime.
2. Put the data directory on a tmpfs mount (--tmpfs with docker run) and relax the engine's durability settings for non-production use, since the data is disposable.
3. Apply migrations once to a template database. The PostgreSQL documentation states that CREATE DATABASE works by copying an existing database, so CREATE DATABASE t_42 TEMPLATE t_base gives each test file a fresh copy without re-running migrations. The source database must have no other connections while it is copied.
4. Choose one isolation strategy and apply it everywhere: a fresh database per test file, a transaction per test that is rolled back, or truncation of the tables a test touched.
5. Generate connection settings from the running container and pass them through the same configuration path the application uses in production, so the test also covers configuration handling.
6. Pin the image tag to the production major version and use the identical setup locally and in CI.

Expected result

Tests exercise the real query planner, constraints, isolation levels and extension functions; a green run means the schema and the queries agree, and a migration that breaks a query fails before deployment.

Limits and test basis

Startup adds seconds to every run, so keep pure unit tests in a separate, faster suite. Testcontainers documents reuse of a container across runs as an experimental opt-in feature that is not suited for CI usage. A rolled-back transaction cannot test code that commits, uses several connections or relies on triggers that fire at commit. A managed cloud database may differ from the image in extensions and parameters; this method tests the engine, not the managed service. No timings are claimed.