Relationships and `AsyncSession`: why async changes the rules

Capsule overview

So far we've seen joinedload and selectinload with a hidden detail: all the code assumes AsyncSession. But we didn't dig into what changes with async vs sync. And a lot changes.

In sync SQLAlchemy, this code works:

# Sync (NOT what we use in FastAPI)
author = session.get(Author, 42)
print(author.books)  # ← lazy load: fires a SQL query right now

In async SQLAlchemy, the same pattern raises an exception:

# Async
author = await session.get(Author, 42)
print(author.books)  # ← MissingGreenlet error

Why? Because accessing author.books would require firing a synchronous SQL query from async code. SQLAlchemy refuses — explicitly — to do it, because that would block the event loop.

This capsule teaches you:

• Why direct lazy loading doesn't work in async (the technical problem).
• How to load relationships in async: the three alternatives (selectinload/joinedload from the query, awaitable_attrs, manual refresh).
• Specific errors you'll see: MissingGreenlet, DetachedInstanceError, IO operation on closed transaction.
• Canonical patterns of FastAPI + AsyncSession + relationships in real endpoints.

By the end, you'll be able to structure async endpoints that load relationships without falling into cryptic errors, and you'll understand why selectinload is even more important in async than in sync.

Mental model: the event loop doesn't allow blocking SQL in the middle of an await

In FastAPI with asyncpg, your request enters an event loop. Each await is a point where the event loop can run other tasks (other requests, concurrent IO). The async def functions run cooperatively.

Sync lazy loading breaks this model:

When you do author.books in sync SQLAlchemy, the ORM internally:

1. Detects that books isn't loaded.
2. Fires a SQL query.
3. Waits blocking for the response (sync, without await).
4. Builds the book objects.
5. Returns.

Step 3 blocks the thread. In sync there's no problem. In async it would block the entire event loop — all the other requests would freeze until the query finishes.

That's why SQLAlchemy 2.0 with AsyncSession simply refuses to do it. It raises MissingGreenlet with a message like:

sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called;
can't call await_only() here. Was IO attempted in an unexpected place?

(The message is cryptic — you'll see it many times in your career.)

The three options for loading relationships in async

Your async code has three legitimate ways to load relationships:

1. Eager loading from the query (recommended, 90% of cases). selectinload, joinedload. You'll use this whenever you know in advance which relationships you want.

2. awaitable_attrs to load on demand with await. Useful when you don't know in advance whether you'll need the relationship.

3. Refresh the object manually with await session.refresh(obj, ['books']). Useful for special cases.

None of them is "direct lazy loading like sync". Async is explicit by design.

The error that will haunt you: MissingGreenlet

Before explaining the solutions, let's get to know the error.

Minimal reproduction

from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy import select
from app.models import Author, Book

engine = create_async_engine("postgresql+asyncpg://...")
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)

async def buggy_endpoint():
    async with SessionLocal() as session:
        author = await session.get(Author, 42)
        # ↓ This access to the lazy relationship RAISES an exception
        for book in author.books:
            print(book.title)

Output:

sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called;
can't call await_only() here. Was IO attempted in an unexpected place?
(Background on this error at: https://sqlalche.me/e/20/xd2s)

Reading the error:

• "greenlet_spawn has not been called" — SQLAlchemy tries to run synchronous IO but there's no greenlet prepared to bridge it to async.
• "Was IO attempted in an unexpected place?" — yes, you attempted it when accessing author.books after the parent query finished.

Why it happens: after the session.get(Author, 42) query finished, the author.books relationship isn't loaded (lazy default). When you try to iterate it, SQLAlchemy would want to fire a query, but it's in async without a greenlet context.

Related sub-error: DetachedInstanceError

async def another_buggy():
    async with SessionLocal() as session:
        author = await session.scalar(select(Author).where(Author.id == 42))
    # session closed here
    print(author.name)        # ✅ Works — name was already loaded
    print(author.books)       # ❌ DetachedInstanceError — session closed, can't load lazy

DetachedInstanceError occurs when you try to access a lazy relationship on an object whose session has already closed. It's similar to MissingGreenlet but for a different reason.

How to distinguish the two errors:

• MissingGreenlet: you're inside the session, but the lazy access can't fire async IO correctly.
• DetachedInstanceError: the session closed, there's no way to load anything more.

Both errors are prevented with the same strategy: load eagerly from the start.

Solution 1: eager loading from the query (the canonical pattern)

90% of your endpoints will use this pattern. You load everything you need explicitly when making the main query.

Canonical FastAPI pattern

from fastapi import FastAPI, Depends
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload, joinedload

from app.db import get_db
from app.models import Author, Book

app = FastAPI()


@app.get("/authors/{author_id}/full")
async def get_author_full(
    author_id: int,
    session: AsyncSession = Depends(get_db),
):
    stmt = (
        select(Author)
        .options(
            selectinload(Author.books).selectinload(Book.reviews),
        )
        .where(Author.id == author_id)
    )
    author = await session.scalar(stmt)
    if not author:
        raise HTTPException(404, "Author not found")

    # Here you can access author.books, author.books[0].reviews, etc.
    # No MissingGreenlet, no DetachedInstanceError.
    return {
        "name": author.name,
        "books": [
            {
                "title": book.title,
                "review_count": len(book.reviews),
            }
            for book in author.books
        ],
    }

Why it works

• The query with selectinload loads author.books and book.reviews while the session is active and inside the async context.
• Afterward, the accesses to author.books and book.reviews only read already-loaded data — they don't fire IO.
• When the endpoint returns and the session closes (via the Depends), the data is in the Python objects, not in the session. It can be serialized without problems.

Correct session setup

# app/db.py
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://bookstore:bookstore@localhost:5432/bookstore",
    echo=False,
)

