SQLAlchemy Relationship Loading: Joined, Selectin, or Raise

The problem: your loop is issuing one query per row

You fetch a list of orders, then loop through them to access each order's line items. SQLAlchemy's default behavior loads related attributes only when you first touch them. That means one query to fetch the orders, then one additional query for every order's items. With 100 orders, you get 101 queries. This pattern is called an N+1 query problem, and it is a common cause of slow read-heavy endpoints.

The useful takeaway: you do not need to rewrite your data model. You need to tell SQLAlchemy how to load related rows up front, using an eager-loading strategy that matches the relationship shape.

Decision: choose a loading strategy per relationship

SQLAlchemy lets you set a default loading strategy on each relationship declaration, and override it per query. The decision depends on how many parents you expect, how many children each parent has, and whether you will access the relationship in the same request.

Constraints to name first

• Are you loading one parent or many?
• Is the relationship one-to-one, many-to-one, one-to-many, or many-to-many?
• Will you paginate the parent results?
• Do you need to combine this query with UNION, set operations, or correlated subqueries?

Supported strategies compared

Strategy	Declaration or query option	Best suited for	Main risk
Default lazy loading	lazy="select" (the default)	Relationships accessed rarely	N+1 queries when iterating collections
Joined eager loading	lazy="joined" or joinedload()	One-to-one and low-cardinality many-to-one	Row multiplication with multiple collections
Selectin eager loading	lazy="selectin" or selectinload()	One-to-many and many-to-many collections	Extra round trip; limited with UNION or set operations
Raise on lazy access	lazy="raise"	Tests and strict read paths	Breaks legitimate lazy loads in templates or jobs

Trade-offs by relationship shape

Joined load can duplicate parent rows

joinedload emits a single query with a LEFT OUTER JOIN. That is efficient for scalar relationships such as order.user. It becomes problematic when you join multiple collections, such as order.items and order.payments, because the database returns the cross product of every combination. One order with ten items and five payments produces fifty rows, which SQLAlchemy then de-duplicates in memory. The result can be slower than issuing separate queries.

Selectin load avoids duplication but adds a round trip

selectinload issues one query for the parents, then one additional IN query per collection to fetch all related rows at once. It does not duplicate parent rows, so it is usually the better choice for one-to-many and many-to-many collections. The trade-off is an extra network round trip, and it does not combine cleanly with some UNION, set operations, or correlated subqueries.

Raise turns accidental lazy loading into test failures

lazy="raise" disables implicit lazy loading at runtime. If code accesses a relationship that was not explicitly loaded, SQLAlchemy raises an error instead of silently issuing another query. This is valuable in test suites and internal APIs, but it will break legitimate lazy loads in templates, background jobs, or any code path that expects transparent loading.

Implementation: declare models and override per query

The following example assumes SQLAlchemy 2.0 style declarative models. Placeholders such as <DATABASE_URL> should be replaced with your actual connection string. Run this in a Python application or test file; no special database permissions are required beyond normal read access.

Example models

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, Session

engine = create_engine("<DATABASE_URL>", echo=True)
Base = declarative_base()

class Order(Base):
    __tablename__ = "orders"
    id = Column(Integer, primary_key=True)
    code = Column(String)
    items = relationship("OrderItem", back_populates="order")

class OrderItem(Base):
    __tablename__ = "order_items"
    id = Column(Integer, primary_key=True)
    order_id = Column(Integer, ForeignKey("orders.id"))
    sku = Column(String)
    order = relationship("Order", back_populates="items")

Load items with joinedload

Use joinedload when you expect few orders and want a single round trip:

from sqlalchemy.orm import Session, joinedload

with Session(engine) as session:
    orders = (
        session.query(Order)
        .options(joinedload(Order.items))
        .all()
    )
    for order in orders:
        print(order.code, [item.sku for item in order.items])

Expected check: with echo=True, you should see one SQL statement that joins orders to order_items.

Load items with selectinload

Use selectinload when orders may have many items, or when you also load other collections:

from sqlalchemy.orm import Session, selectinload

with Session(engine) as session:
    orders = (
        session.query(Order)
        .options(selectinload(Order.items))
        .all()
    )

Expected check: with echo=True, you should see one query for orders, then one query for order_items using an IN clause containing the parent IDs.

Set a default strategy on the relationship

If almost every query needs items preloaded, declare it on the model:

items = relationship(
    "OrderItem",
    back_populates="order",
    lazy="selectin",
)

You can still override per query with lazyload() if a specific code path genuinely does not need the relationship.

Validation: count the queries your code emits

Enable statement logging

Set echo=True on the engine, traverse a parent with children, and count the emitted SQL statements. If you see one statement per parent row, you have an N+1 problem and should add an eager-loading option.

Assert query count in tests

Add a test that executes a fixed traversal and verifies the total query count stays below a predetermined threshold. SQLAlchemy provides event listeners for engine events that let you record each statement without parsing logs.

Use lazy="raise" in tests to catch regressions

Configure test sessions or relationship defaults with lazy="raise". Any unplanned lazy access throws an exception during the test run instead of reaching production as a slow query.

Limitations and practical checks

• Row multiplication: Do not apply joinedload to multiple unbounded collections in the same query. Check the row count in your database log or explain plan if results look inflated.
• Query compatibility: selectinload may not work with UNION or some set operations. Test the exact query shape before adopting it broadly.
• Strict mode breakage: lazy="raise" fails on any unplanned access. Limit it to tests or internal services where all access paths are known.
• Pagination: Changing a loading strategy changes generated SQL, which can affect existing pagination, query plans, or cached query results. Verify page counts and cursor behavior after the change.

Practical check: enable echo=True in a staging environment, run the endpoint with realistic data, and confirm the number of SQL statements matches your expectation—one for joined load, or parent-plus-collections for selectin load.

Quick decision checklist

1. Identify the relationship and its cardinality.
2. Use joinedload for one-to-one or sparse many-to-one.
3. Use selectinload for one-to-many or many-to-many collections.
4. Avoid multiple joinedload collections in one query.
5. Use lazy="raise" in tests to prevent accidental lazy loads.
6. Verify with echo=True or a query-count assertion before deploying.