fastapi sqlalchemy n+1 query fix

Your FastAPI endpoint returns the right data, but it takes three seconds to respond and your database logs show hundreds of nearly identical queries. That’s the N+1 query problem, and it’s one of the sneakiest performance bugs in any SQLAlchemy-backed API.

from sqlalchemy.orm import selectinload

stmt = select(Author).options(selectinload(Author.books))
result = await session.execute(stmt)
authors = result.scalars().all()
# Now author.books is already loaded — no extra queries per author

Why This Happens

SQLAlchemy relationships are lazy by default. When you load an Author object, it doesn’t automatically fetch the related Book rows — it waits until you actually access author.books. That’s efficient in isolation, but it becomes a trap the moment you loop over a list of objects.

Here’s the pattern that triggers it in almost every FastAPI project:

# This code triggers the problem:
@app.get("/authors")
async def list_authors(session: AsyncSession = Depends(get_session)):
    result = await session.execute(select(Author))
    authors = result.scalars().all()

    return [
        {"name": a.name, "books": [b.title for b in a.books]}
        for a in authors
    ]

With 1 author, this runs 2 queries: one for the author list, one for that author’s books. With 200 authors, it runs 201 queries — one initial query plus one lazy-load query per author, executed one at a time. That’s the “N+1” in the name: 1 query to get the N rows, then N more queries to fill in the relationships.

Worse, in async FastAPI apps this often doesn’t just slow things down — it can crash the request entirely. Lazy loading triggers an implicit synchronous-style database call, and with AsyncSession that raises a MissingGreenlet error instead of silently working:

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

You’ll typically see this the moment you try to access a.books outside of an await-friendly context — for example, inside a Pydantic serializer or a list comprehension after the session has already moved on.

It’s worth being clear on why this is worse than it sounds. In a sync Flask-style app, an N+1 problem is “just” slow — the extra queries fire one after another and the request eventually finishes. In an async FastAPI app, that assumption breaks down. AsyncSession expects every database call to go through the event loop via await. Lazy loading tries to sneak a synchronous call in behind the scenes using a greenlet trick, and that trick only works in specific contexts (inside the session’s own methods, basically). The moment you access a lazy attribute from, say, a background task, a Pydantic model_validate call, or after await session.close() has already run, SQLAlchemy has no safe way to fetch the data — so it raises instead of silently blocking the event loop.

How to Spot It Before It Ships

You don’t need a profiler to catch most N+1 bugs — you just need to look at the query count. Two quick ways to do that:

Turn on SQL echo. Pass echo=True when creating your engine and watch the terminal while hitting the endpoint locally:

engine = create_async_engine(DATABASE_URL, echo=True)

If a request to /authors prints one SELECT for authors followed by twenty near-identical SELECT ... FROM books WHERE books.author_id = ? statements, you’ve found your N+1.

Count queries in a test. SQLAlchemy exposes an event hook you can use to assert on query counts directly, which is far more reliable than eyeballing logs:

from sqlalchemy import event

query_count = 0

@event.listens_for(engine.sync_engine, "before_cursor_execute")
def count_queries(*args, **kwargs):
    global query_count
    query_count += 1

# ... call your endpoint or service function ...
assert query_count < 5  # fails loudly if a new N+1 sneaks in

Wiring a version of this into your test suite means a future code change that reintroduces lazy loading in a loop fails CI instead of showing up as a slow endpoint three weeks later.

Solutions by Scenario

Scenario 1: You need the relationship every time

If your endpoint always needs author.books, load it eagerly in the same query using selectinload(). This issues one extra query total (not per row) that fetches all related books in a single WHERE book.author_id IN (...) statement.

from sqlalchemy.orm import selectinload
from sqlalchemy import select

@app.get("/authors")
async def list_authors(session: AsyncSession = Depends(get_session)):
    stmt = select(Author).options(selectinload(Author.books))
    result = await session.execute(stmt)
    authors = result.scalars().all()

    return [
        {"name": a.name, "books": [b.title for b in a.books]}
        for a in authors
    ]

Now you get exactly 2 queries no matter how many authors there are: one for authors, one for all their books combined. That’s the difference between a request that scales linearly with your dataset and one that doesn’t.

Scenario 2: Nested or deeply related data

