Skip to content

skillDatabasespython

SQLAlchemy Patterns

Applies SQLAlchemy 2 patterns for sessions, typed models, relationship loading and Alembic migrations, and removes legacy 1.x habits.

npx workspacetemplates add sqlalchemy-patterns --tool claude-code

Writes 1 file to .claude/skills/sqlalchemy-patterns/

.claude/skills/sqlalchemy-patterns/SKILL.md27 lines · 1.8 KBFile
---
name: sqlalchemy-patterns
description: Applies SQLAlchemy 2 patterns for sessions, typed models, relationship loading and Alembic migrations, and removes legacy 1.x habits.
---

# SQLAlchemy patterns

Use the 2.0 API: typed models, `select()` statements and short, explicit sessions.

## Steps

1. Define models with `DeclarativeBase`, `Mapped[...]` and `mapped_column(...)`. Give each foreign key an index, set `nullable` on purpose, and name constraints through a naming convention so Alembic can drop them later.
2. Create one engine per process and a `sessionmaker`. Open a session per unit of work (a request or a job) with a context manager: `with Session(engine) as session, session.begin():` so it commits on success and rolls back on error. Never share a session between threads or tasks.
3. Query with `session.execute(select(User).where(...))` and `.scalars()`. Do not use the legacy `Query` API in new code.
4. Choose loading per relationship and per query: `selectinload` for collections, `joinedload` for many-to-one, `raiseload("*")` in tests to make surprise lazy loads fail. In async code lazy loading is not allowed at all, so load what you need up front.
5. Do bulk work in the database: `update()`, `delete()` and `insert()` statements, not loops over objects. Stream big reads with `yield_per=1000`.
6. Handle concurrency with `with_for_update()` or an atomic `UPDATE ... SET n = n + 1`.
7. For migrations run `alembic revision --autogenerate -m "..."`, then read the file: autogenerate misses renames (it emits drop and add), server defaults and some type changes. Edit by hand, and add a working `downgrade`.

## Rules

- Never commit an autogenerated migration unread.
- Keep session handling out of models; pass the session in.

## Report

What changed, query counts before and after for any loading fix, and the migration reviewed.

What it does

Much SQLAlchemy code online is still written for version 1.x, and it shows: global sessions, implicit lazy loads and autogenerated migrations committed unread. This skill steers the agent to the 2.0 style.

It focuses on session lifetime, loading strategy for relationships and the review of Alembic output before it reaches a database.

Example

you › Update our SQLAlchemy code to current patterns and fix the slow orders list

agent › Replaced the global scoped session with a per-request context manager, moved 14 Query calls to select(), and added selectinload(Order.lines), taking the list from 121 queries to 3. The Alembic revision had a column drop that was really a rename, so it was rewritten with alter_column.

Questions

Why does my object raise DetachedInstanceError?
It was loaded in a session that has closed, then a lazy attribute was read. Load the attribute eagerly or finish the work inside the session.
Can I trust Alembic autogenerate?
It is a draft. It detects added and dropped items but not renames, and it can miss defaults and constraints, so always review.
  • Database Connection Pooling

    SkillDatabases

    Free
  • Database Migration Safety

    SkillDatabases

    Free
  • Database Schema Design

    SkillDatabases

    Free
  • MongoDB Schema Modeling

    Skillmongodb

    Free