SQLModel with FastAPI: Table and Schema Models, CRUD, Relationships, Alembic

What SQLModel is

SQLModel is a library by the author of FastAPI. One class can be a SQLAlchemy table and a Pydantic model at the same time, so you describe fields once and use them for the database, request validation and responses.

Install it with pip install sqlmodel fastapi. The library is still at 0.0.x, so pin its version and read the changelog before upgrading.

💡 SQLModel is a thin layer over SQLAlchemy. When you need an advanced feature, you can use SQLAlchemy directly.

Models and a first query

from sqlmodel import Field, Session, SQLModel, create_engine, select

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    age: int | None = None

engine = create_engine("sqlite://")
SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    session.add(Hero(name="Deadpond", age=30))
    session.add(Hero(name="Rusty-Man", age=48))
    session.commit()
    stmt = select(Hero).where(Hero.age > 35)
    heroes = session.exec(stmt).all()
    print([h.name for h in heroes])
# ['Rusty-Man']

id is int | None because the database assigns it on commit. The table name defaults to the lowercase class name (hero).

Table model and schemas