For relationships that go multiple levels deep — say Author → Book → Review — chain the loader options instead of nesting separate queries:

stmt = (
    select(Author)
    .options(
        selectinload(Author.books).selectinload(Book.reviews)
    )
)
result = await session.execute(stmt)
authors = result.scalars().all()

This still results in a small, fixed number of queries (one per level), rather than one per author, per book, per review — which is where N+1 problems get genuinely painful in production.

Scenario 3: joinedload vs selectinload — which one?

Both eagerly load relationships, but they generate different SQL, and picking the wrong one can hurt as much as it helps.

Approach 1: Using joinedload

from sqlalchemy.orm import joinedload

stmt = select(Author).options(joinedload(Author.books))

This uses a single LEFT OUTER JOIN. It’s efficient for one-to-one or many-to-one relationships, but for one-to-many relationships (like Author.books), it duplicates the parent row for every child row returned, which can bloat the result set if an author has hundreds of books.

Approach 2: Using selectinload

from sqlalchemy.orm import selectinload

stmt = select(Author).options(selectinload(Author.books))

This issues a second, separate SELECT ... WHERE author_id IN (...) query instead of a join. No row duplication, and it scales better for one-to-many and many-to-many relationships.

Which to choose? Default to selectinload for collections (one-to-many, many-to-many). Reach for joinedload when you’re loading a single related object (many-to-one, one-to-one) and want it in the same round trip.

Scenario 4: You only need the relationship sometimes

Don’t eagerly load a relationship on every request if only one endpoint actually needs it — that just trades one performance problem for another (over-fetching). Instead, apply the loader option only where it’s needed, and keep the default lazy behavior everywhere else:

# Endpoint that needs books — load eagerly
@app.get("/authors/with-books")
async def authors_with_books(session: AsyncSession = Depends(get_session)):
    stmt = select(Author).options(selectinload(Author.books))
    result = await session.execute(stmt)
    return result.scalars().all()

# Endpoint that doesn't — leave it lazy, no extra query cost
@app.get("/authors")
async def authors_only(session: AsyncSession = Depends(get_session)):
    result = await session.execute(select(Author))
    return result.scalars().all()

If you want lazy loading to be impossible to trigger by accident in async code (so it fails loudly in tests instead of slowly in production), set lazy="raise_on_sql" on the relationship definition itself:

class Author(Base):
    __tablename__ = "authors"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    books: Mapped[list["Book"]] = relationship(lazy="raise_on_sql")

Any code path that forgets to eager-load books will now raise immediately during development rather than quietly issuing hundreds of queries in production.

Prevention Tips

A few habits will save you from re-discovering this bug every few months:

• Turn on SQL echo in development. Set create_async_engine(url, echo=True) locally and actually read the query log for a few minutes. If you see the same SELECT ... WHERE author_id = ? repeated dozens of times, you’ve found an N+1.
• Never access relationships inside Pydantic response serialization without eager loading first. By the time FastAPI serializes your response model, the session may have already closed, and any lazy access will fail or, worse, silently detach.
• Use lazy="raise_on_sql" in staging/test environments. It turns a slow, silent problem into an immediate, loud one — much easier to catch in code review than in a production incident.
• Profile with realistic data volumes. N+1 bugs are invisible with 3 test rows and catastrophic with 3,000 real ones. Seed your dev database with enough rows to make the pattern visible.

If you’re chasing down where these extra queries are actually coming from, pasting the resulting SQLAlchemy traceback or slow query log into Debugly’s trace formatter makes it much easier to spot the repeated query pattern buried in a wall of log output.

Wrap-Up

N+1 queries are easy to write and easy to miss in code review, because the code itself looks completely correct — it’s only the query count that gives it away. The fix is almost always the same shape: identify the relationship being accessed in a loop, and load it eagerly with selectinload() (or joinedload() for single related objects) in the original query.

If you’re also dealing with connection pool errors alongside slow queries, check out our guide on fixing SQLAlchemy QueuePool exhaustion in FastAPI — the two problems often show up together, since N+1 queries hold connections open far longer than they should. And if you’re newer to async session handling in general, our Flask SQLAlchemy session guide covers the fundamentals that carry over directly to FastAPI’s async sessions.