Orm Patterns

Model example (2.0 style)

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from sqlalchemy import ForeignKey, String
from datetime import datetime

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(255), unique=True, index=True)
    created_at: Mapped[datetime] = mapped_column(default=datetime.utcnow)
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(500))
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)
    author: Mapped["User"] = relationship(back_populates="posts")

Loading strategies

from sqlalchemy.orm import joinedload, selectinload
from sqlalchemy import select

# One-to-many: use selectinload (2 queries, no cartesian product)
stmt = select(User).options(selectinload(User.posts)).where(User.id == 1)

# Many-to-one: use joinedload (single JOIN)
stmt = select(Post).options(joinedload(Post.author)).limit(20)

# Nested: chain strategies
stmt = select(User).options(
    selectinload(User.posts).joinedload(Post.tags)
)

Decision rules

• Default to selectinload for collections — avoids cartesian product explosion.
• Use joinedload only for single-object relationships (many-to-one, one-to-one).
• Never access lazy-loaded relationships outside an active session — use eager loading or expire_on_commit=False.
• Use Mapped[type] for all columns — it provides type checking and IDE support.
• Run alembic check in CI to ensure models and migrations stay in sync.

References

• https://docs.sqlalchemy.org/en/20/orm/quickstart.html
• https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html
• https://alembic.sqlalchemy.org/

Related skills

• bigquery — analytics SQL patterns