Module 6: Real Boundaries Db Files Http

3. The database boundary: a real transaction

Next →

Description

We cross the first boundary in depth, and we open it by its most characteristic rule: the transaction. In modules 5 and 1 you used SqliteBookingRepository and saw that the booking survives closing and reopening the file, but you passed over why: the line self._conn.commit() at the end of save. That line isn't a hygiene detail —it's the essence of the database boundary—. A database engine doesn't write each instruction in stone the moment you run it; it accumulates it in a transaction, a draft of changes that exists in suspense until you decide one of two things: commit it (commit), and then it becomes permanent and visible to the world; or discard it (rollback), and then it disappears as if it had never happened. Between the INSERT and the commit there's a limbo, and understanding that limbo is understanding the boundary.

This matters for two reasons this lesson demonstrates with real output. The first: the commit is what makes SqliteBookingRepository truly persist. Without it, the write lives only within your connection and dies when you close it; with it, it crosses to stone and another connection sees it. The second: the transaction is the lever module 7 will use to isolate tests —write without committing and do rollback at the end, so no test leaves a trace—. Here we don't use it to isolate yet; here we study it as a tool: what commit does, what rollback does, and what another connection sees —or doesn't see— while the transaction is in suspense. With that, the database boundary stops being magic and becomes a mechanism you can test.

Connection to the module: this is the first of two lessons on the database boundary. Here you see the transaction —commit, rollback, the visibility between connections—; lesson 4 sees the other decision of that boundary —a :memory: database versus a file one—. Together they give you the complete mastery of the SQLite resource. The border with module 7 is carefully respected: the rollback you see here is the transaction's tool (undo a draft of changes); using it as a technique to isolate each test —wrapping the test in a transaction and reverting it when done— is module 7. The difference is the same as between learning what a brake is and learning to drive in the city: here you meet the brake; there you use it with a strategy.

Analogy: the order before confirming

Think of building an order in an online store. You keep adding things to the cart: a book, some headphones, a charger. While the cart is open, nothing is final —you can remove the book, change the headphones, empty it entirely—, and, a key fact, no one else sees your cart: the store's inventory isn't touched, your card isn't charged, the warehouse prepares nothing. All of that lives in a draft that's only yours. There are exactly two ways to take the order out of that limbo. One: you press "Confirm purchase" —the commit—: at that instant the charge is made, the inventory drops, the warehouse receives the order, and the order becomes a fact all the store's systems see. The other: you close the tab without confirming —the rollback—: the cart is discarded, nothing was charged, the inventory never moved, and it's as if you never entered. The open cart is the transaction; confirming or abandoning are commit and rollback.

A SQLite transaction is that cart. When you do an INSERT, you put the booking in the cart: it exists, but in a draft that only your connection sees. If you do commit, you confirm the purchase: the row becomes permanent and any other connection finds it. If you do rollback, you abandon the cart: the row disappears and the file stays as it was. And while the cart is open —between the INSERT and the commit—, another connection that looks at the database doesn't see your booking, just as another customer doesn't see your cart. This whole lesson is opening the cart, looking at it from inside and from outside, and confirming it or abandoning it, to feel in your hands how the database boundary decides what's real and what isn't.

rollback: discard the cart

Let's start with what a double could never do: write a row and then undo it. We're going to insert a booking and, before committing, do rollback. Then we query the table: there should be no row. To have clean ground, first we create the schema and commit it separately —so the transaction we're going to undo contains only the INSERT—.

# tests/test_db_boundary.py — the rollback discards the write
import sqlite3
from reservo.sqlite_repo import SCHEMA

ROW = ("bk-1", "focus", "m-ana", "2026-03-10T09:00:00",
       "2026-03-10T12:00:00", "confirmed", 6000)
INSERT = ("INSERT INTO bookings "
          "(id, room_id, member_id, start, end, status, price_cents) "
          "VALUES (?, ?, ?, ?, ?, ?, ?)")


