Skip to content

secondsky/claude-skills / plugins/cloudflare-durable-objects/skills/cloudflare-durable-objects

cloudflare-durable-objects

Verified

Cloudflare Durable Objects for stateful coordination and real-time apps. Use for chat, multiplayer games, WebSocket hibernation, or encountering class export, migration, alarm errors.

FileKindSize
SKILL.mdskill md21.6 KB
references/advanced-sql-patterns.mdmarkdown15.8 KB
references/alarms-api.mdmarkdown6.5 KB
references/best-practices.mdmarkdown8.5 KB
references/common-patterns.mdmarkdown16.7 KB
references/data-modeling.mdmarkdown16.9 KB
references/error-codes.mdmarkdown7.7 KB
references/gradual-deployments.mdmarkdown13.9 KB
references/migration-cheatsheet.mdmarkdown3.2 KB
references/migrations-guide.mdmarkdown7.3 KB
references/monitoring-debugging.mdmarkdown18.2 KB
references/performance-optimization.mdmarkdown16.2 KB
references/rpc-metadata.mdmarkdown16.9 KB
references/rpc-patterns.mdmarkdown6.5 KB
references/security-best-practices.mdmarkdown15.9 KB
references/state-api-reference.mdmarkdown6.0 KB
references/stubs-routing.mdmarkdown10.6 KB
references/top-errors.mdmarkdown10.2 KB
references/typescript-config.mdmarkdown8.3 KB
references/vitest-testing.mdmarkdown18.1 KB
references/websocket-hibernation.mdmarkdown6.3 KB
references/wrangler-commands.mdmarkdown3.4 KB
scripts/check-versions.shexecutablescript593 B
scripts/migration-generator.shexecutablescript13.4 KB
scripts/setup-vitest-do.shexecutablescript10.2 KB
scripts/validate-do-config.shexecutablescript10.0 KB
templates/alarms-api-do.tsscript6.1 KB
templates/basic-do.tsscript2.9 KB
templates/gradual-deployment-config.jsoncother10.4 KB
templates/location-hints.tsscript6.8 KB
templates/multi-do-coordination.tsscript7.8 KB
templates/package.jsonmanifest384 B
templates/rpc-metadata-do.tsscript10.8 KB
templates/rpc-vs-fetch.tsscript6.0 KB
templates/state-api-patterns.tsscript5.8 KB
templates/ttl-cleanup-do.tsscript17.6 KB
templates/vitest-do-test.tsscript12.3 KB
templates/websocket-hibernation-do.tsscript6.0 KB
templates/wrangler-do-config.jsoncother2.0 KB

references/advanced-sql-patterns.md

View on GitHub
# Advanced SQL Patterns for Durable Objects

**Status**: Production Ready ✅
**Last Verified**: 2025-12-27
**Official Docs**: https://developers.cloudflare.com/durable-objects/api/storage-api/

Complete guide to advanced SQL features and patterns in Durable Objects SQLite backend.

---

## Overview

Durable Objects SQL storage provides a full SQLite database (1GB limit) with support for:
- Common Table Expressions (CTEs)
- Window functions
- Aggregate functions
- Full-text search (FTS5)
- JSON functions
- Recursive queries
- Transactions
- Triggers

---

## Common Table Expressions (CTEs)

CTEs provide temporary result sets for complex queries.

### Basic CTE

```typescript
// Find users with above-average activity
const result = await this.ctx.storage.sql.exec(`
  WITH avg_activity AS (
    SELECT AVG(activity_count) as avg_count
    FROM user_stats
  )
  SELECT u.id, u.username, us.activity_count
  FROM users u
  JOIN user_stats us ON u.id = us.user_id
  CROSS JOIN avg_activity
  WHERE us.activity_count > avg_activity.avg_count
  ORDER BY us.activity_count DESC
`);
```

### Multiple CTEs

```typescript
// Complex analytics query
const result = await this.ctx.storage.sql.exec(`
  WITH
    daily_totals AS (
      SELECT
        DATE(timestamp / 1000, 'unixepoch') as date,
        COUNT(*) as total_events,
        SUM(value) as total_value
      FROM events
      GROUP BY date
    ),
    weekly_averages AS (
      SELECT
        AVG(total_events) as avg_events,
        AVG(total_value) as avg_value
      FROM daily_totals
      WHERE date >= DATE('now', '-7 days')
    )
  SELECT
    dt.date,
    dt.total_events,
    dt.total_value,
    dt.total_events - wa.avg_events as events_vs_avg,
    dt.total_value - wa.avg_value as value_vs_avg
  FROM daily_totals dt
  CROSS JOIN weekly_averages wa
  ORDER BY dt.date DESC
  LIMIT 30
