SQLAlchemy 2.0 ORM

This lesson on SQLAlchemy 2.0 ORM is hands-on and example-driven. You will define relational database schemas and execute CRUD operations using modern SQLAlchemy 2.0 declarative mapping in Python. You will manage transactions using the Session unit-of-work pattern and hydrate related models without manual SQL joins while avoiding architectural tight coupling.

What You'll Be Able To Do

• Define declarative database models using SQLAlchemy 2.0 type-hinted column mapping.
• Generate and materialize database schemas using create_engine and create_all.
• Execute transactional create and read operations using the Session context manager.
• Configure foreign key constraints and automated object hydration using relationship.
• Decouple domain business logic from ORM models using intermediate data classes (DTOs).

Detailed Concept Walkthrough

1. Declarative Mapping and Engine Setup

SQLAlchemy 2.0 maps Python classes directly to database tables by inheriting from a DeclarativeBase class, bridging the object-relational impedance mismatch. Type annotations define column datatypes while the engine manages the physical database connection.

• Mechanism: You define models as classes inheriting from a common base class created with DeclarativeBase. Attributes use Python type annotations alongside mapped_column to express database column constraints and data types.
• Under the Hood: Calling create_engine establishes the database dialect and connection pool. Invoking Base.metadata.create_all(engine) emits DDL (CREATE TABLE) statements for any mapped models that do not already exist in the target database.
• Best Practice: Keep in mind that create_all is non-destructive and checks for table existence before emitting DDL. It will not alter or update columns on tables that are already present in the database.

from sqlalchemy import create_engine, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class Employee(Base):
    __tablename__ = "employees"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))

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

Key Takeaway: Base.metadata.create_all only creates non-existent tables and will not update modified schema structures on existing tables.

2. Transactional Session and CRUD Operations

The Session acts as an in-memory holding zone and transactional unit of work that tracks object state changes before synchronizing them with the database.

• Mechanism: Instantiate models as standard Python objects and stage them for insertion using session.add(). Changes remain pending in memory until you explicitly invoke session.commit() to flush and finalize the transaction.
• Execution Flow: Using the Session context manager ensures transactions close cleanly. Read queries fetch mapped instances directly into the session identity map, requiring no commit call for read-only operations.
• Under the Hood: The session translates Python object operations into parameterized SQL statements (e.g., INSERT, SELECT) and executes them against the connection engine during flushes and commits.

from sqlalchemy.orm import Session

# Create (Insert)
with Session(engine) as session:
    new_emp = Employee(name="Alice")
    session.add(new_emp)
    session.commit()  # Flushes pending changes and commits transaction

# Read (Query)
with Session(engine) as session:
    employees = session.query(Employee).all()
    for emp in employees:
        print(emp.id, emp.name)

Key Takeaway: Write operations require an explicit session.commit() to persist changes, whereas read-only queries do not.

3. Relational Modeling and Object Hydration

Foreign keys enforce referential integrity at the database level, while relationship constructs provide Python-level object navigation across relational boundaries.

• Mechanism: Define a ForeignKey constraint on a child model's mapped column to point to the parent primary key. Add a relationship attribute to navigate directly to the related parent model via dot notation.
• Under the Hood: SQLAlchemy intercepts dot notation access on relationship attributes and hydrates the related object from the database without requiring manual SQL join statements in your query logic.
• Syntax Rule: SQLite does not enforce foreign keys by default at the connection level. You must configure the engine to enforce foreign key constraints explicitly to prevent invalid relational insertions.

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Department(Base):
    __tablename__ = "departments"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))

class Employee(Base):
    __tablename__ = "employees"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    department_id: Mapped[int] = mapped_column(ForeignKey("departments.id"))
    
    department: Mapped[Department] = relationship()

Key Takeaway: ForeignKey enforces database integrity constraints, while relationship() handles automated Python-level object hydration.

4. Architectural Decoupling and DTOs

Passing ORM models directly across application layers tightly couples your business logic to database infrastructure and hidden framework dependencies.

