---

name: database-patterns description: Database design, query optimization, migration strategies, and ORM best practices.

Database Patterns

Core Principles

• Normalization: Reduce redundancy (3NF minimum)
• Denormalization: Strategic duplication for performance
• Indexing: Query patterns drive index design
• Migrations: Version-controlled, reversible schema changes
• Transactions: ACID guarantees for critical operations

Schema Design

Naming Conventions

-- Tables: plural, snake_case
users
blog_posts
order_items

-- Columns: singular, snake_case
id
user_id
created_at
updated_at

-- Indexes: descriptive
idx_users_email
idx_posts_user_id_created_at

-- Foreign keys: descriptive
fk_posts_user_id

Primary Keys

-- Good: UUID for distributed systems
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Good: Auto-increment for single-instance
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Foreign Keys

-- Good: Enforce referential integrity
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Index foreign keys for join performance
CREATE INDEX idx_posts_user_id ON posts(user_id);

Timestamps

-- Good: Track creation and updates
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Trigger to auto-update updated_at
CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

Indexing Strategies

Single Column Index

-- Good: Frequently queried columns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_status ON posts(status);

Composite Index

-- Good: Multi-column queries (order matters!)
CREATE INDEX idx_posts_user_id_created_at ON posts(user_id, created_at DESC);

-- Supports:
-- WHERE user_id = ? ORDER BY created_at DESC
-- WHERE user_id = ?

-- Does NOT support:
-- WHERE created_at > ?

Partial Index

-- Good: Index subset of rows
CREATE INDEX idx_posts_published ON posts(created_at)
  WHERE status = 'published';

Covering Index

-- Good: Include columns to avoid table lookup
CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name);

-- Query can be satisfied entirely from index
SELECT name FROM users WHERE email = 'user@example.com';

Query Optimization

N+1 Query Problem

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

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

// Good: Separate queries with IN clause
const users = await db.query('SELECT * FROM users');
const userIds = users.map(u => u.id);
const posts = await db.query('SELECT * FROM posts WHERE user_id = ANY(?)', [userIds]);
// Group posts by user_id in application

Pagination

-- Bad: OFFSET with large values (scans all skipped rows)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

-- Good: Cursor-based (keyset pagination)
SELECT * FROM posts 
WHERE created_at < ?
ORDER BY created_at DESC 
LIMIT 20;

Aggregations

-- Good: Use indexes for aggregations
CREATE INDEX idx_orders_user_id_total ON orders(user_id, total);

SELECT user_id, SUM(total) 
FROM orders 
GROUP BY user_id;

EXPLAIN Analysis

-- Always analyze slow queries
EXPLAIN ANALYZE
SELECT * FROM posts 
WHERE user_id = 'abc' 
ORDER BY created_at DESC 
LIMIT 20;

-- Look for:
-- - Seq Scan (bad for large tables)
-- - Index Scan (good)
-- - High cost estimates

Transactions

ACID Guarantees

// Good: Wrap related operations in transaction
await db.transaction(async (trx) => {
  const order = await trx('orders').insert({
    user_id: userId,
    total: 100
  }).returning('*');

  await trx('order_items').insert([
    { order_id: order.id, product_id: 'p1', quantity: 2 },
    { order_id: order.id, product_id: 'p2', quantity: 1 }
  ]);

  await trx('users').where({ id: userId }).decrement('balance', 100);
});

Isolation Levels

-- Read Committed (default, usually sufficient)
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Serializable (strictest, prevents all anomalies)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Optimistic Locking

-- Good: Version column for concurrent updates
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(255),
  stock INT,
  version INT NOT NULL DEFAULT 1
);

-- Update with version check
UPDATE products 
SET stock = stock - 1, version = version + 1
WHERE id = ? AND version = ?;

-- If affected rows = 0, version conflict occurred

Migrations

Migration Structure

// Good: Up and down migrations
export async function up(db: Database) {
  await db.schema.createTable('users', (table) => {
    table.uuid('id').primary().defaultTo(db.raw('gen_random_uuid()'));
    table.string('email', 255).notNullable().unique();
    table.timestamp('created_at').notNullable().defaultTo(db.fn.now());
    table.timestamp('updated_at').notNullable().defaultTo(db.fn.now());
  });

  await db.schema.raw(`
    CREATE INDEX idx_users_email ON users(email)
  `);
}

