Mocking a Database

• Mock a DB connection to assert the right query ran.
• Use an in-memory SQLite as a fast, real "fake".
• Decide: mock the connection vs use a real test DB.
• Keep DB tests fast AND meaningful.

MOCK the connection:     verify the code SENT the right SQL/args.
                          Fast. But doesn't prove the SQL actually works.
REAL in-memory SQLite:   run the real SQL against a throwaway DB.
                          Fast (in RAM) AND proves the query is valid.

The code under test

# repo.py
class UserRepo:
    def __init__(self, conn):
        self.conn = conn
    def add(self, name, email):
        self.conn.execute(
            "INSERT INTO users (name, email) VALUES (?, ?)", (name, email))
        self.conn.commit()
    def find(self, email):
        row = self.conn.execute(
            "SELECT name FROM users WHERE email = ?", (email,)).fetchone()
        return row[0] if row else None

Approach A — real in-memory SQLite (recommended)

import sqlite3, pytest
from repo import UserRepo

@pytest.fixture
def repo():
    conn = sqlite3.connect(":memory:")
    conn.execute("CREATE TABLE users (name TEXT, email TEXT)")
    yield UserRepo(conn)
    conn.close()

def test_add_and_find(repo):
    repo.add("Aisyah", "a@x.com")
    assert repo.find("a@x.com") == "Aisyah"   # real SQL really ran

def test_find_missing(repo):
    assert repo.find("ghost@x.com") is None

This proves the actual SQL works — the table, the INSERT, the SELECT, the parameter binding. Fast because it's all in RAM, and fresh per test.

Approach B — mock the connection

from unittest.mock import Mock

def test_add_sends_correct_sql():
    conn = Mock()
    UserRepo(conn).add("Aisyah", "a@x.com")
    conn.execute.assert_called_once_with(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        ("Aisyah", "a@x.com"))
    conn.commit.assert_called_once()

This verifies your code sent the right SQL with the right args and committed — without a database at all. But it does NOT prove the SQL is valid (a typo'd column name would still pass).

The decision

SQLite, simple queries        → real :memory: DB (Approach A)
heavy DB (Postgres) in unit   → mock the connection (Approach B)
"did we commit / call once?"  → mock (you're testing behaviour)
"does the query return right?" → real DB (you're testing the SQL)

# test_repo.py — mostly real DB, one behaviour test with a mock
import sqlite3, pytest
from unittest.mock import Mock
from repo import UserRepo

@pytest.fixture
def repo():
    conn = sqlite3.connect(":memory:")
    conn.execute("CREATE TABLE users (name TEXT, email TEXT)")
    yield UserRepo(conn)
    conn.close()

# --- real DB: prove the SQL works ---
def test_add_then_find(repo):
    repo.add("Aisyah", "a@x.com")
    assert repo.find("a@x.com") == "Aisyah"

def test_find_unknown_returns_none(repo):
    assert repo.find("nope@x.com") is None

def test_two_users(repo):
    repo.add("A", "a@x.com")
    repo.add("B", "b@x.com")
    assert repo.find("b@x.com") == "B"

# --- mock: prove we commit (behaviour) ---
def test_add_commits():
    conn = Mock()
    UserRepo(conn).add("X", "x@x.com")
    conn.commit.assert_called_once()

$ pytest test_repo.py -v
test_add_then_find PASSED
test_find_unknown_returns_none PASSED
test_two_users PASSED
test_add_commits PASSED
4 passed in 0.02s

Read the diff

Most tests use the real in-memory DB — they prove the SQL actually inserts and queries correctly. One test uses a Mock connection purely to verify commit() is called (a behaviour the real DB wouldn't let you check as easily). Best of both worlds: meaningful SQL coverage plus a targeted behaviour check, all fast.

Two ways to test DB code: a real :memory: SQLite (fast + proves the SQL works — the default for SQLite) or a mocked connection (verifies the code sent the right query/commit, but not that the SQL is valid). Use the real DB for most tests; reach for mocks when the real DB is heavy or you specifically want to assert behaviour. Next: mocking an HTTP API.