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
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 theworkoutstable; eachMapped[...]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_1as its placeholder: the ORM keeps values out of the SQL's text too.- A
Sessionruns it, andscalarshands backWorkoutobjects, read asworkout.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(orpyproject.toml):sqlalchemy,sqlmodeloralembiclisted, or none of them;- the imports in its data code:
import sqlite3,from sqlalchemy, orfrom sqlmodel; - its folders and classes: an
alembicfolder, and classes with__tablename__ortable=True, mean an ORM; amigrationsfolder of.sqlfiles and a data module full ofSELECTs 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.