# expire_on_commit=False is KEY for async
SessionLocal = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
)


async def get_db() -> AsyncSession:
    async with SessionLocal() as session:
        yield session

Why expire_on_commit=False: after a commit, SQLAlchemy "expires" objects by default — it marks them as invalid, so any access forces a re-query. In sync this is fine (the re-query is simple). In async, the re-query is a lazy load, which we already know fails. Always expire_on_commit=False with AsyncSession.

Solution 2: awaitable_attrs for explicit lazy access

There are cases where you don't know in advance which relationships you'll need. Example:

async def lazy_decision(author_id: int, session: AsyncSession):
    author = await session.get(Author, author_id)

    # A decision that depends on the author:
    if author.is_premium:
        # Load books on demand
        books = await author.awaitable_attrs.books
        return {"author": author.name, "books": [b.title for b in books]}
    else:
        return {"author": author.name}

author.awaitable_attrs.books is the async version of lazy access. It returns an Awaitable that you await explicitly. That tells the event loop "now I'm going to do IO, wait for me".

Key differences vs selectinload from the query:

• selectinload: you always load, predictable, efficient.
• awaitable_attrs: you load only if you enter the branch that needs it. More flexible, but easier to generate an N+1 if you use it inside a loop.

Safe pattern vs anti-pattern

✅ Safe pattern (1 author, conditional decision):

author = await session.get(Author, 42)
if condition:
    books = await author.awaitable_attrs.books

One query if you enter the branch, zero if not. Predictable.

❌ Anti-pattern (loop with awaitable_attrs):

authors = (await session.scalars(select(Author).limit(50))).all()
for author in authors:
    books = await author.awaitable_attrs.books  # ← N queries: N+1

This is an N+1 disguised as async. nplusone may or may not detect it (depends on the version); ideally you refactor it to selectinload from the query.

When awaitable_attrs makes sense

• Conditional endpoint: you load books only if the user is premium.
• Explicit lazy loading in a batch script: you process one author at a time, not in a loop.
• Tests: you want to simulate lazy access without paying the cost of eager loading in setup.

In the day-to-day of FastAPI, selectinload from the query covers 95% of your cases. Reserve awaitable_attrs for the cases where the decision really depends on runtime.

Solution 3: await session.refresh(obj, [relationships])

For cases where you have an already-loaded object and need to add relationships to it afterward:

author = await session.get(Author, 42)

# After some logic...
await session.refresh(author, ["books", "awards"])

# Now author.books and author.awards are loaded
for book in author.books:
    print(book.title)

session.refresh(obj, attrs) runs a query (or several) to load the specified relationships, and modifies the object in-place.

When to use it

• After a commit: you need to reload relationships that changed.
• After a bulk operation: you inserted a bunch of rows and want to access their relationships.
• Cases where you can't modify the original query (example: an external library that returns the query).

When NOT to use it