def test_rollback_discards_the_write(tmp_path):
    conn = sqlite3.connect(tmp_path / "reservo.db")
    conn.execute(SCHEMA)
    conn.commit()                       # the schema stays; we start clean

    conn.execute(INSERT, ROW)           # we write, inside an open transaction
    conn.rollback()                     # ...and undo it before committing

    rows = conn.execute("SELECT id FROM bookings").fetchall()
    assert rows == []                   # the rollback erased the write
    conn.close()

Read it slowly. We create the table and commit that creation: the schema is permanent. Then we insert the booking —that opens a transaction, the cart—. But instead of commit, we do conn.rollback(): we discard the cart. When we query SELECT id FROM bookings, the table is empty: the write was undone. Notice what this means: the row existed within the transaction —between the INSERT and the rollback it was there for this connection—, but since we didn't commit it, it was never real. The cart was abandoned.

commit: confirm the purchase, and have another connection see it

Now the opposite: insert, commit, and prove that the write is permanent in the most conclusive way —by opening it from a new connection—. If another connection, which didn't participate in the transaction, sees the row, it's because the commit made it real for everyone, not just for the connection that wrote it.

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

    conn1 = sqlite3.connect(path)
    conn1.execute(SCHEMA)
    conn1.execute(INSERT, ROW)
    conn1.commit()                      # now yes: permanent and visible to others
    conn1.close()

    conn2 = sqlite3.connect(path)       # NEW connection to the same file
    rows = conn2.execute("SELECT id, price_cents FROM bookings").fetchall()
    conn2.close()
    assert rows == [("bk-1", 6000)]

conn1 inserts and does commit; then closes. conn2 is a completely new connection to the same file, which never saw conn1's transaction. And yet it finds the row ("bk-1", 6000): that's only possible because the commit wrote it in stone, to the shared file. The price_cents comes back as 6000, an integer —the INTEGER crossed the boundary intact—. This is the real persistence you saw in module 5 with the booking that survived reopening the file; now you see the exact lever that allows it: the commit.

The limbo: the uncommitted write is invisible from outside

The most revealing proof of the boundary is looking at the cart while it's open, from another connection. An uncommitted write lives only within the transaction that made it; another connection sees the database as it was before. Let's prove it with two connections open at once: one writes without committing, the other looks and sees nothing; then the first commits, and then the second does see it.

def test_uncommitted_write_is_invisible_to_another_connection(tmp_path):
    path = tmp_path / "reservo.db"
    setup = sqlite3.connect(path)
    setup.execute(SCHEMA)
    setup.commit()
    setup.close()

    writer = sqlite3.connect(path)
    reader = sqlite3.connect(path)
    writer.execute(INSERT, ROW)         # written, but WITHOUT commit

    seen = reader.execute("SELECT id FROM bookings").fetchall()
    assert seen == []                   # the reader does NOT see the uncommitted write

    writer.commit()                     # we commit...
    seen_after = reader.execute("SELECT id FROM bookings").fetchall()
    assert seen_after == [("bk-1",)]    # ...now it does see it
    writer.close()
    reader.close()

The writer inserts but doesn't commit —the cart is open—. The reader, at that moment, queries the table and sees it empty: the writer's booking is invisible to it, because it lives in an uncommitted transaction. As soon as the writer does commit, the row becomes real for everyone, and the reader —the same connection, without reopening anything— now finds it. This property has a name —transaction isolation— and is one of a database's most important guarantees: a connection's half-made changes don't contaminate what the others see. Here you observe it; exercising it to isolate your tests is module 7.

What to expect. On my machine (Python 3.14.0, pytest 9.1.1), the three tests together, plus the repository one below:

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

tests/test_db_boundary.py::test_rollback_discards_the_write PASSED [ 25%]
tests/test_db_boundary.py::test_commit_persists_across_connections PASSED [ 50%]
tests/test_db_boundary.py::test_uncommitted_write_is_invisible_to_another_connection PASSED [ 75%]
tests/test_db_boundary.py::test_repository_save_persists_the_booking PASSED [100%]

