Module 7: Test Data And Isolation In Integration

4. Temporary DB fixtures with yield

Next →

Description

The previous lesson left you with an open problem: the rollback isolates wonderfully, but only if nobody commits inside the test, and our save commits. This lesson solves that case with the technique you'll use more than any other in integration: a fixture with yield that gives each test its own temporary database —fresh, empty, newborn— and destroys it when the test ends. The idea is different from the rollback's, and that's why it doesn't share its weakness. The rollback says: "a single database, revert what each test wrote". The new-database fixture says: "a different database per test, and throw it away whole at the end". If each test has its own database, save's commit stops being a problem: let it commit all it wants, because that database is private to that test and dies with it —there's no neighbor inheriting anything, because they don't share a database—. It's the difference between cleaning the table between experiments and giving each experiment its own table; the second is simpler to guarantee, because it doesn't depend on the cleaning running well.

The heart of the technique is a Python word that pytest loads with meaning: yield. A normal fixture returns a value with return; a fixture with yield does something richer: it runs setup code (create the database, the schema), hands over the resource with yield, lets the test run, and when the test ends, runs the teardown code that comes after the yield (close the connection, destroy the database). The yield is the exact border between each test's "before" and "after", and —this is the decisive part— pytest guarantees the "after" runs no matter what, even if the test fails midway. That's why the cleanup of a fixture with yield doesn't have the hole of the DELETE at the end or the rollback in the test body: it's not a line the test can skip when it blows up, it's a responsibility pytest always fulfills. You're going to write that fixture, watch it isolate a suite in any order with real output, and understand the role of scope in how long the resource lives.

Connection to the module: lesson 3 gave you the rollback and its limit; this one gives you the technique without that limit, the one that solves the committing-service case. Together they're the module's two isolation tools: rollback when you control the transaction and recreating is expensive; new database per test when the code commits or when you want maximum simplicity. Lesson 5 will take this very fixture and decide the resource it hands over —:memory: or a temporary file—, with measured numbers. Lesson 6 will add the seeding of known data. And lesson 7 will use it to demonstrate the principles of independence and repeatability. That is: the fixture with yield you write here is the skeleton the rest of the module is mounted on. It's worth understanding thoroughly.

Analogy: the operating room that prepares and cleans itself

Think of how an operating room is organized between one surgery and the next. Before each operation, a team prepares the room: sterilized instruments, clean sheets, everything in its place. During surgery, the medical team uses the room without worrying about what came before, because it arrived spotless. And afterward —however the surgery went, with complications or without them— another team cleans and sterilizes everything, leaving the room ready for the next one. The crucial thing: the cleaning happens always, not only when the surgery goes well. If something got complicated, the room is cleaned all the more, not less. Nobody would say "since the operation got complicated, let's leave the room dirty for the next one"; it would be absurd and dangerous.

A fixture with yield is that operating-room protocol. The code before the yield is the preparation: it creates the database, sets up the schema. The yield is the moment the room is handed to the surgeon: the test runs and uses the resource. The code after the yield is the cleaning: it closes the connection, destroys the database. And like in the operating room, that cleaning runs no matter what happens with the test —passed, failed, blew up with an exception—, because pytest treats it as a responsibility of the protocol, not as an optional step that depends on how the operation ended. That guarantee is exactly what was missing: an isolation that doesn't get skipped when the test fails, which is when you need it most.

The fixture: create a fresh database and destroy it

Let's write the fixture and a suite that uses it, integrating through the committing service —the case the rollback couldn't isolate—.

# tests/test_fresh_db_fixture.py — a fixture that creates a real DB and destroys it per test
import sqlite3
import pytest
from datetime import datetime
from reservo.calendar import Calendar
from reservo.doubles import FixedClock, SpyEmailSender, StubPaymentGateway
from reservo.models import Member, Room
from reservo.services import BookingService
from reservo.sqlite_repo import SqliteBookingRepository

FOCUS = Room("focus", "Focus", 4, 2500); ANA = Member("m-ana", "Ana", "pro")

