With SQLAlchemy's AsyncSession, which attribute accesses on a mapped object raise MissingGreenlet, and why do they fail instead of loading?
answer
- attribute access cannot await
- internal await_ needs a greenlet
- lazy, expired, deferred, unfetched
- the refused SELECT is in the message
basics
~20 sUnder AsyncSession, any attribute access that would emit a SELECT raises MissingGreenlet: an unloaded lazy relationship, an attribute expired by commit, a deferred column, or an unfetched server default. Plain attribute access cannot await, so SQLAlchemy refuses the IO.
solid answer
~40 s`AsyncSession` runs the ordinary synchronous ORM inside a greenlet started by `greenlet_spawn()`; when the driver must wait, an internal `await_()` hands its coroutine back to the event loop. A bare `order.items` read happens outside any awaited call, so `await_()` finds no such greenlet and raises `MissingGreenlet`: "greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place?" The usual reads that need IO are an unloaded lazy relationship, any attribute expired by `commit()` while `expire_on_commit` is `True`, a deferred column, a collection never assigned on a new object that has been flushed, and a server-generated value the flush did not fetch. A value already loaded, or a many-to-one the identity map can answer, reads without IO and works.
code
python · 45 linesimport asyncio
from sqlalchemy import ForeignKey, String
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
class Base(DeclarativeBase):
pass
class Order(Base):
__tablename__ = "orders"
id: Mapped[int] = mapped_column(primary_key=True)
status: Mapped[str] = mapped_column(String(20))
items: Mapped[list["OrderItem"]] = relationship()
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)
Session = async_sessionmaker(engine)
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 # first access of a lazy relationship, no await
except Exception as exc:
print(str(exc).splitlines()[0])
await engine.dispose()
asyncio.run(main())go deeper
Recall that under AsyncSession reading a relationship that was never loaded raises MissingGreenlet, and that eager loading in the query is the normal way to avoid it.
Explain the greenlet bridge in a sentence and list every kind of access that needs IO: lazy relationships, expired and deferred columns, uninitialised collections and unfetched server values.
Show you can read the refused SELECT in the message, trace the access to a serializer or repr far from the query, and explain why a many-to-one sometimes works from the identity map.
Treat MissingGreenlet as a design signal: data needs should be declared at the query, and a team needs conventions that make a missing load fail in tests rather than intermittently in production.
## What MissingGreenlet is SQLAlchemy's asyncio extension does not contain a second, asynchronous ORM. An `AsyncSession` wraps an ordinary synchronous `Session` and runs each of its operations inside a **greenlet** started by the internal `greenlet_spawn()` function. Deep inside, when the async driver has to wait for the database, SQLAlchemy calls its internal `await_()`, which switches out of that greenlet and hands the driver's coroutine to the event loop. This only works because the outer call (`await session.execute(...)`, `await session.get(...)`, `await session.commit()`) is itself awaited. A plain attribute read such as `order.items` is not awaited. If the ORM decides it must run a SELECT to produce the value, `await_()` finds no SQLAlchemy greenlet around it and raises **`MissingGreenlet`**, a subclass of `InvalidRequestError` with error code `xd2s`: > greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place? The refusal is deliberate. An asyncio program is expected to show every point of IO with an `await`; a SELECT hidden behind attribute access would either stall the event loop or break that rule, so SQLAlchemy raises instead of blocking. ## How it surfaces Depending on where in statement execution the driver first awaits, SQLAlchemy's error handling can wrap the exception in `sqlalchemy.exc.StatementError`. With the aiosqlite driver, a refused lazy load arrives that way: the message starts with `(sqlalchemy.exc.MissingGreenlet)`, then prints the SELECT the ORM tried to run, and the original exception sits on `.orig`. That printed SELECT is the quickest clue to which attribute triggered it, because its FROM and WHERE clauses name the table and the key. ## Where implicit IO hides 1. **An unloaded lazy relationship.** The default `lazy="select"` strategy loads `order.items` or `order.customer` on first access. Under asyncio that first access raises unless a loader option such as `selectinload()` already filled it. 2. **An expired attribute.** `commit()` expires every attribute of every object in the session while `expire_on_commit` is `True`, and `async_sessionmaker` leaves it at `True` unless told otherwise. The next read of even a plain column such as `order.status` needs a SELECT. `AsyncSession.expire()` does the same for the attributes it names. 3. **A deferred column.** A column mapped with `mapped_column(deferred=True)`, or deferred by a `defer()` option, loads on first access. 4. **A collection never set on a new object.** If you add `Order(status="new")` without `items=[]` and flush, `order.items` is unloaded and its first read queries the database. 5. **A server-generated value the flush did not fetch.** A `server_default` or `server_onupdate` column that the INSERT or UPDATE did not bring back through RETURNING stays expired until read. | Access | Why it needs IO | Usual remedy | |---|---|---| | unloaded relationship | the lazy loader runs a SELECT | an eager loader option in the query | | attribute after `commit()` | expired by `expire_on_commit` | `expire_on_commit=False` | | deferred column | deferred until access | `undefer()`, or no deferral | | new object's collection | never initialised | assign an empty collection | | server-generated column | not fetched at flush | `eager_defaults`, or `refresh()` | ## What does not raise - Any attribute already present in the object's state, loaded by the query or set by your own code. - A **many-to-one** whose target's primary key is already in the session's identity map: the lazy loader returns that object without SQL. This makes the bug intermittent, passing when an earlier query happened to load the customer and failing when none did. - Anything awaited explicitly: `await order.awaitable_attrs.items` from the `AsyncAttrs` mixin, or `await session.refresh(order, ["items"])`. Writing `await order.items` does not help. Python evaluates `order.items` first, and that access raises before there is anything to await. ## Diagnosing one - Read the SQL in the message: it shows which load was refused. - Find the access in the traceback. A template, a serializer, logging code or a `__repr__` that touches a relationship is a common culprit, because it runs far from the query that should have loaded the data. - Decide whether the value belonged in the original query (add a loader option) or is genuinely occasional (await it explicitly). Catching the exception and retrying is never the fix: the access has to move inside an awaited call, or happen on data that is already loaded.
- Why does the error sometimes arrive as StatementError rather than MissingGreenlet?When the refused await happens while SQLAlchemy is executing a statement, its error handling can wrap the non-driver exception in `sqlalchemy.exc.StatementError`. The message then begins with `(sqlalchemy.exc.MissingGreenlet)`, prints the SQL, and `.orig` holds the original exception. With aiosqlite a lazy load surfaces this way. Matching on the class alone can therefore miss it, but the real answer is to fix the access, not to catch it.
- Does a many-to-one lazy load always raise under AsyncSession?No. If the related object's primary key is already in the session's identity map, the lazy loader returns it without emitting SQL, so `order.customer` works. That makes the failure depend on what an earlier query loaded, which is why it can pass in one request and fail in the next. Declaring the relationship with `lazy="raise"` turns it into a deterministic `InvalidRequestError`.
It is like a cashier who may only use the till while the manager's key is turned. Inside an awaited call the key is turned and the ORM can query; a bare attribute read happens with the key out, so the till refuses instead of waiting.
- MissingGreenlet means the greenlet package is not installed.
- Writing await order.items turns the lazy load into something awaitable.
- Only relationships can raise MissingGreenlet; plain column attributes never do.
- Catching MissingGreenlet and retrying the access is an acceptable fix.
- Under asyncio SQLAlchemy runs the lazy load in a thread so it does not block.