database-patterns

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Database Patterns

数据库模式指南

Comprehensive patterns for database migrations, schema design, and version management. Each category has individual rule files in

rules/

loaded on-demand.

涵盖数据库迁移、Schema设计和版本管理的全面模式指南。每个分类的规则文件单独存放在

rules/

目录中，可按需加载。

Quick Reference

快速参考

Category	Rules	Impact	When to Use
Alembic Migrations	3	CRITICAL	Autogenerate, data migrations, branch management
Schema Design	3	HIGH	Normalization, indexing strategies, NoSQL patterns
Versioning	3	HIGH	Changelogs, rollback plans, schema drift detection
Zero-Downtime Migration	2	CRITICAL	Expand-contract, pgroll, rollback monitoring

Total: 12 rules across 5 categories

分类	规则数量	影响级别	适用场景
Alembic迁移	3	关键	自动生成迁移、数据迁移、分支管理
Schema设计	3	高	数据规范化、索引策略、NoSQL模式
版本管理	3	高	变更日志、回滚计划、Schema漂移检测
零停机迁移	2	关键	扩展-收缩模式、pgroll、回滚监控

| 数据库选型 | 1 | 高 | 数据库选型、PostgreSQL vs MongoDB、成本分析 |

总计：5个分类，共12条规则

Quick Start

快速入门

python

undefined

python

undefined

Alembic: Auto-generate migration from model changes

Alembic：根据模型变更自动生成迁移

alembic revision --autogenerate -m "add user preferences"

def upgrade() -> None: op.add_column('users', sa.Column('org_id', UUID(as_uuid=True), nullable=True)) op.execute("UPDATE users SET org_id = 'default-org-uuid' WHERE org_id IS NULL")

def downgrade() -> None: op.drop_column('users', 'org_id')


