Unit vs integration test

A test stores one booking successfully. That result says nothing yet about a second booking for the same slot. In this illustrative example, the missing case also reveals a difference between a convenient database substitute and the real SQLite adapter.

Choose the boundary for the question#

To check a deadline calculation, call the function with known times and inspect its results. To check that an insert respects a database constraint, run the adapter against that database. The first question needs little surrounding machinery. The second needs the interaction that could be wrong.

Here we call the function-level check a unit test and the adapter-plus-SQLite check an integration test. Teams draw these boundaries differently. Martin Fowler’s discussion of unit tests explains both solitary tests, which replace collaborators, and sociable tests, which keep some collaborators real. A mock is not a requirement for every unit test.

These categories do not divide all defects into separate boxes. A wrong calculation may fail both a function test and a full booking test. An integration test with no useful assertion may miss a mismatch. The boundary determines what can be observed; the selected inputs and assertions determine what is actually checked.

What the substitute leaves out#

A test double stands in for a collaborator. A stub supplies chosen responses; a fake provides a simplified working implementation; a mock can check expected interactions. Those terms follow Fowler’s summary of Gerard Meszaros’s terminology. Their value depends on what the test is trying to learn.

Our fake stores bookings in a Python list. It deliberately leaves out uniqueness. The real adapter creates a fresh in-memory SQLite database, uses a parameterised insert and lets the schema enforce one stored reservation per slot.

class FakeBookings:
    """Deliberately incomplete substitute: does not enforce uniqueness."""

    def __init__(self) -> None:
        self.rows: list[tuple[int, str]] = []

    def add(self, booking_id: int, slot: str) -> None:
        self.rows.append((booking_id, slot))

    def count(self) -> int:
        return len(self.rows)

    def close(self) -> None:
        pass


class SQLiteBookings:
    """Tiny adapter: this example models one reservation per slot, no expiry."""

    def __init__(self) -> None:
        self.db = sqlite3.connect(":memory:")
        self.db.execute(
            "CREATE TABLE bookings ("
            "id INTEGER PRIMARY KEY, slot TEXT NOT NULL UNIQUE)"
        )

    def add(self, booking_id: int, slot: str) -> None:
        with self.db:
            self.db.execute(
                "INSERT INTO bookings (id, slot) VALUES (?, ?)",
                (booking_id, slot),
            )

    def count(self) -> int:
        return self.db.execute("SELECT count(*) FROM bookings").fetchone()[0]

    def close(self) -> None:
        self.db.close()

The UNIQUE constraint rejects a second non-null slot with the same value; NOT NULL also excludes a missing slot. These rules are described in SQLite’s CREATE TABLE documentation. Python exposes a constraint violation as sqlite3.IntegrityError, documented in its SQLite interface.

This is actual SQLite, even though its database lives in memory. It is not evidence for PostgreSQL, a deployed database’s migrations or two concurrent network requests. The example has no expiry or release of cancelled reservations; the rule is simply one stored reservation per slot.

A passing test with a missing constraint#

The first check below inserts one row and reads the count. Both implementations pass. The duplicate-slot check adds another row with a different booking ID but the same slot, expects rejection and checks that the original row remains. The last check makes sure different slots are allowed.

class DatabaseTests(unittest.TestCase):
    repository_type = SQLiteBookings

    def setUp(self) -> None:
        self.repository = self.repository_type()
        self.addCleanup(self.repository.close)

    def test_one_booking_is_stored(self) -> None:
        self.repository.add(1, "room-a-1800")
        self.assertEqual(self.repository.count(), 1)

    def test_duplicate_slot_is_rejected(self) -> None:
        self.repository.add(1, "room-a-1800")
        with self.assertRaises(sqlite3.IntegrityError):
            self.repository.add(2, "room-a-1800")
        self.assertEqual(self.repository.count(), 1)

    def test_different_slots_are_allowed(self) -> None:
        self.repository.add(1, "room-a-1800")
        self.repository.add(2, "room-b-1800")
        self.assertEqual(self.repository.count(), 2)

The shared test deliberately expects the SQLite-level error because its subject is this tiny storage adapter. A larger application might translate that error into a domain result such as slot_unavailable. Test that translation at its own boundary rather than leaking database-specific exceptions into every application test.

No deployment failed here. This is a reproducible explanation of one way an isolated test can omit a dependency’s behaviour. The wider lesson is not that substitutes are bad: it is that a substitute’s passing result does not establish details it did not model.

Run the same checks against both stores#

Save the complete Python example. Use Python 3.11 or later with the standard sqlite3 module. Nothing connects to a server or writes a database file. Each database test gets a fresh connection and closes it afterwards.

python3 booking_tests.py –demo fake runs three checks against the list. The duplicate-slot check fails because the expected exception is not raised. Exit code 1 is intentional.

python3 booking_tests.py –demo database runs the same three checks against SQLite. All pass and the process exits with code 0. The tests were executed with Python 3.13.5 and SQLite 3.46.1; no speed comparison is claimed.

python3 booking_tests.py runs all 11 passing checks across the corrected teaching examples. The run notes describe the deliberate failures and their maintenance check.

For a deadline mistake that needs no database, continue with the missing-boundary testing exercise. To preserve an existing caller response while changing structure, use characterization tests. These are different reasons to write a test, not competing demands to put every test at one layer.

When the boundary includes retries and external effects, idempotency keys and safe retries explains the additional questions. Choosing the component boundaries themselves belongs to the architecture roadmap. In each case, name the observation you need before selecting the test machinery.