Stop SQLAlchemy from Duplicating Parent Rows with selectinload

Duplicate orders in the dashboard

You fetch a list of orders and their line items so a dashboard can show totals. The code looks correct, but the response contains the same order multiple times, once for each line item. Memory usage spikes, and the JSON serializer emits duplicate records. This is a common SQLAlchemy loading problem: using joinedload on a one-to-many collection creates a Cartesian product of parent and child rows.

Takeaway: for one-to-many collections, prefer selectinload over joinedload. It returns each parent row once and loads children in a separate IN query. Use joinedload only for small, bounded relationships such as many-to-one references.

Why joinedload repeats parent rows

joinedload emits a single LEFT OUTER JOIN between the parent and child tables. For a one-to-many relationship, the database returns one row per child. SQLAlchemy de-duplicates objects in its identity map, but the result set still repeats parent columns for every child row. If you load two collections at once—orders with line items and shipments—the row count multiplies. That wastes bandwidth and can pressure application memory because parent data is repeated in every row.

What selectinload does instead

selectinload issues one query for parents, then a second query using WHERE parent_id IN (...) to fetch all children for those parents. Each parent appears exactly once in the first result set. The trade-off is additional round trips: one per collection, plus one more for each nesting level. On a high-latency network, that can be slower than a single join for very small, bounded datasets. For unbounded collections such as line items per order, the extra round trip is usually cheaper than moving and de-duplicating duplicated rows.

Worked example: SQLAlchemy 2.0

Assumptions: SQLAlchemy 2.0, a running database, and SELECT access to the orders and line_items tables. The 2.0 select() API is preferred for new code; SQLAlchemy 1.4 uses the legacy Query API with the same option.

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, select
from sqlalchemy.orm import DeclarativeBase, relationship, Session, selectinload

class Base(DeclarativeBase):
    pass

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

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

engine = create_engine("sqlite:///app.db", echo=True)
session = Session(engine)

stmt = (
    select(Order)
    .options(selectinload(Order.items))
    .limit(10)
)
orders = session.execute(stmt).scalars().all()

Where to run: in a Python shell or application service that already has a SQLAlchemy session. Required permissions: SELECT on orders and line_items. Replace sqlite:///app.db with your actual connection string.

Expected checks:

• With echo=True, the log shows two statements: one SELECT from orders, then one SELECT from line_items with WHERE order_id IN (...).
• len(orders) equals the number of distinct orders, not the total number of line items.
• Accessing order.items does not trigger another query because the collection is already loaded.

Risk: if you access a relationship that was not included in selectinload outside an open session, SQLAlchemy may issue a lazy load or raise an error depending on configuration. Do not detach loaded objects and expect every relationship to be populated.

Diagnostic decision checklist

• Many-to-one or scalar reference? Use joinedload.
• One-to-many collection with an unknown or large number of children? Use selectinload.
• Loading multiple collections at the same time? Avoid joinedload for all of them; selectinload prevents row multiplication.
• High network latency and a tiny dataset? Benchmark joinedload, but default to selectinload for collections.

Limitations and how to verify

selectinload increases query count. Each nested level adds another round trip, so deeply nested chains can be slower than a single carefully written join. It also does not load arbitrary columns; use load_only for column-level selection, not relationship options.

Practical verification:

1. Set echo=True on the engine.
2. Run the query and count distinct parent IDs in Python: len({o.id for o in orders}).
3. Compare that count to len(orders). They should match.
4. In tests, pair selectinload with raiseload to catch accidental lazy loads on relationships you forgot to include.

Closing

Relationship loading is not a one-size-fits-all setting. Choose joinedload for small references and selectinload for collections. Verify with echo=True and a distinct-parent count before shipping the change. If your dashboard still shows duplicate orders after switching, check whether another code path is re-querying the same relationship without the option.