Database

Database Rules

1. Migration File Conventions

Naming Format

V{version}__{description}.sql

Migration Examples

V1__create_users_table.sql
V2__add_email_index_to_users.sql
V3__create_orders_table.sql
V4__add_status_column_to_orders.sql

Migration Rules

• Never modify a migration that has been applied to any environment
• Each migration must be idempotent where possible (use IF NOT EXISTS, IF EXISTS)
• Include both schema and essential seed data in migrations
• Test migrations against a copy of production data before applying

Migration Safety Checklist

Rollback Strategy

• Write corresponding rollback scripts for each migration
• For irreversible changes (drop column), document the decision and ensure backups exist
• Test rollback scripts in staging before production deployment

2. Query Performance

Index Guidelines

• Add indexes on columns used in WHERE, JOIN, ORDER BY
• Use composite indexes for multi-column queries (column order matters)
• Avoid over-indexing — each index slows down writes
• Monitor slow query logs to identify missing indexes

Composite Index Column Order

Index (a, b, c) supports:
  ✅ WHERE a = ?
  ✅ WHERE a = ? AND b = ?
  ✅ WHERE a = ? AND b = ? AND c = ?
  ✅ WHERE a = ? ORDER BY b
  ❌ WHERE b = ?           (leftmost prefix not satisfied)
  ❌ WHERE b = ? AND c = ? (leftmost prefix not satisfied)

• Place equality columns before range columns
• Place high-selectivity columns first

Query Best Practices

• Use pagination for list queries — never fetch unbounded result sets
• Avoid SELECT * — specify only needed columns for large tables
• Use EXISTS instead of COUNT for existence checks
• Avoid complex subqueries — prefer JOIN for readability and performance

Pagination Patterns

-- Offset-based (simple but slow for large offsets)
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 100;

-- Cursor-based (fast and consistent)
SELECT * FROM users WHERE id > :last_seen_id ORDER BY id LIMIT 20;

N+1 Query Detection

Symptom: N similar queries in logs for a single operation

-- 1 query to fetch users
SELECT * FROM users WHERE status = 'ACTIVE';

-- N queries to fetch each user's orders (BAD)
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
...

-- Fix: single join query
SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'ACTIVE';

3. Transaction Management Principles

General Rules

Scope Example

1. Read data (inside transaction or read-only)
2. Call external API (outside transaction)
3. Write result (separate transaction)

Isolation Levels

• Default to database's built-in isolation level (usually READ COMMITTED)
• Only increase isolation when business logic requires stronger guarantees
• Higher isolation = more locking = lower concurrency

Deadlock Prevention

• Always acquire locks in a consistent, predictable order
• Keep lock scope as small as possible
• Set statement/query timeouts to avoid indefinite waits
• Log and monitor deadlock occurrences

4. Data Integrity

Constraints

Rules

• Enforce data integrity at the database level, not just application level
• Application validations may be bypassed; database constraints are the last line of defense
• Use ON DELETE CASCADE cautiously — prefer explicit application-level deletion

5. Anti-Patterns

• Unbounded queries without pagination
• SELECT * on large tables
• N+1 query patterns (loop of individual queries)
• Long-running transactions with external calls
• Missing indexes on frequently queried columns
• Modifying applied migration files
• No slow query monitoring
• Using database as a message queue
• Storing large blobs in relational tables without evaluating object storage
• Missing foreign key constraints on related tables
• Using FLOAT/DOUBLE for monetary values (use DECIMAL/NUMERIC)

Additional References

• For MySQL-specific rules, see references/mysql.md
• For PostgreSQL-specific rules, see references/postgresql.md
• For concurrency control and reliability patterns, see references/concurrency-and-reliability.md