How to Configure Pytest with FastAPI and SQLite

When building a FastAPI application backed by a SQLite database, configuring Pytest properly is crucial for achieving fast, reliable, and isolated tests.

This guide will walk you through setting up an in-memory SQLite database specifically for your test suite, ensuring that your test data does not pollute your persistent development database.

Prerequisites

Ensure you have pytest installed in your environment:

uv add pytest --dev

Step 1: Centralize Database Connection Logic

Your FastAPI application should instantiate its database connection through a central config file (e.g., config/database.py). It should respect an environment variable (like SQLITE_DATABASE) to determine which file to connect to.

# config/database.py
import os
from pathlib import Path
from sqlite3 import connect

conn = None
curs = None

def get_db():
    global conn, curs
    # Default to the local file, but allow environment variable overrides
    db_name = os.getenv("SQLITE_DATABASE", "crud-in-the-cloud.db")

    conn = connect(db_name, check_same_thread=False)
    curs = conn.cursor()

get_db()

Step 2: Create the `conftest.py` File

Pytest looks for a conftest.py file to load global fixtures and configurations before running any tests. Place this file in your tests/ directory.

We will do two things in this file:

Intercept the database connection by setting the SQLITE_DATABASE environment variable to :memory:. This must happen before config.database is imported.
Create an autouse=True fixture that clears the database state and reseeds it before every test.

# tests/conftest.py
import os

# 1. Override the database connection BEFORE anything else imports it
os.environ["SQLITE_DATABASE"] = ":memory:"

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

# 2. Create an automatic isolation fixture
@pytest.fixture(autouse=True)
def reset_db():
    """
    Automatically runs before every test.
    This fixture ensures total test isolation by wiping the table 
    and reseeding it. Tests do not inherit mutated state.
    """
    # Wipe existing data
    curs.execute("DELETE FROM book")
    conn.commit()

    # Repopulate with baseline data
    seed_db()

Step 3: Write Your Tests

Now you can write your tests knowing that the database will always have exactly the data provided by seed_db(), and any modifications you make during the test will be destroyed instantly afterward.

# tests/unit/services/test_books.py
from services import books as code

def test_delete_book():
    # We know book ID 3 exists because of our seed_db() baseline
    resp = code.delete_book(3)
    assert resp is True

    # Ensure it's actually gone
    assert code.get_one_book_by_id(3) is None

def test_get_all_books():
    # Even if this runs AFTER test_delete_book, it will still find 5 books
    # because the reset_db fixture restored the database!
    resp = code.get_all_books()
    assert len(resp) == 5

Step 4: Run the Suite

Run your test suite from the terminal:

uv run pytest tests/