export async function down(db: Database) {
  await db.schema.dropTable('users');
}

Migration Best Practices

1. Never modify existing migrations - create new ones
2. Test rollback - ensure down() works
3. Avoid data migrations in schema migrations - separate concerns
4. Use transactions - all-or-nothing
5. Add indexes concurrently (PostgreSQL):

   CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
   

Zero-Downtime Migrations

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

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

-- Step 3: Make non-nullable
ALTER TABLE users ALTER COLUMN new_email SET NOT NULL;

-- Step 4: Drop old column
ALTER TABLE users DROP COLUMN email;

-- Step 5: Rename new column
ALTER TABLE users RENAME COLUMN new_email TO email;

ORM Best Practices

Query Builder (Knex, Kysely)

// Good: Type-safe query builder
const users = await db
  .selectFrom('users')
  .select(['id', 'email', 'name'])
  .where('status', '=', 'active')
  .orderBy('created_at', 'desc')
  .limit(20)
  .execute();

Raw Queries (When Needed)

// Good: Use raw for complex queries
const result = await db.raw(`
  WITH user_stats AS (
    SELECT 
      user_id,
      COUNT(*) as post_count,
      MAX(created_at) as last_post
    FROM posts
    GROUP BY user_id
  )
  SELECT u.*, us.post_count, us.last_post
  FROM users u
  LEFT JOIN user_stats us ON us.user_id = u.id
  WHERE u.status = ?
`, ['active']);

Connection Pooling

// Good: Configure pool size
const pool = new Pool({
  host: 'localhost',
  database: 'mydb',
  max: 20,              // Max connections
  min: 5,               // Min idle connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000
});

Denormalization Patterns

Computed Columns

-- Good: Store frequently accessed aggregates
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(255),
  post_count INT NOT NULL DEFAULT 0
);

-- Update via trigger
CREATE TRIGGER update_user_post_count
  AFTER INSERT OR DELETE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION update_user_post_count();

Materialized Views

-- Good: Pre-compute expensive queries
CREATE MATERIALIZED VIEW user_stats AS
SELECT 
  u.id,
  u.email,
  COUNT(p.id) as post_count,
  MAX(p.created_at) as last_post
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.email;

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

Soft Deletes

-- Good: Add deleted_at column
CREATE TABLE posts (
  id UUID PRIMARY KEY,
  title VARCHAR(255),
  deleted_at TIMESTAMP
);

-- Query active records
SELECT * FROM posts WHERE deleted_at IS NULL;

-- Index for performance
CREATE INDEX idx_posts_deleted_at ON posts(deleted_at) WHERE deleted_at IS NULL;

Full-Text Search

-- PostgreSQL: tsvector for full-text search
ALTER TABLE posts ADD COLUMN search_vector tsvector;

CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

-- Update search vector
UPDATE posts SET search_vector = 
  to_tsvector('english', title || ' ' || body);

-- Search
SELECT * FROM posts 
WHERE search_vector @@ to_tsquery('english', 'database & optimization');

Common Pitfalls

Avoid SELECT *

-- Bad: Fetches unnecessary data
SELECT * FROM users;

-- Good: Select only needed columns
SELECT id, email, name FROM users;

Avoid OR in WHERE

-- Bad: Can't use indexes efficiently
SELECT * FROM posts WHERE user_id = ? OR status = ?;

-- Good: Use UNION or separate queries
SELECT * FROM posts WHERE user_id = ?
UNION
SELECT * FROM posts WHERE status = ?;

Avoid Functions on Indexed Columns

-- Bad: Can't use index
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Good: Store lowercase, or use functional index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

Monitoring

Track these metrics:

• Query execution time (p50, p95, p99)
• Connection pool utilization
• Cache hit ratio
• Index usage
• Table bloat
• Lock contention

Use pg_stat_statements (PostgreSQL) or equivalent for query analysis.