• If you can add selectinload to the original query, do it. Cleaner.
• If you want to load all the relationships, selectinload from the query is more efficient (it uses the original query's context).

Canonical patterns in FastAPI

Three patterns you'll repeat in every serious endpoint.

Pattern A: endpoint with a response model and complete eager loading

from pydantic import BaseModel

class BookResponse(BaseModel):
    id: int
    title: str
    review_count: int

class AuthorResponse(BaseModel):
    id: int
    name: str
    books: list[BookResponse]


@app.get("/authors/{author_id}", response_model=AuthorResponse)
async def get_author(author_id: int, session: AsyncSession = Depends(get_db)):
    stmt = (
        select(Author)
        .options(selectinload(Author.books).selectinload(Book.reviews))
        .where(Author.id == author_id)
    )
    author = await session.scalar(stmt)
    if not author:
        raise HTTPException(404)

    return AuthorResponse(
        id=author.id,
        name=author.name,
        books=[
            BookResponse(
                id=b.id,
                title=b.title,
                review_count=len(b.reviews),
            )
            for b in author.books
        ],
    )

Characteristics:

• Nested selectinload to load everything the response_model needs.
• Manual construction of the response (not model_validate(author) directly, because the response_model and the SQLAlchemy model are different).
• Zero access to relationships post-session.

Pattern B: endpoint with pagination and eager loading

from fastapi import Query

@app.get("/authors")
async def list_authors(
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
    session: AsyncSession = Depends(get_db),
):
    offset = (page - 1) * page_size

    stmt = (
        select(Author)
        .options(selectinload(Author.books))
        .order_by(Author.name)
        .limit(page_size)
        .offset(offset)
    )
    authors = (await session.scalars(stmt)).all()

    return {
        "page": page,
        "items": [
            {"id": a.id, "name": a.name, "book_count": len(a.books)}
            for a in authors
        ],
    }

Why selectinload (not joinedload): the LIMIT is on the parent. joinedload with LIMIT behaves oddly (LIMIT of the JOIN, not of the parent). selectinload applies LIMIT correctly to the parent and loads books afterward.

Pattern C: endpoint with conditional loading

@app.get("/authors/{author_id}")
async def get_author(
    author_id: int,
    include_books: bool = False,
    include_reviews: bool = False,
    session: AsyncSession = Depends(get_db),
):
    options = []
    if include_books:
        if include_reviews:
            options.append(selectinload(Author.books).selectinload(Book.reviews))
        else:
            options.append(selectinload(Author.books))

    stmt = select(Author).options(*options).where(Author.id == author_id)
    author = await session.scalar(stmt)
    if not author:
        raise HTTPException(404)

    response = {"id": author.id, "name": author.name}
    if include_books:
        response["books"] = [
            {
                "title": b.title,
                **({"reviews": [r.comment for r in b.reviews]} if include_reviews else {}),
            }
            for b in author.books
        ]
    return response

Characteristics:

• Loading options built according to the request's parameters.
• Access to relationships ONLY if they were loaded (conditional eager loading consistent with the response).
• No awaitable_attrs — everything predictable when building the query.

Engine configuration for async

Minimal production setup:

from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://user:pass@host:5432/db",

    # Basic pool (module 6 goes deeper)
    pool_size=20,
    max_overflow=10,
    pool_pre_ping=True,
    pool_recycle=3600,

    # Echo for dev (off for production)
    echo=False,

    # For PgBouncer transaction mode (module 6)
    # connect_args={"server_settings": {"application_name": "my-app"}},
    # connect_args={"prepared_statement_cache_size": 0},
)

SessionLocal = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,  # CRITICAL for async
    autoflush=False,         # Recommended for async
)

Canonical dependency

from typing import AsyncIterator

async def get_db() -> AsyncIterator[AsyncSession]:
    async with SessionLocal() as session:
        try:
            yield session
            await session.commit()
        except Exception:
            await session.rollback()
            raise

Explicit variant: await session.commit() at the end of the request. If your endpoint only reads, it can cause unnecessary overhead (an empty commit). A more minimal version:

async def get_db() -> AsyncIterator[AsyncSession]:
    async with SessionLocal() as session:
        yield session

No explicit commit. The context manager rolls back on exit if there's an unhandled exception. Suitable for read-only endpoints or when you do an explicit commit in each endpoint that writes.

Why this matters in real work

1. MissingGreenlet is the #1 cause of incomprehensible stack traces in FastAPI + SQLAlchemy.

Without understanding why async doesn't allow direct lazy loading, you'll stumble on this error for weeks. With the mental model clear, you diagnose it in 30 seconds: "ah, I accessed a relationship I didn't load eagerly".