`);
```

### Recursive CTEs

```typescript
// Hierarchical data (thread replies)
const result = await this.ctx.storage.sql.exec(`
  WITH RECURSIVE thread_tree AS (
    -- Base case: root message
    SELECT id, parent_id, content, 0 as depth
    FROM messages
    WHERE id = ?

    UNION ALL

    -- Recursive case: replies
    SELECT m.id, m.parent_id, m.content, tt.depth + 1
    FROM messages m
    JOIN thread_tree tt ON m.parent_id = tt.id
    WHERE tt.depth < 10  -- Prevent infinite recursion
  )
  SELECT * FROM thread_tree
  ORDER BY depth, id
`, rootMessageId);
```

**Use Cases**:
- Hierarchical data (comments, categories)
- Graph traversal
- Bill of materials
- Organization charts

---

## Window Functions

Window functions perform calculations across rows related to the current row.

### ROW_NUMBER()

```typescript
// Rank messages by user
const result = await this.ctx.storage.sql.exec(`
  SELECT
    user_id,
    content,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rank
  FROM messages
`);

// Get top 3 messages per user
const top3 = await this.ctx.storage.sql.exec(`
  WITH ranked_messages AS (
    SELECT
      user_id,
      content,
      created_at,
      ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rank
    FROM messages
  )
  SELECT * FROM ranked_messages
  WHERE rank <= 3
`);
```

### RANK() and DENSE_RANK()

```typescript
// Leaderboard with tie handling
const result = await this.ctx.storage.sql.exec(`
  SELECT
    user_id,
    score,
    RANK() OVER (ORDER BY score DESC) as rank,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
  FROM leaderboard
`);

// RANK: 1, 2, 2, 4 (gaps after ties)
// DENSE_RANK: 1, 2, 2, 3 (no gaps)
```

### LAG() and LEAD()

```typescript
// Compare with previous/next values
const result = await this.ctx.storage.sql.exec(`
  SELECT
    date,
    value,
    LAG(value) OVER (ORDER BY date) as previous_value,
    LEAD(value) OVER (ORDER BY date) as next_value,
    value - LAG(value) OVER (ORDER BY date) as change_from_prev
  FROM metrics
  ORDER BY date
`);
```

### Running Totals

```typescript
// Cumulative sum
const result = await this.ctx.storage.sql.exec(`
  SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) as running_total
  FROM transactions
  ORDER BY date
`);
```

### Moving Averages

