How SQLAlchemy loads relationships

When you define a relationship in a SQLAlchemy model, you also define how that relationship loads data. The default behavior is lazy="select", which means: do not load the related data until someone accesses the attribute, and when they do, fire a separate SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more query.

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

    # Default: lazy="select" (the N+1 trigger)
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)

    author: Mapped["User"] = relationship(back_populates="posts")
    comments: Mapped[list["Comment"]] = relationship(back_populates="post")

class Comment(Base):
    __tablename__ = "comments"
    id: Mapped[int] = mapped_column(primary_key=True)
    content: Mapped[str] = mapped_column(Text)
    post_id: Mapped[int] = mapped_column(ForeignKey("posts.id"), index=True)

    post: Mapped["Post"] = relationship(back_populates="comments")

Nothing in this model definition looks dangerous. The problem emerges when you query.

The N+1 queryWhat is n+1 query?A performance bug where fetching a list triggers one extra database query per item instead of loading all related data in a single query. Ask AI for more problem

Here is a typical AI-generated endpointWhat is endpoint?A specific URL path on a server that handles a particular type of request, like GET /api/users. Ask AI for more:

@app.get("/users-with-posts")
def get_users_with_posts(db: Session = Depends(get_db)):
    users = db.scalars(select(User).limit(20)).all()
    result = []
    for user in users:
        result.append({
            "name": user.name,
            "posts": [{"title": p.title} for p in user.posts]  # TRIGGER
        })
    return result

The code reads naturally. It loads 20 users, loops through them, and includes each user's posts. But here is what SQLAlchemy actually sends to PostgreSQL:

-- Query 1: load the users
SELECT id, name FROM users LIMIT 20;

-- Query 2: load posts for user 1
SELECT id, title, user_id FROM posts WHERE user_id = 1;

-- Query 3: load posts for user 2
SELECT id, title, user_id FROM posts WHERE user_id = 2;

-- Query 4: load posts for user 3
SELECT id, title, user_id FROM posts WHERE user_id = 3;

-- ... 17 more queries ...

-- Query 21: load posts for user 20
SELECT id, title, user_id FROM posts WHERE user_id = 20;

One query for the users, plus one query per user for their posts. With 20 users, that is 21 queries. With 100 users, it is 101 queries. The name "N+1" comes from this arithmetic: 1 initial query + N follow-up queries.

Each individual query might take 5ms. But 21 queries at 5ms each is 105ms, and that is on a local database. Over a network to a managed database (AWS RDS, Neon, Supabase), each query takes 20-50ms due to network latencyWhat is latency?The time delay between sending a request and receiving the first byte of the response, usually measured in milliseconds. Ask AI for more. Suddenly your endpoint takes 400ms to 1 second, for just 20 users.

Visualizing the problem

At 18 seconds per request, your APIWhat is api?A set of rules that lets one program talk to another, usually over the internet, by sending requests and getting responses. Ask AI for more is unusable. And the data set is only 100 users.

Fixing N+1 with eager loadingWhat is eager loading?Fetching related records in the same query as the parent records, rather than lazily fetching them one by one inside a loop. Ask AI for more

SQLAlchemy provides loading strategies that replace the N+1 pattern with 1-2 optimized queries.

joinedload(): one query with JOINWhat is join?A SQL operation that combines rows from two or more tables based on a shared column, letting you query related data in one request. Ask AI for more

from sqlalchemy.orm import joinedload

@app.get("/users-with-posts")
def get_users_with_posts(db: Session = Depends(get_db)):
    stmt = select(User).options(joinedload(User.posts)).limit(20)
    users = db.scalars(stmt).unique().all()
    result = []
    for user in users:
        result.append({
            "name": user.name,
            "posts": [{"title": p.title} for p in user.posts]
        })
    return result

Now SQLAlchemy generates a single query:

SELECT users.id, users.name, posts.id, posts.title, posts.user_id
FROM users
LEFT OUTER JOIN posts ON users.id = posts.user_id
LIMIT 20;

One query. All the data. The .unique() call is required because the JOIN creates duplicate user rows (one per post), and unique() deduplicates them back into proper User objects.

When to use joinedload(): Single relationships where the related data is small. Loading a user and their profile (one-to-one) or a post and its author (many-to-one).

