SQLModel: SQLAlchemy Meets Pydantic

What is SQLModel and Why It Exists

SQLModel is a library created by Samuel Colvin — the same person behind Pydantic. It takes the best parts of SQLAlchemy’s ORM and Pydantic’s data validation and smashes them together into a single class.

Before SQLModel, you had to maintain two separate classes for the same database table: one SQLAlchemy model for the database and one Pydantic model for validation. That meant duplicate field definitions, duplicate validation rules, and twice the maintenance work.

SQLModel solves this by giving you one class that does both jobs. Define a field once, and you get database persistence and request validation in one go.

Installation

pip install sqlmodel

That’s it. SQLModel depends on both SQLAlchemy and Pydantic, so they’ll install automatically.

Defining Models

A SQLModel class looks like a Pydantic model, but you add table=True when you want database persistence.

from sqlmodel import Field, SQLModel

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

table=True tells SQLModel this is a database table. Without it, you get a Pydantic-only model — useful for request/response schemas that don’t need a database backing.

The Field() function gives you control over individual columns:

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)  # creates a database index
    age: int | None = Field(default=None, ge=0)  # age must be >= 0
    secret_name: str

ge=0 is a Pydantic validator, so you get type coercion and validation at the same time as column configuration.

Creating Tables and the Engine

Now you need a database to put those tables in.

from sqlmodel import create_engine, SQLModel

engine = create_engine("sqlite:///heroes.db")

This creates an SQLite database called heroes.db in your current directory. The create_engine URL format follows SQLAlchemy conventions, so you can swap in PostgreSQL, MySQL, or other databases just by changing the connection string.

With the engine in place, you create all your tables in one call:

SQLModel.metadata.create_all(engine)

This reads all SQLModel subclasses with table=True and creates the corresponding tables if they don’t exist. It’s idempotent — running it again is safe.

CRUD Operations

CRUD stands for Create, Read, Update, Delete. SQLModel uses SQLAlchemy’s Session for all of these.

Create

from sqlmodel import Session

with Session(engine) as session:
    hero = Hero(name="Deadpond", secret_name="Dive Wilson")
    session.add(hero)
    session.commit()
    session.refresh(hero)  # fetches DB-generated fields like id

    print(hero.id)  # output: 1

session.add() stages the object. session.commit() writes it to the database. session.refresh() pulls in any database-generated values (like auto-increment IDs).

Read

from sqlmodel import select

with Session(engine) as session:
    statement = select(Hero).where(Hero.name == "Deadpond")
    hero = session.exec(statement).first()
    print(hero.name)  # output: Deadpond
    print(hero.secret_name)  # output: Dive Wilson

select() builds a query. session.exec() runs it. .first() returns the first result or None.

To fetch heroes with no age set (where age IS NULL in SQL):

with Session(engine) as session:
    statement = select(Hero).where(Hero.age.is_(None))
    heroes = session.exec(statement).all()
    for hero in heroes:
        print(hero.name)

To fetch all heroes:

with Session(engine) as session:
    statement = select(Hero)
    heroes = session.exec(statement).all()
    for hero in heroes:
        print(hero.name)

Update

with Session(engine) as session:
    statement = select(Hero).where(Hero.name == "Deadpond")
    hero = session.exec(statement).one()

    hero.age = 42
    session.add(hero)
    session.commit()
    session.refresh(hero)

    print(hero.age)  # output: 42

SQLModel tracks changes to objects. When you modify a field and call commit(), SQLAlchemy writes the update automatically.

Delete

with Session(engine) as session:
    statement = select(Hero).where(Hero.name == "Deadpond")
    hero = session.exec(statement).one()

    session.delete(hero)
    session.commit()

To delete heroes with no age:

with Session(engine) as session:
    statement = select(Hero).where(Hero.age.is_(None))
    heroes = session.exec(statement).all()

    for hero in heroes:
        session.delete(hero)
    session.commit()

Relationships Between Models

You define relationships the SQLAlchemy way, using Relationship from SQLModel.