```typescript
// 7-day moving average
const result = await this.ctx.storage.sql.exec(`
  SELECT
    date,
    value,
    AVG(value) OVER (
      ORDER BY date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7day
  FROM daily_metrics
  ORDER BY date
`);
```

---

## Aggregate Functions

Advanced aggregation patterns beyond basic COUNT/SUM/AVG.

### GROUP_CONCAT()

```typescript
// Aggregate related values into array
const result = await this.ctx.storage.sql.exec(`
  SELECT
    user_id,
    GROUP_CONCAT(tag, ',') as tags
  FROM user_tags
  GROUP BY user_id
`);

// Result: user_id=1, tags="javascript,typescript,react"
```

### JSON Aggregation

```typescript
// Build JSON arrays
const result = await this.ctx.storage.sql.exec(`
  SELECT
    user_id,
    JSON_GROUP_ARRAY(
      JSON_OBJECT(
        'tag', tag,
        'count', count
      )
    ) as tag_data
  FROM user_tags
  GROUP BY user_id
`);

// Result: user_id=1, tag_data=[{"tag":"js","count":5},{"tag":"ts","count":3}]
```

### HAVING with Aggregates

```typescript
// Filter groups by aggregate conditions
const result = await this.ctx.storage.sql.exec(`
  SELECT
    user_id,
    COUNT(*) as message_count,
    MAX(created_at) as last_message
  FROM messages
  GROUP BY user_id
  HAVING COUNT(*) > 10
    AND MAX(created_at) > ?
  ORDER BY message_count DESC
`, Date.now() - 86400_000); // Active in last 24h
```

---

## Full-Text Search (FTS5)

SQLite FTS5 provides powerful full-text search capabilities.

### Setup FTS5 Table

```typescript
// Create FTS5 virtual table
await this.ctx.storage.sql.exec(`
  CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts
  USING fts5(
    content,
    user_id UNINDEXED,
    tokenize='porter unicode61'
  )
`);

// Keep FTS table in sync with main table using triggers
await this.ctx.storage.sql.exec(`
  CREATE TRIGGER IF NOT EXISTS messages_ai
  AFTER INSERT ON messages
  BEGIN
    INSERT INTO messages_fts(rowid, content, user_id)
    VALUES (new.id, new.content, new.user_id);
  END
`);

await this.ctx.storage.sql.exec(`
  CREATE TRIGGER IF NOT EXISTS messages_au
  AFTER UPDATE ON messages
  BEGIN
    UPDATE messages_fts
    SET content = new.content, user_id = new.user_id
    WHERE rowid = old.id;
  END
`);

await this.ctx.storage.sql.exec(`
  CREATE TRIGGER IF NOT EXISTS messages_ad
  AFTER DELETE ON messages
  BEGIN
    DELETE FROM messages_fts WHERE rowid = old.id;
  END
`);
```

### Basic Search

```typescript
// Simple search
const result = await this.ctx.storage.sql.exec(`
  SELECT
    m.id,
    m.user_id,
    m.content,
    m.created_at
  FROM messages_fts fts
  JOIN messages m ON m.id = fts.rowid
  WHERE messages_fts MATCH ?
  ORDER BY rank
`, 'cloudflare workers');
```

### Advanced Search Syntax

```typescript
// Phrase search
const phrase = await this.ctx.storage.sql.exec(`
  SELECT * FROM messages_fts
  WHERE messages_fts MATCH '"durable objects"'
`);

// AND search
const and = await this.ctx.storage.sql.exec(`
  SELECT * FROM messages_fts
  WHERE messages_fts MATCH 'cloudflare AND workers'
`);

// OR search
const or = await this.ctx.storage.sql.exec(`
  SELECT * FROM messages_fts
  WHERE messages_fts MATCH 'cloudflare OR workers'
`);

// NOT search
const not = await this.ctx.storage.sql.exec(`
  SELECT * FROM messages_fts
  WHERE messages_fts MATCH 'cloudflare NOT pages'
`);

// Prefix search
const prefix = await this.ctx.storage.sql.exec(`
  SELECT * FROM messages_fts
  WHERE messages_fts MATCH 'cloud*'
`);
```

### Ranked Results with Snippets

```typescript
const result = await this.ctx.storage.sql.exec(`
  SELECT
    m.id,
    m.content,
    snippet(messages_fts, 0, '<mark>', '</mark>', '...', 32) as snippet,
    rank as relevance
  FROM messages_fts fts
  JOIN messages m ON m.id = fts.rowid
  WHERE messages_fts MATCH ?
  ORDER BY rank
  LIMIT 20
`, searchQuery);
```

---

## JSON Functions

SQLite provides powerful JSON manipulation functions.

### JSON Storage

```typescript
// Store JSON data
await this.ctx.storage.sql.exec(`
  CREATE TABLE IF NOT EXISTS user_profiles (
    id INTEGER PRIMARY KEY,
    user_id TEXT NOT NULL,
    profile_data TEXT NOT NULL  -- JSON as TEXT
  )
`);

await this.ctx.storage.sql.exec(`
  INSERT INTO user_profiles (user_id, profile_data)
  VALUES (?, ?)
`, userId, JSON.stringify({
  name: 'John Doe',
  preferences: {
    theme: 'dark',
    notifications: true
  },
  tags: ['developer', 'cloudflare']
}));
```

### JSON Queries

```typescript
// Extract JSON fields
const result = await this.ctx.storage.sql.exec(`
  SELECT
    user_id,
    JSON_EXTRACT(profile_data, '$.name') as name,
    JSON_EXTRACT(profile_data, '$.preferences.theme') as theme,
    JSON_EXTRACT(profile_data, '$.tags') as tags
  FROM user_profiles
  WHERE user_id = ?
`, userId);

// Filter by JSON values
const darkThemeUsers = await this.ctx.storage.sql.exec(`
  SELECT user_id
  FROM user_profiles
  WHERE JSON_EXTRACT(profile_data, '$.preferences.theme') = 'dark'
`);
```

### JSON Array Operations

```typescript
// Query JSON arrays
const result = await this.ctx.storage.sql.exec(`
  SELECT
    user_id,
    JSON_EXTRACT(profile_data, '$.tags') as tags
  FROM user_profiles
  WHERE JSON_EXTRACT(profile_data, '$.tags') LIKE '%developer%'
`);

// Expand JSON arrays into rows
const expanded = await this.ctx.storage.sql.exec(`
  SELECT
    up.user_id,
    jt.value as tag
  FROM user_profiles up,
    JSON_EACH(JSON_EXTRACT(up.profile_data, '$.tags')) jt
`);
```

### JSON Modification

```typescript
// Update JSON fields
await this.ctx.storage.sql.exec(`
  UPDATE user_profiles
  SET profile_data = JSON_SET(
    profile_data,
    '$.preferences.theme',
    ?
  )
  WHERE user_id = ?
`, 'light', userId);

// Add to JSON array
await this.ctx.storage.sql.exec(`
  UPDATE user_profiles
  SET profile_data = JSON_INSERT(
    profile_data,
    '$.tags[#]',
    ?
  )
  WHERE user_id = ?
`, 'new-tag', userId);
```

---

## Advanced Transaction Patterns

Complex transaction scenarios with proper error handling.

### Nested Transactions (Savepoints)

```typescript
async complexTransaction() {
  try {
    await this.ctx.storage.sql.exec('BEGIN');

    // First operation
    await this.ctx.storage.sql.exec(
      'INSERT INTO accounts (user_id, balance) VALUES (?, ?)',
      userId, 100
    );

    // Savepoint for partial rollback
    await this.ctx.storage.sql.exec('SAVEPOINT sp1');

    try {
      // Risky operation
      await this.ctx.storage.sql.exec(
        'UPDATE accounts SET balance = balance - ? WHERE user_id = ?',
        amount, userId
      );

      // Check balance constraint
      const result = await this.ctx.storage.sql.exec(
        'SELECT balance FROM accounts WHERE user_id = ?',
        userId
      );

      if ((result.rows[0].balance as number) < 0) {
        throw new Error('Insufficient funds');
      }

      // Release savepoint if successful
      await this.ctx.storage.sql.exec('RELEASE sp1');
    } catch (error) {
      // Rollback to savepoint
      await this.ctx.storage.sql.exec('ROLLBACK TO sp1');
      throw error;
    }

    await this.ctx.storage.sql.exec('COMMIT');
  } catch (error) {
    await this.ctx.storage.sql.exec('ROLLBACK');
    throw error;
  }
}
```

### Optimistic Locking

```typescript
async updateWithOptimisticLock(id: number, newValue: string, expectedVersion: number) {
  const result = await this.ctx.storage.sql.exec(`
    UPDATE records
    SET value = ?, version = version + 1
    WHERE id = ? AND version = ?
  `, newValue, id, expectedVersion);

  if (result.rowsWritten === 0) {
    throw new Error('Concurrent modification detected');
  }

  return expectedVersion + 1;
}
```

### Batch Insert with UPSERT

```typescript
// Efficient bulk upsert
async bulkUpsert(records: Array<{id: string, value: number}>) {
  await this.ctx.storage.sql.exec('BEGIN');

  for (const record of records) {
    await this.ctx.storage.sql.exec(`
      INSERT INTO metrics (id, value, updated_at)
      VALUES (?, ?, ?)
      ON CONFLICT(id) DO UPDATE SET
        value = excluded.value,
        updated_at = excluded.updated_at
    `, record.id, record.value, Date.now());
  }

  await this.ctx.storage.sql.exec('COMMIT');
}
```

---

## Performance Patterns

Advanced optimization techniques.

### Materialized Views (Manual)

```typescript
// Create summary table
await this.ctx.storage.sql.exec(`
  CREATE TABLE IF NOT EXISTS daily_stats (
    date TEXT PRIMARY KEY,
    total_users INTEGER,
    total_messages INTEGER,
    avg_message_length REAL,
    last_updated INTEGER
  )
`);

// Refresh materialized view
async refreshDailyStats(date: string) {
  await this.ctx.storage.sql.exec(`
    INSERT OR REPLACE INTO daily_stats (
      date, total_users, total_messages, avg_message_length, last_updated
    )
    SELECT
      ?,
      COUNT(DISTINCT user_id),
      COUNT(*),
      AVG(LENGTH(content)),
      ?
    FROM messages
    WHERE DATE(created_at / 1000, 'unixepoch') = ?
  `, date, Date.now(), date);
}

// Query materialized view (fast)
const stats = await this.ctx.storage.sql.exec(
  'SELECT * FROM daily_stats WHERE date >= ? ORDER BY date DESC',
  '2025-01-01'
);
```

### Partial Indexes

```typescript
// Index only active records
await this.ctx.storage.sql.exec(`
  CREATE INDEX IF NOT EXISTS idx_active_sessions
  ON sessions(user_id, last_activity)
  WHERE expires_at > ?
`, Date.now());

// Much smaller index, faster queries for active sessions
const activeSessions = await this.ctx.storage.sql.exec(`
  SELECT * FROM sessions
  WHERE user_id = ? AND expires_at > ?
  ORDER BY last_activity DESC
`, userId, Date.now());
```

### Expression Indexes

```typescript
// Index on computed column
await this.ctx.storage.sql.exec(`
  CREATE INDEX IF NOT EXISTS idx_day_of_week
  ON events((strftime('%w', timestamp / 1000, 'unixepoch')))
`);

// Fast queries by day of week
const weekendEvents = await this.ctx.storage.sql.exec(`
  SELECT * FROM events
  WHERE strftime('%w', timestamp / 1000, 'unixepoch') IN ('0', '6')
`);
```

---

## Anti-Patterns to Avoid

### ❌ SELECT * in Production

```typescript
// Bad: Returns all columns (wasteful)
const result = await this.ctx.storage.sql.exec('SELECT * FROM messages');

// Good: Select only needed columns
const result = await this.ctx.storage.sql.exec(
  'SELECT id, content, created_at FROM messages'
);
```

### ❌ OFFSET Pagination

```typescript
// Bad: OFFSET gets slower as you paginate further
const page10000 = await this.ctx.storage.sql.exec(
  'SELECT * FROM messages ORDER BY id LIMIT 50 OFFSET 500000'
);

// Good: Cursor-based pagination
const page = await this.ctx.storage.sql.exec(
  'SELECT * FROM messages WHERE id > ? ORDER BY id LIMIT 50',
  lastSeenId
);
```

### ❌ OR Conditions on Different Columns

```typescript
// Bad: Can't use indexes efficiently
const result = await this.ctx.storage.sql.exec(`
  SELECT * FROM users
  WHERE username = ? OR email = ?
`, searchTerm, searchTerm);

// Good: Use UNION
const result = await this.ctx.storage.sql.exec(`
  SELECT * FROM users WHERE username = ?
  UNION
  SELECT * FROM users WHERE email = ?
`, searchTerm, searchTerm);
```

### ❌ Functions on Indexed Columns in WHERE

```typescript
// Bad: Can't use index
const result = await this.ctx.storage.sql.exec(`
  SELECT * FROM events
  WHERE LOWER(category) = ?
`, 'important');

// Good: Store lowercase version or use expression index
await this.ctx.storage.sql.exec(`
  CREATE INDEX IF NOT EXISTS idx_category_lower
  ON events(LOWER(category))
`);
```

---

## Sources

- [SQLite Documentation](https://www.sqlite.org/docs.html)
- [Cloudflare Durable Objects SQL](https://developers.cloudflare.com/durable-objects/api/storage-api/)
- [SQLite Window Functions](https://www.sqlite.org/windowfunctions.html)
- [SQLite JSON Functions](https://www.sqlite.org/json1.html)
- [SQLite FTS5](https://www.sqlite.org/fts5.html)

---

**Last Updated**: 2025-12-27
**Maintainer**: Claude Skills Team
slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack."Stars: 196kUpdated Claude CodeCodexVerifiedInstall
ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read.Stars: 193kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report.Stars: 193kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt.Stars: 193kUpdated Claude CodeCodexVerifiedInstall