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