Setting Up a Database for FastAPI — The Relational Rom-Com You Didn’t Know You Needed

"We solved CSRF, tamed CORS, and rate-limited our users. Now let’s give your app a place to store secrets (and regrets): a database."

You’ve already learned about security and authentication (nice work — remember CORS and rate limits?). Now we move from defending the castle to furnishing it: persistent storage. This piece shows how to pick, provision, and connect a database to your FastAPI app in a sane, secure, and repeatable way.

---

Why this matters (quickly)

• Without a DB your app is a glorified ephemeral calculator. Great for demos, terrible for real users.
• With the wrong DB setup you’ll have race conditions, leaked secrets, and mysterious timeouts at 2 AM. Fun for hackers, not for you.

We’ll walk through choosing a DB, spinning one up (Docker Compose), configuring FastAPI to talk to it (async SQLAlchemy pattern), migrations, testing, and basic security best practices.

---

1) Pick your fighter: SQLite, Postgres, or MySQL? (TL;DR table)

DB	When to use	Pros	Cons
SQLite	Prototyping, tests, tiny apps	Zero config, file-based, fast for small loads	Not for concurrency or production scale
PostgreSQL	Most web apps	Powerful features (JSONB, full-text), robust, reliable	Slightly heavier to manage than SQLite
MySQL/MariaDB	Legacy systems, certain clouds	Ubiquitous, fast	Fewer advanced features vs Postgres

Pick Postgres for most new projects unless you have a compelling reason not to.

---

2) Spin up Postgres with Docker Compose (because we’re civilized)

Create a minimal docker-compose.yml:

version: '3.8'
services:
  db:
    image: postgres:15
    environment:
      POSTGRES_USER: fastapi_user
      POSTGRES_PASSWORD: supersecret
      POSTGRES_DB: fastapi_db
    volumes:
      - db-data:/var/lib/postgresql/data
    ports:
      - '5432:5432'
volumes:
  db-data:

Create a .env (DON'T commit this to git):

DATABASE_URL=postgresql+asyncpg://fastapi_user:supersecret@localhost:5432/fastapi_db

Pro tip: In production, use a secrets manager (AWS Secrets Manager, HashiCorp Vault) and never, ever commit passwords.

---

3) Database driver + ORM: async SQLAlchemy (recommended) or SQLModel

FastAPI plays nicely with async SQLAlchemy 1.4+. You can also use SQLModel (built on SQLAlchemy) for convenience. Below is a common async SQLAlchemy setup.

# db.py
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker, declarative_base
from pydantic import BaseSettings

class Settings(BaseSettings):
    database_url: str
    class Config:
        env_file = '.env'

settings = Settings()

engine = create_async_engine(settings.database_url, echo=True, pool_pre_ping=True)
AsyncSessionLocal = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)
Base = declarative_base()

async def get_session():
    async with AsyncSessionLocal() as session:
        yield session

And a simple model:

# models.py
from sqlalchemy import Column, Integer, String
from db import Base

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String, unique=True, index=True)
    hashed_password = Column(String)

Use dependency injection in your endpoints:

from fastapi import Depends
from sqlalchemy.ext.asyncio import AsyncSession
from db import get_session

@app.post('/users')
async def create_user(payload: UserCreate, session: AsyncSession = Depends(get_session)):
    user = User(username=payload.username, hashed_password='...')
    session.add(user)
    await session.commit()
    await session.refresh(user)
    return user

---

4) Migrations (Alembic) — because manual schema updates are a horror movie

• Initialize Alembic, configure the async URL, and autogenerate migrations with models.
• Use a separate alembic/env.py that uses your SQLAlchemy metadata.

Commands (high-level):

1. alembic init alembic
2. Edit alembic.ini and env.py to import your models
3. alembic revision --autogenerate -m "create users"
4. alembic upgrade head

Automate migrations in CI/CD; don’t rely on manual scripts on a production server.

---

5) Testing strategy

• For unit tests, use SQLite in-memory where possible. For integration tests, spin up a throwaway Postgres (e.g., with pytest-docker or Testcontainers).
• Reset tables between tests or use transactional rollbacks.

---

6) Security checklist (we’re building on your auth work)

• Secrets management: store DB credentials outside source control.
• Least privilege: create a DB user with only the permissions your app needs.
• Connection limits: set appropriate pool sizes to avoid exhausting DB connections (match to your app workers).
• SQL injection: avoid raw string concatenation; use ORM/parameterized queries.
• Network rules: in production, restrict DB access to app servers and private subnets.

Quick reminder: rate limiting and CORS keep malicious traffic from hammering your routes — but your DB still needs its own armor (connection throttling, monitoring, backup policies).

---

7) Performance & operational tips

• Monitor slow queries and add indexes where needed.
• Use connection pooling (create_async_engine handles pooling).
• Configure timeouts and keepalives to avoid hanging sessions.
• Back up regularly and test restores.

---

8) Common pitfalls (aka what will burn your midnight oil)

1. Using too-large connection pool without matching worker count → connections exhaust DB.
2. Running migrations manually on prod without backups.
3. Committing .env with credentials.
4. Mixing sync DB drivers in async code (blocking calls = blocked server).

---

Closing — TL;DR + checklist

• Choose Postgres for most production apps; SQLite for prototypes & tests.
• Use async SQLAlchemy (or SQLModel) with proper session scoping via dependencies.
• Manage secrets with env files in dev, secret managers in prod.
• Automate migrations (Alembic) and include them in CI/CD.
• Test with ephemeral DB instances and monitor performance.

Key actions to take right now:

1. Add Docker Compose postgres and a .env (local only).
2. Wire create_async_engine in your FastAPI app.
3. Add Alembic, generate the first migration.
4. Put DB credentials into your CI secret store and test deployment.

Final thought: Your app’s security stack (CORS, rate limiting, auth) keeps attackers out of the front door. A well-configured DB ensures they don’t sneak in through the back window. Do both.

---

Happy databasing. Bring snacks.