database-patterns

# Database Patterns

## Overview

Database design and access patterns for relational and NoSQL databases.

## Schema Design

### Normalization Levels

| Level | Description | Use Case |
|-------|-------------|----------|
| 1NF | Atomic values, no repeating groups | Base requirement |
| 2NF | No partial dependencies | Most applications |
| 3NF | No transitive dependencies | OLTP systems |
| Denormalized | Redundant data for reads | Read-heavy, analytics |

### Common Table Patterns

```sql
-- Users table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Soft delete pattern
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;
CREATE INDEX idx_users_deleted ON users(deleted_at) WHERE deleted_at IS NULL;

-- Audit columns
ALTER TABLE users ADD COLUMN created_by UUID REFERENCES users(id);
ALTER TABLE users ADD COLUMN updated_by UUID REFERENCES users(id);
```

### Relationships

```sql
-- One-to-Many
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_orders_user ON orders(user_id);

-- Many-to-Many
CREATE TABLE order_products (
    order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID REFERENCES products(id) ON DELETE CASCADE,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Self-referential (tree/hierarchy)
CREATE TABLE categories (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id UUID REFERENCES categories(id)
);
CREATE INDEX idx_categories_parent ON categories(parent_id);
```

## Indexing Strategies

### Index Types

| Type | Use Case | Example |
|------|----------|---------|
| B-tree | Range, equality | Most columns |
| Hash | Equality only | Exact matches |
| GIN | Arrays, JSON, full-text | JSONB, text search |
| GiST | Geometric, range types | PostGIS, IP ranges |

### Index Guidelines

```sql
-- Primary key (automatic)
CREATE TABLE users (id UUID PRIMARY KEY);

-- Foreign keys
CREATE INDEX idx_orders_user ON orders(user_id);

-- Frequent filters
CREATE INDEX idx_users_status ON users(status);

-- Composite for multi-column queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

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

-- Expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
```

### When NOT to Index

- Small tables (< 1000 rows)
- Frequently updated columns
- Low cardinality columns
- Columns rarely used in WHERE

## Query Patterns

### Efficient Queries

```sql
-- Use specific columns, not *
SELECT id, name, email FROM users WHERE id = $1;

-- Limit results
SELECT * FROM users ORDER BY created_at DESC LIMIT 20;

-- Exists vs COUNT
SELECT EXISTS(SELECT 1 FROM users WHERE email = $1);

-- Batch inserts
INSERT INTO users (name, email) VALUES
    ('User 1', 'user1@example.com'),
    ('User 2', 'user2@example.com'),
    ('User 3', 'user3@example.com');
```

### Pagination

```sql
-- Offset pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 100;

-- Cursor pagination (better performance)
SELECT * FROM users
WHERE created_at < $cursor
ORDER BY created_at DESC
LIMIT 20;

-- Keyset pagination with tie-breaker
SELECT * FROM users
WHERE (created_at, id) < ($cursor_time, $cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
```

### Common Query Patterns

```sql
-- Upsert (INSERT or UPDATE)
INSERT INTO users (email, name)
VALUES ($1, $2)
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();

-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = $1;
SELECT * FROM users WHERE deleted_at IS NULL;

-- Lock for update (prevent race conditions)
SELECT * FROM accounts WHERE id = $1 FOR UPDATE;

-- Bulk update
UPDATE orders SET status = 'shipped'
WHERE id = ANY($1::uuid[]);
```

## Repository Pattern

### Interface

```typescript
interface UserRepository {
  findById(id: string): Promise<User | null>;
  findByEmail(email: string): Promise<User | null>;
  findAll(filter: UserFilter, pagination: Pagination): Promise<PaginatedResult<User>>;
  create(data: CreateUserInput): Promise<User>;
  update(id: string, data: UpdateUserInput): Promise<User>;
  delete(id: string): Promise<void>;
}
```

### Implementation

```typescript
class PostgresUserRepository implements UserRepository {
  constructor(private db: Database) {}

  async findById(id: string): Promise<User | null> {
    const result = await this.db.query(
      'SELECT * FROM users WHERE id = $1 AND deleted_at IS NULL',
      [id]
    );
    return result.rows[0] || null;
  }

  async create(data: CreateUserInput): Promise<User> {
    const result = await this.db.query(
      `INSERT INTO users (name, email, password_hash)
       VALUES ($1, $2, $3)
       RETURNING *`,
      [data.name, data.email, await hashPassword(data.password)]
    );
    return result.rows[0];
  }
}
```

## Transaction Patterns

### Basic Transaction

```typescript
async function transferFunds(fromId: string, toId: string, amount: number) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Lock accounts
    await client.query(
      'SELECT * FROM accounts WHERE id IN ($1, $2) FOR UPDATE',
      [fromId, toId]
    );

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

    // Credit
    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();
  }
}
```

### Isolation Levels

| Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|-------|------------|---------------------|--------------|
| Read Uncommitted | Yes | Yes | Yes |
| Read Committed | No | Yes | Yes |
| Repeatable Read | No | No | Yes |
| Serializable | No | No | No |

```sql
-- Set isolation level
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
```

## Migration Patterns

### Migration Structure

```
migrations/
├── 001_create_users.sql
├── 002_add_user_status.sql
├── 003_create_orders.sql
└── 004_add_order_index.sql
```

### Migration Best Practices

```sql
-- Always reversible
-- UP
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- DOWN
ALTER TABLE users DROP COLUMN phone;

-- Non-blocking index creation
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);

-- Safe column renames (PostgreSQL)
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Add NOT NULL safely
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
UPDATE users SET status = 'active' WHERE status IS NULL;
ALTER TABLE users ALTER COLUMN status SET NOT NULL;
```

## Connection Pooling

### Pool Configuration

```typescript
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,              // Max connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});
```

### Best Practices

- Use connection pool (don't create new connections)
- Release connections promptly
- Set appropriate pool size (CPU cores * 2-4)
- Handle connection errors gracefully

## NoSQL Patterns (MongoDB/DynamoDB)

### Document Design

```javascript
// Embedded (for one-to-few)
{
  _id: ObjectId("..."),
  name: "John",
  addresses: [
    { type: "home", street: "123 Main St" },
    { type: "work", street: "456 Office Blvd" }
  ]
}

// Referenced (for one-to-many)
{
  _id: ObjectId("..."),
  name: "John",
  orderIds: [ObjectId("..."), ObjectId("...")]
}
```

### DynamoDB Single-Table Design

```
PK              | SK                | Attributes
----------------|-------------------|------------------
USER#123        | METADATA          | name, email, ...
USER#123        | ORDER#001         | total, status, ...
USER#123        | ORDER#002         | total, status, ...
ORDER#001       | METADATA          | userId, total, ...
ORDER#001       | ITEM#1            | productId, qty, ...
```

---

*Database design and access patterns*