When to avoid it: One-to-many relationships with lots of related data. If each user has 50 posts, the JOIN result has 1,000 rows for 20 users (20 * 50). The database sends the user's data 50 times, once per post row. This wastes bandwidthWhat is bandwidth?How much data can flow through a connection at once - like the number of lanes on a highway rather than the speed limit. Ask AI for more and memory.

selectinload(): two efficient queries

from sqlalchemy.orm import selectinload

@app.get("/users-with-posts")
def get_users_with_posts(db: Session = Depends(get_db)):
    stmt = select(User).options(selectinload(User.posts)).limit(20)
    users = db.scalars(stmt).all()
    result = []
    for user in users:
        result.append({
            "name": user.name,
            "posts": [{"title": p.title} for p in user.posts]
        })
    return result

SQLAlchemy generates two queries:

-- Query 1: load the users
SELECT id, name FROM users LIMIT 20;

-- Query 2: load ALL posts for those users in one go
SELECT id, title, user_id FROM posts
WHERE user_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20);

Two queries instead of 21. The IN (...) clause fetches all related posts in a single round trip. SQLAlchemy then matches them to the correct users in memory.

When to use selectinload(): One-to-many relationships. It is the default best choice for loading collections (user's posts, post's comments, order's items).

Chaining strategies for nested relationships

The real power shows when you load multiple levels of relationships:

# Load users -> their posts -> each post's comments
stmt = (
    select(User)
    .options(
        selectinload(User.posts)
        .selectinload(Post.comments)
    )
    .limit(20)
)

This generates three queries:

-- Query 1: users
SELECT id, name FROM users LIMIT 20;

-- Query 2: posts for those users
SELECT id, title, user_id FROM posts WHERE user_id IN (1, 2, ... 20);

-- Query 3: comments for those posts
SELECT id, content, post_id FROM comments WHERE post_id IN (101, 102, ... 180);

Three queries total, regardless of how many users, posts, or comments exist. Compare that to the N+1 version: 1 (users) + 20 (posts per user) + 20 * N (comments per post). With 5 comments per post on average, that is 121 queries reduced to 3.

Detecting N+1 with echo=True

The fastest way to catch N+1 in AI-generated code is to enable SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more logging:

engine = create_engine(DATABASE_URL, echo=True)

With echo=True, SQLAlchemy prints every SQL query to the console. When you hit an endpointWhat is endpoint?A specific URL path on a server that handles a particular type of request, like GET /api/users. Ask AI for more and see 20 identical SELECT statements with different WHERE values, you have found N+1.

INFO: SELECT users.id, users.name FROM users LIMIT 20
INFO: SELECT posts.id, posts.title FROM posts WHERE posts.user_id = 1
INFO: SELECT posts.id, posts.title FROM posts WHERE posts.user_id = 2
INFO: SELECT posts.id, posts.title FROM posts WHERE posts.user_id = 3
...

This wall of repeated queries is the signature of N+1. The fix is always the same: add .options(selectinload(...)) or .options(joinedload(...)) to the original query.

The select() APIWhat is api?A set of rules that lets one program talk to another, usually over the internet, by sending requests and getting responses. Ask AI for more for explicit queries

Sometimes you do not want the ORMWhat is orm?Object-Relational Mapping - a library that lets you interact with a database using your programming language's objects instead of writing raw SQL. Ask AI for more to load entire objects. For API endpoints that return specific fields, use select() with explicit columns:

from sqlalchemy import select, func

# Count posts per user - no ORM objects, just raw data
stmt = (
    select(User.name, func.count(Post.id).label("post_count"))
    .join(Post, User.id == Post.user_id)
    .group_by(User.name)
    .order_by(func.count(Post.id).desc())
    .limit(10)
)

results = db.execute(stmt).all()
# [("Alice", 42), ("Bob", 37), ...]

This generates one precise SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more query that returns exactly the data you need. No N+1 possible, no unnecessary data loaded.

Summary: the decision tree

When you read AI-generated query code, ask these questions in order:

1. Does it access a relationship attribute inside a loop? If yes, it is N+1. Add selectinload() or joinedload().
2. Does it load full objects when it only needs a count or specific columns? If yes, rewrite with select() and SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more functions.
3. Does it use joinedload() on a one-to-many relationship? Consider switching to selectinload() to avoid data duplication.
4. Does it load nested relationships? Chain .selectinload() calls to load multiple levels in minimal queries.

Turn on echo=True during development. Count the queries. If the number grows with the data, you have a problem.