sqlalchemy

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