3. `BookingService` and SQLite, together

Description

You have the definition; now you're going to build the integration in depth and convince yourself, beyond any doubt, that there's a real database on the other side of the seam. It's easy to write SqliteBookingRepository(sqlite3.connect(":memory:")), see a green, and believe you integrated something, without truly feeling that a row entered a table. This lesson closes that distance. You're going to make book write a booking through the real service, and then you're going to look at the row with raw SQL —without going through the repository, querying the table directly— to see with your own eyes what was saved and in what form. And you're going to do the most conclusive proof there is that there's real persistence: write the booking with one connection, close it, open a new connection to the same file, and check that the booking is still there. An in-memory fake doesn't survive that; a real database does.

The purpose isn't to learn SQL —that's tangential and we'll break it down in module 6—, but to feel the seam in your hands. When you see the row ('bk-...', 'focus', 'confirmed', '2026-03-10T09:00:00', 6000) come out of a direct query to the table, you're going to understand what "crossing the seam" means: the Python Booking object turned into a row of text and numbers, and that row lives in a data structure that isn't your process. And when the booking survives closing and reopening the file, you're going to understand why integration catches what the fake can't: the fake is your process; SQLite is another system, with its own rules of serialization, types, and persistence, and testing against it is testing against those rules.

Connection to the module: this lesson turns lesson 2's abstract integration into a tangible seam. Lesson 2 told you what integrating the repository seam is; this one makes you see it —the row in the table, the persistence on disk—. With that installed, lesson 4 will give you the criterion of what to leave real and what to double around this seam, and lesson 6 will use this same, now-familiar seam to catch the datetime bug in the complete flow. Here we don't catch anything yet: we consolidate the basic seam —book→get— and prove, in two different ways, that the persistence is real. The border with module 6 is respected: transactions, rollback, and fine file handling are theirs; here we open and close connections just enough to demonstrate that there's a real database, without getting into its expert handling.

Analogy: the letter you drop in the mailbox

Think of the difference between dictating a message to someone in your same room and dropping a letter in the mailbox. If you dictate the message to the person next to you, and a minute later you ask them what you said, they repeat it: but that doesn't prove the message "went out" anywhere —it's still in the room, in the memory of someone who's with you—. That's the fake: you store the booking in a dict in your process and read it back; it never left your program. Dropping the letter in the mailbox is different: the envelope crosses a border, leaves your house, enters a system —the postal service— that you don't control, turns into a physical object that travels and is stored somewhere else. To prove it was really sent, you close your house door, leave, come back the next day, and find the reply letter in your own mailbox. The message survived your leaving: it existed outside of you.

Writing to SQLite is dropping the letter in the mailbox. The booking leaves your process, crosses the seam, and is stored as a row in a file that keeps existing even if you close the connection —even if you close the program—. The proof that it was real is the same as the letter's: close the connection (leave home), open a new one (come back the next day), and check that the booking is still in the table (the letter arrived). The fake can't pass that test, because the fake is the room, not the mailbox. In this lesson you drop the letter and verify it arrived.

Seeing the row with raw SQL

Let's start by looking at what book writes to the table, querying it without going through the repository. This is important: if we verify with repo.get(...), we're trusting the repository's own code to read what the repository's own code wrote —if both share an error, we wouldn't see it—. Querying the table with direct SQL is looking at the seam from the outside, with a tool that isn't the one that wrote it.

# tests/test_row_in_table.py — see the REAL row left in the table
import sqlite3
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(id="focus", name="Focus", capacity=4, hourly_cents=2500)
ANA = Member(id="m-ana", name="Ana", tier="pro")
START = datetime(2026, 3, 10, 9)
END = datetime(2026, 3, 10, 12)
CLOCK = datetime(2026, 3, 1, 9)


def test_book_writes_one_row_readable_with_raw_sql():
    conn = sqlite3.connect(":memory:")
    repo = SqliteBookingRepository(conn)
    service = BookingService(Calendar(), FixedClock(CLOCK),
                             StubPaymentGateway(ok=True), SpyEmailSender(), repo)

    booking = service.book(FOCUS, ANA, START, END)

    # We query the table directly, without going through the repository.
    rows = conn.execute(
        "SELECT id, room_id, status, start, price_cents FROM bookings"
    ).fetchall()
    assert len(rows) == 1                          # a single row
    print("\nRow in the table:", rows[0])
    assert rows[0][1] == "focus"                   # room_id
    assert rows[0][2] == "confirmed"               # status
    assert rows[0][3] == "2026-03-10T09:00:00"     # start, stored as TEXT
    assert rows[0][4] == 6000                      # price_cents, a real INTEGER

Notice the key detail: we use the same conn connection we passed to the repository, but we query it ourselves, with our own SELECT. The print is there so you see the raw row in the output. And the assertions examine the row exactly as SQLite stored it: the start is the text '2026-03-10T09:00:00' —like that, in quotes, because in the table it's a string—, and the price_cents is the number 6000. Let's run with -s so pytest doesn't swallow the print.

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

