Integration tests

Two ways into the same idea

Before You Start

The Core Idea

1Code with a real database

Here an in-memory SQLite database is created clean for each test. The test goes through the code and the database together:

import sqlite3

SCHEMA = "CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT NOT NULL, qty INTEGER CHECK (qty > 0))"

def place_order(db, item, qty):
    cur = db.execute("INSERT INTO orders (item, qty) VALUES (?, ?)", (item, qty))
    return cur.lastrowid

def orders_for(db, item):
    return db.execute("SELECT id, qty FROM orders WHERE item = ?", (item,)).fetchall()

def fresh_db():
    db = sqlite3.connect(":memory:")
    db.execute(SCHEMA)
    return db

db = fresh_db()
place_order(db, "latte", 2)
place_order(db, "latte", 1)
assert orders_for(db, "latte") == [(1, 2), (2, 1)]
print("insert + select: ok")

db = fresh_db()
try:
    place_order(db, "latte", 0)
except sqlite3.IntegrityError as e:
    print("constraint enforced:", e)

2Testing a whole HTTP API

The test client sends requests to the app directly with no network. You check the status code and body, then the database:

from fastapi.testclient import TestClient
from app import app, get_db
import pytest

@pytest.fixture
def client(tmp_path):
    test_db = make_db(tmp_path / "test.db")       # fresh database per test
    app.dependency_overrides[get_db] = lambda: test_db
    yield TestClient(app)
    app.dependency_overrides.clear()

def test_create_and_read_order(client):
    r = client.post("/orders", json={"item": "latte", "qty": 2})
    assert r.status_code == 201
    order_id = r.json()["id"]
    assert client.get(f"/orders/{order_id}").json()["qty"] == 2

def test_rejects_zero_qty(client):
    assert client.post("/orders", json={"item": "latte", "qty": 0}).status_code == 422

3The test pyramid

The wide base: hundreds of unit tests in seconds. The middle: dozens of integration tests in minutes. The top: a few end-to-end browser tests (Playwright) for only the most important user journeys, since they are slower and more brittle.

Worked example

Problem: every unit test is green, and production returns 500 on creating an order. Analysis: a migration added a branch_id NOT NULL column, and the code does not send it. The unit tests mock the database, so they never noticed. Fix: an integration test that runs migrations on a real database, then creates an order. It failed at once, then passed after the field was added.

Myth vs Reality

Test your understanding

Quick review cards

Check Your Understanding