FastAPI + SQLAlchemy + Alembic + PostgreSQL: The Real Integration Guide

Two years of production FastAPI + async SQLAlchemy. Here's the real deal, not the tutorial fantasy land where everything works first try.

What You're Actually Getting Into

FastAPI is actually fast, not marketing fast. The OpenAPI docs work great until you have nested Pydantic models with circular dependencies, then they shit the bed. But the performance claims are real - we killed a Django API doing 400 req/sec and replaced it with FastAPI hitting around 2,100 req/sec on the same hardware. The benchmarks aren't lying for once.

SQLAlchemy 2.0 fixed a lot of the shit that was broken in 1.4. The async support actually works now, but migrating from sync code will make you question your career choices. The new query syntax is cleaner but everything you knew is deprecated. Spent weeks reading the migration guide when upgrading our first production app.

Alembic generates migrations that usually work. The keyword is "usually." When it fucks up (and it will), you'll be writing raw SQL at 2 AM trying to fix your production database. But when it works, it's magic. Alembic docs are actually readable, which is rare for database tooling.

PostgreSQL is rock solid. Use version 16+ because the performance improvements are real. PostgreSQL 17 dropped in September 2024 with better JSON performance and parallel query improvements. JSON support is excellent, full-text search doesn't suck, and it scales better than you'll ever need.

The Reality of Async Everything

Async is incredibly fast when you get it right and an absolute clusterfuck when you don't. Learned this debugging why our API was pinning CPU at 100% - turns out one sync database call in an async endpoint blocks the entire event loop. Found a Stack Overflow thread that explained the gotchas - why FastAPI runs serial instead of parallel when you mix sync/async wrong.

SQLAlchemy 2.0's async engine actually works with FastAPI now. No more thread pool exhaustion bullshit. But debugging async database issues feels like debugging race conditions while drunk - you think you know what's happening, but you're probably wrong.

Migration Hell and How to Survive It

Alembic auto-generates migrations from your model changes. Sometimes it works perfectly. Sometimes it tries to drop your entire production table because you changed a field name.

Pro tip from 50+ production migrations: Always review the generated migration before running it. I've seen migrations that would have deleted customer data because Alembic thought a renamed column was a drop + add operation.

TestDriven.io has a decent migration walkthrough, but they skip the fun part where your migration works perfectly on your MacBook and pukes spectacularly on production Ubuntu.

Performance: What's Real vs Marketing

FastAPI's benchmarks claim ridiculous numbers. In practice, on our production hardware (8-core AWS instances), we see somewhere between 3-4k requests/second for database-backed APIs, depending on query complexity. That's still damn good - 10x better than our old Django setup.

The async database sessions prevent the connection pool exhaustion that killed our old sync setup. Connection pooling with asyncpg is solid - we run 20 connections max and handle thousands of concurrent users.

Docker and Deployment Reality

This stack containerizes beautifully. Docker-compose runs the whole thing cleanly. Alembic migrations run automatically on startup - usually. Sometimes they hang and you're SSH'ing into production containers like a caveman.

The type annotations help catch bugs before deployment, but they won't save you from logic errors or async session leaks. SQLAlchemy 2.0's improved typing is better than 1.4, but still not as good as the marketing claims.

Skip the theory. This is what actually works in production after debugging this stack 20 times since 2023. Half these patterns came from fixing shit that broke during deployments.

Project Structure That Won't Drive You Insane

Forget the perfect folder structure - this is what actually works after 6 months of development:

project/
├── app/
│   ├── database.py          # All database config in one place
│   ├── models.py           # SQLAlchemy models - don't split too early
│   ├── schemas.py          # Pydantic models
│   ├── main.py             # FastAPI app
│   ├── api/                # Split by feature, not by type
│   │   ├── users.py
│   │   └── orders.py
│   └── migrations/         # Keep it close to the models
├── docker-compose.yml      # For local development
└── requirements.txt        # Pin your fucking versions

Dependencies That Won't Break on You

