database-versioning

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Database Versioning Patterns

数据库版本控制模式

Version control strategies for database schemas and data across environments.

跨环境的数据库架构与数据版本控制策略。

Overview

概述

Tracking schema changes over time
Coordinating database changes across dev/staging/prod
Implementing database audit trails
Managing stored procedures and functions
Versioning reference data

追踪架构随时间的变更
跨开发/预发布/生产环境协调数据库变更
实现数据库审计追踪
管理存储过程与函数
参考数据版本控制

Schema Versioning Table

架构版本控制表

sql

CREATE TABLE schema_version (
    version_id SERIAL PRIMARY KEY,
    version_number VARCHAR(20) NOT NULL,
    description TEXT NOT NULL,
    applied_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    applied_by VARCHAR(100),
    execution_time_ms INTEGER,
    checksum VARCHAR(64),
    CONSTRAINT uq_version_number UNIQUE (version_number)
);

sql

CREATE TABLE schema_version (
    version_id SERIAL PRIMARY KEY,
    version_number VARCHAR(20) NOT NULL,
    description TEXT NOT NULL,
    applied_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    applied_by VARCHAR(100),
    execution_time_ms INTEGER,
    checksum VARCHAR(64),
    CONSTRAINT uq_version_number UNIQUE (version_number)
);

Semantic Versioning for Databases

数据库语义化版本控制

MAJOR.MINOR.PATCH

MAJOR: Breaking changes (drop tables, rename columns)
MINOR: Backward-compatible additions (new tables, nullable columns)
PATCH: Bug fixes, index changes, data migrations

MAJOR.MINOR.PATCH

MAJOR：破坏性变更（删除表、重命名列）
MINOR：向后兼容的新增（新表、可为空列）
PATCH：Bug修复、索引变更、数据迁移

Detailed Guides

详细指南

Audit trails: See references/audit-trails.md for row versioning, temporal tables, CDC
Environment coordination: See references/environment-coordination.md for multi-env flows, locks
Object versioning: See references/object-versioning.md for procedures, views, reference data
Migration testing: See references/migration-testing.md for pytest patterns

审计追踪：查看references/audit-trails.md了解行版本控制、时态表、CDC
环境协调：查看references/environment-coordination.md了解多环境流程、锁机制
对象版本控制：查看references/object-versioning.md了解存储过程、视图、参考数据
迁移测试：查看references/migration-testing.md了解pytest模式

Best Practices

最佳实践

Practice	Reason
Version everything	Full traceability
Immutable history	Audit compliance
Test rollbacks	Ensure recoverability
Environment parity	Consistent deployments
Checksum verification	Detect unauthorized changes

实践	原因
版本化所有内容	完整可追溯性
不可变历史记录	审计合规
测试回滚	确保可恢复性
环境一致性	一致的部署
校验和验证	检测未授权变更

Anti-Patterns

反模式

python

undefined

python

undefined

NEVER modify deployed migrations - create new migration instead

绝不要修改已部署的迁移 - 应创建新的迁移

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 -> 预发布 -> 生产

NEVER version sensitive data in migrations

绝不要在迁移中版本化敏感数据

op.bulk_insert(users, [{"password": "secret"}]) # Security risk!

undefined

op.bulk_insert(users, [{"password": "secret"}]) # 安全风险！

undefined

Capability Details

功能详情

schema-versioning

Keywords: schema version, database version, migration history Solves: Track schema changes, version history

关键词： schema版本、数据库版本、迁移历史 解决问题： 追踪架构变更、版本历史

temporal-queries

Keywords: temporal, point-in-time, history query Solves: Query historical data, time-travel queries

关键词： 时态、时间点查询、历史查询 解决问题： 查询历史数据、时间旅行查询

change-tracking

Keywords: cdc, change data capture, audit log Solves: Track all changes, audit compliance

关键词： cdc、变更数据捕获、审计日志 解决问题： 追踪所有变更、审计合规

environment-sync

Keywords: environment sync, migration coordination Solves: Sync across environments, coordinate deployments

关键词： 环境同步、迁移协调 解决问题： 跨环境同步、协调部署

database-versioning

Original

Translation

Database Versioning Patterns

数据库版本控制模式

Overview

概述

Schema Versioning Table

架构版本控制表

Semantic Versioning for Databases

数据库语义化版本控制

Detailed Guides

详细指南

Best Practices

最佳实践

Anti-Patterns

反模式

NEVER modify deployed migrations - create new migration instead

绝不要修改已部署的迁移 - 应创建新的迁移

NEVER delete migration history

绝不要删除迁移历史

NEVER skip environments

绝不要跳过环境

Always: local -> CI -> staging -> production

正确流程：本地 -> CI -> 预发布 -> 生产

NEVER version sensitive data in migrations

绝不要在迁移中版本化敏感数据

Capability Details

功能详情

schema-versioning

schema-versioning

temporal-queries

temporal-queries

change-tracking

change-tracking

environment-sync

environment-sync

Related Skills

相关技能