Ask a coding agent for tests and you will often get a wall of mocks: a fake session, a patched query, an assertion that the mock was called with the expected arguments. They go green fast. They also tell you almost nothing about whether your SQL works. This post sets up the alternative: a real Postgres started per test run with Testcontainers, a rule file that tells the agent to use it, and a checklist for deciding which mock-heavy tests to delete.
Why mocks mislead here
A mocked database returns whatever the test author (or the agent) told it to return. Unique constraints, ON CONFLICT behaviour, transaction semantics and type coercion are never exercised, because the thing that implements them is not there. The Testcontainers getting-started guide makes the same point about substitutes in general: it notes that in-memory services may not have all the features of your production service and behave slightly differently. Its pitch is to run the real dependency, such as a database or message broker, in a Docker container, so tests talk to the same kind of service you run in production.
The same guide lists what you get along the way: each test run gets its own isolated services, container ports are mapped to random free host ports, built-in wait strategies make sure the container is ready before tests start, and a Ryuk sidecar removes containers, volumes and networks afterwards, even if the process exits abnormally. You need a Docker-API-compatible runtime (Docker Desktop, Docker Engine on Linux, or Testcontainers Cloud).
Setup (Python, pytest, Postgres)
Testcontainers exists for Java, .NET, Go, Node.js, Python, Rust and more. I use Python here; the idea carries over. Install the Postgres module plus a driver. The testcontainers-python docs point out that the extra does not include the database driver, so you declare your own:
pip install "testcontainers[postgres]" sqlalchemy psycopg2-binary pytest
The module is testcontainers.postgres.PostgresContainer. Its constructor takes an image (default postgres:latest), and get_connection_url() returns a SQLAlchemy-style URL (the default driver is psycopg2; pass driver=None for a URL without one). Pin the image to the major version you run in production.
The code under test
A tiny repository with the kind of SQL mocks cannot check:
# app/users.py
import sqlalchemy as sa
SCHEMA = """
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL
)
"""
def upsert_user(conn, email: str, name: str) -> int:
row = conn.execute(
sa.text(
"INSERT INTO users (email, name) VALUES (:email, :name) "
"ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name "
"RETURNING id"
),
{"email": email, "name": name},
).one()
return row[0]
def count_users(conn) -> int:
return conn.execute(sa.text("SELECT count(*) FROM users")).scalar_one()
One container per test session, one rollback per test
Starting a container takes time, so start it once per session and keep tests isolated with a transaction that is rolled back after each test. The container is started explicitly and stopped in a finally block, so it stays up for the whole session and is always torn down:
# tests/conftest.py
import pytest
import sqlalchemy as sa
from testcontainers.postgres import PostgresContainer
from app.users import SCHEMA
@pytest.fixture(scope="session")
def engine():
pg = PostgresContainer("postgres:16")
pg.start()
try:
eng = sa.create_engine(pg.get_connection_url())
with eng.begin() as conn:
conn.execute(sa.text(SCHEMA))
yield eng
eng.dispose()
finally:
pg.stop()
@pytest.fixture()
def conn(engine):
with engine.connect() as connection:
tx = connection.begin()
yield connection
tx.rollback()
Tests that can actually fail
# tests/test_users.py
import pytest
import sqlalchemy as sa
from sqlalchemy.exc import IntegrityError
from app.users import count_users, upsert_user
def test_upsert_updates_existing_row(conn):
first = upsert_user(conn, "a@example.com", "Ann")
second = upsert_user(conn, "a@example.com", "Anna")
assert first == second
assert count_users(conn) == 1
name = conn.execute(
sa.text("SELECT name FROM users WHERE id = :id"), {"id": first}
).scalar_one()
assert name == "Anna"
def test_email_is_unique(conn):
conn.execute(sa.text("INSERT INTO users (email, name) VALUES ('b@example.com', 'Bob')"))
with pytest.raises(IntegrityError):
conn.execute(sa.text("INSERT INTO users (email, name) VALUES ('b@example.com', 'Bobby')"))
pytest -q
Break the ON CONFLICT clause and the first test fails, because Postgres says so. A mock of conn.execute would have stayed green.
Tell the agent to use it
Agents follow the instructions in the repo. Put the rule where your tool reads project guidance (CLAUDE.md, AGENTS.md or similar):
## Testing
- Code that touches the database is tested against real Postgres via the
`conn` fixture in tests/conftest.py (Testcontainers). Do not mock the
database, sessions or cursors.
- Mock only things that cross a network boundary we do not own
(payment APIs, email providers).
- A new query needs a test that would fail if the SQL were wrong.
- Run `pytest -q` before reporting a task as done. Docker must be running.
The last line matters: if the agent cannot reach Docker, it should say so rather than quietly fall back to mocks.
