Tutorial 3: Write a Useful Integration Test

Contributor
Apr 19
3 min read

Unit tests with mocks can pass while production fails. Integration tests catch the bugs that live at the boundaries — your code meeting the database, the queue, another service. This tutorial walks through writing one.

What You'll Build

An integration test that exercises your code with a real database (in a test container), verifying actual data flow.

Step 1: Pick the Right Function (5 min)

Good candidates:

A function that writes to and reads from the database
A function that queries multiple tables
A function with transaction logic

Bad candidates:

Pure logic (covered by unit tests)
Code that hits a real external API (use a contract test)

For this tutorial: a create_user function that writes to a users table.

Step 2: Set Up a Test Database (15 min)

Don't use the dev database. Use a fresh test database.

Options:

Testcontainers — spin up a real DB container per test run
Docker Compose — pre-built test environment
SQLite for tests — only if your production DB behaves similarly
Schema-isolated test DB — schema reset between tests

Testcontainers is the cleanest. Sample for Python with Postgres:

from testcontainers.postgres import PostgresContainer

@pytest.fixture(scope="session")
def db_url():
    with PostgresContainer("postgres:15") as postgres:
        yield postgres.get_connection_url()

The test database is real Postgres. Tests run against the same engine as production.

Step 3: Write the Test Setup (10 min)

Each test should start clean.

import pytest
from sqlalchemy import create_engine
from yourapp.db import Base, User
from yourapp.services import create_user

@pytest.fixture
def db_session(db_url):
    engine = create_engine(db_url)
    Base.metadata.create_all(engine)
    
    Session = sessionmaker(bind=engine)
    session = Session()
    
    yield session
    
    session.rollback()
    session.close()
    Base.metadata.drop_all(engine)

The fixture creates tables, hands a session to the test, then cleans up.

Step 4: Write the Test (10 min)

def test_create_user_persists_to_database(db_session):
    # Arrange — no fixtures needed; database is clean
    
    # Act
    user = create_user(
        db_session,
        name="Sam Example",
        email="sam@example.com"
    )
    
    # Assert — verify by querying the DB
    fetched = db_session.query(User).filter_by(id=user.id).first()
    assert fetched is not None
    assert fetched.name == "Sam Example"
    assert fetched.email == "sam@example.com"
    assert fetched.created_at is not None

Notice: verification by re-querying, not by trusting the return value.

Step 5: Run It (1 min)

pytest tests/test_create_user.py

First run will be slow (container spin-up). Subsequent runs within the same session reuse the container.

Step 6: Add Edge Cases (15 min)

What if email is duplicate?

def test_create_user_rejects_duplicate_email(db_session):
    create_user(db_session, name="First", email="dup@example.com")
    
    with pytest.raises(IntegrityError):
        create_user(db_session, name="Second", email="dup@example.com")

What if email is malformed?

def test_create_user_validates_email_format(db_session):
    with pytest.raises(ValueError, match="invalid email"):
        create_user(db_session, name="Sam", email="not-an-email")

These tests catch issues that unit tests with mocks would miss.

Step 7: Watch for Test Isolation Issues (5 min)

If test_a leaves data that test_b depends on, tests pass when run together and fail when run alone. Or vice versa.

Test the isolation:

pytest tests/test_create_user.py::test_create_user_rejects_duplicate_email
pytest tests/test_create_user.py::test_create_user_persists_to_database

Each should pass alone.

If they don't, your fixtures aren't cleaning up properly. Fix the fixture, not the test.

Step 8: Measure the Speed (5 min)

Integration tests are slower than unit tests. Target:

Single test: 50-500ms
Full suite: under 5 minutes

If you're way over, profile. Common culprits:

Container spin-up per test (use session-scoped fixture)
Full schema recreation per test (use transactions instead)
Test data not cleaned, accumulating

What You Just Did

You wrote a test that exercises the real database. It catches:

SQL issues (typos, missing columns)
Schema mismatches
Constraint violations
Transaction-related bugs

These bugs are silent in unit-tested code. Integration tests surface them.

Common Failure Modes

SQLite for tests, Postgres in production. Different SQL dialects; tests pass, production fails.

Shared state between tests. Order-dependent failures.

Container-per-test slowness. Use session-scoped containers; transaction-scoped reset.

Mocking the database in integration tests. Defeats the purpose. Use the real engine.

No edge case coverage. Happy path only. Most integration bugs live in edges.

Next Tutorial

Test data is becoming complex. Build a factory: Tutorial 4: Build a Test Data Factory.

ShiftQuality