Playbook Data

Database Practices Playbook

Schema design, query optimization, and data integrity patterns. Essential for building reliable, performant applications.

# agent_prompt

Copy this prompt and give it to your AI agent:

"Follow these database practices:

1. Use explicit column names (never SELECT *)

2. Add indexes for columns used in WHERE, JOIN, ORDER BY

3. Use parameterized queries (never string concatenation)

4. Normalize to 3NF, denormalize intentionally for reads

5. Add created_at, updated_at timestamps to all tables

6. Use transactions for multi-step operations

7. Soft delete with deleted_at instead of hard delete"

# schema_design

Good schema design prevents problems down the road. Think about data relationships and access patterns upfront.

-- Standard table template

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  status VARCHAR(20) DEFAULT 'active',

  -- Timestamps
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  deleted_at TIMESTAMP WITH TIME ZONE,

  -- Indexes
  CONSTRAINT valid_status CHECK (status IN ('active', 'inactive', 'pending'))
);

-- Updated timestamp trigger
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_timestamp();

• Primary keys: UUID or auto-increment based on use case
• Timestamps: Always include created_at, updated_at
• Soft delete: Use deleted_at instead of hard delete
• Constraints: Enforce data integrity at the database level

# indexing_strategy

Indexes speed up reads but slow down writes. Add them intentionally based on actual query patterns.

-- Index columns used in WHERE clauses
CREATE INDEX idx_users_email ON users(email);

-- Index columns used in ORDER BY
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Composite index for common query patterns
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index for common filters
CREATE INDEX idx_active_users ON users(email)
  WHERE deleted_at IS NULL AND status = 'active';

-- Index for foreign keys (usually automatic)
CREATE INDEX idx_orders_user_id ON orders(user_id);

When to add indexes

• Columns in WHERE clauses
• Columns used in JOIN conditions
• Columns in ORDER BY
• Foreign key columns
• Columns with high cardinality (many unique values)

# query_optimization

Write efficient queries that leverage indexes and minimize data transfer.

❌ Bad queries

-- Avoid SELECT *
SELECT * FROM users;

-- Avoid functions on indexed columns
SELECT * FROM users
WHERE LOWER(email) = '[email protected]';

-- Avoid OR conditions
SELECT * FROM orders
WHERE status = 'pending'
   OR status = 'processing';

✅ Better queries

-- Explicit columns
SELECT id, email, name FROM users;

-- Use citext or index expression
SELECT * FROM users
WHERE email = '[email protected]';

-- Use IN instead of OR
SELECT * FROM orders
WHERE status IN ('pending', 'processing');

Use EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT id, email FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 10;

-- Look for: Seq Scan (bad), Index Scan (good), rows affected

# security_practices

Protect against SQL injection and secure sensitive data.

❌ SQL Injection Risk

// NEVER do this
const query = `SELECT * FROM users
  WHERE email = '$${email}'`;

// Attacker input: ' OR '1'='1
// Results in: WHERE email = '' OR '1'='1'

✅ Parameterized Query

// Always use parameters
const query = 'SELECT * FROM users WHERE email = $1';
const result = await db.query(query, [email]);

// Or use an ORM
const user = await User.findOne({
  where: { email }
});

• Parameterized queries: Always use placeholders, never concatenate
• Least privilege: DB users should only have necessary permissions
• Encrypt at rest: Use database-level encryption for sensitive data
• Audit logging: Track who changed what and when

# migrations

Version control your database schema. Make changes incremental and reversible.

-- migrations/001_create_users.sql
-- UP
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

-- DOWN
DROP TABLE users;

-- migrations/002_add_user_name.sql
-- UP
ALTER TABLE users ADD COLUMN name VARCHAR(255);

-- DOWN
ALTER TABLE users DROP COLUMN name;

• One change per migration: Makes rollback predictable
• Include rollback: Every UP should have a DOWN
• Test migrations: Run up and down in development first
• Avoid data migrations in schema: Separate data changes from schema changes

# avoiding_n_plus_1

The N+1 problem kills performance. Load related data in bulk, not one at a time.

❌ N+1 Problem

// 1 query for orders
const orders = await getOrders();

// N queries for users (one per order!)
for (const order of orders) {
  order.user = await getUser(order.userId);
}
// If 100 orders = 101 queries!

✅ Eager Loading

// Option 1: JOIN
SELECT o.*, u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id;

// Option 2: Batch load
const orders = await getOrders();
const userIds = orders.map(o => o.userId);
const users = await getUsers(userIds);
// Just 2 queries total!

# transactions

Use transactions for operations that must succeed or fail together.

async function transferFunds(fromId, toId, amount) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    // Debit from source
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId]
    );

    // Credit to destination
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );

    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

• ACID: Atomicity, Consistency, Isolation, Durability
• Keep short: Long transactions can cause lock contention
• Always rollback on error: Never leave partial state
• Use appropriate isolation level: READ COMMITTED is usually fine