Stop mocking your database for integration tests

Why this matters: Mocking database drivers in application tests creates a false sense of security that ignores SQL syntax errors, constraint violations, and transaction behavior. Using transient, containerized Postgres instances ensures your integration tests run against the same engine as production, catching schema-level failures before they reach your CI/CD pipeline.

Most data engineers start by mocking psycopg2 or SQLAlchemy calls. This is a trap. You end up testing your ability to mock a library rather than the integrity of your queries. The alternative—a dedicated "test" database—is often a fragile, manually managed instance that drifts from the production schema, leading to "works on my machine" failures.

The robust approach is to treat your database as a disposable, versioned component of your testing lifecycle.

How it actually works

The goal is to spin up a clean, ephemeral Postgres instance for every test run, apply migrations, execute your test suite, and destroy the container immediately after.

We use docker-compose to handle the lifecycle. This ensures that the network stack, environment variables, and volumes are consistent across local development and CI environments like GitHub Actions.

Here is a minimal docker-compose.yml configured for test automation:

services:
  postgres-test:
    image: postgres:16.3-alpine
    environment:
      POSTGRES_USER: test_user
      POSTGRES_PASSWORD: test_password
      POSTGRES_DB: test_db
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U test_user -d test_db"]
      interval: 5s
      timeout: 5s
      retries: 5
    ports:
      - "5432:5432"
    tmpfs:
      - /var/lib/postgresql/data

Understanding the configuration

• tmpfs: This is critical. By mounting the data directory to tmpfs (RAM), we bypass disk I/O latency. Since the database is disposable, we don't care about persistence after the container exits.
• healthcheck: Never assume a container is "running" just because the process started. pg_isready ensures the application code only attempts to connect once the Postgres socket is actually accepting signals.
• image: Pinned to a specific digest or tag. Never use latest, as a minor update to the Postgres image could change default collation settings or extensions, breaking your tests unpredictably.

Automating schema and seeding

Your test runner should handle the migration logic. Using a tool like alembic (for Python) or flyway (for JVM), your test initialization script should look like this:

# 1. Start the container
docker compose up -d postgres-test

# 2. Wait for the healthcheck to pass
until docker inspect -f '{{.State.Health.Status}}' postgres-test | grep -q "healthy"; do
  sleep 1
done

# 3. Run migrations
alembic upgrade head

# 4. Run your test suite
pytest tests/integration/

# 5. Teardown
docker compose down -v

The -v flag in the teardown command is vital; it removes the anonymous volumes created by the container, ensuring the next test run starts from a truly blank slate.

Photo by Daniil Komov on Unsplash

Design choices and tradeoffs

The biggest downside of this pattern is CI latency. Spinning up a container, waiting for the OS to boot, and running migrations takes time. If you have hundreds of small integration tests, running this lifecycle for every single test is inefficient.

I recommend a test-suite-level teardown rather than a test-level one. Group your integration tests together so the container boots once, processes all tests, and shuts down at the end.

Another consideration is security. Running containers in CI requires careful management of the Docker daemon. If your CI runner is shared, ensure you are not running the container as root. While the Postgres image defaults to postgres user, if you extend the image, ensure your Dockerfile includes:

FROM postgres:16.3-alpine
# Create a dedicated user for the app
RUN addgroup -S appgroup && adduser -S appuser -G appgroup
USER appuser

Avoid mounting local directories into the container during testing. It introduces dependency on the host's file system structure. Keep your migrations inside the container image or copy them in during the build phase.

When to reach for it (and when not to)

Use ephemeral containers when:

• You are performing complex joins or window functions where the SQL dialect nuances matter.
• You need to verify that your migrations actually execute without conflict.
• Your application uses specific Postgres extensions (e.g., pg_trgm or postgis) that are difficult to mock.

Do not use ephemeral containers when:

• You are testing pure business logic that happens in memory. Those should be fast, unit-level tests.
• The database is massive. If your "seed" data is gigabytes in size, the time taken to restore that state into a new container will turn your CI pipeline into a bottleneck. In such cases, consider using a permanent, shared test database and use transaction-level rollbacks to clean up individual test states.

Conclusion

Mocking databases is a legacy habit from an era when spinning up infrastructure was slow and painful. Today, Docker handles the isolation and orchestration for us. By treating your database container as a disposable, health-checked component, you bridge the gap between development and production, effectively turning your test suite into a real-world validation of your schema and query logic.

Start by isolating your database interactions into a controlled container lifecycle. Your future self, debugging a production migration failure that you already caught in CI, will thank you.

Cover photo by Kier in Sight Archives on Unsplash.

---

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.