<!--
hoody-sqlite Subskill (sdk)
Auto-generated by Hoody Skills Generator
Generated: 2026-06-20T00:09:19.730Z
Model: mimo-v2.5-pro + fixer:mimo-v2.5-pro
Mode: sdk


Tokens: 11036

DO NOT EDIT MANUALLY - Changes will be overwritten on next generation
-->

# hoody-sqlite Subskill

## Overview

**hoody-sqlite** provides portable SQLite databases accessible from anywhere via HTTP. It combines full SQL transaction support with a built-in key-value store, enabling both structured relational data and fast document-style storage through a single service.

### When to Use hoody-sqlite

- **Key-Value Storage**: Fast get/set operations for configuration, session data, feature flags, or any structured document
- **SQL Queries**: Complex relational queries, joins, aggregations, and multi-table transactions
- **Atomic Operations**: Increment/decrement counters, array manipulation (push/pop/remove), compare-and-swap updates
- **Time Travel**: Query historical states, compare snapshots between timestamps, rollback individual keys or entire tables
- **Batch Operations**: Bulk read/write/delete up to 100 keys in a single transaction
- **Auditable Storage**: Built-in operation history tracking per key and per database

### How It Fits Hoody Philosophy

hoody-sqlite embodies "portable databases accessible from anywhere." Each database file lives in your Hoody container's persistent storage and is accessible over HTTPS with automatic authentication—no connection strings, no VPN, no SSH tunnels. The dual interface (SQL + KV) means you can start simple with key-value operations and graduate to full SQL as your data model evolves.

### Service URL

```
https://{projectId}-{containerId}-sqlite-{serviceId}.{node}.containers.hoody.com
```

All endpoints require the `db` query parameter specifying the database path (relative to container storage). Databases are created automatically on first write if they don't exist.

---

## Common Workflows

### 1. Basic Key-Value Operations

Store, retrieve, update, and delete individual keys.

```
import { HoodyClient } from '@hoody-ai/hoody-sdk'

const client = new HoodyClient({ baseURL: 'https://api.hoody.com', token: 'TOKEN' })

// Set a value (creates database automatically)
await client.sqlite.kvStore.set('user:1001', 'my-app.db', {
  data: JSON.stringify({ name: 'Alice', role: 'admin', score: 0 })
})

// Get the value back
const user = await client.sqlite.kvStore.get({ key: 'user:1001', db: 'my-app.db' })
console.log(user)

// Check if key exists (returns headers only)
const exists = await client.sqlite.kvStore.exists({ key: 'user:1001', db: 'my-app.db' })

// Delete the key
await client.sqlite.kvStore.delete({ key: 'user:1001', db: 'my-app.db' })
```

### 2. Atomic Numeric Operations

Increment and decrement numeric values atomically without read-modify-write races.

```
// Initialize a counter
await client.sqlite.kvStore.set('page:views', 'analytics.db', {
  data: '0'
})

// Increment by 1 (default)
await client.sqlite.kvStore.incr({ key: 'page:views', db: 'analytics.db' })

// Increment by specific amount
await client.sqlite.kvStore.incr('page:views', 'analytics.db', {
  delta: 42
})

// Decrement
await client.sqlite.kvStore.decr('page:views', 'analytics.db', {
  delta: 5
})

// Read current value
const views = await client.sqlite.kvStore.get({ key: 'page:views', db: 'analytics.db' })
```

### 3. Array Operations (Push, Pop, Remove)

Use JSON arrays stored in values and manipulate them atomically.

```
// Initialize an array
await client.sqlite.kvStore.set('queue:tasks', 'worker.db', {
  data: JSON.stringify([])
})

// Push items to the end
await client.sqlite.kvStore.push('queue:tasks', 'worker.db', {
  data: { task: 'send-email', to: 'alice@example.com' }
})

await client.sqlite.kvStore.push('queue:tasks', 'worker.db', {
  data: { task: 'generate-report', format: 'pdf' }
})

// Pop the last item
const lastTask = await client.sqlite.kvStore.pop({ key: 'queue:tasks', db: 'worker.db' })

// Remove by index
await client.sqlite.kvStore.removeElement('queue:tasks', 'worker.db', {
  index: 0
})
```

### 4. Listing and Filtering Keys

Browse keys with prefix filtering and pagination.

```
// List all keys
const allKeys = await client.sqlite.kvStore.list({ db: 'my-app.db' })

// List with prefix filter
const userKeys = await client.sqlite.kvStore.list('my-app.db', {
  prefix: 'user:'
})

// Paginated listing
const page1 = await client.sqlite.kvStore.list('my-app.db', {
  prefix: 'user:',
  limit: 20,
  offset: 0
})

// Collect all pages automatically
const allUsers = await client.sqlite.kvStore.listAll('my-app.db', {
  prefix: 'user:'
})

// Use async iterator for memory-efficient processing
for await (const entry of client.sqlite.kvStore.listIterator('my-app.db', {
  prefix: 'config:'
})) {
  console.log(entry)
}
```

