Write correct SQLAlchemy 2.0 queries, avoid N+1 problems, and use eager loading strategies. Use when writing SQLAlchemy models or queries, fixing N+1 query bugs, choosing between joinedload/selectinload/subqueryload, or migrating from SQLAlchemy 1.x to 2.0 style. Do not use for BigQuery analytics (prefer bigquery) or raw SQL without an ORM.
Write correct SQLAlchemy 2.0 queries with proper relationship loading, N+1 prevention, and session management.
mapped_column and Mapped typesjoinedload, selectinload, and subqueryloadbigqueryMapped[type], mapped_column(), and with type annotations.relationship()echo=True on engine or use SQLAlchemy event listeners to count queries per request.joinedload for one-to-one/many-to-one; selectinload for one-to-many; subqueryload for deep nesting.select() over query() — 2.0 style: session.execute(select(User).where(...)) not session.query(User).filter(...).async_sessionmaker or sessionmaker with context manager; never share sessions across threads.index=True; create composite indexes via __table_args__.--autogenerate to detect model changes; always review generated SQL.connection.execute(text("SELECT ...")) event counters.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")
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)
)
selectinload for collections — avoids cartesian product explosion.joinedload only for single-object relationships (many-to-one, one-to-one).expire_on_commit=False.Mapped[type] for all columns — it provides type checking and IDE support.alembic check in CI to ensure models and migrations stay in sync.bigquery — analytics SQL patterns