PythonTesting19 min read

Testing the hard parts: async, databases, time and randomness

How to write reliable tests for the things that resist testing — coroutines, a real database, the system clock, randomness and outbound HTTP.

Day 97 of the 488-day pathway. Published in full — nothing held back.

Testing the hard parts — async, databases, time, randomness, HTTP SQL I — SELECT, WHERE, NULL, GROUP BY, and the order things actually happen

The sentence to own: everything that makes a test hard is a dependency on something you do not control — the clock, the network, the database, the scheduler — and every technique today is one of two moves: take control of it, or replace it with something you do control.

These five are where real suites fall over. Days 093–096 were the tools; today is production.

Part 1 — Async tests

# pyproject.toml:  [tool.pytest.ini_options]  asyncio_mode = "auto"
import pytest

async def test_fetches_user(async_client):          #   with auto mode, no marker needed
    resp = await async_client.get("/users/1")
    assert resp.status_code == 200

@pytest.fixture
async def async_client(app):                        #    fixtures can be async too
    transport = httpx.ASGITransport(app=app)        #    NO NETWORK, NO PORT, NO SERVER
    async with httpx.AsyncClient(transport=transport, base_url="http://test") as c:
        yield c

   ASGITransport IS THE KEY IDEA AND IT IS UNDER-KNOWN: httpx calls your ASGI
    app IN-PROCESS. No socket, no port to collide, no server to start or stop,
      nothing to leave running when a test fails.
    ⇒    so an "integration test through the API" costs MILLISECONDS, which is
         what makes Day 092's fat integration layer affordable at all.
    ⇒   FastAPI's TestClient does the same thing synchronously (it runs the
         loop for you), which is why a sync test can call an async route.

⚠ Async trap	Fix
Event-loop scope mismatch	A session-scoped async fixture on a function-scoped loop ⇒ "attached to a different loop". Match the scopes
A test that hangs forever	Wrap in `asyncio.timeout(5)`, or set a global timeout plugin
Forgetting `await`	The coroutine is never run — the test passes having tested nothing (Day 070)
Mixing sync DB calls into an async test	Day 072 — it blocks the loop the test is running on

"Forgot to await" is the async bug that a test will not catch for you, because an un-awaited coroutine is truthy and the assertion never runs. filterwarnings = ["error"] (Day 094) turns RuntimeWarning: coroutine was never awaited into a failure — which is the single best reason to have that setting on in an async codebase.

Part 2 — Database tests

   THE THREE STRATEGIES, WORST TO BEST:
   1. ⚠️ RECREATE THE SCHEMA PER TEST      ⇒   correct, and unusably slow (seconds each)
   2. ⚠️ TRUNCATE EVERY TABLE PER TEST     ⇒   faster; ⚠️ still hundreds of ms, and
         you must remember every new table forever
   3.    WRAP EACH TEST IN A TRANSACTION AND ROLL BACK ⇒    milliseconds, and
         it can never forget a table, because the database does the forgetting.

@pytest.fixture(scope="session")
def engine():                                   #   expensive + immutable ⇒ session (Day 093)
    eng = create_engine(TEST_DATABASE_URL)
    run_migrations(eng)                         #    MIGRATIONS, not create_all — see below
    yield eng
    eng.dispose()

@pytest.fixture
def db_session(engine):                         #    mutable ⇒ FUNCTION scope
    connection = engine.connect()
    transaction = connection.begin()            #   open a transaction
    session = Session(bind=connection, join_transaction_mode="create_savepoint")
    yield session
    session.close()
    transaction.rollback()                      #    EVERYTHING THE TEST DID IS UNDONE
    connection.close()

   WHY MIGRATIONS RATHER THAN create_all() IN THE FIXTURE — this is the
    non-obvious one, and it is worth saying in an interview:
    create_all() builds the schema your MODELS describe. Migrations build the
    schema PRODUCTION will actually have.
    ⇒    IF THEY DIVERGE — a migration someone forgot to write — create_all
         HIDES IT and every test passes against a schema that does not exist
         anywhere else.   Running migrations in the fixture tests the migrations
         as a side effect, for free, on every run.
    ⇒   the cost is a slower session fixture. Once per run. Pay it.

⚠️⚠️    "USE SQLITE INSTEAD OF POSTGRES, IT IS FASTER" — THE HONEST ANSWER:
     SQLite differs in ways that MATTER: type affinity instead of real types,
     different NULL and string comparison behaviour, no real concurrency, a
     different dialect for upserts, arrays, JSONB, window functions, and no
     ability to test the constraint that will actually fire in production.
     ⇒    SO YOU GET GREEN TESTS AND PRODUCTION BUGS — the worst possible
          combination, because the suite is actively lying.
     ⇒    RUN THE REAL DATABASE: docker compose, or `testcontainers` which
          starts one from the test session itself.   Session-scoped, so you pay
          the startup once.
     ⇒   SQLite is fine when your app genuinely targets SQLite, and for tests
          of pure logic that touch no SQL — but those should not touch a
          database at all (Day 095's fake).

Part 3 — Time and randomness

#    PREFERRED — inject it (Day 096). No library, no patching, no magic.
def is_expired(token, now: datetime) -> bool: ...

#   WHEN YOU CANNOT — freeze it at the boundary:
import time_machine

@time_machine.travel("2026-03-29 00:30:00+00:00")     #   a real DST morning in London
def test_scheduling_across_dst():
    ...

with time_machine.travel(start, tick=True):           #   time still MOVES from there
    ...

   THE POSITION: INJECT IN CODE YOU OWN, FREEZE AT BOUNDARIES YOU DO NOT.
    ⇒   injection makes the dependency VISIBLE in the signature and needs no
         library ⇒    and it is the change that improves the code for readers
         too (Day 096's seam test).
    ⇒   freezing is right for third-party code, `datetime.now()` buried in a
         framework, or a whole scenario ("what happens on 29 March").
    ⇒ ⚠️    AND TEST THE DATES THAT ACTUALLY BREAK (Day 076): a DST transition,
         29 February, 31 December 23:59, a month-end rollover, and a timezone
         east of UTC where "today" differs from UTC's today.

#   RANDOMNESS — seed it, and make the seed visible on failure:
def test_shuffle_preserves_elements():
    rng = random.Random(12345)               #    an explicit instance, not the global
    ...
# ⇒    and better still: if you are reaching for random inputs, you want
#    PROPERTY-BASED TESTING (Day 098A), which searches deliberately and SHRINKS
#    the failure to its smallest form.

Part 4 — HTTP, and the staleness problem

import respx, httpx

@respx.mock
def test_retries_on_502():
    route = respx.get("https://api.gateway.test/charge").mock(
        side_effect=[httpx.Response(502), httpx.Response(200, json={"id": "r_1"})]
    )
    assert charge(order).id == "r_1"
    assert route.call_count == 2               #   the retry is the behaviour here

   STUB AT THE TRANSPORT LAYER, NOT AT YOUR OWN FUNCTION. Patching
    `myapp.gateway.charge` skips your serialisation, your headers, your timeout
    handling and your error mapping —    i.e. it skips exactly the code most
    likely to be wrong.   respx/responses intercept at the httpx/requests
    transport, so all of your code runs and only the socket is replaced.

Approach	Trade-off
Transport stub (`respx`)	Fast, deterministic — ⚠ encodes your belief about their API (Day 095)
Recorded cassettes (VCR)	Real responses once — ⚠ rot silently; a cassette from 2024 tests nothing
Their sandbox environment	Real behaviour — ⚠ slow, flaky, rate-limited
Contract test, nightly	The answer: fast stubs in CI, one real call on a schedule

The nightly contract test is the piece that makes all the stubbing safe. It is one test, it hits the real (or sandbox) API, it asserts only on the shape of the response your adapter depends on, and it fails the morning after they change something — rather than the afternoon you deploy.

Part 5 — D-07 · SQL I, and the order of operations

SELECT   customer_id, COUNT(*) AS order_count      -- 5
FROM     orders                                    -- 1
WHERE    status <> 'cancelled'                     -- 2
GROUP BY customer_id                               -- 3
HAVING   COUNT(*) > 5                              -- 4
ORDER BY order_count DESC                          -- 6
LIMIT    10;                                       -- 7

   THE LOGICAL ORDER IS  FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
    → LIMIT, AND IT EXPLAINS TWO THINGS PEOPLE MEMORISE SEPARATELY:

   1.    WHY YOU CANNOT USE A SELECT ALIAS IN `WHERE`:
        WHERE runs at step 2; the alias is not created until step 5.   It does
        not exist yet.    AND WHY YOU *CAN* USE IT IN `ORDER BY` — step 6, after.
   2.    WHERE vs HAVING: WHERE filters ROWS (before grouping); HAVING filters
        GROUPS (after). ⇒   `WHERE COUNT(*) > 5` is an error, not a style choice.
        ⇒    AND THE PERFORMANCE COROLLARY: filter in WHERE whenever you can,
          because you then group far fewer rows (Day 095's predicate pushdown).

   NULL, AGAIN (D-02), BECAUSE IT IS WHERE REAL QUERIES GO WRONG:
    WHERE x = NULL          ⇒   never matches. IS NULL.
    WHERE x <> 'a'          ⇒    EXCLUDES ROWS WHERE x IS NULL. Almost never
                               what you meant ⇒ add `OR x IS NULL`, or use
                               `x IS DISTINCT FROM 'a'`.
    COUNT(col) vs COUNT(*)  ⇒   COUNT(col) SKIPS NULLs.
    SUM/AVG                 ⇒   ignore NULLs ⇒ AVG is over NON-NULL rows only
    ⚠️    NOT IN (SELECT … ) WHERE THE SUBQUERY CONTAINS ONE NULL
         ⇒    RETURNS NOTHING AT ALL. `x NOT IN (1, NULL)` is `x<>1 AND x<>NULL`
           ⇒ NULL ⇒ never true.    THIS IS THE MOST EXPENSIVE NULL BUG IN SQL,
           and the fix is `NOT EXISTS` (Day 095's anti-join), which is immune.
    COALESCE(x, 0)          ⇒   substitute a default

And the GROUP BY rule: every non-aggregated column in SELECT must be in GROUP BY. Postgres enforces it; MySQL historically did not, and silently returned an arbitrary row — which is exactly the kind of difference that makes "test against the real database" (Part 2) a correctness argument rather than a preference.

Common mistakes

Mistake	Correction
Forgetting `await`	The test passes having run nothing. `filterwarnings = ["error"]`.
Session-scoped async fixture, function loop	"Attached to a different loop". Match scopes.
Starting a real server for API tests	`ASGITransport` — in-process, no port.
Truncating tables per test	Transaction + rollback. Milliseconds, and it cannot forget a table.
`create_all()` in the fixture	Tests a schema production does not have. Run migrations.
SQLite standing in for Postgres	Green tests, production bugs — the suite is lying.
Patching your own client function	Skips serialisation, headers, timeouts, error mapping. Stub the transport.
Old VCR cassettes	They rot silently. Add a nightly contract test.
Only testing "now"	Test DST, 29 Feb, month-end, and a non-UTC zone.
`random` without a seed	Seed it — or use Hypothesis.
`NOT IN` with a nullable subquery	Returns nothing. Use `NOT EXISTS`.
`x <> 'a'` expecting NULLs	It excludes them. `IS DISTINCT FROM`.
Alias in `WHERE`	It does not exist yet.

Interview questions

Q: How do you test code that talks to a database?

Session-scoped engine, function-scoped transaction that gets rolled back after each test. That gives a clean database per test in milliseconds, and unlike truncating tables it can never forget a new table because the database does the forgetting. I'd build the schema by running the real migrations rather than create_all, because create_all builds what the models describe while migrations build what production will actually have — if someone forgot a migration, create_all hides it and every test passes against a schema that exists nowhere else. And I'd run the real database engine, via docker or testcontainers.

Q: Why not use SQLite for tests when production is Postgres?

Because it differs in ways that matter — type affinity instead of real types, different NULL and comparison behaviour, no real concurrency, a different dialect for upserts and JSON, and constraints that won't fire the way production's will. So you get green tests and production bugs, which is worse than having no tests, because the suite is actively lying to you. The startup cost of a real Postgres is paid once per session with a session-scoped fixture, and that's cheap compared to a class of bug you can't see.

Q: How do you test something that depends on the current time?

Inject the clock where I own the code — pass now as an argument or a callable — because that makes the dependency visible in the signature, needs no library, and improves the code for readers rather than only for tests. I'd freeze time with time-machine when the clock is buried in third-party code or when I want a whole scenario, like "what happens on the morning the clocks change". And I'd deliberately test the dates that actually break things: a DST transition, 29 February, a month-end rollover, and a timezone east of UTC where the local date differs from UTC's.

Q: How do you test code that calls a third-party API?

Stub at the transport layer with something like respx, not by patching my own client function — patching my function skips the serialisation, headers, timeout handling and error mapping, which is exactly the code most likely to be wrong. That gives fast deterministic tests, but it encodes my belief about their API, so I'd pair it with a single nightly contract test that makes one real call and asserts on the shape my adapter depends on. That way I find out about their change the morning after it happens rather than on the afternoon I deploy. Recorded cassettes I'd avoid as the primary mechanism — they rot silently, and a cassette from two years ago tests nothing.

Q: Why can't you use a SELECT alias in WHERE but can in ORDER BY?

Because of the logical evaluation order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. WHERE runs before SELECT, so the alias doesn't exist yet; ORDER BY runs after, so it does. The same order explains WHERE versus HAVING — WHERE filters rows before grouping and HAVING filters groups after, which is why WHERE COUNT(*) > 5 is an error rather than a style preference. And there's a performance corollary: filter in WHERE whenever you can, because then you group far fewer rows.

Q: What's the most expensive NULL bug in SQL?

NOT IN against a subquery that can return a NULL. x NOT IN (1, NULL) expands to x <> 1 AND x <> NULL, and the second comparison is NULL rather than true, so the whole predicate can never be true and the query returns no rows at all. It's silent — no error, just an empty result that looks like "there's no data". NOT EXISTS is immune, because it's an anti-join and asks about existence rather than equality.

Mini task

Write an async API test with httpx.ASGITransport and confirm no port is opened.
Remove an await and watch the test still pass. Then set filterwarnings = ["error"] and watch it fail.
Build the session-engine + function-transaction fixture pair. Insert a row in one test and assert it is absent in the next.
Swap create_all() for real migrations. Then delete a migration on purpose and confirm the tests notice.
Run the same suite against SQLite and Postgres. Find one behaviour that differs.
Add testcontainers and start Postgres from the test session.
Write a DST test for Europe/London on 29 March using time-machine, and one for Australia/Sydney.
Use respx to test retry-on-502, asserting the call count. Then patch your own client function instead and note what stopped being tested.
Write a nightly-marked contract test that makes one real call and asserts only the response shape.
D-07: write a query using a SELECT alias in WHERE and read the error. Move it to ORDER BY.
Create a nullable column, then run NOT IN (SELECT that_column …). Count the rows. Rewrite with NOT EXISTS and compare.
Compare COUNT(*), COUNT(col) and AVG(col) on a table with NULLs.

Exit questions

Answer aloud, no notes.

What does ASGITransport remove from an API test?
Why does a forgotten await pass, and what catches it?
Give the three database strategies and why rollback wins.
Why migrations rather than create_all?
Name four ways SQLite differs from Postgres, and the phrase for the resulting failure.
Inject or freeze — when each?
Which five dates should you always test?
Why stub at the transport rather than your own function?
What makes cassettes dangerous, and what fixes stubbing generally?
Give the seven-step logical order of a SQL query.
Derive the alias rule from it.
WHERE vs HAVING, and the performance corollary.
Why does NOT IN with a NULL return nothing, and what is immune?

Articulation drill

Record two minutes: "How do you test a service that has a database and calls a payment provider?"

Split it into what you control and what you do not, because that is the organising idea: "two different problems. The database I control, so I run the real one. The payment provider I don't, so I replace it — and then buy back the confidence I lost."

Then the database half, concretely: "a session-scoped engine against real Postgres — docker or testcontainers — with the schema built by running the actual migrations, because create_all builds what my models describe rather than what production will have, and it hides a forgotten migration. Then each test runs inside a transaction that's rolled back, which gives a clean database in milliseconds and can't forget a new table. I wouldn't substitute SQLite: the type handling, NULL behaviour and constraints differ enough that you get green tests and production bugs, and a suite that lies is worse than no suite."

Then the provider half, with the piece most people miss: "for the provider I stub at the transport layer, with respx rather than by patching my own client function — patching my function skips serialisation, headers, timeouts and error mapping, which is where the bugs actually are. That gets me fast deterministic tests of retries and error handling. But a stub encodes my belief about their API, so I'd add one nightly contract test that makes a real call and asserts on the shape my adapter depends on. That's what turns a stub from a liability into a safe optimisation — I find out about their change the morning after, not on the afternoon I deploy."

Close on the boundary: "and the business rules themselves shouldn't be in either of those tests. Those are pure functions over plain objects and get tested with no database and no HTTP at all."

Previous: Day 096 · Tomorrow: Day 098 — coverage vs mutation testing: why 100% coverage proves almost nothing