skip to content

As lead of a service built on SQLAlchemy's asyncio extension, which loading conventions would you standardise to keep MissingGreenlet out of production, and what does each cost?

level: principalimportance: should knowfreq 25%

answer

  1. fail in tests, not in production
  2. no expiry, explicit loads
  3. raise versus raise_on_sql
  4. escape hatches with rules

basics

~20 s

Typical conventions: expire_on_commit=False, lazy="raise" on relationships, loader options declared per query, WriteOnlyMapped for large collections, and AsyncAttrs or refresh() as audited escape hatches. Each trades convenience or freshness for loads that fail early and visibly.

solid answer

~40 s

There is no single policy; the choice depends on how relationship-heavy the model is and how much convention the team will hold. A common baseline is `async_sessionmaker(engine, expire_on_commit=False)`, accepting stale values after commit. On top of it, `lazy="raise"` (or `"raise_on_sql"`, which still allows identity-map hits) turns every forgotten loader option into an `InvalidRequestError` in tests, at the cost of naming loads in every query. Unbounded collections become `WriteOnlyMapped`, trading batch loading for zero implicit IO. `AsyncAttrs` and `refresh(obj, [names])` stay as escape hatches, with a rule against using them in loops. Relationship rules do not cover expired or deferred columns or unfetched server defaults, so `expire_on_commit`, `eager_defaults` and `deferred_raiseload` belong in the same policy. Explicit cascades instead of `"all"` round it out.

code

python · 56 lines
python
import asyncio

from sqlalchemy import ForeignKey, String, select
from sqlalchemy.ext.asyncio import AsyncAttrs, async_sessionmaker, create_async_engine
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    mapped_column,
    relationship,
    selectinload,
)


class Base(AsyncAttrs, DeclarativeBase):
    pass


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    status: Mapped[str] = mapped_column(String(20))
    # team rule: relationships never lazy load
    items: Mapped[list["OrderItem"]] = relationship(lazy="raise")


class OrderItem(Base):
    __tablename__ = "order_items"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"))
    sku: Mapped[str] = mapped_column(String(20))


async def main() -> None:
    engine = create_async_engine("sqlite+aiosqlite://")
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
    # team rule: no expiry on commit
    Session = async_sessionmaker(engine, expire_on_commit=False)
    async with Session() as session:
        session.add(Order(status="new", items=[OrderItem(sku="A1")]))
        await session.commit()

    async with Session() as session:
        order = await session.get(Order, 1)
        try:
            order.items
        except Exception as exc:
            print(type(exc).__name__, exc)

        stmt = select(Order).options(selectinload(Order.items))
        order = (await session.scalars(stmt)).one()
        print([i.sku for i in order.items])
    await engine.dispose()


asyncio.run(main())

go deeper

for a junior

Know the basic conventions an async SQLAlchemy codebase uses: expire_on_commit=False, loader options in every query, and lazy="raise" so a forgotten load fails loudly.

for a middle

Explain what each convention prevents and what it does not: relationship rules miss expired and deferred columns and unfetched server defaults.

for a senior

Show how to enforce the rules in code: a shared Base and session factory, statement-count assertions in tests, and a ban on awaitable_attrs inside loops.

for a principal

Frame the choice as explicitness against convenience, sized to the model and the team, and justify which escape hatches remain and who reviews their use.

