How do you integrate a SQL database with FastAPI?

Question

IT Interview Practice · Accepted Answer

Unlike Django, FastAPI is **unopinionated** about databases — you choose the library. The common choice is **SQLAlchemy** (often with **SQLModel**, by FastAPI's author, which combines SQLAlchemy + Pydantic). Database sessions are provided via dependency injection.

## SQLAlchemy setup

```python
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base

engine = create_engine("postgresql://user:pass@localhost/db")
SessionLocal = sessionmaker(bind=engine)
Base = declarative_base()

class User(Base):                        # an ORM model (maps to a table)
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String, unique=True)
```

## Providing the DB session via a dependency

```python
def get_db():
    db = SessionLocal()
    try:
        yield db                 # provide the session
    finally:
        db.close()               # ALWAYS close after the request (cleanup)

@app.get("/users/{id}")
def get_user(id: int, db: Session = Depends(get_db)):   # inject a session per request
    user = db.query(User).filter(User.id == id).first()
    if not user:
        raise HTTPException(404, "Not found")
    return user
```

The `yield` dependency pattern is the standard way to manage sessions: open one per request, inject it, and close it afterward automatically — every endpoint gets a clean, properly-closed session.

## SQLModel — SQLAlchemy + Pydantic in one

```python
from sqlmodel import SQLModel, Field, Session, select

class User(SQLModel, table=True):        # ONE class = ORM model AND Pydantic model
    id: int | None = Field(default=None, primary_key=True)
    name: str
    email: str

@app.get("/users")
def list_users(db: Session = Depends(get_session)):
    return db.exec(select(User)).all()    # query; results are also valid response models
```

SQLModel unifies the ORM model and the Pydantic model into one class — reducing duplication between database and API schemas.

## Migrations and async options

```text
✓ Migrations: use Alembic (SQLAlchemy's migration tool) for schema changes
✓ Async: use SQLAlchemy's async engine + async session for async endpoints
  (don't use a SYNC database driver inside async def — it blocks the event loop)
```

## Why it matters

Database integration is essential for almost any real API, and since FastAPI is deliberately **unopinionated** about it (unlike Django's built-in ORM), understanding the common approach is important practical knowledge.

The standard pattern — **SQLAlchemy** (or **SQLModel**, which elegantly unifies the ORM and Pydantic models to reduce duplication) with **dependency-injected sessions** via the `yield` pattern — is what nearly every FastAPI app uses to persist data.

The session-per-request dependency (open before, close after, automatically) is the idiomatic, correct way to manage database connections, and SQLModel's unification of database and API schemas is a notable productivity feature from FastAPI's own author.

Knowing how to set up the database, provide sessions through DI, perform queries, and handle migrations (Alembic) and the async caveat (using async drivers in async endpoints, never blocking sync ones) is necessary for building data-driven FastAPI applications — since FastAPI gives you the freedom to choose, knowing the conventional, well-trodden path is valuable for building real apps correctly.

SQLAlchemy setup

Providing the DB session via a dependency

SQLModel — SQLAlchemy + Pydantic in one

Migrations and async options

Why it matters

Related questions