Home › SQLAlchemy / Alembic › SQLAlchemy 2 + Alembic Copilot path instructions

SQLAlchemy 2 + Alembic Copilot path instructions

Path-specific Copilot instructions for SQLAlchemy 2.x: typed Mapped models, select() queries, eager loading, session scope, async, and safe Alembic migrations.

GitHub CopilotSQLAlchemy / Alembic

Copy sqlalchemy.instructions.md

How to use

Save this as .github/instructions/sqlalchemy.instructions.md in your repository root, commit it, and your agent will pick it up automatically. Edit the commands to match your project.

Note: Review and test before use. Provided as is, without warranty. See Terms.

---
applyTo: "**/*.py"
description: SQLAlchemy 2.x ORM and Alembic migration conventions
---

# SQLAlchemy 2.x + Alembic

## Models
- Typed declarative models only: `class Base(DeclarativeBase)` with columns as `Mapped[...] = mapped_column(...)`. `Mapped[str | None]` is nullable, `Mapped[str]` is NOT NULL. No `declarative_base()` or bare `Column()` in new models.
- Give `Base` a `MetaData(naming_convention={...})` (ix, uq, ck, fk, pk) so constraints get stable names Alembic can alter and drop.
- Money is `Numeric(12, 2)` with `Decimal`, never `Float`. Timestamps are `DateTime(timezone=True)` with `server_default=func.now()`, stored in UTC.
- Relationships use `back_populates` on both sides (`Mapped[list["Order"]] = relationship(back_populates="user")`); no `backref`. Consider `lazy="raise"` on collections so accidental lazy loads fail loudly.
- Index foreign-key columns explicitly (`index=True`); PostgreSQL doesn't do it for you.

## Queries (2.0 style)
- Build queries with `select()` and run them with `session.scalars()` / `session.execute()`. Use `session.get(Model, pk)` for primary-key lookups. No `session.query()` in new code.
- Pick the result method by expected rows: `.scalar_one()`, `.scalar_one_or_none()`, `.scalars().all()`. Use `.first()` only with an explicit `order_by`.
- Load what you'll touch: `selectinload()` for collections, `joinedload()` for many-to-one. Never loop over rows and trigger lazy loads (N+1).
- Bulk work: `session.execute(insert(Model), rows)` and `update(Model).where(...).values(...)`. Upserts use the dialect insert (`sqlalchemy.dialects.postgresql.insert(...).on_conflict_do_update(...)`).
- Never build SQL with f-strings or `%`. Raw SQL goes through `text("... WHERE id = :id")` with bound parameters.

## Sessions and transactions
- One engine and one `sessionmaker` per database, created at startup. One session per request or unit of work, from a dependency or context manager; never a module-level session.
- The caller owns the transaction: `with Session() as session, session.begin(): ...` commits on success and rolls back on error. Repository functions don't call `commit()`.
- Web APIs that return ORM objects after commit use `sessionmaker(..., expire_on_commit=False)`.
- Async: `create_async_engine("postgresql+asyncpg://...")`, `async_sessionmaker`, `AsyncSession`, and `await session.execute(...)`. Lazy loading doesn't work in async code, so eager-load, or use `AsyncAttrs` and `await obj.awaitable_attrs.items`.
- Long-running services set `pool_pre_ping=True` and call `engine.dispose()` on shutdown.

## Alembic migrations
- `env.py` imports every model module and sets `target_metadata = Base.metadata`.
- Generate with `alembic revision --autogenerate -m "add orders table"`, then read and fix the file. Autogenerate shows renames as drop + add (data loss), can miss enum and server-default changes, and sometimes writes an incomplete `downgrade()`.
- One logical change per migration. Never edit a migration that has already run on a shared database; write a new one.
- Data migrations use `op.execute()` or lightweight `sa.table()` / `sa.column()` constructs, never imported ORM models (they change later).
- Adding a NOT NULL column to a populated table: add it nullable, backfill, then alter to NOT NULL, in separate steps.
- Large PostgreSQL tables: create indexes with `postgresql_concurrently=True` inside `with op.get_context().autocommit_block():`.
- Commands: `alembic upgrade head`, `alembic downgrade -1`, `alembic check` (fails if models and migrations have drifted).

## Tests
- Test against the same database engine as production (e.g. PostgreSQL in Docker or Testcontainers), not SQLite, when you use engine-specific types.
- Run each test inside a transaction that is rolled back (bind the session with `join_transaction_mode="create_savepoint"`).
- CI runs `alembic upgrade head`, then `alembic downgrade base`, then `alembic upgrade head` again to catch broken migrations.