Testing 105: Integration testing with Testcontainers and DuckDB
This is the fifth post in our testing series. Testing 101 drew the map, 102 put unit and end-to-end tests in the agent's loop, 103 checked whether the tests notice bugs, and 104 tested the traits data must keep. This one is about the layer in between: the seams where your code meets a real database, and the mocks that pretend to be one.
A mock is a promise you make to yourself about how the other side behaves. You write repo.available.return_value = Decimal("5"), and from then on the database returns exactly what you expected, in exactly the type you expected, as fast as you like, with no locks, no constraints and no other users. The test passes. It would pass whatever the real database did, because the real database was never asked.
That's the problem with mocking the things you don't own. The bugs at a seam live in the assumptions each side makes about the other, and a mock is made of your assumptions. It can't disagree with you. Integration tests exist to put something on the other side of the seam that can.
For SQL, that has become cheap. A real Postgres in a container starts in seconds. An analytical query can run on DuckDB in-process in milliseconds. And a coding agent can do all of it by itself, which matters, because agents reach for mocks even more often than people do.
Five ways a mock lies about your database
Here is a unit test for stock reservation that every reviewer would approve:
def test_reserve_uses_available_stock(mocker):
repo = mocker.Mock()
repo.available.return_value = Decimal("5")
service = ReservationService(repo)
service.reserve("SKU-42", Decimal("5"))
repo.decrement.assert_called_once_with("SKU-42", Decimal("5"))It's green, and it says nothing about the five ways reservation actually breaks:
- The SQL never runs. A typo in a column name, a query that's valid in SQLite and not in Postgres, a migration that was never applied: none of it is visible through a mock. The mock doesn't parse SQL. It doesn't know there is any.
- Constraints don't exist. The unique index that makes a replayed webhook harmless, the foreign key that stops a line pointing at a deleted order, the
CHECK (on_hand >= 0)that's your last line of defence: a mock enforces none of them, so the code that relies on them is never tested. - There is no concurrency. The code above reads the available stock and then decrements it. Two buyers reserving the last five units at the same moment both read 5, both decrement, and you've promised ten units you don't have. Only a real database, with real transactions and real locks, can show you that, and only if you ask it to.
- Types are whatever you said. The mock returns
Decimal("5"). If a migration created the column asdouble precision, the real driver returns a float, and your money and quantity arithmetic quietly starts rounding. Timestamps with and without time zones, string collations, andNUMERICprecision all have the same problem. - Order and NULLs are wherever you put them. A mock returns rows in the order you listed them. A real query without
ORDER BYreturns them in whatever order the planner likes, which can change when a table grows. And databases don't even agree on where NULLs go: Postgres sorts them last in ascending order, SQLite and MySQL sort them first.[1]
The common shortcut, running the tests against SQLite instead of the production database, fixes the first lie and keeps most of the others. SQLite is a superb database, but by default it will happily store the string 'abc' in an INTEGER column,[2] it has different locking, and its SQL dialect is not Postgres's. A test that passes on SQLite tells you that your code works on SQLite.
The advice that addresses this is old. Freeman and Pryce put it as "only mock types that you own":[3] mock your own interfaces to design the collaboration between your objects, and test the code that talks to someone else's system against that system. Martin Fowler's distinction between mocks, stubs and fakes is still the clearest description of the trade-offs.[4]
Real Postgres in every test run, with Testcontainers
Testcontainers is a library that starts a throwaway container from your test code, waits until it's ready, hands you a connection string and removes it afterwards.[5] For Python and pytest the setup is a session-scoped fixture. Three details in it matter more than they look:
- Pin the same major version you run in production.
postgres:16if production runs 16, notlatest. - Build the schema with your real migrations, not with
metadata.create_all(). Otherwise you're testing a schema that only exists in tests, and the migration that forgot an index ships untested. - Isolate each test in a transaction that's rolled back, so tests can't leak rows into each other and the container only starts once per run.
# conftest.py
import pytest
from alembic import command
from alembic.config import Config
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from testcontainers.postgres import PostgresContainer
@pytest.fixture(scope="session")
def engine():
with PostgresContainer("postgres:16") as pg:
url = pg.get_connection_url()
cfg = Config("alembic.ini")
cfg.set_main_option("sqlalchemy.url", url)
command.upgrade(cfg, "head") # the real migrations, in order
engine = create_engine(url)
yield engine
engine.dispose()
@pytest.fixture
def session(engine):
with engine.connect() as conn:
outer = conn.begin()
with Session(bind=conn, join_transaction_mode="create_savepoint") as s:
yield s
outer.rollback() # nothing a test writes survives itTwo lines in that fixture depend on your setup. get_connection_url() returns a psycopg2 URL by default; if you use psycopg 3, pass driver="psycopg" to PostgresContainer. And command.upgrade only reaches the container if your Alembic env.py reads the URL from config.get_main_option("sqlalchemy.url"). Many read it from application settings instead, and then the migrations quietly run against whatever database those settings point at.
The join_transaction_mode="create_savepoint" option is what lets the code under test call commit() as it normally would: SQLAlchemy turns the commit into a savepoint inside the outer transaction, which the fixture then rolls back.[6] With that in place, the tests that matter are short.
def test_replayed_receipt_is_stored_once(session):
receipts = ReceiptRepo(session)
event = GoodsReceipt(event_id="gr-1", sku="SKU-42", qty=Decimal("10"))
receipts.record(event)
receipts.record(event) # the warehouse system retried the webhook
assert receipts.count_for("SKU-42") == 1
def test_quantities_come_back_as_decimal(session):
stock = SqlStockRepo(session)
stock.set_on_hand("SKU-42", Decimal("12.50"))
assert stock.available("SKU-42") == Decimal("12.50")
assert isinstance(stock.available("SKU-42"), Decimal)The first test passes only if the unique index from the migrations exists and the repository uses INSERT ... ON CONFLICT DO NOTHING against it. The second fails the day someone changes the column type. Neither could have failed against a mock.
Testing the race you can't see
Concurrency bugs need two real connections. This test can't use the rolled-back session fixture, because each buyer needs its own transaction, so it commits for real. The seeded_stock fixture resets the row at the start of each round and deletes what it created when the test ends:
from concurrent.futures import ThreadPoolExecutor
from threading import Barrier
def test_two_buyers_cannot_both_reserve_the_last_five_units(engine, seeded_stock):
for _ in range(25):
seeded_stock("SKU-42", on_hand=Decimal("5"))
start = Barrier(2)
def buy():
with Session(engine) as s, s.begin():
start.wait() # both transactions begin before either reserves
try:
ReservationService(SqlStockRepo(s)).reserve("SKU-42", Decimal("5"))
return "reserved"
except InsufficientStock:
return "rejected"
with ThreadPoolExecutor(max_workers=2) as pool:
outcomes = sorted(pool.map(lambda _: buy(), range(2)))
assert outcomes == ["rejected", "reserved"]A race test is probabilistic: it can pass while the bug is there, which is why it repeats the scenario and uses a barrier to line both transactions up. When it fails, the fix is in the SQL, usually SELECT ... FOR UPDATE on the stock row or a single conditional UPDATE ... WHERE on_hand >= :qty, and the test is the only thing that proves the fix works. If the CHECK (on_hand >= 0) constraint from earlier is in place, the buggy version doesn't oversell: the second buyer's decrement violates the constraint, and the test errors with an IntegrityError instead of failing the assert. That's still red, and it's the constraint doing its job as the last line of defence, but the fix is the same.
Keeping it fast
The container starts once per run, and each test costs a savepoint and a rollback, so a few hundred database tests run in seconds, not minutes. Two habits keep it that way as the suite grows. Run in parallel with one database per worker: Postgres can clone a fully migrated database from a template almost instantly with CREATE DATABASE ... TEMPLATE, so you migrate once and give each worker its own copy.[7] And keep integration tests for the seams. Business rules that don't touch SQL still belong in plain unit tests. For the wider case for parallel test runs, see Most teams run pytest serially.
Fakes that are checked against the real thing
There's a legitimate place for a stand-in: an in-memory fake repository that lets thousands of unit tests run in milliseconds. The danger is the same as with a mock. Over time the fake drifts from the real implementation, and the unit tests keep passing against behaviour that no longer exists.
The fix is to run one set of tests against both, so the fake has to keep up:
@pytest.fixture(params=["fake", "postgres"])
def stock_repo(request):
if request.param == "fake":
return InMemoryStockRepo()
return SqlStockRepo(request.getfixturevalue("session"))
def test_reserving_more_than_available_is_rejected(stock_repo):
stock_repo.set_on_hand("SKU-42", Decimal("3"))
with pytest.raises(InsufficientStock):
ReservationService(stock_repo).reserve("SKU-42", Decimal("5"))
assert stock_repo.available("SKU-42") == Decimal("3")Now the fake is a tested component, not an act of faith. The day the SQL implementation starts rejecting something the fake accepts, a test goes red and tells you which one is wrong.
DuckDB: integration tests for analytical SQL
Not every query in an operations system runs against the transactional database. Reconciliations, inventory ageing, supplier scorecards and the data pipelines that feed forecasting are analytical SQL, often run over CSV or Parquet exports from the ERP. That SQL has bugs too, and it's usually tested even less: someone runs it once, eyeballs the output and schedules it.
DuckDB is an embedded analytical database: it runs inside your Python process, needs no server and no container, and reads CSV and Parquet files directly.[8] That makes it ideal for testing this kind of SQL with tiny fixture files, in milliseconds, as an ordinary pytest test. Here is a classic reconciliation bug, the join fan-out:
-- Received vs invoiced per order line. Looks right. Isn't.
SELECT po_number, sku,
SUM(r.qty) AS received,
SUM(i.qty) AS invoiced
FROM receipts r
JOIN invoices i USING (po_number, sku)
GROUP BY po_number, skuWhen an order is delivered in two parts and invoiced once, the join produces two rows, the invoice is counted twice, and the report says you were billed for twice what you received. Nobody notices until a supplier dispute. The fix is to aggregate each side before joining, and a test with three rows of data pins it down:
import duckdb
RECONCILE = """
WITH rec AS (SELECT po_number, sku, SUM(qty) AS received FROM receipts GROUP BY ALL),
inv AS (SELECT po_number, sku, SUM(qty) AS invoiced FROM invoices GROUP BY ALL)
SELECT po_number, sku,
COALESCE(received, 0) AS received,
COALESCE(invoiced, 0) AS invoiced,
COALESCE(received, 0) - COALESCE(invoiced, 0) AS open_qty
FROM rec FULL OUTER JOIN inv USING (po_number, sku)
ORDER BY po_number, sku
"""
def load(con, name, path, rows):
path.write_text("po_number,sku,qty\n" + "".join(f"{p},{s},{q}\n" for p, s, q in rows))
con.execute(f"CREATE VIEW {name} AS SELECT * FROM read_csv('{path}')")
def test_split_deliveries_are_not_double_counted(tmp_path):
con = duckdb.connect()
load(con, "receipts", tmp_path / "receipts.csv", [("PO-1", "SKU-42", 60), ("PO-1", "SKU-42", 40)])
load(con, "invoices", tmp_path / "invoices.csv", [("PO-1", "SKU-42", 100)])
assert con.execute(RECONCILE).fetchall() == [("PO-1", "SKU-42", 100, 100, 0)]The FULL OUTER JOIN matters as much as the pre-aggregation. A LEFT JOIN from receipts would silently drop lines that were invoiced but never received, and those are the lines a reconciliation exists to find. With the outer join they show up with a negative open quantity.
Because the fixture is just rows, this combines well with the previous post. Let Hypothesis generate receipts and invoices, load them into DuckDB, and assert a conservation property: the open quantities across all lines add up to total received minus total invoiced. A fan-out bug breaks that rule on almost any input with a split delivery.
One caution, and it's the same one as SQLite. DuckDB is not Postgres. Use it to test SQL that actually runs on DuckDB, or SQL in a dialect you've confirmed it shares. Don't use it as a quick stand-in for your transactional database, or you're back to testing a database you don't run. When you need both, DuckDB's Postgres extension can attach a real Postgres database, which is handy for checking that the numbers from an export pipeline match the operational system they came from.
Why agents make this more urgent
A coding agent asked to make a test pass will take the shortest path, and the shortest path through a database is a mock. That isn't a guess. A 2026 study of more than 1.2 million commits made in 2025 across 2,168 repositories found that 36% of commits made by coding agents added mocks to tests, compared with 26% of commits by other contributors.[9] Older research on human mocking habits found that once a mock is added, it tends to stay for the life of the test.[10]
There's a second reason. As the first post in this series put it, when one agent writes the code and the test from the same understanding, both inherit the same mistake. A mock is the clearest case of that: the agent writes the query, then writes a mock that returns what it believes the query returns. The test is a mirror. A real database is an independent oracle that has never read the agent's context window.
The good news, as in Testing 102, is that the agent can run the real thing. Make it the easy path:
- Say it in the agent's instructions. "Never mock the database, the ORM session or SQL. Use the
sessionfixture for repository tests and DuckDB fixtures for analytical SQL." - Give the agent Docker. Testcontainers needs a Docker daemon. GitHub's Linux runners have one, but macOS runners and many agent sandboxes don't, and an agent that can't start the container will go back to mocking.
- Make the real fixture shorter to use than a mock. If a database test is one fixture argument, the agent will use it.
- Review mocks, not just tests. A new
mock.patchon something you don't own is worth a comment in every pull request, from an agent or a person. - Use mutation testing on the data layer. A repository whose tests are all mocks will light up with surviving mutants in fermut, because nothing ever checks what the SQL returns.
Where integration tests stop
Everything above works because the other side of the seam fits in a container or a process. The customer's ERP, the carrier's booking API and the bank don't. For those, a mock is still a lie, but a real call isn't available on every commit either. That's what contract tests are for: a shared, versioned description of the conversation that both sides are checked against. They get their own post later in the series.
A practical starting point
- List the seams where your code meets a database, and find the tests that mock them.
- Start one Postgres per run with Testcontainers, on the production major version, built by your real migrations.
- Roll back every test with a savepoint session, and clone a template database per worker when you go parallel.
- Write the tests a mock can't fail: constraints, idempotency, types, ordering, and one race test for every read-then-write.
- Test analytical SQL on DuckDB with tiny CSV or Parquet fixtures, and add a conservation property for every aggregate.
- Keep fakes honest by running the same tests against the fake and the real implementation.
- Tell the agent not to mock what you don't own, and make the real fixture the shortest path.
What's next in this series
So far every test in this series has had a right answer, even if 104 only checked its traits. The next post is about the systems where there isn't one: models and agents whose output has to be judged rather than compared. It borrows the oldest habit in machine learning, measuring against a baseline, and covers golden datasets, synthetic test data that keeps the proportions of the real thing, LLM judges you can actually trust, and how to notice when the data, the model or your own standards drift.
If a mock has lied to you in a way we didn't list, tell us. We're collecting them.
The testing series
- Testing 101 for the Age of Coding Agents
- Testing 102: Fast unit tests, honest end-to-end tests
- Testing 103: Mutation testing with fermut
- Testing 104: Property-based testing with Hypothesis
- Testing 105: Integration testing with Testcontainers and DuckDB (this post)
References
- PostgreSQL documentation, "Sorting Rows (ORDER BY)." www.postgresql.org/docs/current/queries-order.html
- SQLite documentation, "Datatypes in SQLite" and "STRICT Tables." www.sqlite.org/datatype3.html
- S. Freeman, N. Pryce, Growing Object-Oriented Software, Guided by Tests, Addison-Wesley, 2009; see also S. Freeman, T. Mackinnon, N. Pryce, J. Walnes, "Mock Roles, not Objects," OOPSLA 2004. doi.org/10.1145/1028664.1028765
- M. Fowler, "Mocks Aren't Stubs," 2007. martinfowler.com/articles/mocksArentStubs.html
- Testcontainers for Python documentation. testcontainers-python.readthedocs.io
- SQLAlchemy documentation, "Joining a Session into an External Transaction." docs.sqlalchemy.org/en/20/orm/session_transaction.html
- PostgreSQL documentation, "Template Databases." www.postgresql.org/docs/current/manage-ag-templatedbs.html
- M. Raasveldt, H. Mühleisen, "DuckDB: an Embeddable Analytical Database," SIGMOD 2019. doi.org/10.1145/3299869.3320212
- A. Hora, R. Robbes, "Are Coding Agents Generating Over-Mocked Tests? An Empirical Study," 2026. arxiv.org/abs/2602.00409
- D. Spadini, M. Aniche, M. Bruntink, A. Bacchelli, "Mock objects for testing java systems: Why and how developers use them, and how they evolve," Empirical Software Engineering 24, 1461–1498, 2019. doi.org/10.1007/s10664-018-9663-0
