How to define SQLAlchemy ORM models for FastAPI

Defining SQLAlchemy ORM Models

SQLAlchemy ORM models are Python classes that map to database tables. Each attribute maps to a column. FastAPI does not care about these models directly — they are database-layer objects that you convert to Pydantic schemas before returning them.

Define models

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship
from .database import Base

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True, nullable=False)
    hashed_password = Column(String, nullable=False)
    is_active = Column(Boolean, default=True)

    posts = relationship("Post", back_populates="owner")

class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True)
    content = Column(String)
    owner_id = Column(Integer, ForeignKey("users.id"))

    owner = relationship("User", back_populates="posts")

index=True adds a database index for that column, speeding up lookups. ForeignKey creates a database-level constraint. relationship creates a Python-level link for ORM navigation — it does not add a column.

Creating tables

# In main.py or a setup script
from .database import engine
from . import models

models.Base.metadata.create_all(bind=engine)

This creates all tables that do not exist. For production, use Alembic migrations instead of create_all.