Version history: SQLAlchemy
v1
scan: pass2026-08-11 — Ingested from sourceInitial version.
v2
currentscan: pass2026-11-02 — Currency pass 2026-11-01: removed sessionmaker(autocommit=False) (the option no longer exists in 2.0), dropped the deprecated sqlalchemy.ext.mypy.plugin in favour of native Mapped[] typing, Mapped[DateTime] -> Mapped[datetime], builtin list / X | None hints, bulk_insert_mappings noted as legacy, quoted globs so the frontmatter parses, removed team boilerplate.---- description: This guide enforces modern SQLAlchemy 2.x best practices for Python applications, ensuring type-safe, performant, and maintainable database interactions. - globs: **/*.py+ description: "SQLAlchemy 2.x best practices for Python applications: typed DeclarativeBase models, sessions as context managers, select() queries, eager loading, and Alembic migrations." + globs: "**/*.py" + alwaysApply: false --- # SQLAlchemy Best Practices (2.x Style) - This document outlines the definitive best practices for using SQLAlchemy 2.x in our projects. We exclusively adopt the "2-style" API, leveraging its fully typed, declarative features for robust and maintainable code.+ This document outlines best practices for using SQLAlchemy 2.x. Adopt the "2.0-style" API exclusively, leveraging its fully typed, declarative features for robust and maintainable code. ## 1. Code Organization and Data Modeling Always define your ORM models using the modern `DeclarativeBase` and `Mapped` annotations. Keep model definitions in a dedicated `models.py` or `orm_models/` directory. ### 1.1 Declarative Models with Type Annotations Use `DeclarativeBase` as your base class and `Mapped` for all ORM-mapped attributes. This enables static type checking and clear schema definition. ❌ BAD: Legacy `declarative_base()` function and untyped `Column`. ```python from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) name = Column(String) # No type hint ``` ✅ GOOD: Modern `DeclarativeBase` and `Mapped` for explicit typing. ```python- from typing import List, Optional+ from datetime import datetime from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship from sqlalchemy import String, ForeignKey, func, DateTime class Base(DeclarativeBase): pass class User(Base): __tablename__ = "user_account" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(30))- fullname: Mapped[Optional[str]] - created_at: Mapped[DateTime] = mapped_column(DateTime, insert_default=func.now())+ fullname: Mapped[str | None] + created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), insert_default=func.now()) - addresses: Mapped[List["Address"]] = relationship(back_populates="user", cascade="all, delete-orphan")+ addresses: Mapped[list["Address"]] = relationship(back_populates="user", cascade="all, delete-orphan") class Address(Base): __tablename__ = "address" id: Mapped[int] = mapped_column(primary_key=True) email_address: Mapped[str] user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id")) user: Mapped["User"] = relationship(back_populates="addresses") ``` ### 1.2 Mixins for Common Fields Implement common fields like timestamps, soft-delete flags, or version counters using mixins. ```python from datetime import datetime from sqlalchemy.orm import Mapped, mapped_column from sqlalchemy import DateTime, func class TimestampMixin:- created_at: Mapped[datetime] = mapped_column(DateTime, insert_default=func.now()) - updated_at: Mapped[datetime] = mapped_column(DateTime, insert_default=func.now(), onupdate=func.now())+ created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), insert_default=func.now()) + updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), insert_default=func.now(), onupdate=func.now()) class SoftDeleteMixin:- deleted_at: Mapped[Optional[datetime]] = mapped_column(DateTime, default=None)+ deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), default=None) class User(Base, TimestampMixin, SoftDeleteMixin): __tablename__ = "user_account" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] # ... other fields ``` ## 2. Session Management The `Session` is your unit of work. Always use it as a context manager to ensure proper transaction handling and resource cleanup. ### 2.1 Context Manager for Sessions - Wrap all database operations within a `with Session(engine) as session:` block. This guarantees `commit()` on success and `rollback()` on error, followed by `close()`.+ Wrap all database operations within a `with Session(engine) as session:` block. The context manager guarantees `close()` (and a rollback of anything not committed) when the block exits; call `session.commit()` explicitly, or use `session.begin()` (see 2.2) to commit on success and roll back on error. ❌ BAD: Manual session handling, prone to leaks and uncommitted transactions. ```python session = Session(engine) try: user = User(name="Alice") session.add(user) session.commit() except Exception: session.rollback() finally: session.close() ``` ✅ GOOD: `Session` as a context manager. ```python from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine engine = create_engine("sqlite:///./test.db")- Session = sessionmaker(autocommit=False, autoflush=False, bind=engine)+ Session = sessionmaker(bind=engine) # 2.x sessions are always transactional; `autocommit` no longer exists def create_user(name: str): with Session() as session: user = User(name=name) session.add(user) session.commit() # Flushes changes and commits transaction session.refresh(user) # Refresh to get auto-generated IDs return user ``` ### 2.2 Explicit Transaction Blocks For complex operations requiring multiple steps within a single transaction, use `session.begin()`. ```python def transfer_funds(from_account_id: int, to_account_id: int, amount: float): with Session() as session: with session.begin(): # Explicit transaction block from_account = session.get(Account, from_account_id) to_account = session.get(Account, to_account_id) if not from_account or not to_account: raise ValueError("Account not found") if from_account.balance < amount: raise ValueError("Insufficient funds") from_account.balance -= amount to_account.balance += amount # Transaction commits here if no exceptions, or rolls back if an error occurs print(f"Transferred {amount} from {from_account_id} to {to_account_id}") ``` ## 3. Querying and Optimization Always use the `select()` construct for queries and prioritize eager loading for relationships to avoid N+1 problems. ### 3.1 Use `select()` for All Queries The `Query` object is deprecated in 2.x. Use `select()` for all ORM and Core queries. ❌ BAD: Using the legacy `session.query()` API. ```python users = session.query(User).filter(User.name == "Alice").all() ``` ✅ GOOD: Using `select()` with `session.execute()`. ```python from sqlalchemy import select - def get_user_by_name(session: Session, name: str) -> Optional[User]:+ def get_user_by_name(session: Session, name: str) -> User | None: stmt = select(User).where(User.name == name) return session.scalar(stmt) # Use scalar for single result, one_or_none for one, all for list ``` ### 3.2 Eager Loading Relationships Prevent N+1 query issues by eagerly loading related objects using `selectinload` or `joinedload`. `selectinload` is generally preferred for collections. ❌ BAD: Lazy loading in a loop, leading to N+1 queries. ```python users = session.scalars(select(User)).all() for user in users: print(f"{user.name} has {len(user.addresses)} addresses") # Each access triggers a new query ``` ✅ GOOD: Eager loading with `selectinload`. ```python from sqlalchemy.orm import selectinload - def get_users_with_addresses(session: Session) -> List[User]:+ def get_users_with_addresses(session: Session) -> list[User]: stmt = select(User).options(selectinload(User.addresses))- return session.scalars(stmt).all()+ return list(session.scalars(stmt).all()) # Now, accessing user.addresses won't trigger additional queries users = get_users_with_addresses(session) for user in users: print(f"{user.name} has {len(user.addresses)} addresses") ``` ## 4. Data Manipulation The ORM Unit of Work pattern handles inserts, updates, and deletes efficiently. ### 4.1 Adding and Updating Objects Add new objects with `session.add()`. Modifications to existing objects are tracked automatically. ```python def update_user_name(session: Session, user_id: int, new_name: str): user = session.get(User, user_id) # Efficiently get by primary key if user: user.name = new_name # Change is tracked session.commit() return user def add_new_user_with_address(session: Session, name: str, email: str): user = User(name=name, fullname=name) address = Address(email_address=email, user=user) # Relationship automatically links session.add(user) # Adding user also adds address due to cascade session.commit() return user ``` ### 4.2 Deleting Objects Delete objects using `session.delete()`. ```python def delete_user(session: Session, user_id: int): user = session.get(User, user_id) if user: session.delete(user) session.commit() print(f"User {user_id} deleted.") else: print(f"User {user_id} not found.") ``` ### 4.3 Bulk Operations (Use Sparingly) - Only use bulk operations (`session.bulk_insert_mappings`, `session.execute(insert/update/delete)`) when performance is paramount and ORM event handling (e.g., `onupdate` hooks) is not required.+ Only use bulk operations (`session.execute(insert/update/delete)` with a list of parameter dicts) when performance is paramount and ORM event handling (e.g., `onupdate` hooks) is not required. The legacy `session.bulk_insert_mappings()` / `bulk_update_mappings()` methods are superseded in 2.x by this ORM-enabled `insert()`/`update()` form. ```python from sqlalchemy import insert - def bulk_insert_users(session: Session, user_data: List[dict]):+ def bulk_insert_users(session: Session, user_data: list[dict]): # This bypasses ORM object creation and event hooks. # Use when you have raw data and need speed. session.execute(insert(User), user_data) session.commit() # Example user_data: [{"name": "Charlie", "fullname": "Charlie Brown"}, ...] ``` ## 5. Migrations and Type Checking - Integrate Alembic for schema migrations and ensure static type checking with Mypy or Ruff.+ Integrate Alembic for schema migrations and ensure static type checking with mypy or pyright. ### 5.1 Alembic for Schema Evolution Always use Alembic to manage database schema changes. Never manually alter the database schema. ```bash- # Initialize Alembic (once per project) - alembic init -t async migrations+ # Initialize Alembic (once per project); add `-t async` only for an async engine + alembic init migrations # Generate a new migration script alembic revision --autogenerate -m "Add new_column to user_account" # Apply migrations alembic upgrade head ``` ### 5.2 Static Type Checking - Leverage Python's `typing` and SQLAlchemy's `Mapped` for comprehensive type checking with tools like Mypy or Ruff. This catches schema mismatches and API misuses early.+ Leverage Python's `typing` and SQLAlchemy's `Mapped` for comprehensive type checking with mypy or pyright. This catches schema mismatches and API misuses early. + `Mapped[]` annotations are understood by type checkers natively in 2.x; do not enable the legacy `sqlalchemy.ext.mypy.plugin` (deprecated in 2.0, only needed for 1.4-style models). + ```python- # mypy.ini or pyproject.toml configuration for mypy - [mypy] - plugins = sqlalchemy.ext.mypy.plugin - ```+ def rename(session: Session, user_id: int, new_name: str) -> None: + user = session.get(User, user_id) # typed as User | None + if user is None: + raise LookupError(user_id) + user.name = new_name # str, checked statically + user.name = 42 # type checker error + ```