### 5. Batch Operations

Read, write, or delete up to 100 keys in a single transaction.

```
// Batch set multiple keys
await client.sqlite.kvStore.batchSet('my-app.db', {
  data: {
    'config:theme': 'dark',
    'config:lang': 'en',
    'config:timezone': 'UTC'
  }
})

// Batch get multiple keys
const configs = await client.sqlite.kvStore.batchGet('my-app.db', {
  data: ['config:theme', 'config:lang', 'config:timezone']
})

// Batch delete multiple keys
await client.sqlite.kvStore.batchDelete('my-app.db', {
  data: ['config:theme', 'config:lang', 'config:timezone']
})
```

### 6. SQL Transactions

Execute multiple SQL statements atomically with full ACID guarantees.

```
// Create a database explicitly
await client.sqlite.database.create({ path: '/data/store.db' })

// Execute a transaction with multiple statements
const result = await client.sqlite.database.executeTransaction('store.db', {
  statements: [
    {
      sql: 'CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, name TEXT, price REAL)'
    },
    {
      sql: 'INSERT INTO products (name, price) VALUES (?, ?)',
      params: ['Widget', 9.99]
    },
    {
      sql: 'INSERT INTO products (name, price) VALUES (?, ?)',
      params: ['Gadget', 24.99]
    }
  ]
})

// Query with parameters
const rows = await client.sqlite.database.executeTransaction('store.db', {
  statements: [
    {
      sql: 'SELECT * FROM products WHERE price < ?',
      params: [20.00]
    }
  ]
})
```

### 7. Shareable SQL Queries via GET

Execute read-only queries using base64-encoded SQL in a URL—useful for sharing or embedding.

```
const sql = Buffer.from('SELECT * FROM products ORDER BY price ASC').toString('base64')
const results = await client.sqlite.query.executeShareable('store.db', sql)
```

---

## Advanced Operations

### 8. Time Travel and Snapshots

Query historical states of individual keys or entire tables.

```
// Get a key's value at a specific timestamp (Unix milliseconds)
const pastValue = await client.sqlite.kvStore.get('user:1001', 'my-app.db', {
  at_timestamp: 1700000000000
})

// Get the operation history for a key
const history = await client.sqlite.kvStore.getHistory('user:1001', 'my-app.db', {
  limit: 50
})

// Get a key's state at a specific operation number
const snapshot = await client.sqlite.kvStore.getSnapshot('user:1001', 'my-app.db', {
  op_number: 42
})

// Rollback a key by undoing N operations
await client.sqlite.kvStore.rollback('user:1001', 'my-app.db', {
  steps: 3
})

// Snapshot the entire table at a timestamp
const tableSnapshot = await client.sqlite.kvStore.getTableSnapshot('my-app.db', {
  timestamp: 1700000000000,
  prefix: 'user:'
})

// Compare table states between two timestamps
const diff = await client.sqlite.kvStore.compareSnapshots('my-app.db', {
  from: 1700000000000,
  to: 1700086400000,
  keys: 'user:*'
})
```

### 9. Full Table Rollback

Rollback the entire KV table to a previous timestamp. Requires confirmation for safety.

```
// Dry run first to see what would change
const preview = await client.sqlite.kvStore.rollbackTable('my-app.db', {
  to_timestamp: 1700000000000,
  dry_run: true
})

// Apply the rollback with confirmation
await client.sqlite.kvStore.rollbackTable('my-app.db', {
  to_timestamp: 1700000000000,
  confirm: 'yes'
})
```

### 10. JSON Path Operations

Access and modify nested values within JSON documents stored as values.

```
// Store a nested JSON document
await client.sqlite.kvStore.set('user:1001', 'my-app.db', {
  data: JSON.stringify({
    name: 'Alice',
    profile: { score: 100, badges: ['starter'] }
  })
})

// Increment a nested numeric value
await client.sqlite.kvStore.incr('user:1001', 'my-app.db', {
  path: '$.profile.score',
  delta: 25
})

// Push to a nested array
await client.sqlite.kvStore.push('user:1001', 'my-app.db', {
  path: '$.profile.badges',
  data: 'explorer'
})

// Read only the nested value
const score = await client.sqlite.kvStore.get('user:1001', 'my-app.db', {
  path: '$.profile.score'
})
```

### 11. Compare-and-Swap (CAS) Updates

Prevent lost updates using optimistic concurrency control with ETag matching.

```
// Read the current value and its ETag
const current = await client.sqlite.kvStore.get({ key: 'counter:daily', db: 'my-app.db' })

// Update only if the value hasn't changed since our read
await client.sqlite.kvStore.set('counter:daily', 'my-app.db', {
  data: '43',
  if_match: current.etag
})
```

### 12. TTL (Time-To-Live) on Keys

Set automatic expiration for keys.

```
// Set a key that expires in 3600 seconds (1 hour)
await client.sqlite.kvStore.set('session:abc123', 'my-app.db', {
  data: JSON.stringify({ userId: 1001, role: 'admin' }),
  ttl: 3600
})
```

