# 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 November 19, 2026

By Gregor Dzierzon

## 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

```python
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()
```

```text
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):

```python
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)
```

```text
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*:

```python
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)
```

```text
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 `SELECT`s 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

- [ORM](https://www.ask-codey.com/glossary/orm-database)
- [SQLAlchemy](https://www.ask-codey.com/glossary/sqlalchemy)
- [SQLModel](https://www.ask-codey.com/glossary/sqlmodel)

## Related

- [Keeping the Data Code in One Place](https://www.ask-codey.com/lessons/fastapi-data-module)
- [Creating and Changing Tables: Migrations](https://www.ask-codey.com/lessons/fastapi-data-schema-and-migrations)
- [Request Models with Pydantic](https://www.ask-codey.com/lessons/fastapi-pydantic-request-models)

Source: https://www.ask-codey.com/lessons/fastapi-data-orms
