/database

name: database-fundamentals
description: Reviews schema design, SQL queries, ORM patterns. Use when junior creates schema, writes queries, adds migrations, works with Prisma/MongoDB/PostgreSQL, or asks "is this SQL safe", "N+1", "index".

Database Fundamentals Review

> "Your database is the foundation. Build it wrong, and everything above it will crack."

When to Apply

Activate this skill when reviewing:

• Schema design and migrations
• SQL/NoSQL queries
• ORM model definitions
• Data relationships
• Index creation
• Query performance

---

Review Checklist

Schema Design

• [ ] **Normalization**: Is data normalized appropriately (no excessive duplication)?
• [ ] **Denormalization justified**: If denormalized, is there a performance reason?
• [ ] **Primary keys**: Does every table have a clear primary key?
• [ ] **Foreign keys**: Are relationships enforced at the database level?
• [ ] **Data types**: Are appropriate types used (not everything TEXT)?

Indexes

• [ ] **Query-based**: Are indexes created for frequently queried columns?
• [ ] **Composite indexes**: Are multi-column queries covered?
• [ ] **Not over-indexed**: Are there unnecessary indexes slowing writes?
• [ ] **Unique constraints**: Are unique fields enforced at DB level?

Queries

• [ ] **No N+1**: Are related records fetched in bulk?
• [ ] **Select specific fields**: Are we avoiding `SELECT *`?
• [ ] **Pagination**: Do list queries limit results?
• [ ] **Parameterized**: Are all queries parameterized (no string concatenation)?

Migrations

• [ ] **Reversible**: Can this migration be rolled back?
• [ ] **No data loss**: Will existing data survive the migration?
• [ ] **Tested**: Has this been tested against production-like data?
• [ ] **Incremental**: Are large changes broken into smaller migrations?

---

Common Mistakes (Anti-Patterns)

1. The N+1 Query Problem

❌ // 1 query for users + N queries for posts
   const users = await User.findAll();
   for (const user of users) {
     user.posts = await Post.findAll({ where: { userId: user.id } });
   }

✅ // 1 query with JOIN
   const users = await User.findAll({
     include: [{ model: Post }]
   });

   // Or 2 queries with IN clause
   const users = await User.findAll();
   const userIds = users.map(u => u.id);
   const posts = await Post.findAll({ where: { userId: userIds } });

2. Missing Indexes

❌ // Queried frequently, but no index
   SELECT * FROM orders WHERE user_id = ?
   SELECT * FROM products WHERE category = ? AND status = 'active'

✅ CREATE INDEX idx_orders_user_id ON orders(user_id);
   CREATE INDEX idx_products_category_status ON products(category, status);

3. SELECT * Everywhere

❌ SELECT * FROM users; // Returns 50 columns

✅ SELECT id, name, email FROM users; // Only what's needed

4. String Concatenation (SQL Injection)

❌ db.query(`SELECT * FROM users WHERE email = '${email}'`);

✅ db.query('SELECT * FROM users WHERE email = ?', [email]);

5. Destructive Migrations

❌ -- Can't be rolled back
   DROP TABLE users;
   ALTER TABLE orders DROP COLUMN status;

✅ -- Add new, migrate data, then drop old (in separate migrations)
   -- Migration 1: Add new column
   ALTER TABLE orders ADD COLUMN status_new VARCHAR(20);
   -- Migration 2: Copy data
   UPDATE orders SET status_new = status;
   -- Migration 3: Drop old (after verification)
   ALTER TABLE orders DROP COLUMN status;

---

Socratic Questions

Ask the junior these questions instead of giving answers:

1. **Schema**: "Why did you choose this data type?" 2. **Relationships**: "What happens if this related record is deleted?" 3. **Indexes**: "Which columns are queried together? Are they indexed?" 4. **N+1**: "How many queries does this operation execute?" 5. **Migration**: "What happens if we need to roll this back?"

---

Normalization Quick Reference

| Form | Rule | Example Issue | |------|------|---------------| | 1NF | No repeating groups | `tags: "js,react,node"` should be separate table | | 2NF | No partial dependencies | Order item price duplicated from products | | 3NF | No transitive dependencies | Storing city AND zip code (zip determines city) |

When to Denormalize

• Read-heavy workloads with rare writes
• Calculated aggregates (e.g., order totals)
• Caching frequently accessed derived data

---

Index Strategy

-- Single column (most common)
CREATE INDEX idx_users_email ON users(email);

-- Composite (for multi-column queries)
-- Order matters! Most selective first
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Partial (for filtered queries)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Unique (enforces constraint)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

Index Rules of Thumb

1. Index columns in WHERE clauses 2. Index columns in JOIN conditions 3. Index columns in ORDER BY (if used with WHERE) 4. Don't over-index write-heavy tables 5. Consider composite indexes for multi-column queries

---

Query Optimization Checklist

1. [ ] Use EXPLAIN to analyze query plan 2. [ ] Avoid SELECT * - specify columns 3. [ ] Use LIMIT for pagination 4. [ ] Add indexes for WHERE/JOIN columns 5. [ ] Use WHERE instead of HAVING when possible 6. [ ] Avoid functions on indexed columns in WHERE 7. [ ] Use EXISTS instead of IN for large subqueries

---

Red Flags to Call Out

| Flag | Question to Ask | |------|-----------------| | Query in a loop | "Can we fetch all this data in one query?" | | No pagination | "What if there are 1 million records?" | | SELECT * | "Do we need all 50 columns?" | | String in query | "Is this protected against SQL injection?" | | No indexes on foreign keys | "How fast are JOINs on this table?" | | DROP TABLE in migration | "How do we roll this back?" | | TEXT for everything | "Should this be an INT or DATE instead?" | | No foreign key constraints | "What prevents orphaned records?" |

---

##