skip to content

Loader Strategy Options

Loader options decide how related objects load: lazy select, selectinload, joinedload or raiseload. Interviewers ask how to spot an N+1 and which option fits a collection versus a many-to-one.

on this pageshow

explore

questions

5

In SQLAlchemy 2.1, what SQL does relationship()'s default lazy='select' strategy emit when code first reads Order.customer and Order.lines?

level: juniorimportance: must knowfreq 70%

answer

  1. first attribute access, not query time
  2. one SELECT per object per attribute
  3. many-to-one checks the identity map first
  4. WHERE ? = lines.order_id

basics

~20 s

Nothing at query time. On first access each Order fires its own SELECT for its lines, and Order.customer fires a SELECT by primary key unless that Customer is already in the Session, in which case no SQL runs.

solid answer

~40 s

`relationship()` defaults to `lazy="select"`, so `select(Order)` loads only the `orders` columns. The first read of `order.lines` emits `SELECT ... FROM lines WHERE ? = lines.order_id` for that one order, inside the Session's current transaction, and the result stays on the object. A simple many-to-one such as `order.customer` first looks up the Customer by primary key in the Session's identity map and emits `SELECT ... FROM customers WHERE customers.id = ?` only on a miss. So a loop over 50 orders that touches both attributes, with 5 distinct customers, runs 1 + 5 + 50 = 56 statements. The fix is a per-query loader option such as `selectinload()` or `joinedload()`, or a different `lazy=` value on the mapping.

code

python · 46 lines
python
from sqlalchemy import ForeignKey, create_engine, event, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customers"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
    customer: Mapped[Customer] = relationship()  # lazy="select" by default
    lines: Mapped[list["Line"]] = relationship()


class Line(Base):
    __tablename__ = "lines"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"))
    qty: Mapped[int]


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as s, s.begin():
    customers = [Customer(name=f"c{i}") for i in range(5)]
    for i in range(50):
        s.add(Order(customer=customers[i % 5], lines=[Line(qty=1), Line(qty=2)]))

statements = []
event.listen(engine, "before_cursor_execute",
             lambda conn, cur, stmt, params, ctx, many: statements.append(stmt))

with Session(engine) as s:
    orders = s.scalars(select(Order).limit(50)).all()  # 1 SELECT
    for order in orders:
        order.customer.name  # SELECT only when the customer is not yet in the Session
        len(order.lines)     # one SELECT per order, every time
    print(len(statements))   # 56 = 1 + 5 customers + 50 collections

go deeper

for a junior

Recall that relationship() defaults to lazy='select' and that the related rows load on first attribute access, one SELECT per object.

for a middle

Explain the two SQL shapes, the collection SELECT keyed by the parent id and the many-to-one lookup that skips SQL on an identity-map hit, and how lazyload() overrides a mapping.

for a senior

Read an engine log, count the repeated child SELECTs on a list page, and say which accesses are identity-map hits and which loader option removes the rest.

for a principal

Lazy-by-default keeps simple scripts cheap but lets list pages degrade silently; be ready to say how a team spots that before production.

