5. `:memory:` versus temporary file

Description

Lesson 4's fixture left an open decision that now has to be made with judgment: which resource it hands over. We used sqlite3.connect(":memory:"), but the same fixture could give a database in a temporary file, with pytest's tmp_path fixture. It's not a cosmetic detail; it's a real dilemma with measurable consequences in speed and in what your test proves. A :memory: database lives in your process's RAM: it's blazing fast, it's destroyed when the connection closes, and that's why it isolates naturally —each connection, its own database—. But precisely because it lives in RAM, it never touches disk, so it can't prove anything that depends on disk: persistence between processes, real serialization to a file, behavior with a full disk. A database in a temporary file lives in the file system: it's dozens of times slower —each commit forces the operating system to write to disk— but it proves the real thing, the file that survives closing the process.

The decision, then, is an explicit trade-off between speed and realism, and you're going to make it with numbers measured on your own machine, not with intuitions. You're going to time the same booking load against the fake, against :memory:, and against a file on disk, and you're going to see the gap with your own eyes: memory costs a few times what the fake costs; disk costs dozens of times what memory costs. With those numbers, the rule comes out on its own: use :memory: by default —it's fast and it isolates— and pay for disk only when what the test wants to prove is precisely something about disk. You're going to learn to use tmp_path, pytest's fixture that gives you a unique temporary directory per test —with its own cleanup included—, for when the real file is what you need.

Connection to the module: lesson 4 gave you the skeleton —the fixture with yield that creates and destroys—; this one decides the resource that goes inside, and why. It's the same isolation technique with two different fillings, each with its cost and its reach. The choice connects with module 5, where you already saw that :memory: doesn't prove disk persistence and that a real file was needed for that: here you turn that observation into a systematic decision, with the right fixture for each case and the numbers to justify it. Lesson 6 will add data seeding, which works the same over either of the two resources. Choosing the resource well is what keeps your integration suite fast without giving up on proving what really matters to prove.

Analogy: the flight simulator and the real runway

Think of how a pilot is trained. Most of the training happens in a flight simulator: it's cheap, it resets instantly, it lets you practice a hundred takeoffs in an afternoon, and there's no risk. The simulator faithfully reproduces the controls, the instruments, the physics of flight. But there are things a simulator, by definition, can't test: the real feel of the plane on a runway with a real crosswind, the behavior of the landing gear on real wet asphalt, what happens when real metal is stressed. For that, at some point, the pilot has to fly a real plane on a real runway: it's expensive, slow, risky, and that's why it's done rarely and only when you need to test what the simulator can't reach.

The :memory: database is the simulator: faithful, blazing fast, resettable instantly, perfect for the hundreds of repetitions of daily training —your normal integration suite—. The file on disk is the real runway: it reproduces what the simulator can't —physical persistence, the file that survives the process—, in exchange for being much slower. A good training program doesn't choose one or the other: it uses the simulator for almost everything and the real runway for what demands physical realism. Your suite does the same: :memory: for the vast majority of integrations —fast and isolated— and a temporary file for the handful of tests that really need to test disk. Always choosing the real runway would be absurdly slow; always choosing the simulator would leave real things untested. The criterion is knowing what each test proves.

The same fixture, two resources

First, let's see that it's literally the same fixture with yield, changing only what goes inside. You already know the :memory: version:

@pytest.fixture
def repo():
    conn = sqlite3.connect(":memory:")   # RAM: fast, doesn't touch disk
    yield SqliteBookingRepository(conn)
    conn.close()                         # closing destroys the database

The temporary-file version uses tmp_path, a fixture pytest gives you for free: a Path object pointing to a temporary directory unique to that test, which pytest creates before and cleans up after.

# tests/test_temp_file_db.py — the SAME fixture, with a real temporary file
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(tmp_path):
    db_file = tmp_path / "reservo.db"        # unique path per test, given by pytest
    conn = sqlite3.connect(db_file)          # disk: a real file
    yield SqliteBookingRepository(conn)
    conn.close()                             # pytest deletes tmp_path afterward

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

def test_persists_to_a_real_file(repo):
    make_service(repo).book(FOCUS, ANA,
                            datetime(2026, 3, 10, 9), datetime(2026, 3, 10, 12))
    assert len(repo.find_by_room("focus")) == 1

def test_next_test_gets_its_own_file(repo):
    assert len(repo.find_by_room("focus")) == 0   # new file, empty table

The fixture now requests tmp_path as a parameter and builds the database inside that directory. The elegant thing is that the isolation is still automatic for two mutually reinforcing reasons: each test gets a different tmp_path, so its reservo.db file is unique; and pytest deletes that temporary directory after the test, so no garbage is left on disk. You don't have to create the file with tempfile.mkstemp or delete it with os.remove in a finally —as we did crudely in module 5—; tmp_path does it for you, with guaranteed cleanup.

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

python3 -m pytest tests/test_temp_file_db.py -v

