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?
answer
- mapping default or per-statement wildcard
- raise versus raise_on_sql
- the flush ignores raise
- errors surface at attribute access
basics
~20 sOften 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.
solid answer
~40 sThere are three levers. `relationship(lazy="raise")` makes every unplanned relationship access raise `InvalidRequestError`, so each query must name `selectinload()` or `joinedload()`; a per-query option still overrides it. `lazy="raise_on_sql"` (or `raiseload(attr, sql_only=True)`) is softer: a many-to-one found in the identity map still resolves, and only a load that would emit SQL raises. `raiseload("*")` on a statement guards one read and covers every relationship it reaches, nested ones included, unless scoped with `Load(Order).raiseload("*")`. I usually put `raise_on_sql` on mappings for the service code and keep scripts free to opt back in with `lazyload()`. The costs are real: more loader options to maintain, errors that appear only when a rarely run path executes, and the flush still loads collections it needs, so raising does not make writes free.
code
python · 43 linesfrom sqlalchemy import ForeignKey, create_engine, select
from sqlalchemy.exc import InvalidRequestError
from sqlalchemy.orm import (DeclarativeBase, Mapped, Session, mapped_column,
relationship, selectinload)
class Base(DeclarativeBase):
pass
class Customer(Base):
__tablename__ = "customers"
id: Mapped[int] = mapped_column(primary_key=True)
# team rule: no relationship may lazy-load; each query opts in to a loader
orders: Mapped[list["Order"]] = relationship(back_populates="customer", lazy="raise")
class Order(Base):
__tablename__ = "orders"
id: Mapped[int] = mapped_column(primary_key=True)
customer_id: Mapped[int | None] = mapped_column(ForeignKey("customers.id"))
# raise_on_sql still allows a many-to-one found in the identity map
customer: Mapped[Customer | None] = relationship(back_populates="orders", lazy="raise_on_sql")
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as s, s.begin():
s.add(Customer(id=1, orders=[Order(), Order()]))
with Session(engine) as s:
customer = s.scalars(select(Customer).options(selectinload(Customer.orders))).one()
print(customer.orders[0].customer is customer) # True: identity-map hit, no SQL
with Session(engine) as s:
order = s.scalars(select(Order)).first()
try:
order.customer # would need a SELECT
except InvalidRequestError as err:
print(err) # 'Order.customer' is not available due to lazy='raise_on_sql'
with Session(engine) as s, s.begin():
s.delete(s.get(Customer, 1)) # the flush still loads Customer.orders to null the FKsgo deeper
Know that lazy='raise' and raiseload() make an unplanned lazy load raise InvalidRequestError instead of emitting SQL.
Explain raise versus raise_on_sql, the reach of raiseload('') and Load(Order).raiseload(''), and how a per-query option overrides a mapping default.
Roll the rule out without breaking pages: find the reads that need options, add tests that render them, and remember the flush still loads what it needs.
Choose the scope, mapping-wide raise, raise_on_sql, or per-statement wildcards, and own the cost: verbose queries, review load, and failures in untested paths.
## The decision Lazy loading is SQLAlchemy's default because it is the safest for correctness: any attribute can be read and it will load. It is also how list pages quietly turn into hundreds of statements. A lead can flip the default so that an **unplanned lazy load fails loudly** instead. SQLAlchemy 2.1 gives this as a loader strategy, the **raise** strategy, which fires at the moment a lazy load would happen and raises `InvalidRequestError` instead. Whether the general rule "plan every fetch" is worth having is covered in the ORM concepts material on fetch plans; the question here is which of SQLAlchemy's levers to use and what each one costs. ## The levers | Lever | Scope | Raises when | |---|---|---| | `relationship(lazy="raise")` | every query of the class | any unloaded access, identity-map hits included | | `relationship(lazy="raise_on_sql")` | every query of the class | only when loading would emit SQL | | `raiseload(attr)` / `raiseload(attr, sql_only=True)` | one statement, one attribute | as `raise` / `raise_on_sql` | | `raiseload("*")` | one statement, every relationship reached | any relationship not given another option | | `Load(Order).raiseload("*")` | one statement, `Order`'s relationships only | as above, scoped to one entity | | `defer(col, raiseload=True)`, `mapped_column(deferred_raiseload=True)` | columns | an unloaded column is read | Important details: - A mapping-level `lazy="raise"` is a **default**, not a lock. `select(Customer).options(selectinload(Customer.orders))` still loads the collection; a script can use `lazyload()` to opt back into lazy loading for one statement. - `raise` raises even when a simple many-to-one could be answered from the identity map; `raise_on_sql` allows that hit and also allows a missing foreign key to resolve to `None`. - `raiseload("*")` reaches past the main entity: with `select(Order).options(joinedload(Order.items), raiseload("*"))`, the loaded `Item` objects raise too. `Load(Order).raiseload("*")` limits it to `Order`. - `raiseload()` covers relationships only; columns use the `raiseload=True` flag on `defer()` or `load_only()`. ## What the rule costs 1. **Verbosity and maintenance.** Every read that needs related data must name it. A new field on an API response often means a new loader option, and reviews have to check them. 2. **Late failures.** The error happens at attribute access, at run time. A rarely run admin export, a template branch or a serializer touching one more attribute fails in production unless tests exercise it. The rule shifts work into tests rather than removing it. 3. **The flush is exempt.** The query guide is explicit: raise strategies **do not apply** within the unit-of-work flush. Deleting a `Customer` whose `orders` relationship is `lazy="raise"` still loads that collection so the flush can null the foreign keys. The rule stops accidental reads; it does not make writes cheaper. 4. **Option paths interact.** A `raiseload("*")` chained under a deep path is scoped to that path, and an object that one query reaches along two paths deserves a test of its own. 5. **Sticky options.** A raise strategy attached by one query stays on that object, so a later query in the same Session that returns it can inherit the raise; reads that need different behaviour must refresh the objects with `populate_existing` or use a fresh Session. ## A workable policy There is no single right answer; these are the choices a lead weighs: - **Service code with list endpoints**: `lazy="raise_on_sql"` on relationships, so many-to-one lookups that are already in the Session stay cheap and anything needing SQL must be planned. - **Strictest teams**: `lazy="raise"` everywhere, with `selectinload()` for collections and `joinedload()` for many-to-ones in each query, accepting the verbosity. - **Gradual adoption**: keep the mapping default lazy and add `raiseload("*")` to the statements behind hot pages, so the guard lands where N+1 hurts first. - **Batch scripts and migrations**: allow lazy loading per statement with `lazyload()` when convenience beats statement count. Whatever the choice, pair it with tests that render the real pages so `InvalidRequestError` appears in CI, and with statement-count assertions on the hottest reads; counting statements in tests is covered with test transactions and SQL logs. Under the asyncio extension, implicit lazy loads fail anyway, which pushes async services toward the same explicit loading; that failure mode belongs to the asyncio material.
- Why might you choose raise_on_sql over raise for many-to-one relationships?raise_on_sql only fails when the load would emit SQL. A simple many-to-one whose target is already in the identity map, or whose foreign key is NULL, still resolves without a query, so common in-memory navigation keeps working while unplanned SELECTs still raise.
- Does a lazy='raise' mapping stop a script that genuinely wants lazy loading?No. It is the default strategy, and a per-query option overrides it, so the script can say select(Customer).options(lazyload(Customer.orders)) and load lazily for that statement only.
- lazy='raise' on a mapping makes the relationship impossible to load in any query
- raise_on_sql logs a warning instead of raising
- raiseload('*') only affects relationships on the main entity
- Raise strategies also stop the flush from loading collections
- raiseload() can be applied directly to a column attribute