No history yet

Relationship Loading Fundamentals

The Default Behavior: Lazy Loading

When you define a relationship in SQLAlchemy, it doesn't immediately fetch the related data. Instead, it uses a strategy called "lazy loading." This means SQLAlchemy waits until the very last moment—when you actually access a relationship attribute—to run a SELECT statement and retrieve the necessary objects.

For example, if you have a User object and you access my_user.addresses, SQLAlchemy only runs the query for the addresses at that exact moment. This is the default behavior, specified by lazy='select'. It's convenient because you don't load data you might never use. But this convenience comes with a hidden cost.

The N+1 Query Problem

The hidden cost of lazy loading is a common performance pitfall known as the "N+1 query problem." It happens when your code fetches a list of parent objects and then iterates through them, accessing a lazy-loaded relationship on each one.

You get one query to load the parent objects (the "1"), followed by N additional queries to load the related objects for each of the N parents.

Imagine you query for 10 users. That's your first query. Then, you loop through these 10 users to print their addresses. Accessing .addresses on the first user triggers a second query. Accessing it on the second user triggers a third, and so on. For 10 users, you end up running 11 separate queries. For 1,000 users, you'd run 1,001 queries. This can quickly slow your application to a crawl.

Making Queries Visible

The N+1 problem can be insidious because the queries are triggered implicitly. Your code looks clean, but the database is working overtime. The easiest way to spot this issue is to make SQLAlchemy show you every SQL statement it executes. You can do this by setting echo=True when creating the engine.

from sqlalchemy import create_engine

# Enable logging of all generated SQL
engine = create_engine("sqlite:///:memory:", echo=True)

Now, let's run some code that causes the N+1 problem and watch the output.

# Assume User and Address models are defined and mapped

# 1. Fetch all users (This is our "1" query)
users = session.scalars(select(User)).all()

# 2. Loop and access the lazy-loaded relationship
for user in users:
    # This line triggers one new query per user (The "N" queries)
    print(f"User: {user.name}, Addresses: {len(user.addresses)}")

With echo=True, your console would show something like this:

# The "1" query
SELECT user_account.id, user_account.name 
FROM user_account
...
# The first of the "N" queries
SELECT address.id, address.email_address, address.user_id 
FROM address 
WHERE ? = address.user_id
...
# The second of the "N" queries
SELECT address.id, address.email_address, address.user_id 
FROM address 
WHERE ? = address.user_id
...
# ...and so on for every user.

Seeing this pattern in your logs—a single query followed by many similar queries inside a loop—is the classic signature of an N+1 problem that needs fixing.

Session State and the Identity Map

So why does this happen? It's tied to how the Session manages the state of your objects. The Session maintains a registry of all objects that have been loaded or attached to it, called the Identity Map. Its primary job is to ensure that for any given primary key, only one object instance exists within the session.

When you first access a lazy relationship like my_user.addresses, SQLAlchemy performs these steps:

  1. Runs a SELECT query to find all addresses for my_user.
  2. Creates Address object instances for each row returned.
  3. Places these Address objects into the Identity Map.
  4. Populates the my_user.addresses collection with these objects.
  5. Marks the collection as "loaded."

If you access my_user.addresses again within the same session, SQLAlchemy sees the collection is already loaded and immediately returns it without hitting the database. The N+1 problem arises because this check happens per parent instance. When our loop moves to the next user, its .addresses collection hasn't been loaded yet, so the cycle repeats.

Lazy loading means that related data is not loaded from the database until it is explicitly needed in Python code.

This default behavior is a trade-off between fetching too much data upfront and running too many small queries later. For high-performance applications, you need to be more explicit about how and when relationships are loaded. You can control this either by setting the lazy parameter on the relationship() itself or, more flexibly, by using query-time loader options, which we'll explore next.