2. The sync → async transition breaks assumptions.

If your team migrates from sync psycopg2 to asyncpg + AsyncSession, code that worked will break. Knowing that lazy loading becomes impossible lets you anticipate and refactor before the cutover.

3. Eager loading in async isn't optional — it's mandatory.

In sync you can tolerate lazy loading "because at least it works even if it's slow". In async it doesn't even work. That forces discipline: you'll think about loading strategy in every new endpoint, instead of adding it as an afterthought.

4. Real performance goes hand in hand with correctness.

The eager-loading patterns you learned in capsules 04-05 are the ones you already have to apply in async. It's not an optional optimization — it's the correct way to write the code.

Traps and common mistakes

Mistake 1 (conceptual): assuming await in session.get loads everything

Symptom: "I did await session.get(Author, 42), that should bring the author with everything, right?"

Why it's wrong: session.get brings only the Author's columns. The relationships remain lazy. The await is for the query, not for the complete loading of the graph.

How to distinguish: enable echo=True. session.get(Author, 42) shows a single query: SELECT * FROM authors WHERE id = 42. The relationships require additional queries.

How to fix it: if you know you'll access relationships, use select(Author).options(selectinload(...)).where(Author.id == 42) and await session.scalar(stmt).

Mistake 2 (practical): expire_on_commit=True (default) in async

Symptom: "After a commit, my objects raise MissingGreenlet when accessing attributes."

Why it happens: the default expire_on_commit=True invalidates the objects after the commit. SQLAlchemy tries to refresh them lazily on access — and that's lazy loading, forbidden in async.

How to fix it: in the async_sessionmaker, set expire_on_commit=False. It's the universal practice with AsyncSession.

Mistake 3 (conceptual): awaitable_attrs in loops

Symptom: "I used awaitable_attrs and my endpoint has 50 queries."

Why it happens: inside a loop, each await x.awaitable_attrs.relationship is a separate query. It's an explicit N+1.

How to distinguish: if your code has for x in list: await x.awaitable_attrs.something, it's N+1.

How to fix it: move the loading to the original select(...).options(selectinload(...)). It's the canonical pattern.

Mistake 4 (practical): accessing relationships outside the session

Symptom: DetachedInstanceError: Instance <Author at 0x...> is not bound to a Session.

Why it happens: the session already closed (out of scope), but your code keeps trying to access the object's relationships.

How to fix it: if you need the data after closing the session, load it eagerly and serialize it to a dict/Pydantic model inside the context manager. After exiting, handle dicts/Pydantic, not SQLAlchemy objects.

# ✅ Correct
async def get_data(author_id: int):
    async with SessionLocal() as session:
        stmt = select(Author).options(selectinload(Author.books)).where(Author.id == author_id)
        author = await session.scalar(stmt)
        if not author:
            return None
        # Convert to dict BEFORE closing the session
        result = {
            "name": author.name,
            "books": [b.title for b in author.books],
        }
    # Session closed here
    return result  # dict, not a SQLAlchemy object

Mistake 5 (conceptual): mixing sync session and async session

Symptom: "I have Session in some places and AsyncSession in others, I don't understand what's happening."

Why it's problematic: the two are not interchangeable. Different methods (session.execute vs await session.execute), different lazy behaviors. Mixing them leads to subtle bugs.

How to fix it: in a modern FastAPI app, use AsyncSession for everything. If you need sync (rare), isolate it clearly. Don't mix.

Mistake 6 (practical): connection_args with asyncpg and PgBouncer

Symptom: "My app works locally but in production with PgBouncer in transaction mode, it fails with prepared statement does not exist."

Why it happens: asyncpg caches prepared statements, and PgBouncer in transaction mode rotates physical connections — the statement cached on one connection doesn't exist on another.

How to fix it: disable the prepared statement cache:

engine = create_async_engine(
    "postgresql+asyncpg://...",
    connect_args={
        "prepared_statement_cache_size": 0,
        "statement_cache_size": 0,
    },
)

This is module 6 in depth, but it's relevant to mention: the async + PgBouncer setup requires that configuration.

Exercises

Exercise 1: reproduce and diagnose MissingGreenlet

Write an endpoint that triggers MissingGreenlet. Then fix it with selectinload. Document both versions with SQL captured from echo=True.

