db-review

Database Review (DB Review)

The cost of fixing database issues grows exponentially with each stage: code review < migration review < post-launch troubleshooting < data repair. Find them early, fix them early.

---

Step 1: Determine the review scope and mode

收到审查请求
  │
  ├─ 代码审查模式（改了数据库相关代码）
  │   └─ 检查 SQL 质量 + 查询性能 + 锁风险
  │
  ├─ Migration 审查模式（新增/修改 migration 文件）
  │   └─ 检查安全性 + Schema 漂移 + 回滚方案
  │
  └─ 全量审查（上线前 / 用户主动要求）
      └─ 代码审查 + Migration 审查 + Schema 一致性

Tech stack identification

Automatically detect the database and ORM used by the project:

Detection target	Detection method
Prisma	prisma/schema.prisma、@prisma/client
TypeORM	typeorm dependencies,@Entity() decorators
Sequelize	sequelize dependencies,.define() calls
Drizzle	drizzle-orm dependencies,drizzle.config.ts
SQLAlchemy	sqlalchemy dependencies,Base.metadata
Raw SQL	.sql files,query() / execute() calls
Database type	Connection strings, driver dependencies (pg/mysql2/better-sqlite3）

---

Step 2: Code review — SQL quality and performance

Check dimension 1: Slow SQL patterns

Check each of the following anti-patterns one by one, and mark a risk level for each finding:

Anti-pattern	Problem	Correct approach
SELECT *	Fetches unneeded columns, wasting IO and memory	Explicitly list the fields needed
Full-table query with no WHERE	Directly overwhelms the DB when the data volume is large	Add filter conditions and paginate the query
Using a function on a column in WHERE	WHERE YEAR(created_at) = 2026 cannot use an index	Change to a range query WHERE created_at >= '2026-01-01'
LIKE '%keyword%'	A leading wildcard cannot use an index	Consider a full-text index or a search engine
OR condition spans columns	The optimizer has difficulty using an index	Split into UNION or adjust the indexing strategy
Subquery in WHERE	WHERE id IN (SELECT ...) may execute row by row	Change to JOIN
ORDER BY with no index support	Sorting a large table triggers filesort	Ensure the sort fields are indexed
DISTINCT masks duplicates	Usually a sign that the JOIN was written incorrectly	Check whether the JOIN conditions are correct

Review method：

1. Search all SQL statements (raw queries, ORM raw queries, query builder)
2. For each SQL statement, determine whether it hits any of the above anti-patterns
3. ORM calls must be checked too——findAll() with no conditions added is equivalent to SELECT * a full table scan

Check dimension 2: N+1 queries

This is the most common performance killer in ORM projects.

# 反模式：循环中查询
users = User.findAll()
for user in users:
    orders = Order.findAll({ where: { userId: user.id } })  # N 次查询

# 正确：预加载/JOIN
users = User.findAll({ include: [Order] })  # 1 次查询

How to check：

1. Search for database calls inside loop bodies (for/forEach/map in find/query/select）
2. Check whether the ORM's association queries use eager loading (include/joinedload/with）
3. Check whether data loading in GraphQL resolvers uses DataLoader

Check dimension 3: Table lock risks

The following operations may cause long table locks on large tables：

Operation	MySQL risk	PostgreSQL risk	Safe alternative
ALTER TABLE ADD COLUMN (has default value)	Table lock rewrite (MySQL < 8.0)	Mostly instant on 8.0+	Use pt-online-schema-change for MySQL < 8.0
ALTER TABLE ADD INDEX	Table lock	Supported CONCURRENTLY	PG: CREATE INDEX CONCURRENTLY
ALTER TABLE MODIFY COLUMN Type change	Table lock rewrite	May lock table	Stepwise migration: new column → sync data → switch
UPDATE Large batch without WHERE	Row lock escalates to table lock	Many row locks	Batch update (1000-5000 rows per batch)
DELETE Large batch	Same as above	Same as above	Batch delete + brief sleep
DDL in long transaction	Lock wait, deadlock	Lock wait	DDL in independent transaction, short transaction

Review method：

1. Check ALTER TABLE operations in migration files
2. Evaluate the data volume of the target table (if available)
3. Mark DDL operations on large tables (>100,000 rows) ashigh risk

Check dimension 4: Index review

Check item	Problem signal
Missing index	Columns in WHERE/JOIN/ORDER BY have no index
Redundant index	INDEX(a) and INDEX(a, b) coexist (the former is contained by the latter)
Too many indexes	>6 indexes on a single table, affecting write performance
Index column order	Composite index column order does not match query patterns (leftmost prefix principle)
Low-selectivity index	Indexing low-cardinality columns such as boolean/status (usually ineffective)
Unused index	Index exists but query does not use it (function wrapping, type mismatch)

Review method：

1. Read index definitions in schema/migration
2. Check index coverage against WHERE/JOIN/ORDER BY in query statements
3. ORM's @Index()/index: true must also be checked

Check dimension 5: Transactions and concurrency

Check item	Concern
Transaction scope	Is the transaction too large? Does it include unnecessary operations?
Deadlock risk	Do multiple transactions operate on the same set of tables in different orders?
Isolation level	Is an excessively high isolation level (SERIALIZABLE) used?
Connection leak	Are transactions/connections properly released on exception paths?
Optimistic lock	Do concurrent update scenarios have version number/optimistic lock protection?

---

Step 3: Migration review — safety and consistency

3.1 Destructive operation check

The following operations are irreversible or carry data loss risk, and must be marked as high risk:

Operation	Risk	Safe practice
DROP TABLE	Permanent data loss	Back up first, confirm no references, keep a rollback window
DROP COLUMN	Column data loss	Confirm code references are removed before dropping the column
RENAME TABLE/COLUMN	Broken code references	Step by step: create new → sync → switch → clean up old
TRUNCATE	Data wiped	Should not appear in migration
ALTER COLUMN Narrow the type	Data truncated	First check whether existing data overflows
NOT NULL Constraint (existing data column)	Null rows error	Fill in default values first, then add the constraint

Review method：

1. Read the migration file line by line, marking all DROP/RENAME/ALTER operations
2. Every destructive operation must have a correspondingrollback plan（down migration）
3. Check whether the down migration can actually roll back (not an empty function)

3.2 Schema drift detection

This is the problem you keep running into: local and server schemas are inconsistent, with missing fields.

Sources of drift：

1. 手动改了数据库但没写 migration（最常见）
2. migration 执行顺序不一致（分支合并后）
3. migration 只跑了一半（报错后手动修了但没记录）
4. ORM 的 model 定义和 migration 不同步
5. 多人开发时 migration 文件冲突

Check process：

Step 1: 收集当前 Schema 定义来源
  ├─ ORM model/entity 定义（代码中的"应该是什么"）
  ├─ Migration 文件链（"变更历史"）
  └─ 数据库实际状态（如果能连接）

Step 2: 交叉比对
  ├─ Model vs Migration：model 里的字段/类型/约束是否都有对应的 migration？
  ├─ Migration 完整性：migration 链是否连续？有没有遗漏？
  └─ 新增字段检查：最近加的字段有 migration 吗？默认值/可空设置对吗？

Step 3: 输出不一致清单
  每条记录：字段名、model 中的定义、migration 中的定义、差异描述

Specific check items：

Check item	Method
Model and Migration fields are consistent	Traverse all model fields and confirm that each field has a corresponding CREATE/ALTER in the migration chain
Types are consistent	The type in the model (String/Int/DateTime) matches the SQL type in the migration
Nullability is consistent	Fields marked with optional/nullable in the model do not have NOT NULL
Default values are consistent	Fields that have @default() in the model have DEFAULT
Indexes are consistent	Fields marked with @index/@unique in the model have a corresponding INDEX in the migration
Relationships are consistent	Foreign key relationships in the model have a corresponding FOREIGN KEY in the migration
Migration timeline is continuous	Sort by timestamp and check for gaps or conflicts

3.3 Rollback plan check

Every migration must have an executable rollback plan:

Check item	Requirement
down/rollback function exists	Cannot be an empty function or throw new Error('not implemented')
down function logic is correct	up columns added in ,down must be deleted in ;up types changed in ,down must be changed back in
Data recovery	The down of a destructive operation needs to explain the data recovery strategy (even if it cannot be fully automatically recovered)

3.4 Migration best practices

Practice	Requirement
Single responsibility	A migration does only one thing (adding a table, adding a column, and adding an index are separate)
Repeatable execution	migrations should be idempotent, and repeated runs should not error (IF NOT EXISTS）
Separate data migration	Put schema changes and data population in different migrations
Naming convention	The file name should reflect the operation content (add_email_to_users rather than migration_042）

---

Step 4: Risk grading and output

Risk level

Level	Standard	Handling requirement
High risk	Data loss, table lock exceeding 30s, schema drift (known inconsistency), no rollback plan	Must fix, provide a concrete plan
Medium risk	Performance hazards (N+1, missing indexes), incomplete rollback plan, potential concurrency issues	Recommended fix, provide direction
Low risk	Missing best practices (naming, comments, redundant indexes), small table operations	Record, do not block

Output format

Quick review(terminal output):

## DB Review 结果

🔴 高危 x N | 🟡 中危 x N | 🟢 低危 x N

### 高危
1. [锁表风险] migrations/20260409_add_index.sql:15 — 大表加索引未用 CONCURRENTLY
   → 修复：`CREATE INDEX CONCURRENTLY idx_users_email ON users(email);`

### 中危
1. [N+1] src/services/order.ts:42 — 循环内查询用户信息
   → 修复：使用 include/joinedload 预加载

### Schema 漂移
- ⚠️ User.phone: model 中存在(String, optional)，但无对应 migration
- ⚠️ Order.discount: model 类型 Decimal，migration 中为 Float

Full review, generate a report to docs/audits/YYYY-MM-DD-db-review.md。

---

Integration with other skills

代码开发中
  │
  ├─ 改了数据库代码？→ task-finish 自检时提示跑 db-review（代码审查模式）
  ├─ 新增 migration？→ 提交前跑 db-review（migration 审查模式）
  │
  ↓ 上线前
  ├─ security-audit 审查注入防护（SQL 注入维度）
  └─ db-review 全量审查（Schema 漂移 + 锁表 + 性能）

• perf-profiling: after discovering a slow DB query, hand it off to db-review for in-depth SQL-level analysis
• security-audit: SQL injection is a security issue, handled by security-audit; query performance is handled by db-review
• task-finish: when changes involve migration files, prompt to run db-review

---

Notes

• Understand the business before reviewing: index suggestions divorced from the business are meaningless. First figure out query frequency and data volume
• Do not be dogmatic about small tables: a configuration table with a few hundred rows does not need agonizing over index optimization
• ORM is not an excuse: SQL generated by an ORM can also be terrible; when necessary, look at the actual generated SQL
• Environment differences: with local SQLite + production PostgreSQL, migration syntax differences are a common source of drift
• Does not replace a DBA: complex architecture-level issues such as sharding and primary-replica lag require DBA involvement