Pin your versions or spend midnight debugging conflicts. I'm using:

fastapi==0.116.1
sqlalchemy==2.0.43
alembic==1.16.5
asyncpg==0.30.0
pydantic==2.9.2

These are stable production versions as of mid-2024. TestDriven.io tutorial shows the setup, but they skip the part about breaking changes between SQLAlchemy 2.0 minor versions. Check the SQLAlchemy release notes before upgrading - asyncio connection pool changes in 2.0.25 broke our production deployment patterns. Took us 4 hours to figure out why connections were hanging.

Database Config That Doesn't Leak Connections

Here's the async engine setup that actually works in production. The key is connection pooling - fuck it up and your app will crash under load:

DATABASE_URL = \"postgresql+asyncpg://user:pass@localhost:5432/db\"

engine = create_async_engine(
    DATABASE_URL,
    pool_size=20,
    max_overflow=0,  # Don't let it overflow or you'll hit Postgres connection limits
    pool_pre_ping=True,  # This saved me from connection timeout errors
    echo=False  # Turn on in development, off in production
)

The session dependency is critical. Don't fuck around with session lifetimes - I spent a week debugging memory leaks from improperly closed sessions:

async def get_db():
    async with AsyncSession(engine) as session:
        yield session

That's it. Don't get clever. Context managers handle cleanup automatically, which prevents the memory leaks I spent a week debugging.

Alembic Setup That Doesn't Hate You

The default Alembic config is broken for async. Here's what you need in alembic/env.py to not spend your weekend debugging migration failures:

from app.database import engine
from app.models import Base

## This is the line that will save your sanity
target_metadata = Base.metadata

NEVER trust autogenerated migrations blindly. Always run alembic revision --autogenerate -m \"description\" then read the generated file before running it. I watched Alembic try to drop a 2GB table and recreate it because we renamed a fucking foreign key. SQLAlchemy thought it was a drop+add operation. Test on production data copies or you'll be explaining to your CEO why the database is gone.

Migration workflow that actually works:

1. Change your SQLAlchemy models
2. Generate: alembic revision --autogenerate -m \"add user table\"
3. READ THE GENERATED FILE - I can't stress this enough
4. Test locally: alembic upgrade head
5. Test rollback: alembic downgrade -1 (you'll need this)
6. Deploy and hope you tested everything

Deployment Hell and How to Survive

Docker Compose for development is fine. Here's a setup that doesn't break:

## docker-compose.yml
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: dev
    ports:
      - \"5432:5432\"
  
  app:
    build: .
    depends_on:
      - db
    environment:
      DATABASE_URL: postgresql+asyncpg://postgres:dev@db:5432/postgres

For production, run migrations as an init container. Running them on app startup sounds convenient until a migration hangs and takes down your whole service. Kubernetes init containers are perfect for this - they run once and fail fast if something's fucked.

Add health checks or your load balancer will send traffic to broken containers:

@app.get(\"/health\")
async def health_check(db: AsyncSession = Depends(get_db)):
    try:
        await db.execute(text(\"SELECT 1\"))
        return {\"status\": \"healthy\"}
    except Exception:
        raise HTTPException(status_code=503, detail=\"Database unreachable\")

What Breaks and How to Fix It

Connection pool exhaustion: You'll hit this first. Symptoms: app hangs, then crashes. Fix: Lower pool_size, add connection monitoring.

Migration conflicts: Two developers make migrations at the same time. Git merges fine, Alembic pukes. Fix: Manually create merge migration.

Async session leaks: Symptoms: Memory usage climbs, then crash. Fix: Always use dependency injection, never create sessions manually.

SQLAlchemy 2.0 query changes: Your 1.4 queries are deprecated but still work. Until they don't. The migration guide saved my ass when everything broke after upgrading.

This stack is fast as hell but async will break your brain. Budget extra time for debugging weird race conditions that don't happen locally but crash production. When Stack Overflow fails you (and it will), the FastAPI GitHub discussions have actual humans who've hit the same wall.