Module 6: Real Boundaries Db Files Http

4. `:memory:` versus a file

Next →

Description

Lesson 3 gave you the transaction; what remains is the other big decision of the database boundary, and it's one you make in every test without thinking: where the database lives. When you write sqlite3.connect(":memory:"), SQLite creates a complete database that lives in your process's RAM and dies when you close the connection. When you write sqlite3.connect("reservo.db") or sqlite3.connect(path), SQLite creates —or opens— a file on the disk that persists between connections and between program runs. Both are real SQLite: the same engine, the same SQL, the same transactions, the same serialization of the datetime to text. The difference isn't in the resource's rules —they're identical—, but in two axes: persistence (the memory one doesn't survive closing the connection; the file one does) and speed (the memory one is instant; the file one pays the disk's price on each commit).

Choosing well between the two is one of those small decisions that define whether your integration suite is fast and clean or slow and fragile. The rule, which this lesson justifies with real output, is simple: use :memory: by default —it's real SQLite, it exercises the whole boundary (serialization, transactions, types), and it's more than ten times faster and perfectly isolated because each connection is born with an empty database—; use a file only when what the test needs to prove is specifically persistence on disk —that the booking survives closing the connection, reopening, restarting—. Most of your integrations test the service↔database seam (serialization, transaction, logic), and for that :memory: is ideal; a minority tests that the data lasts, and for that you need the file.

Connection to the module: this lesson closes the database boundary lesson 3 opened. Lesson 3 gave you the transaction (commit/rollback, the visibility); this one gives you the resource choice (:memory: versus file). Together they give you the complete mastery of SQLite as a boundary, and they prepare two later lessons: lesson 5 uses a real file (with tmp_path) for the file boundary, with the same idea of persistence; and lesson 7 comes back to :memory: as the number-one tool for making the database tests fast and deterministic, and to the temporary file as the resource that has to be created and cleaned. The border with module 7 is respected: here you choose the resource according to what you test; how to systematically isolate the state of a shared file is there.

Analogy: the whiteboard and the notebook

Think of two ways to jot something down while you work. The first is a whiteboard: you write fast, read it, erase it, and when you leave the room and someone cleans it, nothing remains —every time you come in, the whiteboard is blank, ready for you, with no trace of the previous—. The second is a notebook: you write in ink, close the notebook, put it in a drawer, and tomorrow you open it and everything is still there; you can lend it to a colleague and they read the same thing you wrote. The whiteboard is instant and always clean, but ephemeral: what you jot down dies with the session. The notebook persists and is shared, but it's slower to handle and has to be cared for —if you don't erase it, yesterday's note mixes with today's—.

sqlite3.connect(":memory:") is the whiteboard: each connection is born with a blank database, you write and read at full speed in RAM, and when you close the connection everything is erased without a trace —which makes it, as a bonus, perfectly isolated between tests—. sqlite3.connect(path) is the notebook: you write to the disk in ink, the booking is still there when you close and reopen, and another connection to the same file reads what you wrote —but each write costs the disk's price, and if you don't clean the file, one test's data leaks into the next—. Neither the whiteboard is better than the notebook nor the reverse: you choose according to what you need. Just do a quick calculation and throw it away? Whiteboard. Jot down something that must last and that another will read? Notebook. This lesson is learning when each one, with Reservo and with numbers.

The whiteboard doesn't share: :memory: between connections

Let's start with the property that confuses most: a :memory: database is private to its connection. Two different sqlite3.connect(":memory:") are two different databases, each empty, that don't see each other —like two whiteboards in two rooms—. Booking in one doesn't appear in the other.

# tests/test_memory_vs_file.py — :memory: doesn't persist between connections
import sqlite3
import pytest
from reservo.sqlite_repo import SqliteBookingRepository
# ...Reservo imports and constants FOCUS, ANA, START, END, CLOCK above...


def make_service(repo):
    return BookingService(Calendar(), FixedClock(CLOCK),
                          StubPaymentGateway(ok=True), SpyEmailSender(), repo)


def test_memory_does_not_persist_across_connections():
    # Each connect(":memory:") is a NEW, empty database.
    repo1 = SqliteBookingRepository(sqlite3.connect(":memory:"))
    booking = make_service(repo1).book(FOCUS, ANA, START, END)

    repo2 = SqliteBookingRepository(sqlite3.connect(":memory:"))
    assert repo2.find_by_room("focus") == []      # the other database didn't even notice
    with pytest.raises(KeyError):
        repo2.get(booking.id)

repo1 books in its memory database. repo2 opens another :memory: connection —a new, blank database— and finds nothing: find_by_room("focus") returns an empty list and get(booking.id) raises KeyError, because for repo2 that booking never existed. This is the whiteboard: each :memory: connection is its own room with its own clean whiteboard. Far from being a defect, it's exactly what makes :memory: so convenient for tests —each one starts isolated, without the previous one leaving it data—.

The notebook does share: a file between connections

Now the file one, with the same test shape, to see the exact contrast. Two connections to the same file share the data: what one writes, the other reads.

def test_file_persists_across_connections(tmp_path):
    path = tmp_path / "reservo.db"

    repo1 = SqliteBookingRepository(sqlite3.connect(path))
    booking = make_service(repo1).book(FOCUS, ANA, START, END)

    repo2 = SqliteBookingRepository(sqlite3.connect(path))
    reread = repo2.get(booking.id)                 # the file shares it
    assert reread.price_cents == 6000

Identical to the previous one except for one thing: instead of ":memory:", the two connections point to the same path. And the result is inverted: repo2, a new connection, does find the booking repo1 saved, with its price_cents == 6000. The file is the shared notebook: repo1 wrote in ink (and save did commit), so repo2 reads the same. This is the only thing :memory: can't give you —persistence between connections—, and the reason why, when what you test is precisely that the data lasts, you need a file.

Both are real SQLite: the same row

A point worth fixing so as not to fall into the mistake of believing :memory: is "less real": both produce exactly the same row, with the same serialization. Memory isn't a toy version; it's the same engine without a disk.

def test_both_are_real_sqlite_same_row_shape(tmp_path):
    # The same code produces the same row in memory and on disk.
    mem = sqlite3.connect(":memory:")
    disk = sqlite3.connect(tmp_path / "reservo.db")
    for conn in (mem, disk):
        make_service(SqliteBookingRepository(conn)).book(FOCUS, ANA, START, END)

    q = "SELECT room_id, status, start, price_cents FROM bookings"
    assert mem.execute(q).fetchall() == disk.execute(q).fetchall()

The same booking, made in memory and on disk, produces identical rows —same room_id, same status, same start serialized to text, same integer price_cents—. That's why :memory: serves to test the database boundary: it exercises the serialization (the datetime→str appears the same), the transactions, and the types. The only thing it doesn't exercise, by not having a disk, is persistence between connections. Everything else is identical.

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

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

tests/test_memory_vs_file.py::test_memory_does_not_persist_across_connections PASSED [ 33%]
tests/test_memory_vs_file.py::test_file_persists_across_connections PASSED [ 66%]
tests/test_memory_vs_file.py::test_both_are_real_sqlite_same_row_shape PASSED [100%]

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

Three greens that draw the map: :memory: doesn't share between connections (whiteboard), the file does (notebook), and both produce the same row (the same engine). With that, the choice stops being a blind habit and becomes a decision with criterion.

How much the disk costs: the measurement

The second axis remains —speed—, and here a number is worth more than an intuition. Let's measure how long 500 bookings take in :memory: versus 500 in a file. Each book does a save with its commit, and there's the cost: committing to disk forces SQLite to make sure the bytes actually arrived, something RAM doesn't need.

# bench_memory_vs_file.py — how much the disk costs versus :memory:
import sqlite3
import tempfile
import time
from datetime import datetime, timedelta
# ...Reservo imports...

N = 500


def run(conn):
    repo = SqliteBookingRepository(conn)
    service = BookingService(Calendar(), FixedClock(datetime(2026, 3, 1, 9)),
                             StubPaymentGateway(ok=True), SpyEmailSender(), repo)
    start = datetime(2026, 3, 10, 9)
    t0 = time.perf_counter()
    for i in range(N):
        s = start + timedelta(hours=3 * i)
        service.book(FOCUS, ANA, s, s + timedelta(hours=3))
    return (time.perf_counter() - t0) * 1000


mem_ms = run(sqlite3.connect(":memory:"))
with tempfile.NamedTemporaryFile(suffix=".db") as f:
    disk_ms = run(sqlite3.connect(f.name))

print(f"{N} bookings in :memory: (RAM):    {mem_ms:8.2f} ms")
print(f"{N} bookings in a file (disk):     {disk_ms:8.2f} ms")
print(f"the disk was {disk_ms / mem_ms:5.1f}x slower")

What to expect. On my machine (Python 3.14.0), running it directly:

python3 bench_memory_vs_file.py
500 bookings in :memory: (RAM):       12.98 ms
500 bookings in a file (disk):       180.49 ms
the disk was  13.9x slower

The exact numbers vary between runs and between machines —the disk is the most unpredictable—, but the order of magnitude is stable: the file is more than ten times slower than memory, and sometimes much more. The cause is the commit of each save: committing to disk forces SQLite to guarantee that the data was physically written, an expensive operation RAM skips. Multiply that difference by hundreds of tests with dozens of writes each and you understand why the choice matters: a suite that uses :memory: where it doesn't need the disk runs in a blink; one that uses files out of habit drags. That's why the rule: :memory: by default, a file only when you test persistence.

Common mistakes

Using :memory: when the test needs to prove persistence on disk. What happens: someone wants to verify that the booking survives restarting the service, but tests it with :memory:; the test passes without proving anything real, because it never closes the connection. Why it happens: :memory: is the default habit. How to detect it: if your test asserts about persistence —"survives closing and reopening"— but uses :memory:, it's not testing what it says: a reopened :memory: database is a new, empty database. How to fix it: persistence on disk is only tested with a real file (with tmp_path), by writing, closing, and reopening. :memory: is for everything else.

Believing two :memory: connections share data. What happens: someone opens a :memory: connection to write and another to read, and is surprised the second sees nothing. Why it happens: it's assumed that :memory: is "a database" that's global, like a named file. How to detect it: if you write with a :memory: connection and another :memory: doesn't see it, it's not a bug: they're two different databases. How to fix it: each connect(":memory:") creates its own private database. If you need two connections to share an in-memory database, it's an advanced case (a shared URI) that's almost never needed; the usual thing is to use a single connection per :memory: database, or switch to a file if you really need to share.

Using a file with a fixed name and not cleaning it. What happens: someone uses sqlite3.connect("test.db") with a fixed name; the first test passes, but the second finds the first's data and fails mysteriously. Why it happens: the notebook persists —that's its virtue and its trap—. How to detect it: if a test passes in isolation but fails when running the whole suite, or depends on the order, it probably shares a file that isn't cleaned. How to fix it: for a temporary file per test, use pytest's tmp_path (lesson 5), which gives a unique path and deletes it when done. If you really don't need persistence between connections, use :memory:, which cleans itself. The systematic isolation of stateful resources is module 7.

Exercises

Exercise 1 — Choose the resource. For each Reservo test, say whether you'd use :memory: or a file, and why: (a) verify that book→get returns the booking with price_cents == 6000; (b) verify that a saved booking survives closing the connection and reopening with a new one; (c) run the repository's contract battery (save-and-read, missing get raises, save twice updates); (d) measure how long your integration suite takes in the realistic worst production case.

See solution
  • (a) :memory:. It tests the service↔database seam (serialization, logic, transaction), not persistence on disk. :memory: exercises it entirely, is instant, and comes isolated. It's the default case.
  • (b) File. Here what's tested is persistence on disk —surviving closing and reopening the connection—, and that :memory: can't give (a reopened :memory: database is new and empty). You need a real file, with tmp_path.
  • (c) :memory:. The contract verifies observable behavior through the interface (save and read, raise, update), all within a connection. :memory: fulfills it just like the disk, faster and isolated. Unless a clause explicitly talks about persisting between connections, memory.
  • (d) File. If the goal is to measure the realistic cost, you have to include the disk's price, because production uses a disk. Measuring with :memory: would give a misleadingly optimistic number. For the measurement, a file; for the functionality of almost all tests, memory.

The rule you're sharpening: :memory: by default (fast, isolated, exercises the whole boundary except persistence); a file when the test is about persistence on disk or when you measure the real cost.

Exercise 2 — Less real for being in memory? A colleague rules out :memory: for the integration tests "because it's not real SQLite, it's a simulation in RAM". Correct them precisely: what does :memory: exercise exactly like the disk, and what's the only thing it doesn't?

See solution

:memory: is not a simulation: it's the complete SQLite engine, running with the database in RAM instead of in a file. It exercises, exactly like the disk: the SQL (same queries, same INSERT ... ON CONFLICT), the serialization (the datetime is stored as text and comes back as str, the int crosses intact —test_both_are_real_sqlite_same_row_shape proved it, identical rows—), the transactions (commit, rollback, the isolation between connections from lesson 3), the column types, the constraints (PRIMARY KEY, NOT NULL). Everything that makes SQLite a boundary with its own rules is present in :memory:.

The only thing :memory: doesn't exercise, by not having a disk, is persistence between connections and between runs: a :memory: database dies when you close its connection, so it can't test "the booking survives a restart". For that —and only that— a file is needed. So the colleague is half wrong: :memory: is real SQLite and serves for the vast majority of the database boundary's tests; what has to be reserved for the file is the specific class of test that verifies durability on disk. Ruling out :memory: for "not being real" is trading speed and isolation for nothing.

Exercise 3 — Explain the number. In the measurement, the disk was more than ten times slower than memory, and the code block does one book (with its save and its commit) per iteration. Explain why the disk commit is so expensive compared to the memory one, and what would happen to the difference if save didn't commit on each call.

See solution

The disk commit is expensive because committing a transaction to a file forces SQLite to guarantee that the bytes were physically written to storage —not just in an operating-system buffer that could be lost in a power outage—. That guarantee (a synchronization operation with the disk) is slow by nature: the disk is physical hardware, orders of magnitude slower than RAM. With 500 bookings, that's 500 disk commits, each waiting for that guarantee. In :memory:, the commit has no disk to synchronize: committing is almost free, it just moves pointers in RAM. Hence the difference of more than 10x —sometimes much more—.

If save didn't commit on each call —for example, accumulating many writes and committing only once at the end—, the difference would shrink a lot, because you'd pay the disk's cost once instead of 500 times. That's a real optimization technique (grouping writes in a transaction), but it has a cost: if the program dies before the final commit, you lose all the accumulated writes —and you break the per-booking persistence Reservo guarantees today with its per-save commit—. It's a trade-off between speed and durability. For the tests, the cleanest way out isn't grouping commits, but using :memory: when you don't need the disk: it gives you the speed without sacrificing the per-write guarantee, because in RAM committing is cheap.

Summary and next step

In this lesson you made, with criterion, the second decision of the database boundary: where the database lives. With the whiteboard and the notebook you separated the two axes: :memory: is instant and always clean but ephemeral (dies with the connection, doesn't share between connections); a file persists and is shared but costs the disk's price. You proved it with real output: two :memory: connections don't see each other, two to the same file do, and both produce identical rows —because they're the same engine—. And you measured the cost: the disk was more than ten times slower, because of the commit that synchronizes with storage. Hence the rule: :memory: by default, a file only when you test persistence on disk or measure the real cost.

Before moving on you should be able to: choose between :memory: and a file according to what the test tests; explain why :memory: is real SQLite and what's the only thing it doesn't exercise; and explain why the disk commit is expensive.

With this you close the database boundary. What comes next is the second boundary: files. In lesson 5 you're going to export Reservo bookings to a real CSV file and import them back, using pytest's tmp_path fixture —which gives you a real temporary directory and cleans it itself—. You're going to see the text left on the disk, check the round-trip, and reencounter the boundary's serialization: in a file, as in the database, everything becomes text, and the integers have to be reconstructed.

Resources

← Previous3. The database boundary: a real transactionNext →5. The file boundary with `tmp_path`