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

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