@pytest.fixture
def repo():
    conn = sqlite3.connect(":memory:")   # (1) SETUP: new, empty DB
    yield SqliteBookingRepository(conn)  # (2) hands the repo to the test
    conn.close()                         # (3) TEARDOWN: the whole DB is destroyed

def make_service(repo):
    return BookingService(Calendar(), FixedClock(datetime(2026, 3, 1, 9)),
                          StubPaymentGateway(True), SpyEmailSender(), repo)

def count(repo):
    return len(repo.find_by_room("focus"))

def test_a_books_and_sees_one(repo):
    make_service(repo).book(FOCUS, ANA,
                            datetime(2026, 3, 10, 9), datetime(2026, 3, 10, 12))
    assert count(repo) == 1

def test_b_starts_empty(repo):
    assert count(repo) == 0            # new DB: nothing from test A
    make_service(repo).book(FOCUS, ANA,
                            datetime(2026, 3, 11, 9), datetime(2026, 3, 11, 12))
    assert count(repo) == 1

def test_c_also_starts_empty(repo):
    assert count(repo) == 0            # another new DB: nothing from A or B

Read the repo fixture with the three markers. In (1), the setup: we open a connection to a freshly created :memory: database —empty, no rows—. In (2), the yield: we build the repository over that connection and hand it to the test; the test runs here, with its private database. In (3), the teardown: when the test ends, we close the connection, and since the database is :memory:, closing it destroys it completely —table, rows, everything—. Each test that requests repo in its parameters triggers this entire cycle: new database before, database destroyed after. Notice what no longer matters: that book commits. It commits over a database that's only its own and that's going to die; its commit reaches nobody.

What to expect. On my machine (Python 3.14.0, pytest 9.1.1):

python3 -m pytest tests/test_fresh_db_fixture.py -v
============================= test session starts ==============================
platform darwin -- Python 3.14.0, pytest-9.1.1, pluggy-1.6.0
collected 3 items

tests/test_fresh_db_fixture.py::test_a_books_and_sees_one PASSED           [ 33%]
tests/test_fresh_db_fixture.py::test_b_starts_empty PASSED                 [ 66%]
tests/test_fresh_db_fixture.py::test_c_also_starts_empty PASSED            [100%]

============================== 3 passed in 0.01s ===============================

All three green, integrating through the committing service —what the rollback in lesson 3 didn't achieve—. Tests B and C assert count == 0 at the start and it's true, because each one gets a newborn database where nobody wrote. And the proof of real isolation, running it backwards:

python3 -m pytest tests/test_fresh_db_fixture.py::test_c_also_starts_empty \
                  tests/test_fresh_db_fixture.py::test_b_starts_empty \
                  tests/test_fresh_db_fixture.py::test_a_books_and_sees_one -v
collected 3 items

tests/test_fresh_db_fixture.py::test_c_also_starts_empty PASSED           [ 33%]
tests/test_fresh_db_fixture.py::test_b_starts_empty PASSED                [ 66%]
tests/test_fresh_db_fixture.py::test_a_books_and_sees_one PASSED          [100%]

Green in reversed order. The order doesn't matter because there's nothing shared to inherit: each test lives and dies in its own database. This is the simplest and most robust isolation technique in integration, and the one you'll use by default.

The yield, in detail: why the cleanup always runs

Let's pause on the mechanics of the yield, because it's what gives the guarantee. A fixture with yield is, on the inside, a generator that pytest handles in three stages:

  1. pytest enters the fixture and runs up to the yield. Everything before the yield is the setup.
  2. pytest takes the value the yield produces and injects it into the test as an argument. The test runs.
  3. when the test ends —whether it passed, failed, or raised an exception—, pytest returns to the fixture and runs what's after the yield. That's the teardown.

Step 3 is the key. pytest runs the teardown in a context that guarantees it runs even if the test fails: internally, it's as if the yield were inside a try/finally, with the cleanup in the finally. That's why conn.close() always runs. Compare it with the two broken forms we already discarded:

  • The DELETE as the last line of the test body: if the test fails on an earlier assertion, that line doesn't run. The fixture's teardown does.
  • The rollback as the last line of the test body: same problem. In a fixture with yield, it goes after the yield and runs no matter what.

