In SQLAlchemy 2.1, what SQL does relationship()'s default lazy='select' strategy emit when code first reads Order.customer and Order.lines?
answer
- first attribute access, not query time
- one SELECT per object per attribute
- many-to-one checks the identity map first
- WHERE ? = lines.order_id
basics
~20 sNothing 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 linesfrom 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 collectionsgo deeper
Recall that relationship() defaults to lazy='select' and that the related rows load on first attribute access, one SELECT per object.
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.
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.
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'