@app.get("/authors/{author_id}/books-broken")
async def authors_books_broken(
    author_id: int,
    session: AsyncSession = Depends(get_db),
):
    author = await session.get(Author, author_id)
    if not author:
        raise HTTPException(404)
    # ↓ MissingGreenlet here
    return {
        "name": author.name,
        "book_count": len(author.books),
    }

500 Internal Server Error
sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called;
can't call await_only() here.

SELECT authors.id, authors.name FROM authors WHERE authors.id = 42

from sqlalchemy.orm import selectinload

@app.get("/authors/{author_id}/books-fixed")
async def authors_books_fixed(
    author_id: int,
    session: AsyncSession = Depends(get_db),
):
    stmt = (
        select(Author)
        .options(selectinload(Author.books))
        .where(Author.id == author_id)
    )
    author = await session.scalar(stmt)
    if not author:
        raise HTTPException(404)
    return {
        "name": author.name,
        "book_count": len(author.books),
    }

SELECT authors.id, authors.name FROM authors WHERE authors.id = 42
SELECT books.id, books.title, books.author_id FROM books WHERE books.author_id IN (42)

Exercise 2: use awaitable_attrs correctly

Write an endpoint where the decision to load Author.awards depends on a query parameter include_awards: bool. Use awaitable_attrs to load on demand only if include_awards=True. Justify why this case is legitimate for awaitable_attrs and doesn't require selectinload.

@app.get("/authors/{author_id}")
async def get_author_optional_awards(
    author_id: int,
    include_awards: bool = False,
    session: AsyncSession = Depends(get_db),
):
    author = await session.get(Author, author_id)
    if not author:
        raise HTTPException(404)

    response = {"name": author.name}

    if include_awards:
        # Load awards only if the client asks for them
        awards = await author.awaitable_attrs.awards
        response["awards"] = [{"name": a.name, "year": a.year} for a in awards]

    return response

options = []
if include_awards:
    options.append(selectinload(Author.awards))

stmt = select(Author).options(*options).where(Author.id == author_id)
author = await session.scalar(stmt)

Exercise 3: fix DetachedInstanceError

This code fails with DetachedInstanceError. Diagnose and fix it.

@app.get("/authors/{author_id}/summary")
async def author_summary(author_id: int):
    async with SessionLocal() as session:
        author = await session.scalar(
            select(Author).where(Author.id == author_id)
        )

    # session closed here
    return {
        "name": author.name,
        "book_count": len(author.books),  # ← DetachedInstanceError
    }

@app.get("/authors/{author_id}/summary")
async def author_summary(author_id: int):
    async with SessionLocal() as session:
        stmt = (
            select(Author)
            .options(selectinload(Author.books))  # ← load eagerly
            .where(Author.id == author_id)
        )
        author = await session.scalar(stmt)

        if not author:
            raise HTTPException(404)

        # Build the response INSIDE the context manager
        result = {
            "name": author.name,
            "book_count": len(author.books),
        }
    # Session closed here. result is a pure dict, no SQLAlchemy objects.
    return result

@app.get("/authors/{author_id}/summary")
async def author_summary(
    author_id: int,
    session: AsyncSession = Depends(get_db),
):
    stmt = (
        select(Author)
        .options(selectinload(Author.books))
        .where(Author.id == author_id)
    )
    author = await session.scalar(stmt)
    if not author:
        raise HTTPException(404)
    return {
        "name": author.name,
        "book_count": len(author.books),
    }

Exercise 4: refactor an endpoint with a hidden N+1 in async

This endpoint uses awaitable_attrs inside a loop, generating an N+1. Refactor it to a single query with eager loading.

@app.get("/users-with-totals")
async def users_with_totals(session: AsyncSession = Depends(get_db)):
    users = (await session.scalars(select(User).where(User.active == True))).all()

    result = []
    for user in users:
        orders = await user.awaitable_attrs.orders  # ← N queries
        total = sum(o.amount for o in orders)
        result.append({"user": user.name, "total": total})

    return result

from sqlalchemy.orm import selectinload

@app.get("/users-with-totals")
async def users_with_totals(session: AsyncSession = Depends(get_db)):
    stmt = (
        select(User)
        .options(selectinload(User.orders))
        .where(User.active == True)
    )
    users = (await session.scalars(stmt)).all()

    return [
        {
            "user": user.name,
            "total": sum(o.amount for o in user.orders),
        }
        for user in users
    ]