from sqlmodel import Field, Relationship, SQLModel

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

    heroes: list["Hero"] = Relationship(back_populates="team")


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    age: int | None = None
    secret_name: str
    team_id: int | None = Field(default=None, foreign_key="team.id")

    team: Team | None = Relationship(back_populates="heroes")

back_populates links the two sides of the relationship. foreign_key on the child side specifies which column holds the parent’s ID.

With this in place, SQLAlchemy loads related objects automatically:

with Session(engine) as session:
    team = session.get(Team, 1)
    print(team.name)  # output: Z-Force
    print(team.heroes[0].name)  # output: Deadpond

The reverse works too:

with Session(engine) as session:
    hero = session.get(Hero, 1)
    print(hero.team.name)  # output: Z-Force

Watch out for circular imports — when two models reference each other, use string annotations ("Team" instead of Team) and put the import inside the function.

Using with FastAPI

FastAPI pairs naturally with SQLModel because both Pydantic and SQLModel use the same validation underpinnings.

from fastapi import FastAPI, HTTPException
from sqlmodel import Field, Relationship, Session, SQLModel, select, create_engine

app = FastAPI()

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


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


class HeroRead(SQLModel):
    id: int | None
    name: str
    age: int | None
    secret_name: str


@app.post("/heroes/", response_model=HeroRead)
def create_hero(hero: Hero):
    with Session(engine) as session:
        session.add(hero)
        session.commit()
        session.refresh(hero)
    return hero


@app.get("/heroes/{hero_id}", response_model=HeroRead)
def read_hero(hero_id: int):
    with Session(engine) as session:
        hero = session.get(Hero, hero_id)
        if hero is None:
            raise HTTPException(status_code=404, detail="Hero not found")
    return hero

The Hero model from earlier becomes the request schema. FastAPI validates the incoming JSON, SQLModel writes it to the database — no extra code needed.

For path parameters and query strings, use httpie to test:

# output
# {"id":1,"name":"Tournament","age":30,"secret_name":"Dove Wilson","team_id":null}

SQLModel also handles response models automatically. FastAPI will serialize the Hero object to JSON using its field definitions. The id, name, age, and other fields appear in the response exactly as defined.

Common Pitfalls

Forgetting to Commit

# Wrong — changes stay in the session, never hit the database
with Session(engine) as session:
    session.add(hero)

# Right
with Session(engine) as session:
    session.add(hero)
    session.commit()

Without commit(), your changes vanish when the context manager exits. This catches beginners regularly.

Field Order and Primary Keys

SQLModel uses field order for column ordering in some databases. If you’re seeing odd errors, put your primary key first:

# Good — PK defined first
class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str

# Risky — PK after other fields may cause issues
class RiskyHero(SQLModel, table=True):
    name: str
    id: int | None = Field(default=None, primary_key=True)

Stick to defining the primary key as the first field to avoid surprises.

Relationship Recursion and Lazy Loading

SQLModel relationships are lazy by default, which means accessing hero.team inside a loop triggers a query for each access — the classic N+1 problem.

# This runs one query per hero
with Session(engine) as session:
    heroes = session.exec(select(Hero)).all()
    for hero in heroes:
        print(hero.team.name)  # separate query each iteration

Fix this with eager loading using selectinload:

from sqlmodel import select
from sqlalchemy.ext import selectinload

with Session(engine) as session:
    statement = select(Hero).options(selectinload(Hero.team))
    heroes = session.exec(statement).all()
    for hero in heroes:
        print(hero.team.name)  # single query with JOIN

Mixing Pydantic-Only and Table Models

If a class doesn’t have table=True, it’s Pydantic-only. It won’t appear in metadata, and metadata.create_all() won’t create a table for it. This trips people up when they copy a model definition from a schema file into a migration script.

Keep your database models and your API schemas separate if you can — it makes metadata.create_all() behave predictably.

---

SQLModel won’t replace SQLAlchemy for advanced use cases, but for the vast majority of applications it removes a whole category of boilerplate. One class, validated, persisted, and serialised — that’s the core idea.

See Also

• FastAPI Quickstart — get up and running with FastAPI in minutes
• Pydantic Guide — master Pydantic validation from the ground up
• Python Type Hints — understand the type annotation syntax SQLModel builds on