============================== 4 passed in 0.03s ===============================

Four greens that portray the whole transaction: rollback discards, commit persists and makes visible, and an uncommitted write is invisible from outside. Those three truths are the database boundary —what distinguishes it from a dict that stores the object and that's it—.

Why save's commit is what makes Reservo persist

Let's close the circle with Reservo. SqliteBookingRepository.save ends with self._conn.commit(); that line is the one that, in the real flow, takes the booking out of limbo and makes it permanent. Let's prove it by crossing the boundary with the complete service: book saves through save (which commits), we close the connection, and we read with a new connection.

def test_repository_save_persists_the_booking(tmp_path):
    path = tmp_path / "reservo.db"
    conn = sqlite3.connect(path)
    repo = SqliteBookingRepository(conn)
    service = BookingService(Calendar(), FixedClock(CLOCK),
                             StubPaymentGateway(ok=True), SpyEmailSender(), repo)
    booking = service.book(FOCUS, ANA, START, END)
    conn.close()

    fresh = sqlite3.connect(path)       # save committed: survives reopening
    reread = SqliteBookingRepository(fresh).get(booking.id)
    fresh.close()
    assert reread.status == "confirmed"
    assert reread.price_cents == 6000

book books, and its internal save does commit; that's why, when we close conn and open fresh —a new connection—, the booking is still there with its confirmed status and its price of 6000 cents. If save didn't commit, the transaction would stay open within conn, and when closing it SQLite would discard it with an implicit rollback —fresh would find nothing and get would raise KeyError—. save's commit is, literally, what turns "I booked" into "the booking exists". That's the database boundary doing its job, and now you know exactly why.

Common mistakes

Forgetting the commit and believing it was saved. What happens: someone does conn.execute("INSERT ...") and takes for granted that the row landed; later, another connection —or the same program after restarting— doesn't find it. Why it happens: within the same connection and transaction, the write is seen, so it seems saved. How to detect it: if the row appears when reading it with the connection that wrote it but disappears when reopening or from another connection, a commit is missing. How to fix it: a write isn't permanent until you commit it; in Reservo, save commits for that reason. If you write raw SQL in a test, commit before closing or reading from another connection.

Believing rollback "deletes" in the sense of a DELETE. What happens: someone thinks rollback runs a delete of the inserted rows. Why it happens: the observable effect —the row is no longer there— resembles a DELETE. How to detect it: ask yourself what happens with a row that was already committed before opening the transaction; a rollback doesn't touch it. How to fix it: rollback deletes nothing; it discards the current transaction's uncommitted changes, returning the database to the state of the last commit. It's not a DELETE; it's an "undo" of the draft. This distinction is exactly what makes rollback useful for isolating tests (module 7): it reverts your changes without touching what was already there.

Testing the write only within the same connection and transaction. What happens: a test inserts and verifies with the same connection, without committing, and passes —giving false confidence that it persists—. Why it happens: the connection that wrote sees its own uncommitted writes. How to detect it: if your test never closes or reopens the connection, nor uses a second one, it's not testing persistence, only the transaction's memory. How to fix it: to test that something truly persists, commit it and read it from a new connection —as in test_commit_persists_across_connections—. It's the only way to distinguish "written in the cart" from "confirmed purchase".

Exercises

Exercise 1 — Predict the state after each operation. A connection runs, in order: execute(SCHEMA), commit(), INSERT of booking A, commit(), INSERT of booking B, rollback(). Without running anything, say which bookings are in the table at the end and why.

See solution

At the end there's only booking A. Go through the operations:

  1. execute(SCHEMA) + commit(): the bookings table is created and committed, empty.
  2. INSERT of A: opens a transaction and puts A in the cart.
  3. commit(): confirms the purchase; A becomes permanent.
  4. INSERT of B: opens a new transaction and puts B in the cart.
  5. rollback(): discards the current cart; B disappears, as if it had never been inserted.