python3 -m pytest tests/test_row_in_table.py -v -s

============================= test session starts ==============================
platform darwin -- Python 3.14.0, pytest-9.1.1, pluggy-1.6.0
collected 1 item

tests/test_row_in_table.py::test_book_writes_one_row_readable_with_raw_sql
Row in the table: ('bk-m-ana-...', 'focus', 'confirmed', '2026-03-10T09:00:00', 6000)
PASSED

============================== 1 passed in 0.01s ===============================

There's the seam, made visible. book ran through the real service and left a row in the bookings table: ('bk-m-ana-...', 'focus', 'confirmed', '2026-03-10T09:00:00', 6000). Read it from left to right and you'll see the Booking object turned into a tuple of database values: the id (text), the room (text), the status (text), the start (ISO text), the price (integer). The id is generated from the booking's start, which is why we show it elided as bk-m-ana-... —the exact number depends on your time zone—. What doesn't depend on anything of yours is the shape: the start is a string —'2026-03-10T09:00:00', not a datetime— because save serialized it with .isoformat() so it fits in the TEXT column. You're seeing, with your own eyes and with a tool foreign to the repository, exactly what a booking turns into when crossing the seam. That conversion —Python object to table row— is what a fake never does, and it's the root of everything module 6 will see with the start assertion.

The definitive proof: surviving a file reopen

Querying the table with SQL proves that the row is there while the connection lives. But a skeptic could say: "that's still memory; :memory: is a database in RAM". True. The unanswerable proof that there's real persistence is to use a file on disk, write with one connection, close it, and read with a new connection. If the booking is still there after closing the first connection, it wasn't your process's memory: it was in the file, on the disk.

# tests/test_survives_reopen.py — the booking survives closing and reopening the DB on disk
import os
import sqlite3
import tempfile
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(id="focus", name="Focus", capacity=4, hourly_cents=2500)
ANA = Member(id="m-ana", name="Ana", tier="pro")
START = datetime(2026, 3, 10, 9)
END = datetime(2026, 3, 10, 12)
CLOCK = datetime(2026, 3, 1, 9)


def test_booking_survives_closing_and_reopening_the_file():
    fd, path = tempfile.mkstemp(suffix=".db")
    os.close(fd)
    try:
        # Connection 1: book and CLOSE the connection.
        conn1 = sqlite3.connect(path)
        service = BookingService(Calendar(), FixedClock(CLOCK),
                                 StubPaymentGateway(ok=True), SpyEmailSender(),
                                 SqliteBookingRepository(conn1))
        booking = service.book(FOCUS, ANA, START, END)
        conn1.close()                                  # the in-memory object is gone

        # Connection 2: NEW connection to the same file, the booking is still there.
        conn2 = sqlite3.connect(path)
        repo2 = SqliteBookingRepository(conn2)
        reread = repo2.get(booking.id)
        conn2.close()

        assert reread.status == "confirmed"
        assert reread.price_cents == 6000
    finally:
        os.remove(path)

Read it calmly, because each step matters. We create a temporary file with tempfile.mkstemp. We open connection 1, book, and close it with conn1.close() —from there on, any state in that connection's memory disappears—. We open connection 2, completely new, pointing to the same file, and ask for the booking. If it comes out, it's because it was on the disk, not in the connection we closed. The finally with os.remove(path) cleans the file when done; it's a raw preview of the isolation module 7 will do with elegance.

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

python3 -m pytest tests/test_survives_reopen.py -v

============================= test session starts ==============================
platform darwin -- Python 3.14.0, pytest-9.1.1, pluggy-1.6.0
collected 1 item

tests/test_survives_reopen.py::test_booking_survives_closing_and_reopening_the_file PASSED [100%]

============================== 1 passed in 0.01s ===============================

Green, and with this no skeptic holds: the booking survived closing the connection that created it. conn1 wrote it, and conn1 no longer exists when conn2 reads it. That persistence between connections is impossible for the FakeBookingRepository —its dict lives in the instance; if you throw away the instance, everything is gone—. The letter reached the mailbox: it left your process, was stored in a disk file, and was still there when you came back with a new connection. This is the material difference between a double and the real thing, and it's why integrating against SQLite proves something no test with a fake can prove: that the booking really persists, with all the rules of serialization, types, and storage SQLite brings built in.

Why the commit matters (and why save does it)

A note is worth it, because it connects with module 6. If you look at save, it ends with self._conn.commit(). That commit is what makes the write permanent and visible to other connections. Without it, the booking would stay in an open transaction, alive only within conn1, and conn2 wouldn't see it —the test above would fail—. The commit is the exact border between "I've got it in mind" and "I dropped it in the mailbox".

