SQLAlchemy Relationships: selectinload vs joinedload

1Concept

To prevent the fatal N+1 Query Problem, SQLAlchemy provides eager loading strategies: `selectinload()` loads related collections using a secondary `SELECT ... WHERE id IN (...)` query (ideal for 1-to-many); `joinedload()` uses SQL `LEFT OUTER JOIN` (ideal for many-to-1).

2Architecture Diagram

Lazy Loading (Anti-Pattern): 1 Query for Users + N queries for each User's Orders (N+1 Problem!)
selectinload (Best Practice): 1 Query for Users + 1 Query for Orders using IN (id1, id2...)

3Code Example

# Demonstrating eager loading strategies
print("=== SQLAlchemy Eager Loading Strategies ===")
print("1. selectinload(User.orders):")
print("   - Emits: SELECT * FROM orders WHERE user_id IN (1, 2, 3...)")
print("   - Best for: One-to-Many and Many-to-Many relationships.")
print("2. joinedload(Order.user):")
print("   - Emits: SELECT * FROM orders LEFT JOIN users ON ...")
print("   - Best for: Many-to-One and One-to-One relationships.")

4Expected Output

=== SQLAlchemy Eager Loading Strategies ===
1. selectinload(User.orders):
   - Emits: SELECT * FROM orders WHERE user_id IN (1, 2, 3...)
   - Best for: One-to-Many and Many-to-Many relationships.
2. joinedload(Order.user):
   - Emits: SELECT * FROM orders LEFT JOIN users ON ...
   - Best for: Many-to-One and One-to-One relationships.

5Key Takeaways

• ✓Never use default lazy loading in async architectures; it will fail with `MissingGreenlet`.
• ✓`selectinload` avoids duplicate row data transfer overhead caused by cartesian products in joins.
• ✓Configure relationship loading in queries with `options(selectinload(User.orders))`.