```sql
-- Schema: Normalization to 3NF with proper indexing
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL REFERENCES customers(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

def upgrade() -> None: op.add_column('users', sa.Column('org_id', UUID(as_uuid=True), nullable=True)) op.execute("UPDATE users SET org_id = 'default-org-uuid' WHERE org_id IS NULL")

def downgrade() -> None: op.drop_column('users', 'org_id')


```sql
-- Schema：规范化至3NF并配置合理索引
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL REFERENCES customers(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Alembic Migrations

Alembic迁移

Migration management with Alembic for SQLAlchemy 2.0 async applications.

Rule	File	Key Pattern
Autogenerate	`rules/alembic-autogenerate.md`	Auto-generate from models, async env.py, review workflow
Data Migration	`rules/alembic-data-migration.md`	Batch backfill, two-phase NOT NULL, zero-downtime
Branching	`rules/alembic-branching.md`	Feature branches, merge migrations, conflict resolution

适用于SQLAlchemy 2.0异步应用的Alembic迁移管理方案。

规则	文件	核心模式
自动生成	`rules/alembic-autogenerate.md`	从模型自动生成、异步env.py、审核流程
数据迁移	`rules/alembic-data-migration.md`	批量回填、两阶段非空设置、零停机
分支管理	`rules/alembic-branching.md`	功能分支、迁移合并、冲突解决

Schema Design

Schema设计

SQL and NoSQL schema design with normalization, indexing, and constraint patterns.

Rule	File	Key Pattern
Normalization	`rules/schema-normalization.md`	1NF-3NF, when to denormalize, JSON vs normalized
Indexing	`rules/schema-indexing.md`	B-tree, GIN, HNSW, partial/covering indexes
NoSQL Patterns	`rules/schema-nosql.md`	Embed vs reference, document design, sharding

包含SQL和NoSQL的Schema设计方案，涵盖规范化、索引和约束模式。

规则	文件	核心模式
数据规范化	`rules/schema-normalization.md`	1NF-3NF、反规范化场景、JSON vs 规范化结构
索引策略	`rules/schema-indexing.md`	B-tree、GIN、HNSW、部分/覆盖索引
NoSQL模式	`rules/schema-nosql.md`	嵌入 vs 引用、文档设计、分片

Versioning

版本管理

Database version control and change management across environments.

Rule	File	Key Pattern
Changelog	`rules/versioning-changelog.md`	Schema version table, semantic versioning, audit trails
Rollback	`rules/versioning-rollback.md`	Rollback testing, destructive rollback docs, CI verification
Drift Detection	`rules/versioning-drift.md`	Environment sync, checksum verification, migration locks

跨环境的数据库版本控制与变更管理方案。

规则	文件	核心模式
变更日志	`rules/versioning-changelog.md`	Schema版本表、语义化版本、审计追踪
回滚机制	`rules/versioning-rollback.md`	回滚测试、破坏性回滚文档、CI验证
漂移检测	`rules/versioning-drift.md`	环境同步、校验和验证、迁移锁

Database Selection

数据库选型

Decision frameworks for choosing the right database. Default: PostgreSQL.

Rule	File	Key Pattern
Selection Guide	`rules/db-selection.md`	PostgreSQL-first, tier-based matrix, anti-patterns

数据库选型的决策框架。默认推荐：PostgreSQL。

规则	文件	核心模式
选型指南	`rules/db-selection.md`	PostgreSQL优先、分层矩阵、反模式规避

Key Decisions

关键决策建议

Decision	Recommendation	Rationale
Async dialect	`postgresql+asyncpg`	Native async support for SQLAlchemy 2.0
NOT NULL column	Two-phase: nullable first, then alter	Avoids locking, backward compatible
Large table index	`CREATE INDEX CONCURRENTLY`	Zero-downtime, no table locks
Normalization target	3NF for OLTP	Reduces redundancy while maintaining query performance
Primary key strategy	UUID for distributed, INT for single-DB	Context-appropriate key generation
Soft deletes	`deleted_at` timestamp column	Preserves audit trail, enables recovery
Migration granularity	One logical change per file	Easier rollback and debugging
Production deployment	Generate SQL, review, then apply	Never auto-run in production

决策项	推荐方案	理由
异步方言	`postgresql+asyncpg`	为SQLAlchemy 2.0提供原生异步支持
非空列添加	两阶段：先设为可空，再修改为非空	避免锁表，保持向后兼容
大表索引创建	`CREATE INDEX CONCURRENTLY`	零停机，无表锁
规范化目标	OLTP场景采用3NF	在减少冗余的同时保证查询性能
主键策略	分布式场景用UUID，单库场景用INT	根据场景选择合适的主键生成方式
软删除	使用 `deleted_at` 时间戳列	保留审计轨迹，支持数据恢复
迁移粒度	每个文件对应一个逻辑变更	便于回滚和调试
生产环境部署	先生成SQL、审核，再执行	绝对不要在生产环境自动执行迁移

Anti-Patterns (FORBIDDEN)

反模式（禁止使用）

python

undefined

python

undefined

NEVER: Add NOT NULL without default or two-phase approach

绝对禁止：未设置默认值或未采用两阶段方式直接添加非空列

op.add_column('users', sa.Column('org_id', UUID, nullable=False)) # LOCKS TABLE!

op.add_column('users', sa.Column('org_id', UUID, nullable=False)) # 会锁表！

NEVER: Use blocking index creation on large tables

绝对禁止：在大表上使用阻塞式索引创建

op.create_index('idx_large', 'big_table', ['col']) # Use CONCURRENTLY

op.create_index('idx_large', 'big_table', ['col']) # 请使用CONCURRENTLY

NEVER: Skip downgrade implementation

绝对禁止：跳过downgrade实现

def downgrade(): pass # WRONG - implement proper rollback

def downgrade(): pass # 错误 - 需实现完整的回滚逻辑

NEVER: Modify migration after deployment - create new migration instead

绝对禁止：部署后修改已存在的迁移文件 - 应创建新的迁移文件

NEVER: Run migrations automatically in production

绝对禁止：在生产环境自动执行迁移

Use: alembic upgrade head --sql > review.sql

正确做法：alembic upgrade head --sql > review.sql

NEVER: Run CONCURRENTLY inside transaction

绝对禁止：在事务中执行CONCURRENTLY

op.execute("BEGIN; CREATE INDEX CONCURRENTLY ...; COMMIT;") # FAILS

op.execute("BEGIN; CREATE INDEX CONCURRENTLY ...; COMMIT;") # 执行失败

NEVER: Delete migration history

绝对禁止：删除迁移历史

command.stamp(alembic_config, "head") # Loses history

command.stamp(alembic_config, "head") # 会丢失历史记录

NEVER: Skip environments (Always: local -> CI -> staging -> production)

绝对禁止：跳过环境验证（必须遵循：本地 -> CI -> 预发布 -> 生产）

undefined

undefined

Detailed Documentation

详细文档资源

Resource	Description
references/	Advanced patterns: Alembic, normalization, migration, audit, environment, versioning
checklists/	Migration deployment and schema design checklists
examples/	Complete migration examples, schema examples
scripts/	Migration templates, model change detector

资源	描述
references/	高级模式：Alembic、规范化、迁移、审计、环境、版本管理
checklists/	迁移部署和Schema设计检查清单
examples/	完整的迁移示例、Schema示例
scripts/	迁移模板、模型变更检测器

Zero-Downtime Migration

零停机迁移

Safe database schema changes without downtime using expand-contract pattern and online schema changes.

Rule	File	Key Pattern
Expand-Contract	`rules/migration-zero-downtime.md`	Expand phase, backfill, contract phase, pgroll automation
Rollback & Monitoring	`rules/migration-rollback.md`	pgroll rollback, lock monitoring, replication lag, backfill progress

采用扩展-收缩模式和在线Schema变更，实现无停机的安全数据库Schema变更。

规则	文件	核心模式
扩展-收缩模式	`rules/migration-zero-downtime.md`	扩展阶段、数据回填、收缩阶段、pgroll自动化
回滚与监控	`rules/migration-rollback.md`	pgroll回滚、锁监控、复制延迟、回填进度追踪

database-patterns

Original

Translation

Database Patterns

数据库模式指南

Quick Reference

快速参考

Quick Start

快速入门

Alembic: Auto-generate migration from model changes

Alembic：根据模型变更自动生成迁移

alembic revision --autogenerate -m "add user preferences"

alembic revision --autogenerate -m "add user preferences"

Alembic Migrations

Alembic迁移

Schema Design

Schema设计

Versioning

版本管理

Database Selection

数据库选型

Key Decisions

关键决策建议

Anti-Patterns (FORBIDDEN)

反模式（禁止使用）

NEVER: Add NOT NULL without default or two-phase approach

绝对禁止：未设置默认值或未采用两阶段方式直接添加非空列

NEVER: Use blocking index creation on large tables

绝对禁止：在大表上使用阻塞式索引创建

NEVER: Skip downgrade implementation

绝对禁止：跳过downgrade实现

NEVER: Modify migration after deployment - create new migration instead

绝对禁止：部署后修改已存在的迁移文件 - 应创建新的迁移文件

NEVER: Run migrations automatically in production

绝对禁止：在生产环境自动执行迁移

Use: alembic upgrade head --sql > review.sql

正确做法：alembic upgrade head --sql > review.sql

NEVER: Run CONCURRENTLY inside transaction

绝对禁止：在事务中执行CONCURRENTLY

NEVER: Delete migration history

绝对禁止：删除迁移历史

NEVER: Skip environments (Always: local -> CI -> staging -> production)

绝对禁止：跳过环境验证（必须遵循：本地 -> CI -> 预发布 -> 生产）

Detailed Documentation

详细文档资源

Zero-Downtime Migration

零停机迁移

Related Skills

相关技能