Eager Loading with selectinload

Scenario

You fetch a list of 50 users and return it. Then you add their 'team' name to the Pydantic response model. Suddenly your simple endpoint is throwing a MissingGreenlet crash, or making 51 database queries and taking 2 seconds to load.

Mental model

Lazy loading is sending the intern to the basement archives 50 times to fetch one file each. Eager loading is giving them a list of 50 files upfront and having them bring the whole box up at once.

Instead of waiting for the code to ask for related data one-by-one (lazy loading), eager loading instructs the ORM to fetch the parent rows AND all their related child rows in one cohesive database operation.

Deep dive

By default, SQLAlchemy handles relationships (like User.team or Post.comments) lazily. When you query a User, it does NOT fetch the Team. It waits until you actually type user.team.name in your code, and then it fires a new SELECT query.

Relationships are ignored by the database until explicitly requested.

In synchronous code, lazy loading inside a loop causes the N+1 queries performance issue. In async code, it is fatal: it crashes with MissingGreenlet because it tries to perform network I/O without an await statement.

Lazy loading crashes async SQLAlchemy applications.

The solution is eager loading: telling SQLAlchemy upfront to fetch the related data at the same time it fetches the parent. The modern, highly optimized way to do this in SQLAlchemy is using the selectinload strategy.

Eager loading grabs related data in the initial database trip.

selectinload works by emitting the main query (fetching Users), collecting all their IDs, and then emitting exactly ONE more query (fetching Teams WHERE id IN (...)). It then stitches them together in Python memory.

selectinload emits exactly 2 queries, avoiding expensive Cartesian product JOINs.

Code examples

The MissingGreenlet relationship crash

async def get_users(session: AsyncSession):
    result = await session.execute(select(User))
    users = result.scalars().all()
    
    # CRASH: user.team is not loaded, triggers sync lazy-load
    return [{"name": u.name, "team": u.team.name} for u in users]

Because team wasn't loaded in the initial query, accessing u.team attempts to fire a synchronous query, causing a MissingGreenlet exception in the async engine.

The fix: selectinload

from sqlalchemy.orm import selectinload

async def get_users_eager(session: AsyncSession):
    # Tell SQLAlchemy to fetch the 'team' relationship upfront
    stmt = select(User).options(selectinload(User.team))
    
    result = await session.execute(stmt)
    users = result.scalars().all()
    
    # Works instantly in memory. Total queries = 2.
    return [{"name": u.name, "team": u.team.name} for u in users]

Using .options(selectinload(...)) instructs SQLAlchemy to fetch all related teams in a single batch query right after fetching the users.

Key points

• selectinload is the default choice: It emits exactly 2 queries regardless of row count, safely loading one-to-many collections.
• joinedload vs selectinload: joinedload uses a SQL JOIN (1 query). selectinload uses an IN clause (2 queries). selectinload is generally safer because joinedload duplicates parent row data for every child row.

Common mistakes

• Forgetting to eager load in async: In sync Python, forgetting to eager load just makes your app slow (N+1). In async Python, it hard-crashes your app (MissingGreenlet).

Recall questions

• What does selectinload do under the hood?
• Why is lazy-loading relationships fatal in async SQLAlchemy?
• How many queries does selectinload execute to fetch 100 users and their teams?

Questions & answers

You're reviewing an async FastAPI endpoint that fetches a list of orders. The code loops through the orders and accesses `order.customer.email`. The author complains it's throwing MissingGreenlet. How do you fix it?

The customer relationship is being lazy-loaded inside the loop. Fix it by modifying the initial query to include .options(selectinload(Order.customer)) so the data is eagerly loaded into memory upfront.

When would you prefer selectinload over joinedload for eager loading?

When loading one-to-many or many-to-many collections. joinedload creates a Cartesian product (duplicating parent rows for every child row), which inflates memory usage over the network. selectinload cleanly separates them into two queries.

Continue learning

Previous: Sessions and expire_on_commit

Next: The N+1 Queries Problem

Return to Python Backend Roadmap