sqlalchemy-alembic-expert-best-practices-code-review

SKILL.md

SQLAlchemy & Alembic Expert Best Practices

Simple, pragmatic, opinionated. Only what matters for writing production-grade SQLAlchemy and Alembic code.

When to Apply

Reference these guidelines when:

Writing Alembic migrations for schema changes

Creating or modifying SQLAlchemy models

Adding indexes, constraints, or foreign keys via Alembic

Reviewing database migration code for safety

Refactoring existing database schemas

Optimizing query patterns or database performance

Rule Categories by Priority

Priority
Category
Impact
Prefix

1
Index Management
CRITICAL-HIGH
only-concurrent-indexes, verify-query-patterns-are-indexed

2
Constraint Safety
HIGH
unique-constraint, split-foreign-key, change-column-type

3
Optimization
MEDIUM
split-check-constraint, limit-non-unique-index

4
Index Efficiency
LOW
ensure-index-not-covered

Quick Reference

only-concurrent-indexes - Always use postgresql_concurrently=True with autocommit blocks for index operations

verify-query-patterns-are-indexed - Ensure SQLAlchemy queries have appropriate indexes defined

unique-constraint - Split unique constraint creation into concurrent index + constraint steps

split-foreign-key - Add foreign keys with NOT VALID first, then validate separately

change-column-type - Use multi-step approach for column type changes to avoid table locks

split-check-constraint - Add check constraints with NOT VALID first, then validate separately

limit-non-unique-index - Limit non-unique indexes to maximum three columns for efficiency

ensure-index-not-covered - Prevent redundant indexes that are already covered by composite indexes

How to Use

Read individual rule files for detailed explanations and code examples:

rules/only-concurrent-indexes.md
rules/verify-query-patterns-are-indexed.md
rules/unique-constraint.md
rules/split-foreign-key.md
rules/change-column-type.md
rules/split-check-constraint.md
rules/limit-non-unique-index.md
rules/ensure-index-not-covered.md

Each rule file contains:

Brief explanation of why it matters

Impact level and description

Incorrect SQLAlchemy/Alembic example with explanation

Correct implementation with best practices

Additional context for safe migrations