### 13. Database Maintenance

Run maintenance operations that require exclusive database access.

```
// WAL checkpoint and truncation
await client.sqlite.sql.runMaintenance('store.db', {
  operation: 'wal_checkpoint_truncate',
  timeout: 30000
})

// VACUUM INTO a new file (compact and backup)
await client.sqlite.sql.runMaintenance('store.db', {
  operation: 'vacuum_into',
  dest_path: '/data/store-compact.db',
  timeout: 60000
})
```

### 14. Health Monitoring

```
// Full health check (service status, memory, fd count, cache stats)
const health = await client.sqlite.health.getHealth()

// Cache-only health for lightweight dashboards
const cacheHealth = await client.sqlite.health.getHealthCache()
```

### 15. Query History and Statistics

```
// Get recent query history
const history = await client.sqlite.history.list('store.db', {
  limit: 100
})

// Get aggregated statistics
const stats = await client.sqlite.history.getStats({ db: 'store.db' })

// Clear all history
await client.sqlite.history.clear({ db: 'store.db' })

// Delete a specific history entry
await client.sqlite.history.deleteEntry(42, 'store.db')
```

### Error Recovery Patterns

```
// Retry with exponential backoff for transient failures
async function withRetry<T>(fn: () => Promise<T>, maxRetries = 3): Promise<T> {
  for (let attempt = 0; attempt <= maxRetries; attempt++) {
    try {
      return await fn()
    } catch (err) {
      if (attempt === maxRetries) throw err
      await new Promise(r => setTimeout(r, Math.pow(2, attempt) * 100))
    }
  }
  throw new Error('Unreachable')
}

// Use CAS to recover from concurrent modification
async function safeUpdate(key: string, db: string, updater: (val: any) => any) {
  for (let i = 0; i < 5; i++) {
    const current = await client.sqlite.kvStore.get(key, db)
    const updated = updater(current.value)
    try {
      return await client.sqlite.kvStore.set(key, db, {
        data: JSON.stringify(updated),
        if_match: current.etag
      })
    } catch (err) {
      if (i === 4) throw err
    }
  }
}
```

### Performance Considerations

- **Batch over individual**: Use `batchGet`/`batchSet`/`batchDelete` for multiple keys (max 100 per call)
- **Prefix filtering**: Always use `prefix` when listing to reduce payload size
- **History toggle**: Set `history: false` on high-frequency writes to reduce storage overhead
- **WAL checkpoint**: Run `wal_checkpoint_truncate` periodically on write-heavy databases to prevent WAL file growth
- **Shareable queries**: Use the GET `/query` endpoint for read-only queries that can be cached at the HTTP layer

---

## Quick Reference

### Most Common Operations

| Operation | SDK Method | HTTP |
|-----------|-----------|------|
| Get value | `kvStore.get(key, db)` | GET /api/v1/sqlite/kv/{key} |
| Set value | `kvStore.set(key, db, { data })` | PUT /api/v1/sqlite/kv/{key} |
| Delete key | `kvStore.delete(key, db)` | DELETE /api/v1/sqlite/kv/{key} |
| List keys | `kvStore.list(db)` | GET /api/v1/sqlite/kv |
| Batch get | `kvStore.batchGet(db, { data })` | POST /api/v1/sqlite/kv/batch/get |
| SQL transaction | `database.executeTransaction(db, { statements })` | POST /api/v1/sqlite/db |
| Increment | `kvStore.incr(key, db)` | POST /api/v1/sqlite/kv/{key}/incr |
| Health check | `health.getHealth()` | GET /api/v1/sqlite/health |

### Essential Query Parameters

| Parameter | Type | Description |
|-----------|------|-------------|
| `db` | string | **Required.** Database path (relative to container storage) |
| `key` | string | **Required.** KV store key name |
| `table` | string | KV table name (default: `kv`) |
| `prefix` | string | Filter keys by prefix |
| `path` | string | JSON path for nested value access (e.g. `$.profile.score`) |
| `limit` | integer | Max results to return |
| `offset` | integer | Skip N results |
| `at_timestamp` | integer | Query value at this Unix timestamp (ms) |
| `history` | boolean | Track this operation in history (default: true) |

### KV List Response Format

```
{
  "keys": ["user:1001", "user:1002", "config:theme"],
  "total": 3,
  "limit": 100,
  "offset": 0
}
```

### Health Response Shape

```
{
  "status": "ok",
  "service": "sqlite",
  "built": "2024-01-15T10:00:00Z",
  "started": "2024-01-20T08:00:00Z",
  "pid": 1234,
  "memory": { "rss": 45678912 },
  "fds": 24,
  "cache": { "entries": 150, "size_bytes": 1048576 },
  "counters": { "queries": 5420, "errors": 3 }
}
```

### Documentation Endpoints

```
// Get OpenAPI spec in YAML
const spec = await client.sqlite.docs.getYaml()

// OpenAPI JSON redirects to YAML
await client.sqlite.docs.getJson()
```