Inbox/Go项目实战/01_数据模型建立/规范数据库设计 & 变更管理及工程流操作.md

tags, aliases, date created, date modified

tags	aliases	date created	date modified
	🛠️ Database Engineering & Migration Standard (v1.0)	星期日, 十二月 7日 2025, 10:31:59 晚上	星期二, 十二月 9日 2025, 10:14:44 晚上

🛠️ Database Engineering & Migration Standard (v1.0)

文档用途: 规范数据库设计、变更管理及工程流操作。

适用范围: 所有涉及 Schema 变更的后端开发任务。

核心原则: Code First (Logic) but SQL First (Schema). 严禁生产环境使用 ORM 自动建表。

---

1. 基础设施与工具链 (Infrastructure & Tools)

本项目采用 “容器化数据库 + 版本化迁移工具” 的架构。

组件	选型	说明
Database	PostgreSQL 15+	运行于 Docker 容器中，保证开发/生产环境一致。
Schema Mgmt	Golang-Migrate	CLI 工具，用于生成和执行版本化 SQL 脚本。
GUI Client	Navicat	推荐 Navicat / DataGrip / DBeaver，仅用于设计和验证。
Automation	Make	封装常用命令，屏蔽底层复杂参数。

1.1 目录结构规范

Plaintext

project-root/
├── migrations/                 # [Source of Truth] 存放所有 SQL 变更文件
│   ├── 000001_init_users.up.sql
│   └── 000001_init_users.down.sql
├── internal/
│   └── {domain}/               # 领域包
│       └── entity.go           # [Code Mapping] GORM 结构体定义
├── docker-compose.yml          # 定义本地 DB 容器
└── Makefile                    # 集成迁移命令

---

2. 数据库设计规范 (Design Standards)

2.1 命名约定

• 表名: 必须使用复数形式，snake_case (e.g., users, order_items).
• 字段名: 全小写，snake_case (e.g., created_at, user_id).
• 索引名:
  • 普通索引: idx_tablename_column
  • 唯一索引: uniq_tablename_column
• 外键名: fk_tablename_ref_tablename

2.2 关键字段约束

所有业务表必须包含以下基础字段：

id          BIGSERIAL PRIMARY KEY, -- 或 UUID
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at  TIMESTAMPTZ            -- 仅在需要软删除时添加

2.3 设计禁忌

1. 严禁 使用物理外键的级联删除 (ON DELETE CASCADE)，除非是关联性极强的子表（如文章标签关联）。核心业务数据必须使用 ON DELETE RESTRICT。
2. 严禁 在涉及金额的字段使用 FLOAT 或 DOUBLE，必须使用 DECIMAL 或 BIGINT (分)。
3. 严禁 将 NULL 作为布尔值的第三种状态。布尔字段必须设置 NOT NULL DEFAULT FALSE。

---

3. 标准作业流程 (SOP)

开发人员需严格遵循以下 5 步闭环 进行数据库变更：

Step 1: 启动环境

确保本地 Docker 数据库正在运行。

make network  # 对应 docker-compose up -d

Step 2: 创建迁移文件 (Create)

使用 Makefile 生成成对的 .sql 文件（up/down）。

• name 参数应简短描述变更内容（如 add_avatar_to_users）。

make new_migration name=init_schema
# 输出:
# Created migrations/000001_init_schema.up.sql
# Created migrations/000001_init_schema.down.sql

Step 3: 编写 SQL (Edit)

• UP 文件: 填入 CREATE TABLE, ALTER TABLE, CREATE INDEX 等正向操作。
  • 技巧: 可在 GUI 工具中设计好表结构，复制生成的 DDL 语句粘贴至此。
• DOWN 文件: 填入对应的回滚操作（如 DROP TABLE, DROP INDEX）。

Step 4: 执行变更 (Apply)

将 SQL 应用到本地数据库。

make migrate_up

验证: 使用 GUI 工具连接数据库，确认表结构已更新。

Step 5: 代码映射 (Mapping)

在 internal/{domain}/entity.go 中编写对应的 Go Struct。

• 确保 gorm tag 与数据库定义一致。
• 确保 json tag 符合 API 契约。

---

4. 自动化配置 (Automation)

将以下内容固化到项目根目录的 Makefile 中。

注意: 确保 DB_DSN 与 docker-compose.yml 中的配置完全一致。

# ==============================================================================
# Database & Migration Logic
# ==============================================================================

# Database Connection String
# 格式: postgres://user:password@host:port/dbname?sslmode=disable
DB_DSN := postgres://postgres:secret@localhost:5432/cms_core?sslmode=disable

.PHONY: network new_migration migrate_up migrate_down migrate_force

# 1. 启动本地环境
network:
	docker-compose up -d

# 2. 创建新的迁移文件 (Usage: make new_migration name=create_users)
new_migration:
	@if [ -z "$(name)" ]; then echo "Error: name is required"; exit 1; fi
	migrate create -ext sql -dir migrations -seq $(name)

# 3. 执行所有未执行的迁移 (Up)
migrate_up:
	migrate -path migrations -database "$(DB_DSN)" up

# 4. 回滚上一次迁移 (Down 1 step)
migrate_down:
	migrate -path migrations -database "$(DB_DSN)" down 1

# 5. 强制修复版本 (当 dirty database 时使用, version 为具体的版本号)
migrate_force:
	migrate -path migrations -database "$(DB_DSN)" force $(version)

---

5. 故障排查 (Troubleshooting)

Q: 执行 migrate_up 时报错 "Dirty database version x".

• 原因: 上一次迁移执行到一半失败了（可能是 SQL 语法错误），导致版本锁死。

• 解决:

  1. 手动修复 SQL 文件中的语法错误。
  2. 执行 make migrate_force version=x (x 是失败前的那个版本号)。
  3. 再次执行 make migrate_up。

Q: 多人协作时产生版本冲突。

• 现象: 你有一个 0003_add_xx.up.sql，同事提交代码后也有一个 0003_add_yy.up.sql。
• 解决: 重命名你的迁移文件编号为 0004，确保序列号在时间轴上是递增且唯一的。