Python Testing & Debugging Beyond unittestAdvanced Python testing playbooks
Integration & Data

Starting Postgres with testcontainers-python

A test suite that talks to the real Postgres finds constraint violations, type coercion and isolation behaviour that no in-memory stand-in reproduces. The cost is start-up time, and with a session-scoped container that cost is paid exactly once: roughly two to four seconds with a warm image cache, amortised over however many tests the suite contains.

Prerequisites

  • testcontainers[postgres] >= 4.0 and a reachable Docker daemon.
  • psycopg >= 3.1 or asyncpg, depending on which driver the application uses.
  • pytest >= 8.0, and the lifecycle rules from spinning up services with Testcontainers.

Solution

Python
import pytest
from sqlalchemy import create_engine, text
from testcontainers.postgres import PostgresContainer

POSTGRES_IMAGE = "postgres:16.4-alpine"    # exact tag; match production's major


@pytest.fixture(scope="session")
def postgres():
    container = (
        PostgresContainer(POSTGRES_IMAGE, username="test", password="test", dbname="test")
        # Durability is worthless for a container deleted in minutes, and fsync
        # dominates the runtime of insert-heavy suites.
        .with_command(
            "postgres -c fsync=off -c full_page_writes=off "
            "-c synchronous_commit=off -c max_connections=200"
        )
    )
    with container:                         # __exit__ stops and removes it
        yield container


@pytest.fixture(scope="session")
def postgres_dsn(postgres):
    url = postgres.get_connection_url()     # ephemeral host port, read not assumed

    # One-time preparation, before any test: extensions and roles.
    engine = create_engine(url, isolation_level="AUTOCOMMIT")
    with engine.connect() as conn:
        conn.execute(text("CREATE EXTENSION IF NOT EXISTS pg_trgm"))
        conn.execute(text('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"'))
    engine.dispose()
    return url
Python
def test_trigram_search_ranks_close_matches(db_session):
    db_session.execute(text("INSERT INTO product (name) VALUES ('widget'), ('wodget')"))
    rows = db_session.execute(text(
        "SELECT name FROM product ORDER BY similarity(name, 'widgit') DESC"
    )).scalars().all()
    assert rows[0] == "widget"               # only testable against real Postgres
Session start-up sequence for the Postgres fixture A left-to-right sequence. The pinned image is started with durability settings disabled. The module's wait strategy blocks until Postgres accepts connections. Extensions and roles are created once over an autocommit connection. The fixture then yields a connection URL built from the ephemeral host port, which every test uses. Everything expensive happens once, before the first test start pinned image postgres:16.4-alpine fsync=off wait strategy accepting connections no sleep prepare once extensions, roles, migrations yield the URL Per-test isolation is layered on top by a rolled-back transaction — the container itself is never restarted, cleaned or truncated between tests.
Roughly three seconds for the whole sequence with a warm image cache, paid once per session and shared by every test in it.

Why this works

PostgresContainer wraps the official image, sets the credentials through environment variables, maps port 5432 to an ephemeral host port, and — crucially — ships a wait strategy that polls until the server accepts connections before __enter__ returns. The fixture therefore yields only once the database is genuinely usable, with no sleep anywhere.

The with_command override replaces the image's default command, so every -c flag becomes a server setting for this instance only. fsync=off stops Postgres forcing writes to disk on commit, full_page_writes=off shrinks the write-ahead log, and synchronous_commit=off lets commits return before the WAL is flushed. All three trade durability for speed, which is exactly the right trade for data that will be deleted when the session ends.

Edge cases and failure modes

  • Unpinned tag. postgres:16-alpine moves when upstream publishes a patch release. Pin the full version and upgrade deliberately.
  • Extensions missing. pg_trgm and uuid-ossp ship in the official image's contrib set; PostGIS does not. Use postgis/postgis:16-3.4-alpine or a custom image for anything outside contrib.
  • max_connections exhausted under xdist. Each worker's pool takes connections from the same server. Raise max_connections, or give each worker a small pool.
  • Running tests inside Docker-in-Docker. The container's host is not localhost. get_connection_url() handles this; hand-built URLs do not.
  • Teardown skipped on a crash. The context manager covers normal failures; Ryuk covers SIGKILL. Disabling Ryuk means a crashed run leaves the container behind.

Building the template database once

The template trick above deserves its own fixture, because it turns per-test and per-worker database creation from seconds into milliseconds and it is the step most setups skip.

Python
import pytest
from alembic import command
from alembic.config import Config
from sqlalchemy import create_engine, text


@pytest.fixture(scope="session")
def template_database(postgres):
    base = postgres.get_connection_url()
    admin = create_engine(base, isolation_level="AUTOCOMMIT")

    with admin.connect() as conn:
        conn.execute(text('DROP DATABASE IF EXISTS "template_test"'))
        conn.execute(text('CREATE DATABASE "template_test"'))

    # Run the real migration history into the template, exactly once.
    template_url = base.rsplit("/", 1)[0] + "/template_test"
    cfg = Config("alembic.ini")
    cfg.set_main_option("sqlalchemy.url", template_url)
    command.upgrade(cfg, "head")

    with admin.connect() as conn:
        # Disconnect everything and mark it: templates must have no sessions.
        conn.execute(text(
            "SELECT pg_terminate_backend(pid) FROM pg_stat_activity "
            "WHERE datname = 'template_test' AND pid <> pg_backend_pid()"
        ))
        conn.execute(text("ALTER DATABASE template_test WITH is_template = true"))
    admin.dispose()
    return "template_test"

Two details in that fixture are the ones that make it reliable. CREATE DATABASE … TEMPLATE x fails if any session is connected to x, so terminating stray connections after the migrations is not optional — Alembic's own engine may still hold one. And marking the database as a template prevents anything from connecting to it by accident later, which would make the next copy fail with a confusing "source database is being accessed by other users" error.

One migrated template copied into per-worker databases The migration history runs once into a template database, which is then marked as a template. Each worker's database is created as a file-level copy of it in tens of milliseconds, rather than each worker running the full migration history. Migrate once, copy many times template_test alembic upgrade head once · several seconds test_gw0 — copied in ~40 ms test_gw1 — copied in ~40 ms test_gw2 — copied in ~40 ms versus running the full history in every worker, every run
The copy is a file-level operation inside Postgres, so its cost barely depends on the size of the schema — a hundred tables copy as fast as one.

The same template serves tests that need a genuinely fresh database — migration tests, tests that must commit — through a function-scoped fixture that copies it, uses it, and drops it. At forty milliseconds a copy, "a clean database per test" stops being an extravagance for the few tests that really need one.

Matching production closely enough to matter

A container is only as useful as its resemblance to the database the application actually runs against, and three settings account for most of the divergence that lets bugs through.

Version. Postgres changes behaviour between major versions in ways that matter to applications: MERGE arrived in 15, NULLS NOT DISTINCT in 15, JSON path functions changed across 12 to 16. Pin the test image to production's major version and upgrade both together, in one change, so the suite is the first place a version bump is exercised.

Collation and locale. Sorting and comparison of text depend on the database's collation, and the official image defaults to en_US.utf8 while managed services often use C or a provider-specific ICU collation. A test that asserts on ORDER BY name can pass locally and disagree with production for names containing accents or mixed case. Set it explicitly:

Python
container = PostgresContainer(POSTGRES_IMAGE).with_env(
    "POSTGRES_INITDB_ARGS", "--locale-provider=icu --icu-locale=en-GB"
)

Timezone. The server's TimeZone setting changes how timestamp without time zone values are interpreted and how now() renders. Setting -c timezone=UTC in the command removes a source of tests that fail only on a developer's machine in a different region.

Settings that must match production Three cards. The major version determines available SQL features and function behaviour. The collation determines text ordering and comparison. The server timezone determines how naive timestamps and now() are interpreted. Each mismatch produces tests that pass in the container and disagree with production. Three places a container quietly differs from production major version MERGE, NULLS NOT DISTINCT, JSON path behaviour pin to production's and upgrade together collation ORDER BY on text, accents, mixed case set initdb locale to match explicitly timezone naive timestamps, now(), date_trunc -c timezone=UTC unless production differs
None of these produce an error. They produce a test that agrees with the container and disagrees with production, which is the more expensive kind of wrong.

A short test that reads SHOW server_version, SHOW lc_collate and SHOW timezone and compares them to values recorded from production turns this list into something the suite checks rather than something a reviewer has to remember. It runs in milliseconds, it fails the day someone bumps one image without the other, and its failure message names the exact setting that drifted — which is considerably faster than discovering the collation difference from a customer report about sort order. Record the production values once, in the test file itself, and update them in the same change as any infrastructure upgrade.

One server, one database per worker

Under pytest-xdist every worker runs its own session fixtures, so a naive setup starts one Postgres per worker. That gives total isolation at the cost of several seconds and a few hundred megabytes per worker; the lighter alternative is one server shared by all workers, with a database per worker created on it.

Python
import os

import pytest
from sqlalchemy import create_engine, text


@pytest.fixture(scope="session")
def worker_dsn(postgres):
    worker = os.environ.get("PYTEST_XDIST_WORKER", "gw0")
    name = f"test_{worker}"
    base = postgres.get_connection_url()

    admin = create_engine(base, isolation_level="AUTOCOMMIT")
    with admin.connect() as conn:
        conn.execute(text(f'DROP DATABASE IF EXISTS "{name}"'))
        conn.execute(text(f'CREATE DATABASE "{name}" TEMPLATE template_test'))
    admin.dispose()

    return base.rsplit("/", 1)[0] + f"/{name}"

The TEMPLATE template_test clause is the detail that keeps this fast. Build one database with migrations applied and extensions installed, mark it as a template, and every worker's database becomes a file-level copy of it — tens of milliseconds, versus running the whole migration history again per worker.

But note the catch: this fixture runs in each worker process, and those processes do not share a container unless the container is started outside pytest. In practice that means either starting the container in a CI step before pytest and passing its URL in an environment variable, or accepting one container per worker. Both are legitimate; the choice is between CI configuration and memory.

Frequently Asked Questions

Which Postgres image should the tests use? The same major version as production, pinned to an exact tag. The alpine variants start fastest and are fine unless an extension needs glibc; postgis and timescale have their own images. Never use latest, because the suite's behaviour then depends on when the runner's cache was warmed.

How do I install an extension like pg_trgm or uuid-ossp? Run CREATE EXTENSION IF NOT EXISTS in the session fixture after the container starts, or mount an init script into /docker-entrypoint-initdb.d. Extensions shipped with the contrib package are available in the official image; third-party ones need an image that includes them.

Is it safe to disable fsync in the test container? Yes, and it roughly halves the runtime of insert-heavy suites. The container is deleted when the session ends, so durability has no value. The same setting in any environment holding real data would be negligent.

← Back to Spinning Up Services with Testcontainers