Sign in Get started

Testing in Practice · Foundations: Strategy, pytest and Test Doubles

Integration Tests Against a Real Database

Test data-access code against a real SQL engine with schema setup, per-test transactions that roll back, and checks that constraints hold.

Create a free account to save this page, take notes and track your progress. Sign in

What you'll build

Integration tests for an orders repository against a real SQL database: the schema is created once, each test runs inside a transaction that's rolled back (fast, isolated), and the tests prove that the SQL, constraints and transactions actually behave as expected, which mocks can't.

The idea

  • Data-access code fails in ways mocks hide: SQL typos, wrong joins, missing constraints, transaction bugs.
  • Run these tests against the same engine as production where possible (PostgreSQL in a container via Testcontainers or Docker Compose). This example uses SQLite to stay self-contained; the pattern is identical.
  • Create the schema once per session; wrap each test in a transaction and roll back, so tests don't see each other's data.
  • Test constraints and failure paths, not just inserts.

The code

Repository tests with per-test rollback
# file: orders.py
class OrderRepository:
    def __init__(self, conn):
        self.conn = conn

    def place(self, customer: str, sku: str, qty: int) -> int:
        cur = self.conn.execute("UPDATE stock SET available = available - ? WHERE sku = ? AND available >= ?", (qty, sku, qty))
        if cur.rowcount != 1:
            raise ValueError(f"not enough {sku} in stock")
        cur = self.conn.execute("INSERT INTO orders (customer, sku, qty) VALUES (?, ?, ?)", (customer, sku, qty))
        return cur.lastrowid

    def totals_by_customer(self):
        return self.conn.execute("SELECT customer, SUM(qty) FROM orders GROUP BY customer ORDER BY customer").fetchall()

# file: conftest.py
import sqlite3
import pytest

SCHEMA = '''
CREATE TABLE stock (sku TEXT PRIMARY KEY, available INTEGER NOT NULL CHECK (available >= 0));
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, sku TEXT NOT NULL REFERENCES stock(sku),
                     qty INTEGER NOT NULL CHECK (qty > 0));
'''

@pytest.fixture(scope="session")
def database(tmp_path_factory):
    conn = sqlite3.connect(tmp_path_factory.mktemp("db") / "test.db", isolation_level=None)
    conn.execute("PRAGMA foreign_keys = ON")
    conn.executescript(SCHEMA)
    conn.execute("INSERT INTO stock VALUES ('mug', 5), ('teapot', 1)")
    return conn

@pytest.fixture
def conn(database):
    database.execute("BEGIN")
    yield database
    database.execute("ROLLBACK")          # every test starts from the same data

# file: test_orders.py
import sqlite3
import pytest
from orders import OrderRepository

def test_placing_an_order_reduces_stock(conn):
    OrderRepository(conn).place("ana", "mug", 2)
    assert conn.execute("SELECT available FROM stock WHERE sku = 'mug'").fetchone()[0] == 3

def test_cannot_oversell(conn):
    repo = OrderRepository(conn)
    repo.place("ana", "teapot", 1)
    with pytest.raises(ValueError, match="not enough teapot"):
        repo.place("ben", "teapot", 1)

def test_totals_group_by_customer(conn):
    repo = OrderRepository(conn)
    for customer, qty in [("ana", 1), ("ben", 2), ("ana", 1)]:
        repo.place(customer, "mug", qty)
    assert repo.totals_by_customer() == [("ana", 2), ("ben", 2)]

def test_unknown_sku_violates_foreign_key(conn):
    with pytest.raises(sqlite3.IntegrityError):
        conn.execute("INSERT INTO orders (customer, sku, qty) VALUES ('ana', 'lamp', 1)")

def test_previous_tests_left_no_data(conn):
    assert conn.execute("SELECT COUNT(*) FROM orders").fetchone()[0] == 0

# file: main.py
import pytest
pytest.main(["-q", "--tb=short", "-p", "no:cacheprovider"])

Output

.....                                                                    [100%]
5 passed in <time>

Run this on your computer — see “Get Started” for setup.

Common mistakes

  • Replacing the database with mocks or an in-memory fake for tests about SQL.
  • Tests that depend on data left by other tests (and fail when run alone or in parallel).
  • A different database engine in tests than in production when features differ (JSON, locking, collations); use containers for the real engine.
  • Slow suites that recreate the whole schema for every test instead of rolling back transactions.

Key points

  • Test data-access code against a real database engine.
  • Create the schema once; roll back a transaction per test.
  • Assert on constraints and failure paths too.

Further reading: Testcontainers for Python · pytest fixture scopes

Practice questions

Why test repositories against a real database?

Answer

SQL errors, constraint behaviour, joins and transactions can only be verified by the database engine; mocks would pass while production fails.

How does per-test transaction rollback keep tests independent and fast?

Answer

Each test runs inside a transaction that's rolled back afterwards, so no data persists between tests and the schema isn't rebuilt each time.

When should tests use a containerised database instead of SQLite?

Answer

When production uses another engine whose features or behaviour (types, JSON, locking, SQL dialect) differ from SQLite.

Open in the app to mark lessons complete, take notes and track your progress.

Was this page helpful?