• Mechanism: Define lightweight, pure Python Data Classes or Data Transfer Objects (DTOs) that mirror the data required by your core domain logic without any ORM base class inheritance.
• Execution Flow: Query the database using the ORM session, extract the required attribute values from the hydrated ORM instances, and instantiate internal DTOs before passing them to application services.
• Best Practice: Isolating ORM models to the data access layer prevents accidental lazy-loading errors outside active sessions and decouples business logic from database schema changes.

from dataclasses import dataclass

@dataclass
class EmployeeDTO:
    id: int
    name: str
    department_name: str

# Decoupling ORM instance to pure DTO
with Session(engine) as session:
    emp = session.query(Employee).first()
    emp_dto = EmployeeDTO(
        id=emp.id,
        name=emp.name,
        department_name=emp.department.name
    )

Key Takeaway: Copy data from ORM models into decoupled DTOs to isolate business logic from database framework dependencies.

Topics Covered in SQLAlchemy 2.0 ORM

1. ORM Justification and Impedance Mismatch (0:00 - 1:14) — The instructor explains the mismatch between objects and tables and introduces ORM as an abstraction layer.
2. Declarative Base and Engine Setup (1:15 - 2:13) — The video demonstrates defining the declarative base, modeling the Employee table, and binding the SQLite engine.
3. Declarative Mapping and Schema Materialization (2:14 - 3:04) — The instructor examines type annotation syntax and verifies table creation behavior using create_all.
4. Transactional Data Insertion (3:05 - 3:47) — The session context manager is used to stage and commit new Employee records to the database.
5. Basic Data Retrieval (3:48 - 4:14) — The video covers querying mapped objects across the session without needing transaction commits.
6. Foreign Keys and Relational Integrity (4:15 - 5:15) — The instructor adds a Department model and enforces relational constraints using ForeignKey.
7. Object Hydration with Relationships (5:16 - 6:13) — The lesson demonstrates using relationship attributes to navigate directly between connected entities.
8. Architectural Decoupling with DTOs (6:14 - 7:32) — The instructor highlights the risks of ORM coupling and shows how to decouple models using internal data classes.

FastAPI Cheat Sheet

• create_engine(url) — Initializes the database connection engine and pool

  engine = create_engine("sqlite:///app.db")
  

• Base.metadata.create_all(engine) — Creates all uncreated mapped tables in database

  Base.metadata.create_all(engine)
  

• Session(engine) — Creates transactional context manager for database operations

  with Session(engine) as session:
      pass
  

• session.add(instance) — Stages a new object for insertion

  session.add(Employee(name="Bob"))
  

• session.commit() — Flushes pending operations and commits current transaction

  session.commit()
  

• ForeignKey("table.column") — Defines relational column constraint referencing another table

  dept_id: Mapped[int] = mapped_column(ForeignKey("departments.id"))
  

• relationship() — Enables Python-level navigation to related ORM objects

  department: Mapped[Department] = relationship()
  

Comparison Table

Common Pitfalls

• Mistake: Expecting create_all to alter existing tables after schema changes. Avoid: Modifying existing tables requires explicit migrations or dropping and recreating tables.
• Mistake: Forgetting to call session.commit() after adding objects. Avoid: Explicitly invoke commit on write transactions to flush and persist staged entities.
• Mistake: Relying on SQLite foreign key constraints without explicit configuration. Avoid: Ensure foreign key enforcement is explicitly enabled on SQLite database connections.
• Mistake: Leaking ORM model entities into core domain logic. Avoid: Map ORM entities into pure Python dataclasses before returning them from data layers.

FAQs

• Why doesn't Base.metadata.create_all update a table when I add a new column? create_all only emits CREATE TABLE for missing tables; it never inspects or alters existing database structures.
• Do I need to call session.commit() when retrieving data with session.query()? No, commit is only required to persist write operations like inserts, updates, and deletes.
• What is the difference between ForeignKey and relationship in SQLAlchemy? ForeignKey defines the database-level relational constraint, while relationship provides the Python attribute used to navigate between linked objects.
• Why should I convert ORM models to Data Classes (DTOs)? Converting to DTOs isolates business logic from ORM dependencies and avoids unexpected lazy-loading database queries outside active sessions.