SELECT users.id, users.name FROM users WHERE users.active = true
SELECT orders.id, orders.user_id, orders.amount FROM orders WHERE orders.user_id IN (1, 2, 3, ..., 100)

Exercise 5: configure async_sessionmaker correctly

Your colleague shows you this configuration and says "everything works, why would you change anything?":

engine = create_async_engine("postgresql+asyncpg://...")
SessionLocal = async_sessionmaker(engine)  # default config

What would you suggest changing and why?

SessionLocal = async_sessionmaker(
    engine,
    expire_on_commit=False,  # ← add this
)

SessionLocal = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
)

SessionLocal = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
    autoflush=False,
)

engine = create_async_engine(
    "postgresql+asyncpg://...",
    pool_size=20,
    max_overflow=10,
    pool_pre_ping=True,    # health check before using a connection
    pool_recycle=3600,     # recycle connections every 1h
)

engine = create_async_engine(
    "postgresql+asyncpg://bookstore:bookstore@localhost:5432/bookstore",
    pool_size=20,
    max_overflow=10,
    pool_pre_ping=True,
    pool_recycle=3600,
    echo=False,
)

SessionLocal = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
    autoflush=False,
)

Exercise 6: distinguish MissingGreenlet from DetachedInstanceError

For each snippet, predict which error it raises (if any):

Snippet A:

async def a(session: AsyncSession):
    author = await session.get(Author, 42)
    print(author.books)  # ← ?

Snippet B:

async def b():
    async with SessionLocal() as session:
        author = await session.scalar(select(Author).where(Author.id == 42))
    print(author.name)  # ← ?

Snippet C:

async def c():
    async with SessionLocal() as session:
        author = await session.scalar(select(Author).where(Author.id == 42))
    print(author.books)  # ← ?

Snippet D:

async def d(session: AsyncSession):
    stmt = select(Author).options(selectinload(Author.books)).where(Author.id == 42)
    author = await session.scalar(stmt)
    print(author.books)  # ← ?

Summary and next step

In this capsule you learned:

• Direct lazy loading doesn't work in async. Accessing author.books raises MissingGreenlet because the ORM can't fire sync IO from async code.
• Three ways to load relationships in async: eager from the query (selectinload/joinedload), awaitable_attrs for explicit lazy access, session.refresh(obj, attrs) for special cases.
• expire_on_commit=False is mandatory in async_sessionmaker. Without it, any post-commit access causes MissingGreenlet.
• DetachedInstanceError: occurs when you access relationships outside the session. Solution: load eagerly + serialize inside the context.
• Canonical FastAPI patterns: endpoint with eager loading, pagination with selectinload (not joinedload), conditional loading with dynamic options.
• MissingGreenlet is the #1 error to understand when working with FastAPI + AsyncSession.

Before moving on you should be able to:

• Diagnose MissingGreenlet and DetachedInstanceError in existing code.
• Configure async_sessionmaker correctly (expire_on_commit=False, etc.).
• Structure a FastAPI endpoint with correct eager loading.
• Decide between selectinload from the query and awaitable_attrs depending on the case.

Next capsule — Eager loading anti-patterns. You already know how to load relationships eagerly. But loading EVERYTHING eagerly has its own problem: over-fetching. If you list 1,000 authors just to show names and selectinload(Author.books) loads 50,000 books you never read, you wasted IO and memory. Capsule 07 teaches you the anti-patterns: unnecessary eager, dangerous selectinload(*), and how raiseload forces you to be explicit in production so an accidental lazy access fails instead of blowing up silently.

Resources

1. SQLAlchemy 2.0 — Asynchronous I/O (asyncio) — the complete official reference for SQLAlchemy async.
2. SQLAlchemy 2.0 — awaitable_attrs — the specific section on how to load relationships explicitly in async.
3. SQLAlchemy Error Code: xd2s (MissingGreenlet) — the official page for the error with an explanation of the problem.
4. Mike Bayer — "Asynchronous I/O in SQLAlchemy" (PyCon 2020) — the creator of SQLAlchemy explaining the async model, greenlets, and why direct lazy loading doesn't work.
5. FastAPI — SQL (Relational) Databases — the official FastAPI guide with SQLAlchemy.
6. Pydantic v2 — model_validate — to serialize SQLAlchemy objects to Pydantic models in async endpoints.
7. asyncpg Documentation — Prepared Statements — to understand how asyncpg handles prepared statements (relevant for module 6 with PgBouncer).

Module 4 — Database Performance & Query Tuning Guide