Integration tests with a real database

Testing backends Mid-level Python 3.13+ PostgreSQL 17+ Reviewed 31 Oct 2026 By Best Latest Apps

Predict the output

What do the two lines print?

Python 3.13

import psycopg

with psycopg.connect() as conn:
    conn.execute("CREATE TEMP TABLE t (id int) ON COMMIT PRESERVE ROWS")
    conn.commit()
    try:
        with conn.transaction():
            conn.execute("INSERT INTO t VALUES (1), (2)")
            n = conn.execute("SELECT count(*) FROM t").fetchone()
            print("inside test:", n)
            raise RuntimeError("roll back after the test")
    except RuntimeError:
        pass
    print("after:", conn.execute("SELECT count(*) FROM t").fetchone())

Optionsinside test: (2,) then after: (0,)inside test: (2,) then after: (2,)inside test: (0,) then after: (0,)An unhandled RuntimeError

Hint 1 of 1

What does conn.transaction() do on an exception?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A inside test: (2,) then after: (0,)

Correct. The rows exist inside the transaction and vanish on rollback.

Wrapping each test in a transaction that rolls back gives every test a clean database. Inside, the rows are visible; once the exception rolls back, they are gone.

Why not the others?

B inside test: (2,) then after: (2,). The exception rolls the transaction back.
C inside test: (0,) then after: (0,). Inside the transaction, the inserts are visible.
D An unhandled RuntimeError. It's caught after the block rolls back.

30-second answer

Start a real Postgres of the production version (Testcontainers or a CI service container), apply your migrations once, then isolate each test: run it inside a transaction that's rolled back at the end, or give each worker its own schema or template-cloned database. That keeps tests independent and fast while exercising real SQL, constraints and types. Avoid SQLite stand-ins; their behaviour differs.

Practise saying it · 30 s. No recording, just a timer

Test with a real DB.

Say your answer out loud, then check which of these points you covered:

Same version Postgres
Migrate once
Rollback or per-test DB
No SQLite stand-in

Deep dive

CREATE DATABASE ... TEMPLATE clones a migrated database in well under a second for small schemas.

Rollback isolation can't test code that commits itself; use per-test databases there.

Disable fsync in the test container for speed (never in production).

Seed only what each test needs.

Roll back after each test Ran on Python 3.13.5 · mypy --strict

Python 3.13

import psycopg

with psycopg.connect() as conn:
    conn.execute("CREATE TEMP TABLE t (id int) ON COMMIT PRESERVE ROWS")
    conn.commit()
    try:
        with conn.transaction():
            conn.execute("INSERT INTO t VALUES (1), (2)")
            n = conn.execute("SELECT count(*) FROM t").fetchone()
            print("inside test:", n)
            raise RuntimeError("roll back after the test")
    except RuntimeError:
        pass
    print("after:", conn.execute("SELECT count(*) FROM t").fetchone())

Common mistakes

SQLite in tests, Postgres in production.
Tests that depend on each other's data.
Running migrations before every test.

Senior take & follow-ups

Real same-version Postgres, migrate once, isolate per test, avoid substitutes.

Follow-up questions

Think of your answer first, then open the hint.

Why not SQLite?
Show hint
Different types, locking, constraints and SQL dialect.
When does rollback isolation fail?
Show hint
When the code under test commits or uses several connections.

Similar on Android: Testing Room and migrations The Android page tests Room against an in-memory database on the device; on a server, test against the real Postgres version.

Suggest a fix if something here is wrong or out of date.