Skip to content

How to Isolate FastAPI Tests with SQLite In-Memory Database

When building APIs with FastAPI and SQLite, running tests against a persistent development database can lead to "state pollution." If one test creates a record, subsequent tests inherit that modified state, leading to unpredictable failures like IntegrityError from primary key collisions or assertion errors on expected row counts.

This guide demonstrates how to achieve total test isolation by intercepting database initialization and dynamically swapping out the persistent database for an ephemeral, in-memory SQLite database (:memory:) during test execution.

1. Abstract the Database Connection

First, ensure your application doesn't hardcode the database file path. Use an environment variable to define the database location.

# config/database.py
import os
import sqlite3

# Default to the persistent file if the env variable isn't set
DB_PATH = os.environ.get("SQLITE_DATABASE", "crud-in-the-cloud.db")

conn = sqlite3.connect(DB_PATH, check_same_thread=False)
curs = conn.cursor()

2. Override the Database in conftest.py

Pytest automatically loads conftest.py before executing any tests. This makes it the perfect place to override the environment variable before any application modules are imported.

# tests/conftest.py
import os

# Intercept the database connection BEFORE importing application modules
os.environ["SQLITE_DATABASE"] = ":memory:"

import pytest
from config.database import conn, curs
from data.books import seed_db

@pytest.fixture(autouse=True)
def reset_db():
    """
    Automatically runs before every test to wipe and reseed the in-memory database.
    """
    curs.execute("DELETE FROM book")
    conn.commit()
    seed_db()

3. Understand the autouse=True Fixture

The reset_db fixture includes autouse=True. This instructs Pytest to automatically execute this function before every single test run without needing to explicitly inject the fixture into the test function's arguments.

By deleting all rows and reseeding the database, we guarantee an identical, clean baseline database state for every test, ensuring idempotency and eliminating flaky tests.

4. Write Endpoint Tests with TestClient

Now you can write integration tests without fear of side effects.

# tests/full/test_books_api.py
from fastapi.testclient import TestClient
from main import app

client = TestClient(app)

def test_create_book():
    # This will succeed even if another test created a book with ID 6 previously,
    # because the database was wiped and reseeded before this test ran.
    new_book = {
        "id": 6,
        "title": "New Book API",
        "author": "New Author",
        "year": 2024
    }
    response = client.post("/api/books/", json=new_book)
    assert response.status_code == 200