============================= test session starts ==============================
platform darwin -- Python 3.14.0, pytest-9.1.1, pluggy-1.6.0
collected 2 items

tests/test_temp_file_db.py::test_persists_to_a_real_file PASSED            [ 50%]
tests/test_temp_file_db.py::test_next_test_gets_its_own_file PASSED        [100%]

============================== 2 passed in 0.02s ===============================

Both green, and isolated: the second test sees count == 0 because it gets its own file, different from the one the first used. Notice the time: 0.02s, a hair more than the :memory: version (0.01s), because now there's disk in the mix. With two tests the difference is imperceptible; with hundreds, it's not. Let's measure it.

The numbers: fake versus memory versus disk

Intuiting "disk is slower" isn't enough to decide; you have to see how much. Let's time the same load —six hundred bookings with their read-back— against the three resources: the fake (dict), SQLite in :memory:, and SQLite in a disk file.

# bench_resources.py — how much each resource costs, measured
import sqlite3, tempfile, os, time
from datetime import datetime, timedelta
from reservo.calendar import Calendar
from reservo.doubles import (FakeBookingRepository, 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")
N = 600

def run(repo):
    base = datetime(2026, 3, 10, 9)
    for i in range(N):
        s = base + timedelta(hours=3 * i)
        svc = BookingService(Calendar(), FixedClock(datetime(2026, 3, 1, 9)),
                             StubPaymentGateway(True), SpyEmailSender(), repo)
        b = svc.book(FOCUS, ANA, s, s + timedelta(hours=3))
        repo.get(b.id)

t = time.perf_counter(); run(FakeBookingRepository()); fake = time.perf_counter() - t
t = time.perf_counter(); run(SqliteBookingRepository(sqlite3.connect(":memory:"))); mem = time.perf_counter() - t
fd, path = tempfile.mkstemp(suffix=".db"); os.close(fd)
try:
    t = time.perf_counter(); run(SqliteBookingRepository(sqlite3.connect(path))); disk = time.perf_counter() - t
finally:
    os.remove(path)

print(f"fake (dict):      {fake*1000:8.1f} ms")
print(f"sqlite :memory:   {mem*1000:8.1f} ms   ({mem/fake:5.1f}x the fake)")
print(f"sqlite on disk:   {disk*1000:8.1f} ms   ({disk/mem:5.1f}x the :memory:)")

What to expect. On my machine (Python 3.14.0, macOS, SSD). The exact numbers vary between runs and between machines; what matters is the ratios, which hold:

python3 bench_resources.py

fake (dict):           2.1 ms
sqlite :memory:        6.8 ms   (  3.2x the fake)
sqlite on disk:      217.6 ms   ( 32.2x the :memory:)

Read the staircase. The fake, a dict in RAM, is as fast as possible: 2.1 ms for 600 bookings. SQLite in :memory: costs about three times more (6.8 ms): it's still RAM, but now there's real SQL —parsing the query, touching indexes, serializing the datetime to text—, and that has a small and very payable price. The brutal jump is disk: 217.6 ms, more than thirty times what :memory: costs. That factor isn't SQL being slow; it's the disk: each commit of save forces the operating system to guarantee that the write reached the physical file (an fsync), and that's orders of magnitude slower than touching RAM. Six hundred bookings, six hundred commits, six hundred awaited disk writes.

The lesson of the numbers: :memory: gives you real SQLite —the same engine, the same type and serialization rules you tested in module 5— at a cost close to the fake's. The file on disk gives you, in addition, real physical persistence, at a cost dozens of times greater. For a suite of hundreds of integration tests, that difference decides whether your suite runs in a second or in half a minute.

The rule: memory by default, disk when you're testing it

With the numbers, the decision is clear and can be stated as a rule:

Use :memory: by default. It's fast, it isolates naturally (one database per connection), and it runs real SQLite —the engine you want to test in integration—. The vast majority of your Reservo integration tests verify the service↔repository seam: that book writes a row, that get reads it, that find_by_room filters. None of that depends on disk; :memory: tests them just as well and much faster.

Pay for disk only when what the test proves is about disk. There are things :memory: can't verify because, by definition, it doesn't touch disk:

• Persistence between processes or connections: that the booking survives closing the connection that wrote it and reopening the file with another —module 5's letter-in-the-mailbox test—. That requires a real file.
• Real startup behavior: creating the file if it doesn't exist, opening one that already has data, migrations running over a file with state.
• File-system details: permissions, full disk, paths —edge cases that only the real file reproduces—.

For those, tmp_path is the tool: it gives you the real file with cleanup included. For everything else, :memory:. The healthy proportion in an integration suite is usually: most in :memory:, a deliberate handful in files for the tests that specifically test disk persistence. That way your suite is fast where it can be and realistic where it must be.

A nuance about isolation, so as not to confuse it with the resource: neither :memory: nor the file isolates on its own; what isolates is the fixture that gives a new database per test. A :memory: shared between tests contaminates (lesson 2); a file with a fixed name, shared, does too. tmp_path isolates the file because it gives a unique path per test and deletes it afterward, just as :memory: isolates when each test opens its own connection. You choose the resource for speed and realism; the isolation is guaranteed by the fixture's life cycle, always.

Common mistakes

Believing :memory: isn't good for integration "because it's not real". What happens: someone dismisses :memory: thinking only disk is "real SQLite". Why it happens: "in memory" sounds like a mock-up. How to detect it: :memory: runs the same engine of SQLite as the file —the same type rules, the same serialization, the same SQL—; the only thing it doesn't have is the physical disk. Most of the seams you test don't depend on disk. How to fix it: use :memory: for everything that isn't specifically about physical persistence; reserve the file for that.

Using a file with a fixed name (test.db) and believing it isolates. What happens: sqlite3.connect("test.db") is done in the tests, and the file is shared between runs and between tests. Why it happens: it's the most direct to write. How to detect it: the file persists on disk after the suite, and a second run finds the first one's data —mysterious failures that depend on whether you ran before—. How to fix it: use tmp_path, which gives a unique path per test and deletes it; never a shared fixed name.

Paying for disk across the whole suite "just in case". What happens: everything is put in temporary files to "test the real thing", and the suite becomes dozens of times slower with no gain. Why it happens: it seems more rigorous. How to detect it: if your integration suite takes a long time and almost no test actually tests disk persistence, you're paying for the real runway to train takeoffs the simulator covered. How to fix it: :memory: by default; a file only in the tests that verify physical persistence. The suite's speed is a resource; don't burn it without gaining realism in exchange.

Exercises

Exercise 1 — Classify each test. For each one, say whether you'd run it in :memory: or in a temporary file, and why: (a) book writes a row and get reads it back with price_cents == 6000; (b) a booking survives closing the connection that created it and reopening the file with another; (c) find_by_room("focus") returns only Focus's bookings and not Studio's; (d) the repository creates the database file if it doesn't exist at startup.

Exercise 2 — Explain the 30x factor. Disk turned out to be more than thirty times slower than :memory: for the same load. Explain where that factor comes from —it's not that the SQL is slower— and what would happen to the ratio if save didn't commit on every write but only one at the end.

Exercise 3 — Convert module 5's fixture to tmp_path. In module 5 we used tempfile.mkstemp(suffix=".db") with os.close(fd) and a finally with os.remove(path) to test disk persistence. Rewrite that idea as a pytest fixture with tmp_path, and explain which two responsibilities tmp_path saves you compared to the raw code.

@pytest.fixture
def db_path(tmp_path):
    return tmp_path / "reservo.db"     # unique path per test; the file doesn't exist yet

def test_survives_reopen(db_path):
    conn1 = sqlite3.connect(db_path)
    # ... book with conn1 ...
    conn1.close()
    conn2 = sqlite3.connect(db_path)   # same path, new connection
    # ... verify that the booking is still there ...
    conn2.close()

Summary and next step

In this lesson you made the decision lesson 4 left open: which resource it hands over. You saw that it's the same fixture with yield with two fillings —:memory: or a temporary file with tmp_path— and you measured the difference with numbers on your machine: the fake at 2.1 ms, :memory: about three times more (6.8 ms), and disk more than thirty times what memory costs (217.6 ms), dominated by the fsyncs of each commit. With that you reached the rule: :memory: by default —fast, isolated, real SQLite—, and a temporary file only when the test proves something about disk: persistence between connections, startup over the file system, physical edge cases. And you learned that tmp_path gives you the unique file per test with cleanup included, without module 5's raw mkstemp/os.remove.

Before moving on you should be able to: decide between :memory: and a file depending on whether the test relies on disk; explain where the thirty-times factor comes from (the commit's fsyncs); and use tmp_path for a real, isolated, self-cleaning file.

What comes next is the other ingredient of the known state: the data. Up to here each test started from an empty database, but many integrations need to start from a populated state —"given there are already two bookings in Focus, when I query..."—. In lesson 6 you're going to seed integration data: create a known, minimal initial state, said out loud, before each test. You'll see how to make it explicit and why that keeps the tests readable, and where the boundary is with the Builder pattern in depth, which is the doubles guide. You already have the resource isolation; now you put the right data inside.

Resources

• pytest documentation — The tmp_path fixture — the official reference for tmp_path: a unique temporary directory per test with cleanup managed by pytest, the tool for this lesson's file databases.
• sqlite3 — Connecting to the database (Python documentation) — the reference for sqlite3.connect, including the special name :memory: for a RAM database and passing a file path for a disk database.
• SQLite — In-Memory Databases — SQLite's official documentation on :memory: databases: what they are, that they live tied to their connection, and that they run the same engine as file databases, the foundation for why :memory: is real SQLite.
• SQLite — How To Corrupt An SQLite Database File (section on fsync) — context on why SQLite waits for the disk on each commit (the durability guarantee), the origin of the thirty-times factor you measured.