The rollback in step 5 only affects the transaction open at that moment —the one containing B—. It doesn't touch A, which was already committed by its own commit in step 3. That's why the common-mistake rule matters: rollback returns the database to the state of the last commit, it doesn't delete what was already committed. The table ends with [A].

Exercise 2 — The missing-commit bug. A colleague "optimizes" SqliteBookingRepository.save by removing the line self._conn.commit() "because it makes saving slow". The tests that use a single connection and don't close it stay green. Explain which test would catch it and what error it would give exactly.

See solution

It would be caught by any test that reads the booking from a connection different from the one that wrote it, or that closes and reopens the connection —like test_repository_save_persists_the_booking—. Without the commit, save's write stays in an open transaction within the original connection. When that connection is closed (conn.close()), SQLite discards the uncommitted transaction with an implicit rollback, so the row never reaches the file.

The test then opens fresh = sqlite3.connect(path), a new connection, and does get(booking.id). The SELECT ... WHERE id = ? finds no row, fetchone() returns None, and the repository, seeing row is None, raises KeyError with the booking's id. The test would fail with KeyError, not with an AssertionError —it's not that the booking is wrong, it's that it doesn't exist—. The tests that use a single connection without closing it don't catch it because that connection sees its own uncommitted writes: the INSERT is in its open cart, visible only to it. The moral: to protect persistence you have to test it by actually crossing the boundary —another connection, or close and reopen—, not by reading from the same cart that wrote it.

Exercise 3 — Isolation between two connections. Two connections, a and b, are open to the same database with a booking already committed. a runs an INSERT of a second booking but does not commit. At that instant, how many bookings does a see when doing a SELECT? How many does b see? Explain.

See solution
  • a sees two bookings. The connection that did the INSERT sees its own uncommitted writes: the already-committed booking plus the one it just inserted in its open cart. For a, both exist.
  • b sees a single booking. The connection that did not participate in a's transaction sees the database as it was at the last commit: only the already-committed booking. The second, which a inserted without committing, is invisible to b —it lives in the limbo of a's transaction—.

This is exactly the isolation that test_uncommitted_write_is_invisible_to_another_connection demonstrated: a connection's half-made changes don't contaminate what the others see until they're committed. If a did commit, then b (without reopening) would start seeing two; if a did rollback, b would keep seeing one and a would go back to seeing one too. This guarantee is the one module 7 leverages to isolate tests: each test works within its own transaction, and by reverting it with rollback it leaves no trace for the others. Here you observe it as a property; there you use it as a strategy.

Summary and next step

In this lesson you crossed the database boundary by its most characteristic rule: the transaction. With the cart before confirming you understood that a write lives in a limbo until you decide commit (confirm: permanent and visible to everyone) or rollback (discard: as if it had never happened). You proved it with real output: the rollback left the table empty, the commit made a new connection see the row, and an uncommitted write was invisible to another connection until it was committed. And you closed the circle with Reservo: save's commit is, literally, what turns "I booked" into "the booking persists", as the close-and-reopen test showed.

Before moving on you should be able to: explain what commit and rollback do and how a rollback differs from a DELETE; demonstrate persistence by reading from a new connection; and explain why an uncommitted write is invisible from outside (transaction isolation).

What comes next is the other big decision of this boundary. In lesson 4 we put side by side the two ways of having real SQLite: a :memory: database —instant, without disk, that dies with the connection— and a file one —that persists between connections but costs more than ten times in speed—. You're going to see, with a real measurement, how much the disk weighs, and decide which to use according to what each test needs to prove. With this lesson's transaction and the next one's resource choice, you'll have the complete mastery of the database boundary.

Resources

← Previous2. What a boundary isNext →4. `:memory:` versus a file