Database

Database Skill

Load this skill when working with databases, migrations, queries, or data modeling.

Rule

DATABASE CHANGES MUST BE SAFE AND REVERSIBLE!

• Always test migrations on staging
• Never delete data without backup
• Index queries, not tables

Migration Best Practices

Safe Migration Checklist

• Migration is reversible (has down method)
• No data loss
• No long locks on production tables
• Tested on copy of production data
• Backward compatible with current code

Dangerous Operations

Migration Patterns

Adding Column Safely

-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Step 2: Backfill data (in batches)
UPDATE users SET phone = 'unknown' WHERE phone IS NULL LIMIT 1000;

-- Step 3: Add NOT NULL constraint (after all data filled)
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

Zero-Downtime Column Rename

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Step 2: Dual-write in application (write to both columns)

-- Step 3: Backfill existing data
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- Step 4: Switch reads to new column

-- Step 5: Stop writing to old column

-- Step 6: Drop old column (separate migration, later)
ALTER TABLE users DROP COLUMN name;

Index Strategies

When to Add Index

Index Rules

1. Order matters in composite indexes
2. Leftmost prefix rule
3. Don't over-index - indexes slow writes
4. Cover queries when possible

Composite Index Order

-- Query: WHERE status = ? AND created_at > ?
-- Index should be: (status, created_at)

-- Query: WHERE user_id = ? ORDER BY created_at DESC
-- Index should be: (user_id, created_at DESC)

Check Index Usage

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'active';

-- MySQL
EXPLAIN SELECT * FROM users WHERE status = 'active';

N+1 Prevention

The Problem

# N+1 - BAD
users = User.all()
for user in users:
    print(user.posts.count())  # Query for each user!

Solutions

Eager Loading

# Django
users = User.objects.prefetch_related('posts').all()

# Laravel
$users = User::with('posts')->get();

# Prisma
const users = await prisma.user.findMany({
  include: { posts: true }
});

Batch Loading

# Load IDs first, then batch fetch
user_ids = [u.id for u in users]
posts = Post.where(user_id__in=user_ids).all()
posts_by_user = groupby(posts, 'user_id')

Detecting N+1

# Django Debug Toolbar
# Laravel Debugbar
# Prisma: logging queries

# Manual: count queries per request
# If count >> 10 for simple page, investigate

Transaction Patterns

Basic Transaction

# Python/SQLAlchemy
with session.begin():
    user = User(email='test@example.com')
    session.add(user)
    session.add(Profile(user=user))
    # Auto-commit or rollback

// Prisma
await prisma.$transaction([
  prisma.user.create({ data: userData }),
  prisma.profile.create({ data: profileData }),
]);

// Or interactive
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: userData });
  await tx.profile.create({ data: { userId: user.id } });
});

Isolation Levels

Default: READ COMMITTED (PostgreSQL), REPEATABLE READ (MySQL)

Connection Pooling

Why Pool

• Creating connections is expensive
• Databases have connection limits
• Pool reuses connections efficiently

Configuration

# Recommended settings
min_connections: 2
max_connections: 10
idle_timeout: 30s
connection_timeout: 5s

Pool Size Formula

connections = (core_count * 2) + effective_spindle_count

For SSD: connections = cores * 2 + 1

Framework Examples

// Prisma
datasource db {
  url = env("DATABASE_URL")
  connectionLimit = 10
}

// Node.js pg
const pool = new Pool({
  max: 10,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

Query Optimization

EXPLAIN Output

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'test@example.com';

-- Look for:
-- - Seq Scan (bad for large tables)
-- - Index Scan (good)
-- - Nested Loop (check if efficient)
-- - Sort (might need index)

Common Optimizations

Query Tips

-- Use LIMIT for pagination
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 100;

-- Use EXISTS instead of IN for large sets
SELECT * FROM users WHERE EXISTS (
  SELECT 1 FROM orders WHERE orders.user_id = users.id
);

-- Avoid SELECT * in production
SELECT id, name, email FROM users;

-- Use covering indexes
CREATE INDEX idx_users_status_email ON users(status) INCLUDE (email);

Backup Strategies

Backup Types

Backup Schedule

Daily:    Full backup
Hourly:   Incremental backup
Realtime: WAL archiving (PostgreSQL) / Binlog (MySQL)

Test Restores

# Regular restore testing is CRITICAL
# Monthly: Full restore to test environment
# Verify data integrity after restore

Schema Design Tips

Naming Conventions

-- Tables: plural, snake_case
users, order_items, user_profiles

-- Columns: snake_case
created_at, updated_at, user_id

-- Indexes: idx_table_columns
idx_users_email, idx_orders_user_id_status

-- Foreign keys: fk_table_reference
fk_orders_user_id

Common Patterns

-- Soft deletes
deleted_at TIMESTAMP NULL

-- Timestamps
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

-- UUID primary keys
id UUID DEFAULT gen_random_uuid() PRIMARY KEY

When to Use This Skill

• Writing database migrations
• Optimizing slow queries
• Designing database schema
• Fixing N+1 queries
• Setting up connection pooling
• Planning backup strategy

Source: sergei-aronsen/claude-code-toolkit — distributed by TomeVault.