Why does SQLAlchemy async lazy loading raise MissingGreenlet?
Lazy loading is implicit, synchronous I/O. When you touch user.orders on an unloaded relationship, the ORM issues a SELECT at the moment of attribute access. A plain attribute access cannot be awaited, so there is no point at which the event loop could yield while the query runs.
AsyncSession works by running the ordinary synchronous Session inside a greenlet. Each awaited call, such as await session.execute(...), enters that greenlet context, and the driver's async I/O is bridged back to the event loop from there. An attribute access in your own coroutine happens outside that context. When the ORM tries to emit SQL from there, SQLAlchemy has two options: block the event loop or fail. It fails, and raises MissingGreenlet. That is deliberate. A silent blocking query in an async service would stall every other request on the loop.
The common triggers:
- Plain relationship access on a relationship that the query did not load.
- Expired attributes after commit.
expire_on_commitdefaults toTrue, so afterawait session.commit()every loaded attribute is marked stale. The next read, even of a scalar column, needs a refresh query. - FastAPI response serialization. The response model walks your ORM object after the endpoint returns, often after the session dependency has closed. Any unloaded attribute it touches triggers a load.
- Server-generated columns. After a flush, values produced by server defaults or triggers may not be populated on the object yet, so reading them requires a fetch.
The fix in every case is the same: decide how each relationship loads at query time, never at access time.
import asyncio
from sqlalchemy import ForeignKey, select
from sqlalchemy.exc import MissingGreenlet
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, selectinload
class Base(DeclarativeBase): pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
orders: Mapped[list["Order"]] = relationship() # default lazy="select"
class Order(Base):
__tablename__ = "orders"
id: Mapped[int] = mapped_column(primary_key=True)
user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
async def main():
engine = create_async_engine("sqlite+aiosqlite:///:memory:")
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.create_all)
Session = async_sessionmaker(engine) # expire_on_commit=True by default
async with Session() as session:
user = User(name="ada", orders=[Order()])
session.add(user)
await session.commit() # every loaded attribute on `user` is now expired
try:
user.name # even a scalar column now needs a refresh SELECT
except MissingGreenlet:
print("expired attribute: refresh attempted outside an awaited call")
user = (await session.scalars(select(User))).one() # name reloaded, orders not
try:
user.orders # implicit lazy load: sync I/O from plain attribute access
except MissingGreenlet:
print("lazy relationship: SELECT attempted outside an awaited call")
# The fix: decide the load strategy at query time, inside the await
user = (await session.scalars(select(User).options(selectinload(User.orders)))).one()
print(len(user.orders)) # already loaded, no I/O on access
asyncio.run(main())
Two common triggers of MissingGreenlet (an attribute expired by commit and an unloaded lazy relationship) next to the fix, which declares the relationship load with selectinload in the awaited query.
How do selectinload and joinedload fix lazy loading in async sessions?
Eager loader options are the primary tool for this. Attach them to the statement, for example select(Parent).options(selectinload(Parent.children)), and SQLAlchemy loads the relationship while the query is still being awaited. No attribute access triggers I/O later.
selectinload: a second query keyed on primary keys
selectinload runs the parent query first. It then collects the parent primary keys and emits a second statement of the form SELECT ... FROM child WHERE child.parent_id IN (...). Each parent row is returned exactly once, and the child rows are fetched once and grouped in Python. You pay one extra round trip per relationship path, but row volume stays proportional to the data you actually need. This makes it the default choice for one-to-many and many-to-many collections.
joinedload: one query, wider rows
joinedload adds a LEFT OUTER JOIN to the original statement. It fits many-to-one and one-to-one scalar relationships, such as Order.customer, because each parent row gains at most one related row and no duplication occurs. On a collection, however, every parent row repeats once per child, so ten children produce ten copies of the parent's columns. For that reason SQLAlchemy requires you to call .unique() on the result before you consume ORM entities from a joined-eager collection.
Nested paths
Chain options to reach deeper levels: selectinload(A.bs).selectinload(B.cs). Each link in the chain adds one IN query. Loader styles can be mixed, for example selectinload(A.bs).joinedload(B.owner).
| Loader | Queries per path | Row volume | Best fit |
|---|---|---|---|
selectinload |
+1 per relationship | One row per entity | One-to-many, many-to-many |
joinedload |
0 extra (same query) | Parent rows × children | Many-to-one, one-to-one |
joinedload on collection |
0 extra | Multiplied, requires .unique() |
Small, bounded collections only |
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import joinedload, selectinload
from app.models import Author, Book, Order, Review # placeholder models
async def load_examples(session: AsyncSession) -> None:
# One-to-many: parent SELECT, then a second
# SELECT ... FROM book WHERE book.author_id IN (...)
stmt = select(Author).options(selectinload(Author.books))
authors = (await session.scalars(stmt)).all()
for author in authors:
print(author.name, len(author.books)) # already loaded, no I/O
# Many-to-one scalar: a LEFT OUTER JOIN adds at most one row per order
stmt = select(Order).options(joinedload(Order.customer))
orders = (await session.scalars(stmt)).all()
# joinedload on a collection repeats each author once per book,
# so .unique() is required to collapse duplicate entities
stmt = select(Author).options(joinedload(Author.books))
result = await session.scalars(stmt)
authors = result.unique().all()
# Nested path: Author -> books -> reviews, one IN query per link
stmt = select(Author).options(
selectinload(Author.books).selectinload(Book.reviews)
)
authors = (await session.scalars(stmt)).all()
# Mixed styles: collection via IN, scalar via JOIN inside that query
stmt = select(Author).options(
selectinload(Author.books).joinedload(Book.publisher)
)
authors = (await session.scalars(stmt)).all()
# Review is imported only to show the model used in the nested path
_ = Review
This example eager-loads relationships in an AsyncSession: selectinload for collections, joinedload for scalar relationships, .unique() for a joined collection, and chained options for nested paths.
Should you use lazy='raise' and raiseload to catch accidental loads?
Knowing the right loader is half the fix. The other half is making sure nobody forgets to use it. Without guardrails, a missing selectinload shows up as MissingGreenlet far from the query that caused it, often only on a code path your tests never touched.
Setting lazy="raise" on a relationship() changes the default. Accessing an unloaded attribute then raises InvalidRequestError that names the attribute, instead of trying to emit SQL. The variant lazy="raise_on_sql" is less strict. It still allows loads that can be resolved from the identity map without a query, such as a many-to-one whose target is already in the session.
If you don't want to change the model, raiseload("*") applies the same behavior to a single query. Every relationship not covered by an explicit loader option on that statement will raise. This works well for gradual adoption: add it to queries in tests first, fix what breaks, then tighten the defaults.
The cost is boilerplate. Every query has to state its loads. Code that quietly relied on implicit loading, often code written for sync sessions, will fail loudly until you update it.
Do not solve that by setting lazy="selectin" everywhere. That eager-loads every relationship on every query, including list endpoints that only need IDs. The better split is:
- On the relationship:
raise, as a safe default. - On each query:
selectinloadorjoinedloadfor what that use case actually needs.
New async codebases should start this way. It turns a vague runtime failure into a precise error at development time, which is far cheaper to debug than one in production.
from sqlalchemy import ForeignKey, select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import (DeclarativeBase, Mapped, mapped_column,
raiseload, relationship, selectinload)
class Base(DeclarativeBase):
pass
class Author(Base):
__tablename__ = "authors"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
# Safe default: touching .books without an explicit loader raises
# InvalidRequestError naming the attribute, instead of MissingGreenlet.
books: Mapped[list["Book"]] = relationship(back_populates="author", lazy="raise")
class Book(Base):
__tablename__ = "books"
id: Mapped[int] = mapped_column(primary_key=True)
author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
# Less strict: allowed if the Author is already in the identity map,
# raises only if loading it would need to emit SQL.
author: Mapped["Author"] = relationship(back_populates="books", lazy="raise_on_sql")
async def author_detail(session: AsyncSession, author_id: int) -> Author:
# This use case needs the books, so this query declares the load.
stmt = (select(Author).where(Author.id == author_id)
.options(selectinload(Author.books)))
return (await session.execute(stmt)).scalar_one()
async def list_author_ids(session: AsyncSession) -> list[int]:
# Per-query guardrail, no model change needed: any relationship not
# covered by an explicit option on this statement raises if accessed.
stmt = select(Author).options(raiseload("*"))
authors = (await session.scalars(stmt)).all()
# authors[0].books here would raise immediately, not hit the database.
return [a.id for a in authors]
Shows lazy="raise" and lazy="raise_on_sql" as relationship defaults, with each query declaring its own selectinload, and raiseload("") applied to a single query without changing the model.*
What does expire_on_commit=False actually change?
By default, Session.commit() expires every instance in the identity map. The ORM marks all loaded attributes as stale so the next access reloads them from the database. In synchronous code that reload is invisible. In an async session it is implicit I/O outside an await, so touching user.email after await session.commit() raises MissingGreenlet. This happens even for a plain column you loaded a moment earlier.
Creating the factory as async_sessionmaker(engine, expire_on_commit=False) stops that blanket expiration. After commit, objects keep the attribute values they held in memory, so you can serialize them into a response without another round trip. The setting does not load anything new. Relationships that were never loaded are still unloaded, and accessing them still needs eager loading or hits raiseload. Columns the database generated during flush, such as server defaults or values written by triggers, can remain expired. Accessing those after commit can still trigger a load, so fetch them explicitly or refresh the object.
The cost is staleness. Once the transaction ends, your objects are a snapshot. If a concurrent transaction, a trigger, or a scheduled job modifies those rows, nothing tells your in-memory copy. When freshness matters, call await session.refresh(obj) deliberately. Examples include a read-modify-write that spans transactions, or checking a status that another worker may have changed.
Use the setting for request-scoped sessions, where objects live for one request and then get serialized and discarded. Avoid it, or refresh explicitly, in long-lived sessions and in background workers that reuse the same objects across several commits.
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from myapp.models import User # placeholder ORM model with id, email, status
engine = create_async_engine("postgresql+asyncpg://app:example-password@localhost/appdb")
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)
async def update_email(user_id: int, new_email: str) -> dict:
async with SessionLocal() as session:
user = await session.get(User, user_id)
user.email = new_email
await session.commit()
# Safe without a reload: the attribute was not expired on commit.
# With the default expire_on_commit=True this line would attempt
# implicit I/O and raise MissingGreenlet.
return {"id": user.id, "email": user.email}
async def is_still_pending(user_id: int) -> bool:
async with SessionLocal() as session:
user = await session.get(User, user_id)
await session.commit()
# Another worker may have changed status after we loaded it.
# The snapshot will not notice, so refresh deliberately.
await session.refresh(user, attribute_names=["status"])
return user.status == "pending"
Configuring async_sessionmaker with expire_on_commit=False so attributes stay readable after commit, and calling session.refresh() explicitly when a value another transaction may have changed must be current.
How do you structure an async session FastAPI dependency?
The dependency should do one thing: open an AsyncSession from an async_sessionmaker created once at application startup, yield it, and close it when the request finishes. Wrapping the yield in async with means closing the session rolls back any transaction that was not committed, so a handler that raises never leaves a half-finished unit of work behind. Make commits explicit in the handler or service. If the dependency commits on exit, a write can fail after the endpoint has already returned a success response.
The rule that prevents lazy-loading errors is that ORM objects never leave the handler. FastAPI serializes the return value against response_model after your function returns, and it does that serialization on the event loop rather than inside SQLAlchemy's greenlet context. If Pydantic touches an unloaded relationship at that point, you get MissingGreenlet even when the session is technically still open. The order of dependency teardown relative to response serialization has also differed across FastAPI releases, so don't design around it. Instead, build the response model inside the session scope, for example with model_validate(obj) and from_attributes enabled, and return that. Any missing eager load then fails inside your code and points at the query that caused it.
That approach has a cost. Converting in the handler ties your query shape to your response shape: adding a field to the API silently requires a new selectinload. In a larger service, have a repository or service layer return explicit DTOs it constructed itself. The loading strategy and the mapping then sit next to each other, and the HTTP layer never sees an ORM instance.
When are refresh, awaitable_attrs and run_sync acceptable escape hatches?
Sometimes the access pattern genuinely isn't known when the query runs. A webhook handler may need one related row only on a rare branch, or a background job may inherit code written for a synchronous Session. SQLAlchemy offers three ways around eager loading, and each is safe only within a narrow scope.
await session.refresh(obj, attribute_names=["orders"]) emits a SELECT for the named attributes and populates them on that instance. The AsyncAttrs mixin adds obj.awaitable_attrs, so await user.awaitable_attrs.orders performs the lazy load as an explicit await. Both are fine for a single object you already hold. Inside a loop over fifty parents, though, each call issues its own round trip, and you have rebuilt the N+1 pattern that selectinload existed to prevent. The await keyword only makes those queries visible in the code. It does not batch them.
await session.run_sync(fn) passes a synchronous Session to fn, where implicit lazy loads work again because they run inside the greenlet context. That is useful when you need to call legacy repository code unchanged during a migration. The cost is that every attribute access inside fn may now be a hidden query, so you can no longer tell from the async code where I/O happens. Treat run_sync as a temporary bridge with a removal ticket, not as a pattern for new code.
| Option | Good fit | Main risk |
|---|---|---|
refresh(..., attribute_names=) |
One object, known attributes | N+1 when looped |
awaitable_attrs |
Rare, conditional single load | N+1 when looped; easy to overuse |
run_sync |
Wrapping legacy sync ORM code | Hidden queries, opaque performance |
Who should use this approach?
The pattern described here is explicit loaders per query, lazy="raise" as the relationship default, and ORM objects converted before they leave the session scope. It is aimed at these teams:
- Teams building new services on
AsyncSessionwith an asyncio framework such as FastAPI. You have no legacy implicit-loading code to protect, so adoptingraisedefaults from the start costs the least. - Teams migrating a synchronous SQLAlchemy codebase to async. These codebases have the most implicit lazy loads, and those loads become
MissingGreenletfailures.raiseload("*")in tests andrun_syncas a temporary bridge let you migrate one endpoint at a time. - Services with many endpoints sharing the same models. When a relationship is touched from dozens of places, a per-query declaration is the only reliable way to know what each endpoint loads.
- Teams that have been bitten by N+1 queries. Query-count tests and
raisedefaults turn a performance regression into a test failure.
It is less compelling in some cases. A small script, an admin tool, or a low-concurrency internal service may not benefit from async at all. A synchronous Session with ordinary lazy loading can be the simpler and perfectly adequate choice there. The boilerplate pays off when the codebase and the number of contributors grow, not on day one of a prototype.
What are the alternatives?
There are other ways to deal with lazy loading in async code. Each trades away something the approach above keeps.
- Use a synchronous
Sessioninstead ofAsyncSession. FastAPI runs plaindefendpoints in a threadpool, so a sync session with normal lazy loading works there withoutMissingGreenlet. You give up async I/O for database calls and are bounded by the threadpool and connection pool sizes. Lazy loads still happen, so N+1 problems are still possible. They just don't raise. - Set eager loading on the relationship itself with
lazy="selectin"orlazy="joined". This removes the error with one line per relationship. As discussed above, the cost is over-fetching on every query that doesn't need the relationship, and this cost grows as models gain relationships. - Rely on escape hatches such as
awaitable_attrs,refresh, orrun_synceverywhere. This keeps access-time loading, makes it explicit or hides it again, and reintroduces N+1 risk when used in loops. - Use write-only relationships for large collections. A
WriteOnlyMappedrelationship never loads the whole collection implicitly. You query it with an explicit, awaitedselect(). This fits collections too large to eager-load. Note thatlazy="dynamic"is not compatible with asyncio. - Skip ORM entities for read paths. Selecting specific columns with Core or
select(Model.id, Model.name)returns rows, not ORM instances, so there are no relationships to lazy-load. This works well for list and reporting endpoints. You lose identity-map and unit-of-work behavior on those paths.
Most production codebases end up combining these: per-query loaders for ORM reads, column-only selects for heavy list endpoints, and write-only relationships for unbounded collections.
How do you verify loading behavior and what does it cost?
Verification has two parts: see what SQL each endpoint emits, then make a test fail when that changes.
Count the queries
Passing echo=True to create_async_engine logs every statement. That works for eyeballing one request, but logs are hard to assert on. For tests, attach a before_cursor_execute listener to engine.sync_engine and increment a counter. Async engines dispatch core events through the underlying sync engine, which is why the listener goes there. Each endpoint test can then assert an exact count, such as "list orders issues two queries." A count that grows with the number of rows is an N+1, even if no exception is raised.
Fail on unexpected loads
Run the endpoint's query with raiseload("*") appended after the explicit loaders. Any relationship you did not request then raises InvalidRequestError at the point of access. This error is clearer than MissingGreenlet. It also fires in code paths where an implicit load would otherwise succeed quietly, for example inside run_sync. Combined with the query count, the test fails for both accidental loads and loaders that were added but are wasteful.
What it costs
You pay for this up front. Every query states its loading strategy, and every endpoint gets a count assertion. The return is fewer hours spent tracing MissingGreenlet stack traces and fewer N+1 regressions found under production load.
Illustrative estimate (assumed numbers, not a benchmark):
Net hours saved = (incidents avoided × hours per incident) − (endpoints × minutes of boilerplate ÷ 60)
With these assumed inputs:
- 40 endpoints at 10 minutes of boilerplate each, which is about 6.7 hours
- 4 loading incidents avoided per quarter at 3 hours each, which is 12 hours
The result is roughly 5.3 hours saved in the first quarter. Because the boilerplate is a one-time cost and the incidents recur, the savings grow in later quarters.
How can it save engineering time?
The time savings come from moving failures earlier and making them precise.
- Errors name the problem. With
lazy="raise", a missing loader produces anInvalidRequestErrorthat names the attribute at the line that touched it. AMissingGreenletraised during response serialization does not give you that. Debugging becomes a matter of reading one error instead of reconstructing which query forgot which relationship. - Failures happen in development and CI, not production.
raiseload("*")in tests and per-endpoint query-count assertions catch both accidental loads and N+1 regressions before merge. A reviewer does not have to spot them by reading code. - Code review gets easier. When every query declares its loads and ORM objects are converted inside the session scope, a reviewer can see from the diff what each endpoint fetches. There are no hidden queries triggered by attribute access somewhere downstream.
- Migrations proceed incrementally. Teams moving from sync to async can turn on
raiseload("*")endpoint by endpoint and userun_syncas a tracked, temporary bridge. They avoid a large rewrite that breaks in unpredictable places. - Less performance firefighting. Over-fetching from blanket
lazy="selectin"and N+1 patterns from loopedawaitable_attrsare both avoided by design. That means fewer slow-endpoint investigations under load.
The illustrative estimate in the previous section shows how to reason about the trade-off for your own codebase. Plug in your own endpoint count, boilerplate time, and incident history rather than relying on the assumed inputs.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to sqlalchemy async lazy loading, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- Consumers learn about a breaking change at review time rather than from production errors.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.