FastAPI Test Database
When developing FastAPI applications that interact with databases, it's critical to have a proper testing strategy. In this guide, we'll explore how to set up and use test databases for FastAPI applications to ensure your API endpoints are thoroughly tested without affecting your production data.
Introduction to Test Databases
A test database is a separate database instance used exclusively for testing purposes. When writing tests for your FastAPI application, you want to:
- Isolate your tests from production data
- Start each test with a known database state
- Prevent tests from interfering with each other
- Easily reset the database between tests
Using a dedicated test database allows you to achieve all of these goals while writing comprehensive tests for your API endpoints.
Setting Up a Test Database
Let's start by setting up a test database configuration for a FastAPI application that uses SQLAlchemy ORM.
Step 1: Define Test Database Configuration
First, create a separate configuration for your test database:
python
# config.py
import os
DATABASE_URL = os.environ.get("DATABASE_URL", "postgresql://user:password@localhost/app_db")
TEST_DATABASE_URL = os.environ.get("TEST_DATABASE_URL", "postgresql://user:password@localhost/test_db")
Step 2: Create Database Setup Functions
Next, let's create functions to set up and tear down our test database:
python
# database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from config import DATABASE_URL, TEST_DATABASE_URL
# Production database settings
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()
# Test database settings
test_engine = create_engine(TEST_DATABASE_URL)
TestSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=test_engine)
def get_db():
"""Dependency for getting the database session"""
db = SessionLocal()
try:
yield db
finally:
db.close()
def get_test_db():
"""Dependency for getting the test database session"""
db = TestSessionLocal()
try:
yield db
finally:
db.close()
Creating Test Database Fixtures with Pytest
Pytest fixtures are a great way to set up and tear down your test database. Let's create fixtures for our FastAPI tests:
python
# conftest.py
import pytest
from fastapi.testclient import TestClient
from sqlalchemy.orm import Session
from database import Base, get_test_db, test_engine
from main import app
from models import User, Item # Import your models
# Override the get_db dependency to use the test database
app.dependency_overrides[get_db] = get_test_db
@pytest.fixture(scope="session")
def db_engine():
Base.metadata.create_all(bind=test_engine)
yield test_engine
Base.metadata.drop_all(bind=test_engine)
@pytest.fixture(scope="function")
def db_session(db_engine):
connection = db_engine.connect()
transaction = connection.begin()
session = Session(bind=connection)
yield session
session.close()
transaction.rollback()
connection.close()
@pytest.fixture(scope="function")
def client(db_session):
app.dependency_overrides[get_test_db] = lambda: db_session
with TestClient(app) as test_client:
yield test_client
This setup provides:
- A session-scoped fixture that creates and drops database tables
- A function-scoped fixture that creates a transaction for each test
- A client fixture for making requests to your FastAPI app
Using Test Database in Tests
Now let's write some tests using our test database setup:
python
# test_api.py
import pytest
from fastapi.testclient import TestClient
def test_create_user(client, db_session):
"""Test creating a user via API endpoint"""
response = client.post(
"/users/",
json={"email": "[email protected]", "password": "password123", "full_name": "Test User"}
)
assert response.status_code == 201
data = response.json()
assert data["email"] == "[email protected]"
assert data["full_name"] == "Test User"
assert "id" in data
def test_get_users(client, db_session):
"""Test retrieving users from database"""
# First create a user
client.post(
"/users/",
json={"email": "[email protected]", "password": "password123", "full_name": "User One"}
)
# Now get all users
response = client.get("/users/")
assert response.status_code == 200
data = response.json()
assert len(data) >= 1
assert any(user["email"] == "[email protected]" for user in data)
Using In-Memory SQLite for Testing
For some applications, you might want to use an in-memory SQLite database for testing. This approach is faster and doesn't require setting up a separate database server:
python
# database.py (updated version for in-memory SQLite)
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from config import DATABASE_URL
# Production database settings
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()
# In-memory SQLite for tests
TEST_SQLALCHEMY_DATABASE_URL = "sqlite:///:memory:"
test_engine = create_engine(TEST_SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False})
TestSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=test_engine)
Be aware that SQLite doesn't support all PostgreSQL or MySQL features, so some database functionality might behave differently.
Seeding Test Data
Often, you'll want to prepopulate your test database with data. Here's how to create a fixture for that:
python
# conftest.py (additional fixture)
@pytest.fixture(scope="function")
def seed_data(db_session):
"""Seed the database with test data"""
# Create some users
user1 = User(email="[email protected]", hashed_password="hashed_pwd1", full_name="User One")
user2 = User(email="[email protected]", hashed_password="hashed_pwd2", full_name="User Two")
db_session.add_all([user1, user2])
db_session.commit()
# Create some items related to users
item1 = Item(title="Item One", description="First item", owner_id=user1.id)
item2 = Item(title="Item Two", description="Second item", owner_id=user1.id)
item3 = Item(title="Item Three", description="Third item", owner_id=user2.id)
db_session.add_all([item1, item2, item3])
db_session.commit()
return {"users": [user1, user2], "items": [item1, item2, item3]}
Now you can use this fixture in your tests:
python
def test_get_items_by_user(client, db_session, seed_data):
"""Test getting items belonging to a specific user"""
user_id = seed_data["users"][0].id
response = client.get(f"/users/{user_id}/items/")
assert response.status_code == 200
data = response.json()
# User 1 should have 2 items
assert len(data) == 2
assert data[0]["title"] in ["Item One", "Item Two"]
assert data[1]["title"] in ["Item One", "Item Two"]
Testing Database Migrations
If you use Alembic for database migrations, you'll want to test that migrations work correctly:
python
# test_migrations.py
import pytest
from alembic.command import upgrade as alembic_upgrade
from alembic.config import Config as AlembicConfig
def test_migrations(db_engine):
"""Test that all migrations can be applied successfully"""
# Create an Alembic configuration
alembic_config = AlembicConfig("alembic.ini")
# Apply all migrations
alembic_upgrade(alembic_config, "head")
# If we get here without errors, the test passes
# You could also inspect the database schema to ensure all expected tables exist
Real-World Example: E-commerce API Testing
Let's put everything together in a real-world example for an e-commerce API:
python
# test_orders.py
import pytest
from datetime import datetime
def test_create_order(client, db_session, seed_data):
"""Test creating a new order"""
# Get a user from seed data
user = seed_data["users"][0]
items = seed_data["items"][:2] # Get the first two items
# Create an order
response = client.post(
"/orders/",
json={
"user_id": user.id,
"items": [{"item_id": items[0].id, "quantity": 2}, {"item_id": items[1].id, "quantity": 1}],
"shipping_address": "123 Test St, Test City, TS 12345"
}
)
assert response.status_code == 201
data = response.json()
assert data["user_id"] == user.id
assert len(data["items"]) == 2
assert data["status"] == "pending"
assert "created_at" in data
def test_order_lifecycle(client, db_session, seed_data):
"""Test the complete lifecycle of an order"""
# Create a new order
user = seed_data["users"][0]
item = seed_data["items"][0]
create_response = client.post(
"/orders/",
json={
"user_id": user.id,
"items": [{"item_id": item.id, "quantity": 1}],
"shipping_address": "123 Test St"
}
)
order_id = create_response.json()["id"]
# Update order status to processing
client.patch(f"/orders/{order_id}", json={"status": "processing"})
# Update order status to shipped with tracking info
client.patch(
f"/orders/{order_id}",
json={"status": "shipped", "tracking_number": "TRACK123456"}
)
# Check final order state
get_response = client.get(f"/orders/{order_id}")
assert get_response.status_code == 200
order_data = get_response.json()
assert order_data["status"] == "shipped"
assert order_data["tracking_number"] == "TRACK123456"
Advanced: Using Factory Boy for Test Data
For more complex applications, you might want to use Factory Boy to create test data:
python
# factories.py
import factory
from factory.alchemy import SQLAlchemyModelFactory
from models import User, Item, Order
class UserFactory(SQLAlchemyModelFactory):
class Meta:
model = User
sqlalchemy_session_persistence = "commit"
email = factory.Sequence(lambda n: f"user{n}@example.com")
hashed_password = "hashed_test_password"
full_name = factory.Faker('name')
is_active = True
class ItemFactory(SQLAlchemyModelFactory):
class Meta:
model = Item
sqlalchemy_session_persistence = "commit"
title = factory.Faker('sentence', nb_words=4)
description = factory.Faker('paragraph')
owner = factory.SubFactory(UserFactory)
Using this with pytest:
python
# conftest.py (with factory boy)
@pytest.fixture
def user_factory(db_session):
UserFactory._meta.sqlalchemy_session = db_session
return UserFactory
@pytest.fixture
def item_factory(db_session):
ItemFactory._meta.sqlalchemy_session = db_session
return ItemFactory
# Example test with factories
def test_user_items(client, db_session, user_factory, item_factory):
# Create a user with 3 items
user = user_factory()
items = item_factory.create_batch(size=3, owner=user)
response = client.get(f"/users/{user.id}/items/")
assert response.status_code == 200
data = response.json()
assert len(data) == 3
Summary
In this guide, we've covered:
- Setting up a dedicated test database for FastAPI applications
- Creating pytest fixtures for database testing
- Working with in-memory SQLite databases for faster tests
- Seeding test data and managing database state between tests
- Testing database migrations
- Real-world examples of API testing with databases
- Using Factory Boy to create test data efficiently
By implementing these patterns, you can build a comprehensive test suite for your FastAPI application that verifies your API's behavior with real database interactions while maintaining test isolation and repeatability.
Additional Resources
- FastAPI Testing Documentation
- SQLAlchemy Documentation
- Pytest Documentation
- Factory Boy Documentation
- Alembic Documentation (for database migrations)
Exercises
- Create a test database setup for a FastAPI application with SQLAlchemy
- Write tests for CRUD operations on a User resource
- Create a fixture that resets the database between tests
- Set up Factory Boy factories for your application models
- Write a test that verifies database constraints (e.g., unique email addresses)
- Create a test for a complex query that joins multiple tables
- Implement a test for a database transaction that should be rolled back on error
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)