FastAPI Database Patterns — Core Concepts

Why database patterns matter

Most FastAPI applications are thin layers over a database. The quality of your database integration — how you manage connections, structure queries, and handle transactions — determines your application’s reliability, performance, and maintainability.

FastAPI doesn’t mandate a specific database library, but SQLAlchemy is the dominant choice due to its maturity, flexibility, and excellent async support. The patterns below focus on SQLAlchemy but the concepts apply broadly.

Connection and session management

The fundamental pattern: create an engine once (application startup), create sessions per request (via dependency injection), and let FastAPI’s dependency lifecycle handle cleanup.

The engine represents the database connection pool. You create it once with your database URL and pool configuration. The session is a workspace for database operations within a single request. It tracks changes, manages transactions, and provides query capabilities.

FastAPI’s Depends system makes this elegant. A get_db dependency creates a session, yields it to the route, and closes it afterward — even if the route raises an exception.

Sync vs async database access

Synchronous (traditional): Uses sqlalchemy’s standard create_engine and Session. SQLAlchemy runs queries in the calling thread. FastAPI automatically runs sync dependencies in a thread pool to avoid blocking the event loop.

Asynchronous: Uses create_async_engine and AsyncSession from sqlalchemy.ext.asyncio. Queries are truly async — they yield to the event loop while waiting for the database, allowing other requests to proceed.

Async is better for high-concurrency applications (hundreds of simultaneous requests) because it doesn’t consume thread pool slots for database I/O. Sync is simpler and works with a broader range of SQLAlchemy features.

For most applications, sync SQLAlchemy with FastAPI’s automatic thread pool handling is perfectly adequate up to moderate traffic. Switch to async when thread pool contention becomes measurable.

The repository pattern

Instead of putting database queries directly in route handlers, the repository pattern creates a layer of abstraction:

A repository class encapsulates all database operations for a specific entity (User, Product, Order). Routes depend on repositories instead of raw sessions. Benefits:

• Testability: Mock the repository instead of the database
• Reusability: The same query logic works across routes, background tasks, and CLI commands
• Maintainability: Database logic changes in one place, not scattered across routes

The tradeoff is more code upfront. For small applications with straightforward CRUD, repositories might be over-engineering. For applications with complex queries, business logic, or multiple data consumers, they pay off quickly.

Migration management

Database schemas evolve. Alembic (SQLAlchemy’s migration tool) tracks schema changes as versioned migration scripts:

• alembic revision --autogenerate -m "add user email column" generates a migration by comparing your models to the database
• alembic upgrade head applies pending migrations
• alembic downgrade -1 rolls back the last migration

Auto-generation is a starting point, not a finished product. Always review generated migrations — Alembic can’t detect renamed columns (it sees a drop + add), and it sometimes misses index changes.

Run migrations as a separate step in your deployment pipeline, not at application startup. Starting multiple Uvicorn workers that all try to migrate simultaneously causes failures.

Common misconception

Developers often create a new engine per request instead of a new session. This is expensive — each engine creates its own connection pool. You should have one engine for the entire application lifetime and create lightweight sessions from it. The engine handles connection pooling; the session handles per-request state.

Performance basics

• Connection pool sizing: Default pool size of 5 connections is too small for production. A good starting point: pool_size=20, max_overflow=10, adjusted based on your database’s max connections and number of application workers.
• Eager loading: N+1 queries (loading a list of orders, then querying each order’s items separately) are the most common performance issue. Use joinedload or selectinload to load related data in fewer queries.
• Read replicas: Route read-only requests to database replicas. Create two engines (primary and replica) and use different dependencies for read and write operations.

The one thing to remember: A single engine with per-request sessions via dependency injection is the foundation — layer repositories on top for complex apps, use Alembic for migrations, and watch for N+1 queries as the first performance optimization.