## The decision Most SQLAlchemy services on the asyncio extension meet `MissingGreenlet` in production sooner or later: a serializer touches a relationship that one code path forgot to load, or a handler reads an attribute after commit. The lead's choice is **which conventions make those failures impossible or loud**, and which costs the team accepts for them. There is no single right set; it depends on the size of the model, how relationships are read, and how much discipline the team will sustain. ## The levers and their costs | Convention | What it prevents | What it costs | |---|---|---| | `expire_on_commit=False` on the factory | post-commit reads becoming refresh SELECTs | values stay as read; trigger or concurrent changes are not seen | | `lazy="raise"` on every relationship | silent or intermittent lazy loads | every query must name its loads; more option boilerplate | | `lazy="raise_on_sql"` instead | lazy loads that would emit SQL | many-to-ones served from the identity map still pass, so tests depend on what was loaded earlier | | `WriteOnlyMapped` for large collections | any implicit collection load | no batch loading across parents; reads are hand-written queries | | `AsyncAttrs` on the Base | nothing by itself; it is the sanctioned escape hatch | invites N+1 when awaited in loops | | `eager_defaults=True` where server values are read | expired server-generated columns after flush | per-row UPDATE ... RETURNING, or extra SELECTs | | explicit cascades, never `"all"` | `refresh()` expiring related objects through `refresh-expire` | longer `cascade=` strings | ## What lazy="raise" does not cover A relationship rule is often mistaken for a complete policy. It is not: - **Expired columns** are reloaded by the column loader, not the relationship loader, so `lazy="raise"` does nothing for a read after a commit that expired the object. Only `expire_on_commit=False`, or an explicit awaited read through `refresh()` or `awaitable_attrs`, handles that. - **Deferred columns** need their own guard: `mapped_column(deferred_raiseload=True)`, which also defers the column, or `defer(Order.notes, raiseload=True)` on the query. - **Server-generated values** left expired by a flush are governed by `eager_defaults`. - **Explicit loads bypass it.** `await session.refresh(obj, ["items"])` loads a `lazy="raise"` relationship, while `await obj.awaitable_attrs.items` respects the rule and raises. A team that wants an escape hatch on raise-guarded relationships has to standardise on refresh, or loosen specific relationships. ## Choosing a combination 1. **Small model, few relationships, mostly single-object handlers.** `expire_on_commit=False` plus loader options per query, with `AsyncAttrs` for the rare optional read, is often enough. Adding `lazy="raise"` costs little here and catches the misses in tests. 2. **Large model, shared repository functions, many contributors.** Declare every relationship with `lazy="raise"` (a small wrapper around `relationship()` keeps it uniform) and require each query to declare its loads, because no reviewer can keep every path's needs in their head. Pair it with tests that exercise every handler. 3. **Unbounded children (logs, history, line items in the thousands).** Map them as `WriteOnlyMapped` regardless of the rest, so nobody can load them whole by accident. 4. **Mixed sync code.** Legacy synchronous functions can run under `AsyncSession.run_sync()`, where lazy loading works again; confining them there, rather than spreading `run_sync` through new code, keeps the asyncio rule ("every IO point has an await") meaningful. ## Making the policy stick - **Encode it, do not document it**: put `AsyncAttrs` on the shared declarative `Base`, route relationships through one helper that sets `lazy="raise"`, and build one `async_sessionmaker` the whole service imports. - **Review loader options with the query**, not later, because the place that reads the data is usually far from the place that loads it. - **Watch the SQL**: in tests, assert statement counts with `echo` or an engine event listener so an escape hatch inside a loop shows up as a jump in queries. - **Revisit after incidents**: each `MissingGreenlet` that reaches production marks either a missing rule or a rule with a gap, usually one of the column cases above. The trade-off the lead owns is explicitness against convenience: the stricter the conventions, the more each query states, and the fewer surprises reach users.

  • When would you choose lazy="raise_on_sql" over lazy="raise" as the team default?
    `raise_on_sql` raises only when the lazy load would emit SQL, so a many-to-one whose target is already in the identity map still resolves. That suits code that deliberately preloads reference data into the session. The cost is weaker tests: a path can pass because an earlier query happened to load the target and fail in production when it did not. `raise` is stricter and more predictable.
  • Why does SQLAlchemy's asyncio guidance warn against cascade="all"?
    `"all"` includes the `refresh-expire` cascade, so `AsyncSession.refresh()` on a parent also expires related objects without necessarily reloading them, unless they are eagerly loaded. Those related objects are then left expired, and their next read is implicit IO. Listing the needed cascades explicitly, such as `"save-update, merge, delete"`, avoids it.

  • lazy="raise" on every relationship removes every possible MissingGreenlet.
  • AsyncAttrs is a safe default for reading collections in loops.
  • expire_on_commit=False keeps objects automatically in sync with the database.
  • Wrapping all data access in run_sync is a clean long-term async strategy.
  • Once relationships are guarded, deferred columns need no separate rule.