Lesson 6 - The ORM: Declarative Models and Sessions

Welcome to the ORM#

Everything so far has used SQLAlchemy Core: you built Table objects, and queries returned plain rows — tuples with named attributes, but not full Python objects. The ORM (Object-Relational Mapper) maps those same tables onto Python classes, so a query can hand you back an Employee object whose .department attribute is not a foreign key integer, but the actual related Department object.

By the end of this lesson, you will be able to:

• Define ORM models with DeclarativeBase, Mapped, and mapped_column
• Connect two models with relationship() and back_populates
• Query objects (not rows) with a Session and select()
• Navigate a relationship directly, without writing a second query
• Add and remove objects through the Session

Let’s give your tables a Python identity.

Defining ORM Models#

Every ORM model inherits from a shared DeclarativeBase subclass, which plays the same role MetaData played for Core — it is the registry every model’s table gets attached to:

from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

A model is a regular Python class with one attribute per column, each declared with Mapped[...] and mapped_column():

from typing import Optional, List
from datetime import date
from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship

class Department(Base):
    __tablename__ = "departments"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]

    employees: Mapped[List["Employee"]] = relationship(back_populates="department")

class Employee(Base):
    __tablename__ = "employees"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    job: Mapped[str]
    hiredate: Mapped[date]
    salary: Mapped[float]
    department_id: Mapped[Optional[int]] = mapped_column(ForeignKey("departments.id"))

    department: Mapped[Optional["Department"]] = relationship(back_populates="employees")

A few things to notice:

• Mapped[int], Mapped[str], Mapped[date] are Python type hints that SQLAlchemy reads to infer the SQL column type — you get IDE autocomplete and type checking on top of your database columns.
• mapped_column(primary_key=True) is only needed when a column has options beyond its type — a plain Mapped[str] is enough for an ordinary, required column.
• relationship(back_populates="...") is not a column at all — it does not exist in the database. It tells the ORM “these two attributes describe the same relationship from each side,” so setting one updates the other automatically in Python memory.

Notice this model maps onto the exact same table you built with Core in Lesson 2 — Base.metadata.create_all(engine) below is a no-op here, since departments and employees already exist. Core and the ORM are two different ways of describing the same database, and you can mix them freely in one project.

from sqlalchemy import create_engine

engine = create_engine("sqlite:///company.db")
Base.metadata.create_all(engine)

Querying with a Session#

Core queries ran through a Connection. ORM queries run through a Session, which tracks the objects it loads so it can detect changes to them later:

from sqlalchemy import select
from sqlalchemy.orm import Session

with Session(engine) as session:
    stmt = select(Employee).where(Employee.department_id == 2).order_by(Employee.salary.desc())
    for emp in session.scalars(stmt):
        print(emp.name, emp.job, emp.salary)

Output:

FILLMORE MANAGER 56000.0
ADAMS ENGINEER 34000.0
GRANT ENGINEER 32000.0
MONROE ENGINEER 30000.0

select(Employee) looks almost identical to the Core queries from earlier lessons — the difference is that it selects the Employee class, not a Table object, and session.scalars(stmt) returns full Employee instances rather than row tuples.

Navigating relationships#

Because Employee.department is a relationship(), you can follow it directly — no second query, no join to write yourself:

with Session(engine) as session:
    stmt = select(Employee).where(Employee.department_id == 2).order_by(Employee.salary.desc())
    for emp in session.scalars(stmt):
        print(emp.name, "->", emp.department.name)

Output:

FILLMORE -> Engineering
ADAMS -> Engineering
GRANT -> Engineering
MONROE -> Engineering

The reverse direction works too, since Department.employees was defined with back_populates="department":

with Session(engine) as session:
    engineering = session.scalars(select(Department).where(Department.name == "Engineering")).one()
    print(engineering.name, "headcount:", len(engineering.employees))

Output:

Engineering headcount: 4

.one() unwraps a result you expect to contain exactly one row, raising an error if it finds zero or more than one — a useful assertion when you know a query should be unique, like looking up a department by its unique name.

Adding Objects Through the ORM#

Instead of building an insert() statement, you construct a Python object and hand it to the session:

with Session(engine) as session:
    sales = session.scalars(select(Department).where(Department.name == "Sales")).one()

    new_employee = Employee(
        id=15, name="PIERCE", job="SALES II",
        hiredate=date(2024, 3, 1), salary=27500, department=sales,
    )
    session.add(new_employee)
    session.commit()

Setting department=sales directly — passing the related object, not a raw department_id integer — is the ORM handling the foreign key for you. Confirm it landed correctly, then remove it so later lessons see the same 13 employees:

with Session(engine) as session:
    pierce = session.scalars(select(Employee).where(Employee.name == "PIERCE")).one()
    print(pierce.name, "->", pierce.department.name)

    session.delete(pierce)
    session.commit()

Output:

PIERCE -> Sales

session.add() stages a new object; session.delete() stages a removal; neither takes effect until session.commit(), the same commit-to-persist pattern you already know from Core.

Practice Exercises#

Continue using company.db and the Department / Employee models from this lesson.

Exercise 1: Everyone’s Department, ORM Style#

Query every Employee, and for each one print their name and their department’s name (handle the case where department might be None).

# Your code here

Exercise 2: Add and Remove a Department#

Create a new Department named "Marketing", add it, commit, then query it back by name and print its (empty) employees list.

# Your code here

Exercise 3: Highest Earner Per Department#

Using the ORM, find the single highest-paid employee in each department. (Hint: you do not have to do this in one query — looping over departments and using Python’s max() on each department.employees list is a perfectly good ORM-style solution.)

# Your code here

Summary#

You defined ORM models with DeclarativeBase, Mapped, and mapped_column, connecting Department and Employee with a bidirectional relationship(). You queried objects (not raw rows) through a Session, navigated from an employee to its department and back without writing a join yourself, and added and removed objects with session.add() and session.delete().

Key Concepts#

• DeclarativeBase — the shared base class every ORM model inherits from; plays the same role MetaData plays for Core.
• Mapped[type] / mapped_column() — declares a mapped attribute and its underlying column.
• relationship(back_populates="...") — links two models bidirectionally; not a database column.
• Session — the ORM’s equivalent of a Connection; tracks loaded objects and stages changes.
• session.scalars(select(Model)) — runs a query and returns full model instances.
• .one() — unwraps a result expected to have exactly one row, erroring otherwise.
• session.add() / session.delete() — stage a new or removed object; session.commit() persists the change.

Why This Matters#

The ORM is not a replacement for Core — it is built on top of it, and you will reach for whichever one fits the job. Core’s explicit select()/join() style tends to win for reporting and analytics queries where you want tight control over exactly what SQL runs; the ORM tends to win for application code, where navigating order.customer.email reads far better than threading a join through every function that needs it.

Continue Building Your Skills#

You now have both of SQLAlchemy’s major layers in your toolkit. In the next lesson you will return to Core with an eye on performance — pagination, bulk operations, indexes, and streaming results, for when your tables grow far past fourteen rows.