py-sqlmodel-patternslisted

# SQLModel Patterns ## Problem Statement SQLModel combines Pydantic and SQLAlchemy, blurring the line between models and schemas. Async SQLAlchemy has different rules than sync. Mistakes here cause data corruption, N+1 queries, and hard-to-debug errors. --- ## Pattern: Eager Loading for Async **Problem:** Lazy loading doesn't work with async SQLAlchemy. Accessing relationships without eager loading raises errors. ```python # ❌ WRONG: Lazy loading fails in async result = await session.execute(select(User).where(User.id == user_id)) user = result.scalar_one() assessments = user.assessments # ERROR: greenlet_spawn has not been called # ✅ CORRECT: selectinload for collections from sqlalchemy.orm import selectinload result = await session.execute( select(User) .where(User.id == user_id) .options(selectinload(User.assessments)) ) user = result.scalar_one() assessments = user.assessments # Works - already loaded # ✅ CORRECT: joinedload for single relationships from sqlalchemy.orm import joinedload result = await session.execute( select(Assessment) .where(Assessment.id == assessment_id) .options(joinedload(Assessment.user)) ) assessment = result.scalar_one() user = assessment.user # Works - already loaded ``` **When to use which:** | Relationship | Loading Strategy | |--------------|------------------| | One-to-many (collections) | `selectinload()` | | Many-to-one (single) | `joinedload()` | | Nested relationships | Chain: `.options(selectinload