sqlalchemy_skill

Overview

This skill packages SQLAlchemy ORM patterns and examples for building reliable Python data layers, including the modern 2.0 API, async support, and Alembic migrations. It focuses on declarative models, relationship mapping, session and transaction management, and practical CRUD/query patterns. The content helps integrate SQLAlchemy with web frameworks like FastAPI and provides migration workflows for production databases.

How this skill works

The skill describes how to define typed models using DeclarativeBase, Mapped, and mapped_column, then create engines and session factories for sync and async usage. It shows common query idioms (select, joins, eager loading), transaction patterns, and examples for bulk operations, optimistic locking, and context-managed sessions. Alembic setup and migration workflows are included to keep schema changes versioned and reproducible.

When to use it

• When building Python web APIs or services that need a robust ORM and SQL toolkit
• When you need type-hinted models, modern query syntax, or async DB access
• When you must prevent N+1 queries with eager loading or optimize joins and counts
• When you require production-ready connection pooling, session management, and transactions
• When you want automated, reviewable schema migrations using Alembic

Best practices

• Use SQLAlchemy 2.0 patterns: select(), Mapped[T], mapped_column(), and explicit Session.execute()
• Prefer session context managers or dependency-injected sessions to ensure commits/rollbacks and close connections
• Use selectinload/joinedload to avoid N+1 problems and load_only to limit fetched columns
• Configure engine pooling and pre-ping in production; set expire_on_commit=False if you need detached objects
• Keep Alembic autogenerate workflows but always review generated migration scripts before applying

Example use cases

• Designing a user/posts/tags data model with one-to-many and many-to-many relationships
• Implementing paginated API endpoints with ordering, filtering, and count queries for dashboards
• Writing transactional operations like safe balance transfers using with_for_update and nested transactions
• Converting sync data access to async endpoints using AsyncSession, create_async_engine, and async sessionmaker
• Managing schema changes across environments with Alembic revisions, upgrades, and rollbacks