Seeing it with a small experiment makes it tangible. If you add a print before and after the yield, and a test that fails on purpose, you'll see the print after the yield show up all the same —the cleanup ran even though the test blew up—. That's the property that makes the fixture with yield the right place for isolation: the cleanup isn't a favor the test does if it makes it to the end alive, it's an obligation pytest fulfills for you.

The scope: how long the resource lives

Every fixture has a scope, which decides how often its setup/teardown cycle runs. The default is function: the fixture runs once per test, which is what you want for isolation —a new database per test—. But there are others, and choosing the scope wrong breaks the isolation or wastes time.

  • scope="function" (default): setup and teardown for each test. It's the one that isolates: each test gets its own database. Use it for the resource that must be clean in each test.
  • scope="module": setup and teardown once per test file. All the tests in the module share the resource. Useful for the expensive and read-only (a schema that doesn't change, immutable reference data), but dangerous for what gets written: if you share a database with writes across all the module's tests, you're back to lesson 2's problem.
  • scope="session": once for the whole run of pytest. Even broader; same caution.

The practical rule: put the expensive and stable that doesn't get modified in a wide scope (module/session), and what each test needs fresh in function scope. In lesson 3 you used both together: db of module scope for the schema (expensive, stable), conn of function scope for the rollback (per-test isolation). In this lesson, since each test creates its own :memory: database from scratch, a single function-scope fixture is enough —the "expensive" here (creating an empty table) is so cheap it's not worth splitting out—. With a big schema, you'd go back to the two-fixture pattern. The scope isn't a detail: it's the lever you use to balance isolation against speed.

A concrete warning about wide scope and writing: if for speed you put the database in a module-scope fixture and your tests write to it, you've recreated lesson 2's shared REPO in another disguise, and the contamination comes back. The function scope exists precisely so that isolation is the default behavior; widening it is an optimization that's only safe for resources that don't get modified.

Common mistakes

Using return instead of yield and losing the teardown. What happens: the fixture does return SqliteBookingRepository(conn) instead of yield, and the connection is never closed. Why it happens: return is the natural way to "give a value". How to detect it: with return there's no place for the cleanup code; connections pile up, and with file databases, the files are left undeleted. How to fix it: use yield for the resource and put the cleanup after. If there really is nothing to clean up, return is fine; but a real resource —a connection, a file— almost always needs teardown, and that calls for yield.

Widening the scope to module "to make it faster" and reintroducing the contamination. What happens: the fixture goes to scope="module" so as not to recreate the database per test, and suddenly the tests contaminate each other. Why it happens: the wide scope saves time, and it's tempting. How to detect it: if widening the scope makes order-dependent failures appear, you shared a resource with writes. How to fix it: only widen the scope of resources that aren't written during the tests (schema, reference data). What each test modifies goes in function scope.

Putting the cleanup before the yield by mistake. What happens: someone, confused, closes the connection or deletes the database before the yield, and the test gets an already-destroyed resource. Why it happens: the mental order of setup and teardown gets mixed up. How to detect it: the test fails with a "closed connection" or "no such table" error as soon as it touches the resource. How to fix it: remember the operating-room rule —prepare before the yield, clean after—; everything the test needs ready goes above, everything that has to be undone goes below.

Exercises

Exercise 1 — Trace the execution order. For the test_fresh_db_fixture.py suite with its three tests, write the exact sequence of events pytest runs, marking each setup (before the yield) and each teardown (after the yield) of the repo fixture. Assume definition order.

See solution

The sequence, with the function-scope fixture running once per test:

  1. repo setup (new :memory: connection #1) → test_a_books_and_sees_one runs → repo teardown (conn.close(), DB #1 destroyed).
  2. repo setup (new :memory: connection #2) → test_b_starts_empty runs → repo teardown (DB #2 destroyed).
  3. repo setup (new :memory: connection #3) → test_c_also_starts_empty runs → repo teardown (DB #3 destroyed).

Three complete setup/teardown cycles, one per test, each with its own database. The essential thing: between one test's teardown and the next one's setup there's no state that survives —DB #1 no longer exists when #2 is born—. That's why test_b and test_c see count == 0: their database is new, not the previous one's. And that's why the order doesn't matter: reordering the tests only reorders identical, independent cycles.

Exercise 2 — The teardown that runs even if the test fails. Write (mentally or in your editor) a repo fixture that prints "SETUP" before the yield and "TEARDOWN" after, and a test that uses repo and fails on purpose with assert False. Predict what gets printed when running with -s, and explain what pytest guarantee makes it possible.

See solution

It prints SETUP and then TEARDOWN, even though the test fails. The output would be, in essence: the fixture enters and prints SETUP, the test runs and blows up on assert False, pytest marks the test as failed, and all the same returns to the fixture and runs what's after the yield, printing TEARDOWN. The test shows up as FAILED, but the TEARDOWN is in the output.

The guarantee that makes it possible: pytest runs the code after a fixture's yield in a context equivalent to a finally, so it runs no matter what happens with the test —passed, failed, or with an exception—. That's exactly the property that makes the fixture with yield the right place for isolation cleanup: if the isolation lived in the test body, an assert False halfway through would skip it and the next test would inherit garbage. In the fixture, it doesn't. That's why the isolation goes in the fixture's teardown, never in the test body.

Exercise 3 — Diagnose a badly chosen scope. A colleague changed the repo fixture to scope="module" to speed up the suite, and now test_b_starts_empty and test_c_also_starts_empty fail, both with assert 1 == 0, but only when they run after test_a. Explain what happened and why both see exactly 1.

See solution

By setting scope="module", the repo fixture runs its setup a single time for the whole file: one :memory: database is created that all the tests share, and its teardown (closing the connection) happens only at the end of the module. With that, the database is no longer fresh per test: it's the same for all three, and what one test writes stays for the next ones —exactly lesson 2's shared REPO, now hidden in a badly chosen scope—.

Why both see 1: test_a books once and leaves 1 booking in the shared database (and passes, because it asserts count == 1). Then test_b starts with assert count(repo) == 0, sees A's booking, and fails right there with 1 == 0 —it fails on its first line, so it never reaches the line where it would book—. Since test_b didn't manage to write anything, the database still has 1 booking. Then test_c also starts with assert count(repo) == 0, sees that same booking from A, and fails the same way with 1 == 0. The count stays at 1 for both because the only one that managed to write was test_a.

The moral isn't to memorize the numbers, but to see the mechanism: with a shared database, what each test sees depends on what the previous ones managed to write before ending or failing, and that makes the result fragile and order-dependent. The solution is to go back to scope="function": a new database per test, counts always from zero, order irrelevant.

Summary and next step

In this lesson you wrote the isolation technique you'll use most: a fixture with yield that creates a fresh temporary database per test and destroys it in the teardown. You saw that it isolates even when you integrate through the committing service —the case the rollback couldn't—, because each test has its own database and the commit reaches no neighbor. You understood the yield as the border between setup (before) and teardown (after), and the guarantee that makes it reliable: pytest runs the cleanup no matter what, even if the test fails. And you learned the role of scope: function for what each test needs fresh, module or session only for the expensive and stable that isn't written.

Before moving on you should be able to: write a fixture with yield that opens a resource, hands it over, and closes it; explain why the teardown runs even if the test fails; and choose the right scope depending on whether the resource is written or not.

What comes next is a decision this fixture leaves open: which resource to hand over. We used :memory:, but the fixture could just as well give a database in a temporary file. In lesson 5 you're going to compare :memory: against a temporary file —with pytest's tmp_path fixture— and decide with measured numbers: :memory: is fast and isolated per connection but doesn't touch disk; the file tests real persistence but is dozens of times slower. You're going to see those numbers on your own machine and know when to pay each cost. It's the same fixture with yield, with a different resource inside.

Resources

← Previous3. The transaction rollback as isolationNext →5. `:memory:` versus temporary file