## What the default strategy is Every `relationship()` in SQLAlchemy 2.1 has a **loader strategy**: the rule for when and how the related rows are fetched. Unless the mapping or the statement says otherwise, the strategy is `lazy="select"`, usually called **lazy loading**. The attribute starts out unloaded, and the first time Python code reads it, SQLAlchemy emits a SELECT to fill it. The name "select" comes from that SELECT. Take an order page with three classes: `Order`, its many-to-one `Order.customer`, and its one-to-many collection `Order.lines`. The statement `session.scalars(select(Order).limit(50)).all()` emits exactly one SELECT, against `orders` only. Neither relationship is touched yet. ## The SQL each attribute emits What happens on first access depends on the kind of relationship: - **A collection** (`Order.lines`): SQLAlchemy emits `SELECT lines.id, lines.order_id, lines.qty FROM lines WHERE ? = lines.order_id`, bound to that one order's primary key. Each order runs its own copy, because each collection belongs to one parent. - **A simple many-to-one** (`Order.customer`): the foreign key already on the order row (`orders.customer_id`) names the target's primary key. SQLAlchemy first looks for that Customer in the Session's **identity map**, the per-Session table of objects keyed by primary key. A hit returns the object with **no SQL at all**; a miss emits `SELECT customers.id, customers.name FROM customers WHERE customers.id = ?`. - **After the first load**, the attribute holds its value; reading it again emits nothing until the object is expired (a commit does that by default) or refreshed. The identity-map shortcut only applies to a "simple" many-to-one, where the join condition is the target's primary key compared to a plain foreign key, with no extra criteria. That is why a page of 50 orders that belong to 5 customers emits 5 customer SELECTs, not 50, while the 50 `lines` collections always cost 50. ## Reading it in the SQL log Turn on `create_engine(..., echo=True)` or the `sqlalchemy.engine` logger and the pattern is unmistakable: one SELECT against the parent table, then the same SELECT against the child table repeated with a different bound parameter each time. Each repeat is marked `[cached since ...]` because the compiled statement is reused, so the log shows the same SQL text over and over with only the parameter changing. Counting those repeats is how the extra statements are found; the general method of counting statements per request belongs to the ORM concepts material on N+1 detection. | Access | Strategy `select` emits | When it emits nothing | |---|---|---| | `order.lines` (collection) | `SELECT ... FROM lines WHERE ? = lines.order_id` | once loaded, until expired | | `order.customer` (simple many-to-one) | `SELECT ... FROM customers WHERE customers.id = ?` | the Customer is already in the identity map | ## Where the lazy load runs and when it fails A lazy load always runs through the Session the object belongs to, in that Session's current transaction. Two consequences follow: 1. If the object is detached (its Session was closed), the lazy load cannot run and SQLAlchemy raises `DetachedInstanceError`; detachment itself is covered with the Session's flush, identity and expiry rules. 2. Under the asyncio extension, implicit IO on attribute access is not available, so lazy loading fails there; that failure belongs to the asyncio material. ## Changing the strategy There are two places to change it: - **Per query**, with a loader option on the statement: `select(Order).options(selectinload(Order.lines))` loads every collection in one extra SELECT. `lazyload(Order.lines)` does the reverse and forces lazy loading for a relationship whose mapping says otherwise. - **On the mapping**, with `relationship(lazy="selectin")`, `lazy="joined"` or `lazy="raise"`. This becomes the default for every query that does not state an option. A lazy relationship can still carry instructions for what happens when it does load. `select(Order).options(lazyload(Order.lines).joinedload(Line.product))` returns orders without their lines, but each lazy load of `order.lines` then joins `products` into that SELECT, so the lines arrive with their products. Those sub-options stay attached to the loaded `Order` objects while they remain in memory, so a reload of `order.lines` after expiry joins `products` again. ## Quick checks before blaming lazy loading 1. Count the repeated statements in the log and note which attribute each one fills. 2. Separate collections, which always cost one SELECT per parent, from simple many-to-ones, which may be identity-map hits. 3. Check whether the loop runs over a page of objects or a single one; one object's lazy loads are rarely worth an option. Lazy loading is not wrong in itself. It is cheap when only a few objects are touched, and the many-to-one identity-map shortcut can make it nearly free for a small set of targets. It becomes expensive when a loop touches the same collection on many parents, which is exactly what a list page does.

  • Why does the loop above emit 5 customer SELECTs but 50 line SELECTs?
    Order.customer is a simple many-to-one, so the lazy loader first checks the identity map for that customer's primary key; after the first order of each customer, every later access is a hit. A collection has no such key to look up, so each order's lines need their own SELECT.
  • How do you force lazy loading in one query for a relationship mapped with lazy='selectin'?
    Add the lazyload() option for that attribute, for example select(Order).options(lazyload(Order.lines)). A per-query option overrides the mapping's lazy setting for that statement only; every other query keeps the mapped default.

  • select(Order) loads the related lines and customer up front by default
  • Every lazy many-to-one access always emits its own SELECT
  • A lazy-loaded attribute re-queries the database on every read
  • Lazy loads run outside the Session's transaction
  • The default value of relationship(lazy=...) is 'selectin'
open as a page

In SQLAlchemy 2.1, how do selectinload() and joinedload() differ in the SQL they emit, and which suits a collection versus a many-to-one?

level: middleimportance: must knowfreq 75%

basics

~20 s

selectinload() runs a second SELECT whose IN list holds the parents' keys; joinedload() adds a LEFT OUTER JOIN to the main SELECT. The guide recommends selectinload() for collections and joinedload() for many-to-ones, and a joined collection needs unique().

open as a page

In SQLAlchemy 2.1, how do load_only(), defer() and undefer() decide which columns load for a page listing 10,000 Event rows?

level: middleimportance: should knowfreq 40%

basics

~20 s

load_only() names the columns to fetch, plus the primary key; defer() drops one column; undefer() fetches a column the mapping defers. Reading an unloaded column runs one SELECT per object unless raiseload=True makes it raise.

open as a page

In a SQLAlchemy 2.1 Session, why can a query's loader options seem ignored for objects already loaded, and what makes them apply?

level: seniorimportance: should knowfreq 30%

basics

~10 s

A row matching an object already in the Session is not re-applied, so loaded attributes stay and loader options attached earlier stay too. execution_options(populate_existing=True) overwrites those objects and applies the new options.

open as a page

As a lead, should a SQLAlchemy 2.1 codebase make lazy loads fail by default with lazy='raise' or raiseload('*'), and what does that rule cost?

level: principalimportance: should knowfreq 28%

basics

~20 s

Often yes for request-serving reads, but choose the scope: lazy='raise' on mappings forces every query to name its loaders, raiseload('*') guards selected statements. The cost is more verbose queries and InvalidRequestError in untested code paths.

open as a page