sqlalchemy

name	sqlalchemy
description	SQLAlchemy 2.0 — modern Python ORM + Core toolkit with first-class async support, type-annotated declarative mapping, and Alembic migrations. Use when: sqlalchemy, sqla, sqlalchemy 2.0, Mapped, mapped_column, DeclarativeBase, select(), AsyncSession, async sqlalchemy, asyncpg, psycopg, alembic, autogenerate, env.py, relationship, back_populates, selectinload, joinedload, lazyload, raiseload, postgres orm, python orm, sqlalchemy core, text(), N+1 lazy load, DetachedInstanceError, expire_on_commit, MissingGreenlet, sessionmaker, async_sessionmaker, async_scoped_session, AsyncAttrs, on_conflict_do_update, JSONB column, fastapi sqlalchemy session dependency. SKIP: Django ORM (→django cascade), Tortoise ORM/Pony/Peewee (niche), raw SQL with no ORM (→postgresql), Prisma/Drizzle TS ORMs (→prisma).
stacks	["sqlalchemy","python","postgresql","orm"]
tags	["orm","sqlalchemy","python","database","async","alembic","migrations"]
packages	["sqlalchemy","alembic","asyncpg","psycopg","aiosqlite","greenlet"]
manifests	["pyproject.toml","alembic.ini","alembic/env.py"]
source	vechkasov-global-skills
risk	high-stakes

🎯 Version Requirements (May 2026)

Primary pins:

• SQLAlchemy: 2.0.x
• Python: 3.14.x
• PostgreSQL: 18.x

Source of truth: STACK_VERSIONS.md — verified 2026-05-16

Usage

Loaded automatically when its description matches the active task. Read only the reference file you need — SKILL.md is the navigator.

Use this skill when

• Designing or evolving 2.0-style declarative mappings — class Foo(Base), id: Mapped[int] = mapped_column(primary_key=True), relationship(back_populates=...)
• Writing 2.0-style queries with select() + session.execute(...).scalars() (never the legacy Query class)
• Wiring a sync engine (create_engine) or async engine (create_async_engine) with asyncpg/psycopg/aiosqlite
• Configuring sessionmaker / async_sessionmaker and choosing the right expire_on_commit setting
• Choosing a relationship loading strategy — selectinload, joinedload, subqueryload, lazyload, raiseload — and diagnosing N+1 query bursts
• Diagnosing DetachedInstanceError, MissingGreenlet, pool exhaustion (QueuePool limit ... overflow ... reached), or "object already attached to session"
• Setting up Alembic — alembic init -t async, target_metadata, MetaData(naming_convention=...), autogenerate, data migrations, multi-DB
• Adding Postgres-only features — JSONB, ARRAY, UUID, Insert.on_conflict_do_update, Computed, ENUM
• Wiring FastAPI's AsyncSession dependency with proper try/finally and transaction boundaries
• Writing tests with SAVEPOINT-per-test fixtures or NullPool for parallel test isolation