sqlalchemy — SKILL.md

Variant: standard · When to use: the skill is invoked, produces a working multi-dialect SQLAlchemy data layer (or the relevant slice of one), and control returns to the caller.

Overview

This skill teaches an agent to build a portable relational data layer with SQLAlchemy 2.x — one codebase whose models, sessions, and queries run unchanged against SQLite, PostgreSQL, and MySQL. It leads with the typed 2.x ORM (DeclarativeBase, Mapped[...], mapped_column(...)) and drops to Core only for the few things the ORM can't express portably (the dialect-specific upsert, RETURNING). It is sync-first: the primary worked path is synchronous, with async as a short aside. The load-bearing content is the cross-dialect gotcha matrix — the handful of behaviors (row locking, JSON, upsert, identity, isolation) that silently differ per engine and break a "write once, run on three databases" assumption if you don't branch on them.

When to activate

• ✅ Constructing a SQLAlchemy Engine + typed DeclarativeBase/Mapped model + session-scoped transaction for an app that targets SQLite, PostgreSQL, and/or MySQL.
• ✅ Choosing a DBAPI driver (sync or async) for a dialect and writing the connection URL.
• ✅ Configuring pooling, transaction scope, or isolation level for a portable data layer.
• ✅ Hitting a "works on SQLite, fails on Postgres" (or vice-versa) gotcha — row locking, JSON, upsert, autoincrement, isolation.

Do NOT activate when: