database-workflow

# Database Workflow Language-agnostic guidelines for database design, migrations, schema management, ORM patterns, and query optimization. ## CRITICAL: No Premature Optimization **Default to simplicity. Optimize only when you have measured evidence of a problem.** - Don't add indexes "just in case" - Don't over-normalize without understanding query patterns - Don't implement complex caching without profiling first - Measure before optimizing (EXPLAIN, query logs, monitoring) ## Migration Strategies ### Version Control for Schema Changes **Treat migrations as code:** - Store in version control alongside application code - Timestamp or sequential numbering (001, 002, 003...) - Atomic, single-responsibility changes - Document WHY in migration files, not just WHAT **Naming convention:** ``` migrations/ ├── 001_create_users_table.sql ├── 002_add_email_index.sql ├── 003_create_orders_table.sql └── 004_add_user_fk_to_orders.sql ``` ### Up/Down Migrations **Every migration must be reversible:** ```sql -- UP: Create table CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, username VARCHAR(255) UNIQUE NOT NULL, email VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- DOWN: Drop table DROP TABLE users; ``` **Complex reversals require care:** ```sql -- UP: Add constraint ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id); -- DOWN: Remove constraint (PostgreSQL) ALTER TABLE orders DROP CONSTRAINT fk_user; ``` ### Idempotent Migrations **Migrations must be safely re-runnable:** ```sql -- ✅ Good: Idempotent (safe to run multiple times) CREATE TABLE IF NOT EXISTS users (...); CREATE INDEX IF NOT EXISTS idx_users_email ON users(email); -- ❌ Bad: Fails if run twice CREATE TABLE users (...); CREATE INDEX idx_users_email ON users(email); ``` ### Rollback Strategies **Two approaches:** 1. **Down migrations (reversible):** - Run DOWN SQL to undo changes - Works if migration is reversible - Better for critical systems 2. **Snapshot migrations (not reversible):** - Never roll back; always migrate forward - Create new migration to fix issues - Simpler, safer in practice - Better for high-availability systems **Best practice:** Design migrations to be reversible when possible, but plan for forward-only rollbacks in production. ### Migration Naming Conventions **Use descriptive, action-oriented names:** ``` ✅ Good: - 001_create_users_table - 002_add_email_unique_constraint - 003_create_index_users_email - 004_rename_column_user_id_to_author_id - 005_add_soft_delete_columns ❌ Bad: - 001_update - 002_fix - 003_schema_change - 004_v2 ``` **Include timestamp + sequence:** ``` 2024_11_17_001_create_users_table.sql 2024_11_17_002_add_email_index.sql ``` ## Schema Design Principles ### Normalization (1NF, 2NF, 3NF) **First Normal Form (1NF):** - Eliminate repeating groups - All columns contain atomic (non-divisible) values - Each row is unique ```sql -- ❌ NOT 1NF: phone_numbers is a repeating group CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255), phone_numbers VARCHAR(255) -- "555-1234, 555-5678" ); -- ✅ 1NF: Separate table for phone numbers CREATE TABLE user_phones ( id BIGSERIAL PRIMARY KEY, user_id BIGINT REFERENCES users(id), phone_number VARCHAR(20) ); ``` **Second Normal Form (2NF):** - Meets 1NF - Remove partial dependencies (non-key columns depend on ALL of primary key) ```sql -- ❌ NOT 2NF: course_name depends only on course_id, not on (student_id, course_id) CREATE TABLE enrollments ( student_id BIGINT, course_id BIGINT, course_name VARCHAR(255), -- Should be in courses table grade CHAR(1), PRIMARY KEY (student_id, course_id) ); -- ✅ 2NF: Move course_name to separate table CREATE TABLE courses ( id BIGINT PRIMARY KEY, name VARCHAR(255) ); CREATE TABLE enrollments ( student_id BIGINT, course_id BIGINT REFERENCES courses(id), grade CHAR(1), PRIMARY KEY (student_id, course_id) ); ``` **Third Normal Form (3NF):** - Meets 2NF - Remove transitive dependencies (non-key columns don't depend on other non-key columns) ```sql -- ❌ NOT 3NF: city and state depend on zip_code, not on user_id CREATE TABLE users ( id BIGINT PRIMARY KEY, name VARCHAR(255), zip_code VARCHAR(5), city VARCHAR(100), state CHAR(2) ); -- ✅ 3NF: Move location info to separate table CREATE TABLE zip_codes ( code VARCHAR(5) PRIMARY KEY, city VARCHAR(100), state CHAR(2) ); CREATE TABLE users ( id BIGINT PRIMARY KEY, name VARCHAR(255), zip_code VARCHAR(5) REFERENCES zip_codes(code) ); ``` ### Denormalization (When and Why) **Denormalize when:** - Query patterns are read-heavy (much more than writes) - Measurement shows join performance is a bottleneck - Reporting queries need fast access to aggregated data - Cache invalidation is simpler than join performance **Common denormalization patterns:** 1. **Stored aggregates:** ```sql -- Track order count on user without JOIN CREATE TABLE users ( id BIGINT PRIMARY KEY, name VARCHAR(255), order_count INT DEFAULT 0 ); -- Keep in sync with trigger or application code ``` 2. **Purposeful redundancy:** ```sql -- Store user email on order to avoid JOIN at read time CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT REFERENCES users(id), user_email VARCHAR(255), -- Denormalized for reporting total_amount DECIMAL(10,2) ); ``` 3. **Pre-computed views:** ```sql -- Materialized view for dashboards CREATE MATERIALIZED VIEW monthly_sales AS SELECT DATE_TRUNC('month', created_at) as month, COUNT(*) as order_count, SUM(total_amount) as total_revenue FROM orders GROUP BY DATE_TRUNC('month', created_at); -- Refresh periodically, not on every insert REFRESH MATERIALIZED VIEW monthly_sales; ``` ### Indexing Strategies **Index only when needed. Measure first.** ```sql -- ✅ Create index for frequently searched columns CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_created_at ON orders(created_at DESC); -- ❌ Avoid: Index on every column -- ❌ Avoid: Index on low-cardinality columns (boolean flags) -- ❌ Avoid: Duplicate indexes ``` **Index types:** 1. **Single-column index (most common):** ```sql CREATE INDEX idx_users_email ON users(email); ``` 2. **Composite index (for multi-column WHERE/JOIN):** ```sql -- Good for: WHERE user_id = X AND created_at > Y CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC); ``` 3. **Partial index (index subset of rows):** ```sql -- Index only active users (avoid indexing soft-deleted rows) CREATE INDEX idx_active_users_email ON users(email) WHERE deleted_at IS NULL; ``` 4. **Unique index (enforce constraint):** ```sql CREATE UNIQUE INDEX idx_users_email ON users(email); ``` **Index maintenance:** - Monitor for unused indexes (query database statistics) - Drop unused indexes after measurement - ANALYZE/VACUUM regularly to update statistics ### Primary Keys **Use surrogate keys (auto-incrementing ID) by default:** ```sql -- ✅ Recommended: Surrogate key CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(255) ); -- ✅ Good for high-volume tables CREATE TABLE events ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, event_type VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` **Natural keys only if:** - Column(s) are guaranteed immutable - Never reassigned or repurposed - Are short and stable ```sql -- ✅ Natural key (country code is stable, never changes) CREATE TABLE countries ( code CHAR(2) PRIMARY KEY, name VARCHAR(255) ); -- ❌ Natural key (email changes, should use surrogate) CREATE TABLE users ( email VARCHAR(255) PRIMARY KEY ); ``` ### Foreign Keys and Constraints **Always define foreign key relationships:** ```sql CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL ); CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id), total_amount DECIMAL(10,2) NOT NULL ); -- With explicit constraint name for cleaner error messages CREATE TABLE order_items ( id BIGSERIAL PRIMARY KEY, order_id BIGINT NOT NULL, product_id BIGINT NOT NULL, quantity INT NOT NULL CHECK (quantity > 0), CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id), CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(id) ); ``` **Cascade behaviors:** ```sql -- DELETE CASCADE: Delete orders when user is deleted (use with caution) CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE -- RESTRICT: Prevent user deletion if orders exist (safer default) CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT -- SET NULL: Set user_id to NULL if user deleted (for optional relationships) CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ``` ### Constraints **Use constraints to enforce data integrity at database level:** ```sql CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, age INT CHECK (age >= 18), role VARCHAR(50) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')), created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id), status VARCHAR(50) NOT NULL DEFAULT 'pending', total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount > 0), created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Composite constraint CONSTRAINT valid_status CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')), -- Unique constraint on composite columns CONSTRAINT unique_user_order_date UNIQUE (user_id, DATE(created_at)) ); ``` ## ORM Patterns ### Active Record vs Data Mapper **Active Record:** - Model contains both data and database logic - Simple for small projects - Model directly calls database - Example: Rails, Django ORM ```python # Active Record pattern class User(Model): name = CharField() email = CharField() def save(self): # Object knows how to save itself db.insert('users', {...}) @staticmethod def find_by_email(email): return db.query('SELECT * FROM users WHERE email = ?', email) # Usage user = User(name='John', email='john@example.com') user.save() found_user = User.find_by_email('john@example.com') ``` **Data Mapper:** - Model contains only data; repository handles database logic - Better separation of concerns - Model is a plain object - Example: SQLAlchemy, TypeORM ```python # Data Mapper pattern class User: def __init__(self, name, email): self.name = name self.email = email class UserRepository: def save(self, user): # Repository handles persistence db.insert('users', {'name': user.name, 'email': user.email}) def find_by_email(self, email): row = db.query('SELECT * FROM users WHERE email = ?', email) return User(row['name'], row['email']) if row else None # Usage user = User('John', 'john@example.com') repo = UserRepository() repo.save(user) found_user = repo.find_by_email('john@example.com') ``` **Choose based on project scale:** - Small apps: Active Record (simpler) - Medium to large: Data Mapper (more flexible) ### Query Builders **Use query builders to avoid string concatenation and SQL injection:** ```python # ❌ Vulnerable to SQL injection query = f"SELECT * FROM users WHERE email = '{email}'" result = db.execute(query) # ✅ Safe: Parameterized query result = db.query('SELECT * FROM users WHERE email = ?', [email]) # ✅ Better: Query builder result = ( db.select(User) .where(User.email == email) .where(User.active == True) .order_by(User.created_at.desc()) .limit(10) .execute() ) ``` **Benefits of query builders:** - Prevent SQL injection - Cleaner, more maintainable code - Database-agnostic (can switch databases) - Compose queries dynamically ### Eager Loading vs Lazy Loading **Lazy Loading (default, but can cause N+1):** ```python # Each user fetch triggers a separate query for posts users = db.query(User).limit(10).all() for user in users: print(user.posts) # Additional query per user = 10+ queries! ``` **Eager Loading (prevent N+1):** ```python # Single query with JOIN users = db.query(User).join(Post).limit(10).all() for user in users: print(user.posts) # No additional queries # Or use explicit eager loading users = db.query(User).options(joinedload(User.posts)).limit(10).all()