8. Mini-project: a `book`→`get` integration against SQLite

Description

This is the module's practical close. The previous seven lessons gave you the concept piece by piece —what a real integration is, how to build the BookingService↔SQLite seam, what to double and what to leave real, the solitary/sociable vocabulary, how integration catches what the unit doesn't, and its cost—. Now it's your turn to weave it all into a single deliverable that demonstrates you know how to write a real integration test: you take Reservo's book, connect it to a real SqliteBookingRepository, and verify the complete flow book→get against a real database, green. It's not a new tool; it's the whole module condensed into a well-set-up integration suite and its justification.

What is really evaluated here isn't that you get the green —that, with what you learned, is direct— but that you can justify each design decision. Anyone can plug in a real repository and see a PASSED. The deliverable that matters is the reasoning: naming which seam you integrate, saying what you left real and why (the seam under test), what you doubled and why (the slow, the non-deterministic, the external), and explaining what your green asserts that a unit test couldn't. A student who turns in the green suite without the justification hasn't demonstrated they understood the module; one who turns in the complete justification has demonstrated they already know how to set up an integration with intention, not by imitation.

Connection to the module: this lesson is module 5's practical exam and its close. It gathers lesson 2's definition, lesson 3's seam, lesson 4's rule of what to double, lesson 5's vocabulary, and the awareness of the benefit and cost from lessons 6 and 7, and presents them as a project with deliverables and a reference solution. After the statement, it summarizes the module and points you to module 6, where you'll take this same integration to the difficult boundaries —transactions, files, HTTP— with the expert handling we left pending here.

Analogy: the road test on a real road

Think of the difference between the driving simulator and the final test on a real road. In the classes you used the simulator —doubles, everything controlled— to practice each maneuver without risk. But the license isn't given to you for the simulator: it's given to you when you drive a real car, on a real street, with the examiner beside you, and you demonstrate that the maneuvers you practiced work in the real world. It's not a new maneuver; it's the same ones you already know, now on real asphalt. And the examiner doesn't just watch that you arrive: they ask you why you braked there, why you yielded, why you chose that lane. Driving well isn't enough; you have to drive well with criterion and be able to explain it.

Your mini-project is that road test. You already practiced integration in the previous lessons —the simulator—; now you turn it in as finished work: real BookingService over real SqliteBookingRepository, the book→get on real asphalt (the database), green. And like the examiner, what the project evaluates isn't just the green, but that you know how to justify each decision: why the repository goes real, why the payment and email go doubled, what the green guarantees you. Passing is demonstrating that you no longer follow a recipe: you set up an integration with your own criterion.

The project: formal statement

Your task is to write and turn in a book→get integration test of Reservo against a real SqliteBookingRepository, verifying the complete flow, and justify its design. Specifically:

Write an integration suite that books Focus 3 h for the pro member Ana with real BookingService over a real SqliteBookingRepository, and verifies that the booking was correctly persisted and is retrievable from the real database.

• The seam under test is BookingService↔BookingRepository. The repository goes real (SqliteBookingRepository); it's not doubled, because it's the joint you test.
• The other collaborators —payment, email, clock— go doubled (StubPaymentGateway, SpyEmailSender, FixedClock), by lesson 4's rule: the external, the non-deterministic, the money-costing.
• Verify the complete flow: that get retrieves the booking with its id, its room_id, its confirmed status, and its price_cents of 6000. And, as the second face of the flow, that find_by_room finds it in the real database.

Deliverables

1. The integration suite, green with pytest: book→get against the real SqliteBookingRepository, verifying the fields that cross the seam, plus a find_by_room test. Each assertion looks at the real database through the repository.
2. The doubling decision map. For each of the collaborators —repo, payments, emails, clock, calendar— write whether you left it real or doubled it and one sentence of justification with lesson 4's rule. This is the deliverable that weighs most.
3. The green's claim. Write what your green suite guarantees exactly, and what it does not guarantee —what's left out (for example, physical persistence between restarts, or the start as datetime, which are other tests)—.
4. The real pytest output. Paste the report that proves the suite runs green on your machine —your own "What to expect" block—.

Worked example: the reference suite green

Here's the complete reference suite, the one you'd turn in. Two tests: the book→get that verifies the basic flow, and the find_by_room that verifies the seam's second face. Read it whole; then we run it and break down the reasoning in the reference solution.

# tests/test_reservo_integration.py — mini-project: book -> get against real SQLite
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)          # Focus 3 h
CLOCK = datetime(2026, 3, 1, 9)


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


def test_book_persists_a_confirmed_booking_in_real_sqlite():
    repo = SqliteBookingRepository(sqlite3.connect(":memory:"))
    service = make_service(repo)

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

    saved = repo.get(booking.id)                 # read from the real table
    assert saved.id == booking.id
    assert saved.room_id == "focus"
    assert saved.status == "confirmed"
    assert saved.price_cents == 6000


def test_the_booking_is_findable_by_room_in_real_sqlite():
    repo = SqliteBookingRepository(sqlite3.connect(":memory:"))
    service = make_service(repo)

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

    found = repo.find_by_room("focus")
    assert [b.id for b in found] == [booking.id]

Read it as a well-set-up integration. The repository is real —SqliteBookingRepository, the seam under test—; the payment, the email, and the clock are doubled in the make_service helper. The first test verifies the book→get flow: it books, reads back, checks that the fields that cross the seam arrived intact. The second verifies find_by_room: that the booking not only was saved, but is findable by its room in the real database. Both verify against the real repository, which is where the seam lives.

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

python3 -m pytest tests/test_reservo_integration.py -v

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

tests/test_reservo_integration.py::test_book_persists_a_confirmed_booking_in_real_sqlite PASSED [ 50%]
tests/test_reservo_integration.py::test_the_booking_is_findable_by_room_in_real_sqlite PASSED [100%]

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

Two greens in 0.02s. The book→get integration works: BookingService and SqliteBookingRepository, both real, collaborate through the repository seam. The booking the service created was written to a SQLite table, read back with its fields intact, and is findable by its room. With this you have deliverables 1 and 4. What's missing are the ones that weigh: the decision map and the green's claim.

Reference solution

Common mistakes

Turning in the green suite without the decision map. What happens: someone plugs in the real repository, sees the two PASSED, and considers the project done, without justifying what they left real and what they doubled. Why it happens: the green "feels" like the deliverable. How to detect it: if you can't write, for each collaborator, a sentence starting with "I left it real / I doubled it because...", you're missing the central deliverable. How to fix it: the mini-project evaluates the criterion, not the green; the green is the evidence, the decision map is the thesis. Justify each choice with lesson 4's rule.

Doubling the seam you claim to integrate. What happens: someone, out of habit or for speed, passes a FakeBookingRepository to the suite and calls it "book→get integration". Why it happens: the fake is faster and the doubling reflex is strong. How to detect it: look at which piece is at the seam the title claims to test. If it's a double, you don't integrate that seam —you have a unit test—. How to fix it: the seam under test goes real, no exception. If what you wanted was a unit test, that's fine, but don't call it integration or believe you tested the joint with the database.

Leaving the payment or the email real "to test more". What happens: someone, with the idea that more reality is better, leaves the PaymentGateway or the EmailSender real in addition to the repository. Why it happens: the intuition that more real is more confidence. How to detect it: if your integration charges cards, sends emails, or is slow, you left real something the rule says to double. How to fix it: this seam's integration only needs the real repository; the payment and the email aren't the joint under test and are external with effects, so they're doubled. Testing more joints in a test isn't a virtue: it's extra cost and fragility.

Exercises

Exercise 1 — Add the cancel flow to your suite. Extend the reference suite with a book→cancel→get integration test against the real repository. Think: will it pass with this module's buggy repository, or will the fix be needed? Describe what you expect and why.

def test_book_cancel_get_full_flow():
    repo = SqliteBookingRepository(sqlite3.connect(":memory:"))  # the fixed one
    service = make_service(repo)                                  # CLOCK = 9 days before
    booking = service.book(FOCUS, ANA, START, END)
    refund = service.cancel(booking.id)
    assert refund == 6000                                         # full refund
    assert repo.get(booking.id).status == "cancelled"

Exercise 2 — Verify the write with raw SQL. Add to your suite a test that verifies the row with a direct SELECT over the table, without going through repo.get. Explain what bug this test would catch that the worked example's don't.

def test_book_writes_the_expected_row():
    conn = sqlite3.connect(":memory:")
    repo = SqliteBookingRepository(conn)
    service = make_service(repo)
    booking = service.book(FOCUS, ANA, START, END)

    rows = conn.execute(
        "SELECT room_id, status, price_cents FROM bookings WHERE id = ?",
        (booking.id,),
    ).fetchall()
    assert len(rows) == 1
    assert rows[0] == ("focus", "confirmed", 6000)

Exercise 3 — Justify :memory: versus disk for your submission. The reference suite uses sqlite3.connect(":memory:"). Explain why it's the correct choice for this mini-project and in what case you should switch to a file on disk.

Summary and module close

With this mini-project turned in, you close module 5. You wrote with your own hands a real integration test: BookingService over SqliteBookingRepository, the book→get flow verified against a real database, green, with find_by_room as the seam's second face. And —what the project evaluates— you justified each decision: the repository real because it's the seam under test, the payment, email, and clock doubled by lesson 4's rule, and the precise claim of what your green guarantees and what it leaves out. You turned in the road test, not just the simulator.

You went through the whole module: the leap from certifying each piece to seeing them work together (lesson 1); the precise definition of a real integration, by its seam (lesson 2); the BookingService↔SQLite seam made tangible, with the row in the table and the persistence on disk (lesson 3); the golden rule of what to double and what to keep real (lesson 4); the solitary versus sociable vocabulary (lesson 5); the reward, integration catching the datetime→str in the complete flow with a TypeError (lesson 6); and the cost measured, which gives the pyramid its shape (lesson 7). You come out with your first real integration written and with the criterion for all the ones to come.

Where the guide goes next. This module gave you integration as a concept and the first real test; the two that follow refine it. Module 6 takes you to the specific boundaries in depth: a SQLite transaction with its commit and its rollback, a real file, an HTTP call to a stdlib http.server, and how to make all that fast and deterministic —the expert handling of the resources we used basically here—. Module 7 takes the data and isolation: rollback to isolate tests, fixtures that create and destroy a temporary database, and how to keep the integration tests independent and repeatable when they share real state —the solution to the seeding-and-cleaning burden lesson 7 left you feeling—. The integration you wrote today in its basic form, you're going to take to the difficult boundaries and to state under control.

Resources

• pytest documentation — How to invoke pytest (-v) — the reference for running your suite with -v and producing your own "What to expect" block with the PASSED, as in the worked example.
• sqlite3 — DB-API for SQLite (Python documentation) — the reference for the real resource you integrate; connect(":memory:"), execute, and fetchall, the pieces with which the repository saves and retrieves, and with which you verify the row with raw SQL.
• Martin Fowler — IntegrationTest — the frame that defines what an integration tests and that backs deliverable 3's green claim: a fact about the real collaboration, not an assumption about a double.
• testing-backend-applications-guide — the sister guide that picks up where module 6 arrives: testing a complete web app with a framework, routes, and end-to-end HTTP, the integration level this guide leaves marked at the border.