Relationships and Avoiding N+1 Queries

Lazy loading doesn't work implicitly with async sessions, and in sync code it causes N+1 queries. Load relationships explicitly with selectinload (a second IN query — best for collections) or joinedload (a JOIN — good for many-to-one). Set lazy="raise" on relationships to fail fast when something isn't eagerly loaded.

from sqlalchemy import select
from sqlalchemy.orm import joinedload, selectinload


async def orders_with_items(db: AsyncSession, user_id: int) -> list[Order]:
    stmt = (
        select(Order)
        .where(Order.user_id == user_id)
        .options(
            selectinload(Order.items).joinedload(OrderItem.product),   # collection + many-to-one
            joinedload(Order.user),
        )
    )
    return list(await db.scalars(stmt))


class Order(Base):
    # ...
    items: Mapped[list["OrderItem"]] = relationship(lazy="raise")   # fail fast if not eager-loaded

Common pitfalls

• MissingGreenlet errors from accessing unloaded relationships in async code.
• joinedload on large collections, multiplying rows.
• Serializing nested responses without loading their relationships.

Key points

• selectinload for collections
• joinedload for many-to-one
• lazy='raise' surfaces mistakes

Further reading: SQLAlchemy: Relationship loading