Testing with database in FastAPI - Performance & Optimization

Performance: Testing with database

MEDIUM IMPACT

This concept affects the speed of test execution and the responsiveness of the development feedback loop.

Running automated tests that require database access

FastAPI

import pytest
from fastapi.testclient import TestClient
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from myapp.main import app
from myapp.database import Base, get_db

# Use in-memory SQLite for tests
SQLALCHEMY_DATABASE_URL = 'sqlite:///:memory:'
engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={'check_same_thread': False})
TestingSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base.metadata.create_all(bind=engine)

def override_get_db():
    db = TestingSessionLocal()
    try:
        yield db
    finally:
        db.close()

app.dependency_overrides[get_db] = override_get_db
client = TestClient(app)

def test_create_item():
    response = client.post('/items/', json={'name': 'test'})
    assert response.status_code == 200
    # This test uses a fast in-memory database

Tests run against a lightweight in-memory database, avoiding network and disk delays.

📈 Performance GainReduces test runtime by 70-90%; enables faster feedback and parallel test execution

Running automated tests that require database access

FastAPI

import pytest
from fastapi.testclient import TestClient
from myapp.main import app

client = TestClient(app)

def test_create_item():
    response = client.post('/items/', json={'name': 'test'})
    assert response.status_code == 200
    # This test uses the real database connection

Tests connect to the real database causing slow setup, teardown, and possible data pollution.

📉 Performance CostBlocks test execution for 100-500ms per test; slows CI pipelines significantly

Performance Comparison

Pattern	Test Runtime	I/O Operations	Resource Usage	Verdict
Real database connection	Slow (100-500ms per test)	High (network + disk)	High (DB server resources)	[X] Bad
In-memory SQLite database	Fast (10-50ms per test)	Low (memory only)	Low (no external resources)	[OK] Good

Rendering Pipeline

Testing with a real database triggers network and disk I/O, increasing test runtime and blocking the event loop. Using an in-memory database reduces these costs by keeping data in RAM and avoiding external calls.

→Test Execution

→I/O Operations

→Event Loop Blocking

⚠️ BottleneckI/O Operations with real database connections

Optimization Tips

1Avoid using real databases in automated tests to speed up test execution.

2Use in-memory or mocked databases to reduce I/O and resource usage during tests.

3Measure test durations to identify slow database-dependent tests and optimize them.

Performance Quiz - 3 Questions

What is the main performance drawback of using a real database in FastAPI tests?

A Tests cause browser rendering delays

B Tests use too much CPU for calculations

C Tests run slower due to network and disk I/O

D Tests increase CSS paint cost

DevTools: Terminal / CI logs

How to check: Measure test suite runtime before and after switching to in-memory DB; use pytest --durations to find slow tests

What to look for: Significant reduction in test execution time and less waiting on database setup/teardown