database-patterns

name	database-patterns
version	2.0.0
description	Database design, optimization, and caching strategies for SQL, NoSQL, and Redis
sasmp_version	1.3.0
bonded_agent	03-database-performance
bond_type	PRIMARY_BOND
atomic_design	{"single_responsibility":"Database optimization and caching patterns","boundaries":{"includes":["sql_optimization","nosql_patterns","redis_caching","connection_pooling","indexing"],"excludes":["api_design","business_logic","infrastructure"]}}
parameter_validation	{"schema":{"type":"object","properties":{"database_type":{"type":"string","enum":["postgresql","mysql","mongodb","redis"]},"operation":{"type":"string","enum":["query","cache","index","pool"]},"optimization_goal":{"type":"string","enum":["read_heavy","write_heavy","balanced"]}}}}
retry_config	{"enabled":true,"max_attempts":3,"backoff":{"type":"exponential","initial_delay_ms":1000,"max_delay_ms":30000},"retryable_errors":["ECONNREFUSED","ETIMEDOUT","connection_pool_exhausted"]}
logging	{"level":"INFO","fields":["database_type","operation","query_time_ms","cache_hit"]}
dependencies	{"skills":[],"agents":["03-database-performance"]}

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) => {
   orders =  db.(
    ,
    [userIds]
  );
   userIds.( orders.( o. === id));
});