Database Patterns

Database Patterns Skill

Purpose

Optimize database operations with proven patterns for SQL, NoSQL, and caching.

SQL Optimization

Indexing Strategy

-- B-tree index (default, equality + range)
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Partial index (filter common queries)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Covering index (avoid table lookup)
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total, status);

N+1 Query Prevention

// BAD: N+1 queries
const users = await db.query('SELECT * FROM users');
for (const user of users) {
  user.orders = await db.query(
    'SELECT * FROM orders WHERE user_id = ?',
    [user.id]
  );
}

// GOOD: Single query with JOIN
const usersWithOrders = await db.query(`
  SELECT u.*, json_agg(o.*) as orders
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  GROUP BY u.id
`);

// GOOD: DataLoader pattern
const orderLoader = new DataLoader(async (userIds) => {
  const orders = await db.query(
    'SELECT * FROM orders WHERE user_id = ANY($1)',
    [userIds]
  );
  return userIds.map(id => orders.filter(o => o.user_id === id));
});

Connection Pooling

import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  max: 20,                    // Max connections
  min: 5,                     // Min connections
  idleTimeoutMillis: 30000,   // Close idle after 30s
  connectionTimeoutMillis: 5000,
  acquireTimeoutMillis: 10000,
});

// Monitor pool health
pool.on('error', (err, client) => {
  console.error('Unexpected pool error', err);
});

// Query with automatic release
async function query(sql, params) {
  const client = await pool.connect();
  try {
    return await client.query(sql, params);
  } finally {
    client.release();
  }
}

NoSQL Patterns (MongoDB)

Document Design

// Embedding (1:few, read-heavy)
{
  _id: ObjectId("..."),
  name: "John Doe",
  addresses: [
    { type: "home", city: "NYC" },
    { type: "work", city: "Boston" }
  ]
}

// Referencing (1:many, write-heavy)
{
  _id: ObjectId("..."),
  name: "John Doe",
  order_ids: [ObjectId("..."), ObjectId("...")]
}

// Hybrid (bounded array + overflow)
{
  _id: ObjectId("..."),
  name: "John Doe",
  recent_orders: [...],  // Last 10
  has_more_orders: true
}

Aggregation Pipeline

db.orders.aggregate([
  // Match stage (uses indexes)
  { $match: { status: 'completed', created_at: { $gte: lastMonth } } },

  // Group and calculate
  { $group: {
    _id: '$user_id',
    total_spent: { $sum: '$amount' },
    order_count: { $count: {} }
  }},

  // Sort by spending
  { $sort: { total_spent: -1 } },

  // Limit to top 100
  { $limit: 100 },

  // Lookup user details
  { $lookup: {
    from: 'users',
    localField: '_id',
    foreignField: '_id',
    as: 'user'
  }}
]);

Redis Caching

Cache-Aside Pattern

class CacheService {
  constructor(private redis: Redis, private db: Database) {}

  async get<T>(key: string, fetcher: () => Promise<T>, ttl = 3600): Promise<T> {
    // Try cache first
    const cached = await this.redis.get(key);
    if (cached) {
      return JSON.parse(cached);
    }

    // Fetch from database
    const data = await fetcher();

    // Store in cache
    await this.redis.setex(key, ttl, JSON.stringify(data));

    return data;
  }

  async invalidate(pattern: string): Promise<void> {
    const keys = await this.redis.keys(pattern);
    if (keys.length > 0) {
      await this.redis.del(...keys);
    }
  }
}

// Usage
const user = await cache.get(
  `user:${userId}`,
  () => db.users.findById(userId),
  3600
);

Write-Through Pattern

async function updateUser(userId: string, data: UserUpdate) {
  // Update database
  const user = await db.users.update(userId, data);

  // Immediately update cache
  await redis.setex(`user:${userId}`, 3600, JSON.stringify(user));

  // Invalidate related caches
  await redis.del(`user:${userId}:orders`);

  return user;
}

Cache Stampede Prevention

async function getWithLock<T>(
  key: string,
  fetcher: () => Promise<T>,
  ttl: number
): Promise<T> {
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);

  // Acquire lock
  const lockKey = `lock:${key}`;
  const acquired = await redis.set(lockKey, '1', 'EX', 10, 'NX');

  if (!acquired) {
    // Wait and retry
    await sleep(100);
    return getWithLock(key, fetcher, ttl);
  }

  try {
    const data = await fetcher();
    await redis.setex(key, ttl, JSON.stringify(data));
    return data;
  } finally {
    await redis.del(lockKey);
  }
}

Unit Test Template

import { describe, it, expect, beforeEach } from 'vitest';
import { Pool } from 'pg';
import Redis from 'ioredis-mock';

describe('Database Patterns', () => {
  let pool: Pool;
  let redis: Redis;

  beforeEach(() => {
    redis = new Redis();
  });

  describe('N+1 Prevention', () => {
    it('should batch queries with DataLoader', async () => {
      const queries: string[] = [];
      const loader = new DataLoader(async (ids) => {
        queries.push(`SELECT * FROM orders WHERE user_id IN (${ids})`);
        return ids.map(id => ({ user_id: id, total: 100 }));
      });

      await Promise.all([
        loader.load('1'),
        loader.load('2'),
        loader.load('3'),
      ]);

      expect(queries.length).toBe(1); // Single batched query
    });
  });

  describe('Cache-Aside Pattern', () => {
    it('should return cached value on hit', async () => {
      await redis.setex('user:123', 3600, JSON.stringify({ id: '123' }));

      let dbCalled = false;
      const user = await cache.get('user:123', async () => {
        dbCalled = true;
        return { id: '123' };
      });

      expect(dbCalled).toBe(false);
      expect(user.id).toBe('123');
    });

    it('should fetch and cache on miss', async () => {
      const user = await cache.get('user:456', async () => {
        return { id: '456', name: 'Test' };
      });

      const cached = await redis.get('user:456');
      expect(JSON.parse(cached!).name).toBe('Test');
    });
  });
});

Troubleshooting

Query Optimization Checklist

EXPLAIN ANALYZE your_query;

Check for:
- [ ] Seq Scan (should be Index Scan)
- [ ] Nested Loop with high rows
- [ ] Sort with high memory
- [ ] Hash Join memory usage

Quality Checklist

• Proper indexing strategy implemented
• N+1 queries eliminated
• Connection pooling configured
• Caching strategy defined
• Cache invalidation implemented
• Query plans analyzed
• Monitoring and alerting set up