Database-related code must follow the specifications below.
Before coding
First design the data model, migration strategy, and rollback plan, then write code.
Migration management
- All schema changes go through migration files; do not modify the database manually
- Migrations must be rollback-able (up + down)
- Verify in staging before production migration
- Migration files are sorted by timestamp
Transaction handling
- Use transactions for multi-table write operations
- Minimize transaction scope to avoid long transactions locking tables
- Deadlock prevention: fixed locking order
- Fully roll back on failure, leaving no half-completed state
Index strategy
- Create indexes on query condition fields
- For composite indexes, pay attention to column order (leftmost prefix matching)
- Do not over-index—every index has a write cost
- Regularly check slow queries and optimize indexes
Data integrity
- Foreign key constraints ensure referential integrity
- NOT NULL + DEFAULT avoid dirty data
- Unique constraints prevent duplicates
- Soft delete vs hard delete: choose according to business requirements, but be consistent
Delivery checklist
- Schema changes go through migration files
- Use transactions for multi-table write operations
- Query condition fields have indexes
- No N+1 queries (coordinate with hello-perf)