SQLAlchemy and SQLModel: When a Project Uses an ORM

An ORM such as SQLAlchemy or SQLModel lets a project describe each table as a Python class and ask for rows with Python methods that write the SQL for it; this lesson reads the same workout query written with sqlite3, SQLAlchemy and SQLModel, what each costs and gives, and the signs that show which one a project uses.

Journeyman · FastAPI · REST API · Updated

By

After this lesson you can

  • Read the same query written with sqlite3, SQLAlchemy and SQLModel, and explain what an ORM does
  • Tell which data approach a project uses from its requirements, imports and folders, and weigh what an ORM costs and gives

Key takeaways

  • An ORM (object-relational mapper) describes each table as a Python class, writes the SQL for you, and hands back objects instead of rows.
  • SQLAlchemy is the most used Python ORM. Its query select(Workout).where(Workout.member == "Luis") becomes SQL with a placeholder, the way the course writes it by hand.
  • SQLModel, by FastAPI's author, builds on SQLAlchemy and Pydantic, so one class can be both a table and an API model.
  • An ORM saves typing and catches some mistakes earlier, but hides the SQL and is another library to learn. Hand-written SQL keeps every query in plain sight.
  • A project's requirements.txt, its imports and an alembic folder show which approach it uses.

Join a Python team and open its data code, and there's a good chance you won't see a single SELECT. You'll see classes named after tables and lines like session.scalars(select(Workout)). That's an ORM, and many FastAPI projects use one. This course writes its SQL by hand, so this lesson teaches you to read the other way when you meet it.

The question, all three ways, is Rep Counter's: Luis's workouts, oldest first, from rep_counter.db.

The way you know: sqlite3

import sqlite3

conn = sqlite3.connect("rep_counter.db")
rows = conn.execute(
    "SELECT id, member, day FROM workouts WHERE member = ? ORDER BY day", ("Luis",)
).fetchall()
for workout_id, member, day in rows:
    print(workout_id, member, day)
conn.close()
1 Luis 2026-11-03
3 Luis 2026-11-10
4 Luis 2026-12-01

You write the SQL, and rows come back. Every query is in plain sight, which is why this course's data module works this way.

SQLAlchemy: classes that stand for tables

Think of a translator at a meeting: you speak your language, and they speak the other one for you. An ORM, an object-relational mapper, is that translator between Python and SQL. You describe each table as a class, ask in Python, and it writes the SQL and hands back objects. SQLAlchemy is the most used one in Python (version 2.1 here):

from sqlalchemy import create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):
    pass


class Workout(Base):
    __tablename__ = "workouts"
    id: Mapped[int] = mapped_column(primary_key=True)
    member: Mapped[str]
    day: Mapped[str]


engine = create_engine("sqlite:///rep_counter.db")
query = select(Workout).where(Workout.member == "Luis").order_by(Workout.day)
print(query)
with Session(engine) as session:
    for workout in session.scalars(query):
        print(workout.id, workout.member, workout.day)
SELECT workouts.id, workouts.member, workouts.day
FROM workouts
WHERE workouts.member = :member_1 ORDER BY workouts.day
1 Luis 2026-11-03
3 Luis 2026-11-10
4 Luis 2026-12-01

Read it piece by piece:

  • class Workout(Base) stands for the workouts table; each Mapped[...] line is a column.
  • create_engine("sqlite:///rep_counter.db") says which database: SQLite, in that file.
  • select(Workout).where(...).order_by(...) builds the query in Python. Printed, it's the SQL the ORM writes, with :member_1 as its placeholder: the ORM keeps values out of the SQL's text too.
  • A Session runs it, and scalars hands back Workout objects, read as workout.member.

SQLModel: one class for the table and the API

SQLModel is a library by FastAPI's author, built on SQLAlchemy and Pydantic, so one class can be a table and a model like the ones in Request Models with Pydantic:

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


class Workout(SQLModel, table=True):
    __tablename__ = "workouts"
    id: int | None = Field(default=None, primary_key=True)
    member: str
    day: str


engine = create_engine("sqlite:///rep_counter.db")
with Session(engine) as session:
    query = select(Workout).where(Workout.member == "Luis").order_by(Workout.day)
    for workout in session.exec(query):
        print(workout.id, workout.member, workout.day)
1 Luis 2026-11-03
3 Luis 2026-11-10
4 Luis 2026-12-01

table=True makes the class a table, and its fields look like any Pydantic model's. The query reads the same as SQLAlchemy's; session.exec runs it.

What each costs and gives, and how to tell

Hand-written SQL (sqlite3) SQLAlchemy SQLModel
You write the SQL Python that builds the SQL Python that builds the SQL
Rows come back as rows, or dictionaries objects of your classes objects that are also Pydantic models
To install nothing sqlalchemy sqlmodel
Changing tables numbered .sql scripts Alembic Alembic

An ORM saves typing on everyday queries, lets an editor catch a misspelled column such as Workout.membr, and can move to another database with less change. It also hides the SQL it writes, which you still need to read when a query is slow, and it's one more large library to learn. Hand-written SQL keeps every query in plain sight; that's why this course uses it, and why the SQL you've learned matters with either.

To tell which one a project uses, look for:

  • requirements.txt (or pyproject.toml): sqlalchemy, sqlmodel or alembic listed, or none of them;
  • the imports in its data code: import sqlite3, from sqlalchemy, or from sqlmodel;
  • its folders and classes: an alembic folder, and classes with __tablename__ or table=True, mean an ORM; a migrations folder of .sql files and a data module full of SELECTs mean hand-written SQL.

Check yourself

2 questions to try on the lesson page.

So what

Whichever way a project talks to its database, you can now read it: hand-written SQL in plain sight, or classes that an ORM turns into the same SQL for you.

Key terms

Related