ORMs

Raw SQL gives you control, but application code can become a pile of repeated strings: one query to load a member, another to load their books, another to save a change. An ORM, short for object-relational mapper, is a tool that maps database tables to Python objects, like a careful translator standing between the clipboard world of rows and the blueprint world of classes.

Why teams use ORMs

An ORM does not make SQL disappear. It gives your program a steadier way to say common database ideas:

• A Python class represents a table.
• An instance represents one row.
• An attribute represents one column.
• A relationship is an object-level link between records whose tables are connected by keys.
• A session is the ORM workspace where your code loads objects, tracks changes, and commits database work.

That shape matters on teams because names live in one place. If your members table has a city column, the model class becomes the shared place where that fact is named and typed. Without that, the same table and column names often get copied through dozens of handwritten SQL strings.

The trade-off is real: an ORM adds another layer to learn. Think of it like an automatic transmission. It handles routine gear changes for you, but a serious driver still understands the engine. A serious ORM user still reads SQL.

A tiny manual mapping

Here is the standard-library version of the idea. This is not SQLAlchemy. It is a small runnable sketch of what mapping means: query rows, turn each row into a Python object, then use attributes instead of tuple positions.

The object gives the row a shape Python understands. borrowed.title is easier to read than row[1], and the class name tells you what kind of record you are holding.

Build SQL with objects

SQLAlchemy is a widely used Python toolkit for working with SQL databases. It is an external package, so the next examples are static shapes, not browser-runnable code.

SQLAlchemy Core is SQLAlchemy's lower-level layer for building SQL statements with Python objects instead of writing every SQL string by hand. A SQL expression is a Python object that represents a SQL statement before it is sent to the database.

# Static SQLAlchemy Core shape
from sqlalchemy import Column, Integer, MetaData, String, Table, create_engine, select
 
engine = create_engine("sqlite:///library.db")
metadata = MetaData()
 
members = Table(
    "members",
    metadata,
    Column("member_id", Integer, primary_key=True),
    Column("name", String, nullable=False),
    Column("city", String, nullable=False),
)
 
with engine.begin() as connection:
    metadata.create_all(connection)
    connection.execute(
        members.insert(),
        [
            {"name": "Mina", "city": "Mumbai"},
            {"name": "Ravi", "city": "Bengaluru"},
        ],
    )
 
    statement = select(members.c.name).where(members.c.city == "Mumbai")
    rows = connection.execute(statement)

Core stays close to SQL. You still think in tables, columns, inserts, selects, and connections. The benefit is composition: Python can build a statement without you gluing strings together.

Map classes to tables

SQLAlchemy ORM is SQLAlchemy's object-mapping layer: you define Python classes, and SQLAlchemy maps them to tables. A model is a Python class that represents a database table.

A mapped class is a model class registered with the ORM. The session is like the staging area from Git: you work with objects, then commit the database changes when the unit of work is ready.

# Static SQLAlchemy ORM shape
from sqlalchemy import ForeignKey, String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship
 
class Base(DeclarativeBase):
    pass
 
class Member(Base):
    __tablename__ = "members"
 
    member_id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(80))
    city: Mapped[str] = mapped_column(String(80))
    loans: Mapped[list["Loan"]] = relationship(back_populates="member")
 
class Book(Base):
    __tablename__ = "books"
 
    book_id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(120))
    loans: Mapped[list["Loan"]] = relationship(back_populates="book")
 
class Loan(Base):
    __tablename__ = "loans"
 
    loan_id: Mapped[int] = mapped_column(primary_key=True)
    member_id: Mapped[int] = mapped_column(ForeignKey("members.member_id"))
    book_id: Mapped[int] = mapped_column(ForeignKey("books.book_id"))
    due_date: Mapped[str] = mapped_column(String(10))
 
    member: Mapped[Member] = relationship(back_populates="loans")
    book: Mapped[Book] = relationship(back_populates="loans")
 
engine = create_engine("sqlite:///library.db")
 
with Session(engine) as session:
    mina = session.scalar(select(Member).where(Member.name == "Mina"))
    for loan in mina.loans:
        print(loan.book.title)

An ORM relationship is built from table keys. In SQL you join loans.book_id to books.book_id; in ORM code you can follow loan.book. The database still has tables and foreign keys underneath.

Queries and relationships

ORM queries look like Python, but they still become SQL. This is the right mental model: you are describing a database question through model classes.

# Static SQLAlchemy ORM query shapes
with Session(engine) as session:
    python_books = session.scalars(
        select(Book)
        .where(Book.title.contains("Python"))
        .order_by(Book.title)
    ).all()
 
    ravi = session.scalar(
        select(Member).where(Member.name == "Ravi")
    )
 
    new_loan = Loan(member=ravi, book=python_books[0], due_date="2026-07-20")
    session.add(new_loan)
    session.commit()

That code is shorter than the raw INSERT plus lookup SQL from the previous lesson, but it is not magic. The ORM must still issue SQL statements, obey constraints, and commit transactions. When performance matters, teams inspect the SQL an ORM generates and sometimes drop down to Core or raw SQL for a sharper tool.

Version the schema

A database migration is a versioned change to the database schema, such as adding a table, renaming a column, or creating an index. Migrations are Git commits for your database shape: they let a team move every environment from the old schema to the new one in the same order.

Alembic is SQLAlchemy's migration tool. It is also an external package, so this is a static terminal shape:

alembic init migrations
alembic revision -m "create books table"
# edit the generated revision file: fill in upgrade() and downgrade()
alembic upgrade head

A revision is one migration file. After creating it, you edit the generated file so upgrade() says how to move forward and downgrade() says how to move backward if the migration supports that.

# Static Alembic migration shape
from alembic import op
import sqlalchemy as sa
 
def upgrade():
    op.create_table(
        "books",
        sa.Column("book_id", sa.Integer(), primary_key=True),
        sa.Column("title", sa.String(length=120), nullable=False),
    )
 
def downgrade():
    op.drop_table("books")

You do not edit production tables by hand and hope everyone remembers what changed. You write a migration, review it, run it in order, and keep it with the project history.

ORMs are a bridge: they keep relational databases underneath while giving Python code object-shaped handles. Next, NoSQL & Beyond steps outside the relational model and asks when key-value or document stores fit better than SQL tables.