Search Topics

Search across all FastAPI topics

GitHub

SQLAlchemy Models

Core Concept

Your Python classes become database tables. Get them wrong, and your data has holes you won't notice until production.

You define a User model with an email column but forget unique=True. Two users sign up with the same email. Your login system breaks because the query returns two rows instead of one.

terminal
# Two users with the same email:
db.query(User).filter(User.email == "alice@example.com").one()

sqlalchemy.exc.MultipleResultsFound:
Multiple rows were returned for one()

# Your login endpoint returns 500 Internal Server Error
# because it expected exactly ONE user for that email.
# The database allowed the duplicate — you never told it not to.

Question

Why didn't SQLAlchemy stop you? Because it only knows what you tell it. If you don't add unique=True, the database happily accepts duplicates. Your model IS your schema contract — every constraint you forget is a bug waiting to happen.

How Python classes become database tables

Here's the big picture. Your Python class doesn't just describe data — it generates actual SQL DDL that creates tables, columns, and constraints.

Python Class

class User(Base)

Mapped Columns

Mapped[str], mapped_column()

SQL DDL

CREATE TABLE users (...)

Database Table

Columns + constraints

Your first model

SQLAlchemy 2.0 uses Mapped[] type annotations and mapped_column() for type-safe column definitions. The type hint isn't just for your editor — it tells the database what type of column to create.

models.py
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    email: Mapped[str] = mapped_column(String(200), unique=True, index=True)
    is_active: Mapped[bool] = mapped_column(default=True)

# Mapped[int] tells both Python AND the database the column type
# mapped_column() adds constraints — unique, index, default
# Notice unique=True on email? That's what prevents the bug above.

Basic Model

What you just learned

Every model needs a Base class (DeclarativeBase) — it registers your tables

__tablename__ sets the actual SQL table name. Always set it explicitly.

Mapped[str] = NOT NULL column. Mapped[str | None] = nullable column.

mapped_column() is where you add constraints like unique, index, and default

One-to-Many: Users and Posts

A User can have many Posts. The foreign key always lives on the "many" side (Post), and relationship() creates the Python-level link so you can do user.posts without writing SQL.

models.py
from sqlalchemy import ForeignKey, Text
from sqlalchemy.orm import Mapped, mapped_column, relationship

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

    # One user has many posts — this is just a Python convenience
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

class Post(Base):
    __tablename__ = "posts"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    body: Mapped[str] = mapped_column(Text)

    # The foreign key lives HERE — on the "many" side
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    author: Mapped["User"] = relationship(back_populates="posts")

Watch out

A common mistake: forgetting back_populates on both sides. If you only set it on one model, the relationship works in one direction but not the other. You'll add a post to a user, but post.author will be None. Always connect both ends.

Relationships

What you just learned

Foreign keys go on the 'many' side — Post has author_id, not User

relationship() is a Python-level convenience, not a database column

back_populates must match on both sides — it's a two-way street

Many-to-Many: Posts and Tags

Posts can have multiple Tags, and Tags can belong to multiple Posts. You can't put a foreign key on either side — you need a separate "association table" in between. Think of it as a lookup table that just holds pairs of IDs.

models.py
from sqlalchemy import Table, Column, ForeignKey

# Association table — no ORM model needed, just raw columns
post_tags = Table(
    "post_tags",
    Base.metadata,
    Column("post_id", ForeignKey("posts.id"), primary_key=True),
    Column("tag_id", ForeignKey("tags.id"), primary_key=True),
)

class Post(Base):
    __tablename__ = "posts"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))

    # secondary= points to the association table
    tags: Mapped[list["Tag"]] = relationship(
        secondary=post_tags, back_populates="posts"
    )

class Tag(Base):
    __tablename__ = "tags"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50), unique=True)

    posts: Mapped[list["Post"]] = relationship(
        secondary=post_tags, back_populates="tags"
    )

Many-to-Many

What you just learned

Many-to-many needs an association table with two foreign keys

The association table is a plain Table, not an ORM model

secondary= on relationship() tells SQLAlchemy to use the association table

Pydantic Schemas: Your API boundary

Here's something that trips up almost everyone: SQLAlchemy models and Pydantic schemas look similar, but they serve completely different purposes. Models talk to the database. Schemas talk to the client. Never expose your ORM model directly — that's how you accidentally leak internal fields.

schemas.py
from pydantic import BaseModel, ConfigDict

# What the client SENDS to create a user
class UserCreate(BaseModel):
    name: str
    email: str

# What the API RETURNS — notice: no password, no internal fields
class UserOut(BaseModel):
    id: int
    name: str
    email: str
    is_active: bool

    # This is the magic part — converts ORM objects to Pydantic automatically
    model_config = ConfigDict(from_attributes=True)

# Usage in an endpoint:
@app.post("/users", response_model=UserOut)
async def create_user(user: UserCreate, db: Session = Depends(get_db)):
    db_user = User(**user.model_dump())
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user  # Automatically converted to UserOut

Insight

The from_attributes=True config is what lets you return an ORM object directly from an endpoint and have FastAPI convert it to JSON using the Pydantic schema. Without it, you'd get a serialization error because Pydantic doesn't know how to read SQLAlchemy attributes by default.

Pydantic Schemas

What you just learned

SQLAlchemy models = database layer. Pydantic schemas = API layer. Keep them separate.

from_attributes=True lets Pydantic read ORM object attributes

Use different schemas for Create, Read, and Update operations

model_dump() converts Pydantic → dict, model_validate() converts ORM → Pydantic

Go Deeper: Missing back_populates

One-sided relationship trap

You set up a User-Post relationship but only add back_populates on one side. Creating a post with an author works, but accessing post.author returns None.

Broken code
models.py
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    posts: Mapped[list["Post"]] = relationship()  # No back_populates!

class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    # No relationship defined here at all

Think about it...

What's the difference between Mapped[str] and Mapped[str | None] in terms of the generated SQL column?

Hint: Think about what happens at the SQL level when you try to INSERT a row without providing a value for that column.

Key Points

Mapped Columns

Use Mapped[type] and mapped_column() for type-safe definitions

Relationships

Connect models with relationship() and ForeignKey for joins

__tablename__

Always set the table name explicitly for clear database naming

from_attributes

Use ConfigDict(from_attributes=True) to convert ORM → Pydantic