We're not going to go deep here —transactions, commit, and rollback as tools are module 6, and using rollback to isolate tests is module 7—. But keep the intuition: crossing the seam into a real database isn't just turning the object into a row; it's also deciding when that row becomes permanent. Our save does commit on each write, which is the simplest and what makes the file-reopen test work. In module 6 you'll see why it's sometimes worth controlling that commit by hand, and in module 7, how to use that same lever so each test starts with a clean database.

Common mistakes

Verifying the write only with the same repository that made it. What happens: to check that book saved correctly, someone uses only repo.get(...), which runs the repository's same read code. Why it happens: it's the most convenient and usually enough. How to detect it: ask yourself whether an error shared between save and get —for example, both consistently using a wrong column name— would go unnoticed. If save and get make the same mistake, get doesn't give it away. How to fix it: at least once, verify the write with a tool independent of the repository —a raw SELECT over the table—, as in the first example. Not for every test, but yes to trust that the seam really saves what you think.

Believing :memory: proves persistence on disk. What happens: someone uses sqlite3.connect(":memory:") in all their tests and concludes they proved the booking persists. Why it happens: :memory: is real SQLite and is convenient and fast. How to detect it: a :memory: database lives only while the connection lives; it proves nothing about files or about surviving connection closure. How to fix it: :memory: is perfect for most integrations (fast and isolated), but if what you want to demonstrate is persistence on disk, you need a real file, to write, close, and reopen —as in the second example—. Choose the resource according to what the test wants to prove.

Forgetting to clean the temporary file. What happens: someone creates a database in a file with a fixed name (test.db) and doesn't delete it; the next test finds it with old data and fails mysteriously. Why it happens: real state persists —that's exactly its virtue and its danger—. How to detect it: if a test passes the first time and fails the second, or depends on the execution order, it probably shares a file that isn't cleaned. How to fix it: use tempfile.mkstemp for a unique file per test and delete it in a finally, as in the example. Systematic isolation —fixtures that create and destroy the database— is module 7; here, the finally is the honest minimum not to leave garbage.

Exercises

Exercise 1 — Predict the row. Without running anything, write the tuple SELECT id, room_id, member_id, start, end, status, price_cents FROM bookings would return after booking Focus 3 h for pro Ana with START = datetime(2026, 3, 10, 9) and END = datetime(2026, 3, 10, 12). Pay attention to the type of each value.

('bk-m-ana-...', 'focus', 'm-ana', '2026-03-10T09:00:00', '2026-03-10T12:00:00', 'confirmed', 6000)

Exercise 2 — Break the persistence on purpose. Take the file-reopen survival test and remove the line self._conn.commit() from the repository's save method (imagine a colleague deleted it). Without running, predict what would happen with conn2.get(booking.id) and why.

Exercise 3 — Why raw SQL and not repo.get? The first example verifies the write with a direct SELECT instead of with repo.get(...). Explain in which concrete situation the raw SELECT would catch a bug that repo.get would let through, and invent an example of that bug in SqliteBookingRepository.

Summary and next step

In this lesson you touched the BookingService↔SQLite seam with your hands. You made book write through the real service and looked at the resulting row with a raw SELECT —('bk-m-ana-...', 'focus', 'confirmed', '2026-03-10T09:00:00', 6000)—, seeing the Booking object turned into a tuple of text and numbers, with the start already as a string. And you proved the persistence in the most conclusive way: writing with one connection, closing it, and reading the booking with a new connection to the same file on disk —the letter that survived your leaving home—, something no in-memory fake can achieve. Along the way you saw why save's commit is the border between thinking the message and dropping it in the mailbox.

Before moving on you should be able to: verify a write with a tool independent of the repository (raw SQL) and explain what bug it catches that get doesn't; demonstrate real persistence by closing and reopening a connection to a file; and explain the commit's role in making the booking permanent and visible to other connections.

So far we've left the repository real and doubled the payment, the email, and the clock, and we did it almost by instinct. It's time to turn that instinct into criterion. In lesson 4 comes integration's golden rule: what to keep real —the seam you test— and what to double —the slow, the non-deterministic, the external—, with the reason behind each decision. It's the criterion that lets you build an integration that tests what it wants without inheriting the cost and fragility of what it doesn't.

Resources

• sqlite3 — DB-API for SQLite (Python documentation) — the reference for the real resource we integrate; in particular, Connection.execute, Cursor.fetchall, and Connection.commit, the three pieces with which we write the row, read it with raw SQL, and make it permanent.
• sqlite3 — Connection.commit (Python documentation) — the exact detail of what commit does and why without it the write doesn't persist between connections, the basis of exercise 2.
• tempfile — Temporary files and directories (Python documentation) — the reference for mkstemp, with which we create a unique database file per test to demonstrate persistence on disk without leaving garbage.
• pytest documentation — Output capture (-s) — why pytest swallows prints by default and how -s lets them through, to reproduce the "What to expect" block that shows the raw table row.