database

What this skill does

# Database Development

## Overview

Database design, query optimization, and data management patterns for relational and NoSQL databases.

---

## PostgreSQL

### Schema Design

```sql
-- Users table with proper constraints
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100),
    role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'deleted')),
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Posts with foreign key
CREATE TABLE posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    content TEXT,
    excerpt VARCHAR(500),
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Many-to-many with junction table
CREATE TABLE tags (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

-- Indexes
CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_status ON posts(status) WHERE status = 'published';
CREATE INDEX idx_posts_published_at ON posts(published_at DESC) WHERE status = 'published';
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Full-text search
ALTER TABLE posts ADD COLUMN search_vector tsvector;
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.excerpt, '')), 'B') ||
        setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_search_update
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION update_search_vector();
```

### Advanced Queries

```sql
-- Common Table Expressions (CTE)
WITH post_stats AS (
    SELECT
        author_id,
        COUNT(*) as post_count,
        AVG(LENGTH(content)) as avg_length
    FROM posts
    WHERE status = 'published'
    GROUP BY author_id
)
SELECT
    u.name,
    u.email,
    ps.post_count,
    ps.avg_length
FROM users u
JOIN post_stats ps ON u.id = ps.author_id
ORDER BY ps.post_count DESC
LIMIT 10;

-- Window functions
SELECT
    p.title,
    p.published_at,
    u.name as author,
    ROW_NUMBER() OVER (PARTITION BY p.author_id ORDER BY p.published_at DESC) as author_rank,
    COUNT(*) OVER (PARTITION BY p.author_id) as author_total_posts,
    p.published_at - LAG(p.published_at) OVER (PARTITION BY p.author_id ORDER BY p.published_at) as days_since_last
FROM posts p
JOIN users u ON p.author_id = u.id
WHERE p.status = 'published';

-- Recursive CTE (hierarchical data)
WITH RECURSIVE category_tree AS (
    -- Base case
    SELECT id, name, parent_id, 0 as depth, ARRAY[name] as path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive case
    SELECT c.id, c.name, c.parent_id, ct.depth + 1, ct.path || c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY path;

-- JSONB queries
SELECT
    id,
    metadata->>'theme' as theme,
    metadata->'preferences'->>'notifications' as notifications
FROM users
WHERE metadata @> '{"verified": true}'
AND metadata->'preferences' ? 'dark_mode';

-- Update JSONB
UPDATE users
SET metadata = jsonb_set(
    metadata,
    '{lastLogin}',
    to_jsonb(NOW())
)
WHERE id = $1;
```

### Performance Optimization

```sql
-- Analyze query plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.*, u.name as author_name
FROM posts p
JOIN users u ON p.author_id = u.id
WHERE p.status = 'published'
ORDER BY p.published_at DESC
LIMIT 20;

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

-- Covering index (index-only scan)
CREATE INDEX idx_posts_list ON posts(status, published_at DESC)
INCLUDE (title, slug, excerpt, author_id);

-- BRIN index for time-series data
CREATE INDEX idx_events_created ON events USING BRIN(created_at);

-- Table partitioning
CREATE TABLE events (
    id UUID DEFAULT gen_random_uuid(),
    event_type VARCHAR(50),
    payload JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE events_2024_q2 PARTITION OF events
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
```

---

## MongoDB

### Schema Design

```javascript
// User document with embedded data
const userSchema = {
  _id: ObjectId,
  email: String,
  passwordHash: String,
  profile: {
    name: String,
    avatar: String,
    bio: String,
  },
  preferences: {
    theme: String,
    notifications: {
      email: Boolean,
      push: Boolean,
    },
  },
  roles: [String],
  createdAt: Date,
  updatedAt: Date,
};

// Post with references
const postSchema = {
  _id: ObjectId,
  title: String,
  slug: String,
  content: String,
  authorId: ObjectId,  // Reference to users
  tags: [String],      // Denormalized for read performance
  stats: {
    views: Number,
    likes: Number,
    comments: Number,
  },
  status: String,
  publishedAt: Date,
  createdAt: Date,
};

// Indexes
db.users.createIndex({ email: 1 }, { unique: true });
db.posts.createIndex({ authorId: 1, publishedAt: -1 });
db.posts.createIndex({ tags: 1 });
db.posts.createIndex({ title: "text", content: "text" });
```

### Aggregation Pipeline

```javascript
// Complex aggregation
db.posts.aggregate([
  // Match published posts
  { $match: { status: "published" } },

  // Lookup author
  {
    $lookup: {
      from: "users",
      localField: "authorId",
      foreignField: "_id",
      as: "author",
    },
  },
  { $unwind: "$author" },

  // Group by author
  {
    $group: {
      _id: "$author._id",
      authorName: { $first: "$author.profile.name" },
      postCount: { $sum: 1 },
      totalViews: { $sum: "$stats.views" },
      avgLikes: { $avg: "$stats.likes" },
      posts: {
        $push: {
          title: "$title",
          publishedAt: "$publishedAt",
        },
      },
    },
  },

  // Sort by post count
  { $sort: { postCount: -1 } },

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

  // Project final shape
  {
    $project: {
      _id: 0,
      authorId: "$_id",
      authorName: 1,
      postCount: 1,
      totalViews: 1,
      avgLikes: { $round: ["$avgLikes", 2] },
      recentPosts: { $slice: ["$posts", 5] },
    },
  },
]);

// Faceted search
db.products.aggregate([
  { $match: { $text: { $search: "laptop" } } },
  {
    $facet: {
      results: [
        { $sort: { score: { $meta: "textScore" } } },
        { $skip: 0 },
        { $limit: 20 },
      ],
      priceRanges: [
        {
          $bucket: {
            groupBy: "$price",
            boundaries: [0, 500, 1000, 2000, Infinity],
            default: "Other",
            output: { count: { $sum: 1 } },
          },
        },
      ],
      brands: [{ $group: { _id: "$brand", count: { $sum: 1 } } }],
      totalCount: [{ $count: "count" }],
    },
  },
]);
```

---

## Redis

### Data Structures

```typescript
import Redis from 'ioredis';

const redis = new Redis(process.env.REDIS_URL);

// String operations
await redis.set('user:123:name', 'John');
await redis.setex('session:a