Eager Loading

This is the v1.0 documentation, for a release that is still in development. The API it describes is not on PyPI and can still change.

Reading about the version you have installed? Switch to v0.x.

The problem

posts = await Post.all().limit(50)
for post in posts:
    print(post.author.name)      # raises — the relation was never fetched

Sillo raises rather than quietly issuing a query. That is deliberate: an implicit query inside a loop is the N+1, and it is invisible until the table grows. Being made to ask for the relation is what makes the cost visible.

The fix is to say so in the query:

posts = await Post.all().limit(50).select_related("author")
for post in posts:
    print(post.author.name)      # already loaded

Two queries instead of fifty-one, and one of them was going to happen anyway.

select_related

await Post.all().select_related("author")
await Post.all().select_related("author", "category")
await Post.all().select_related("author__profile")

A join. The related row comes back in the same query, in the same result set.

Use it for forward relations. The ones where this table holds the foreign key:

• ForeignKeyField
• OneToOneField

It cannot be used for reverse relations or many-to-many, because those are one-to-many: a join would multiply the rows rather than widen them.

prefetch_related

await Post.all().prefetch_related("tags")
await Author.all().prefetch_related("posts")
await Post.all().prefetch_related("tags", "comments")

A second query per relation, matched up in Python.

Use it for the one-to-many directions:

• reverse foreign keys (author.posts)
• many-to-many (post.tags)

It works on forward relations too, at the cost of an extra query rather than a join, which is occasionally what you want when the joined row is wide and repeated.

Choosing

Relation	Use
Forward FK: post.author	select_related
One-to-one: user.profile	select_related
Reverse FK: author.posts	prefetch_related
Many-to-many: post.tags	prefetch_related

The rule underneath: one row on the other side, join; many rows, prefetch.

They compose:

await (
    Post.all()
    .select_related("author")        # one query, joined
    .prefetch_related("tags")        # a second query
)

Prefetch: filtering what is prefetched

from tortoise.query_utils import Prefetch

authors = await Author.all().prefetch_related(
    Prefetch(
        "posts",
        queryset=Post.filter(status="published").order_by("-created_at"),
    )
)

Each author’s posts now holds only their published posts, newest first.

Without this, prefetching loads every related row. For an author with ten thousand posts, prefetch_related("posts") fetches all ten thousand to render a list of five.

to_attr puts the result somewhere else, so the unfiltered relation stays available:

Prefetch(
    "posts",
    queryset=Post.filter(status="published"),
    to_attr="published_posts",
)

author.published_posts      # the filtered list

Limiting inside a Prefetch limits the total

A LIMIT in a prefetch queryset applies to the single combined query, not per parent. Prefetch("posts", queryset=Post.all().limit(5)) gives you five posts across all authors, not five each.

“The five most recent per author” is a window function. See raw SQL.

fetch_related

On an instance you already have:

post = await Post.get(id=1)
await post.fetch_related("author", "tags")

Fine for one object. Inside a loop it is the N+1. That is the same sequence of queries, written out. If you are calling it in a loop, the fix is prefetch_related on the query that produced the loop.

Nested

await Post.all().prefetch_related("comments__author")
await Author.all().prefetch_related("posts__tags")
await Comment.all().select_related("post__author")

Depth is fine. Breadth is what gets expensive. Each prefetched relation is another query, and each joined relation widens every row.

Diagnosing an N+1

The exception. Touching an unfetched relation raises, which catches most of these during development.

The query count. Log statements while you exercise a page:

DB_ECHO=true

A list page issuing 50-odd near-identical queries is the signature.

The plan. explain() for one query at a time.

In a generic list. Anything that renders a column per relation — an admin panel, a CSV export, a serializer walking _meta — is one query per row unless the queryset it was handed says otherwise:

queryset.select_related("author").prefetch_related("tags")

When not to eager load

Eager loading trades queries for data transferred. Both have a cost.

• A relation you might not use. Behind a conditional, is better fetched when the condition is true.
• A join that multiplies rows. Two prefetch_related calls are two clean queries; two joins over one-to-many relations would be a cross product.
• Deep chains on a large result set. Fifty posts × their comments × each comment’s author is a lot of objects for a page showing titles.

The honest check is explain() and the query count, not intuition.

Projections instead

Sometimes the relation is wanted for one column, and a projection is cheaper than loading the object at all:

await Post.all().values("title", "author__name")
# [{"title": "Hello", "author__name": "Ada"}, …]

One query, one join, a flat dict, and no related instances constructed. For a list you are about to serialise, this is often the best answer.