sqlalchemy.mdc

---
description: "SQLAlchemy: sessions, models, relationships, async"
globs: ["*.py"]
alwaysApply: true
---

# SQLAlchemy Cursor Rules

You are an expert in SQLAlchemy 2.0+. Follow these rules:

## Models (Declarative)
- Use DeclarativeBase with Mapped[] type annotations for all columns
- mapped_column() over Column() — id: Mapped[int] = mapped_column(primary_key=True)
- Use __tablename__ explicitly on every model — never rely on auto-generation
- Keep models in a dedicated models/ package, one file per domain entity

## Relationships
- Use relationship() with back_populates on both sides — never backref
- Set lazy="selectin" or lazy="joined" explicitly — never rely on default lazy loading
- Use Mapped[list["Child"]] for one-to-many, Mapped["Parent"] for many-to-one
- Define cascade="all, delete-orphan" on parent side of one-to-many relationships

## Sessions & Transactions
- Use sessionmaker or async_sessionmaker bound to engine — never create sessions manually
- Wrap operations in with Session() as session: blocks for auto-cleanup
- Call session.flush() to get IDs mid-transaction, session.commit() at operation boundaries
- Never share sessions across threads or async tasks
- Use session.execute(select(Model).where(...)) — never session.query() (legacy)

## Queries (2.0 Style)
- Use select(), insert(), update(), delete() from sqlalchemy — never string SQL
- Chain .where(), .order_by(), .limit(), .offset() on select statements
- Use .scalars().all() for lists, .scalars().one() for exactly one, .scalars().first() for optional
- Use joinedload(), selectinload() for eager loading in specific queries

## Async
- Use create_async_engine() and async_sessionmaker for async code
- Use async with session.begin(): for auto-commit/rollback
- Awaitable versions: await session.execute(), await session.commit()
- Access lazy-loaded relationships only after await session.run_sync() or eager load

## Migrations (Alembic)
- Generate migrations with alembic revision --autogenerate -m "description"
- Always review autogenerated migrations — they miss indexes, constraints, data migrations
- Use op.execute() for data transforms, not ORM models in migration files
- Test migrations both upgrade and downgrade before merging