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

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

SQLAlchemy & Alembic Expert Best Practices

SQLAlchemy & Alembic 专家级最佳实践

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

简洁、务实、有明确立场。仅涵盖编写生产级SQLAlchemy和Alembic代码所需的关键内容。

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

在以下场景中参考这些指南：

为架构变更编写Alembic迁移脚本
创建或修改SQLAlchemy模型
通过Alembic添加索引、约束或外键
审查数据库迁移代码以确保安全性
重构现有数据库架构
优化查询模式或数据库性能

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`

优先级	类别	影响程度	前缀
1	索引管理	关键-高	`only-concurrent-indexes` , `verify-query-patterns-are-indexed`
2	约束安全性	高	`unique-constraint` , `split-foreign-key` , `change-column-type`
3	优化	中	`split-check-constraint` , `limit-non-unique-index`
4	索引效率	低	`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

only-concurrent-indexes

- 索引操作始终在自动提交块中使用

postgresql_concurrently=True

```
verify-query-patterns-are-indexed
```
- 确保SQLAlchemy查询已定义合适的索引
```
unique-constraint
```
- 将唯一约束创建拆分为并发索引+约束两个步骤
```
split-foreign-key
```
- 先添加带
```
NOT VALID
```
的外键，再单独进行验证
```
change-column-type
```
- 使用多步骤方法修改列类型，避免表锁
```
split-check-constraint
```
- 先添加带
```
NOT VALID
```
的检查约束，再单独进行验证
```
limit-non-unique-index
```
- 为提升效率，将非唯一索引限制为最多三列
```
ensure-index-not-covered
```
- 避免被复合索引已覆盖的冗余索引

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

阅读单个规则文件以获取详细说明和代码示例：

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

每个规则文件包含：

简要说明该规则的重要性
影响级别及描述
错误的SQLAlchemy/Alembic示例及解释
遵循最佳实践的正确实现方式
安全迁移的额外相关内容