# 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 queries and fast key-value operations over a single service. ### When to Use - **Structured data** requiring SQL queries, joins, and transactions - **Key-value storage** for configuration, counters, queues, and caching - **Time-travel debugging** with full operation history and snapshot reconstruction - **Portable databases** that move with your container across nodes ### How It Fits Hoody Philosophy hoody-sqlite embodies "portable databases accessible from anywhere." Your database lives inside your container, travels with it during migrations, and remains accessible via a stable HTTPS endpoint. No external database servers, no connection strings, no network configuration—just HTTP requests to your container's service. ### Authentication All requests require authentication via the Hoody SDK. The SDK handles token management automatically when you initialize with credentials. ``` import { HoodyClient } from '@hoody-ai/hoody-sdk' const client = await HoodyClient.authenticate('https://api.hoody.icu', { username: 'your-user', password: 'your-pass' }) ``` ### Base URL Pattern ``` https://{projectId}-{containerId}-sqlite-{serviceId}.{node}.containers.hoody.icu ``` The SDK abstracts this—you only need to specify the `db` parameter (database path) in most calls. --- ## Common Workflows ### 1. Create a Database and Execute SQL Create a new database, then run SQL statements within a transaction. ``` // Step 1: Create a new database with optional KV store await client.sqlite.database.create('/data/myapp.db', true, 'kv_store') // Step 2: Execute a transaction with multiple SQL statements const result = await client.sqlite.database.executeTransaction('/data/myapp.db', true, { statements: [ { sql: 'CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)', params: [] }, { sql: 'INSERT INTO users (name, email) VALUES (?, ?)', params: ['Alice', 'alice@example.com'] }, { sql: 'INSERT INTO users (name, email) VALUES (?, ?)', params: ['Bob', 'bob@example.com'] } ] }) ``` ### 2. Execute a Shareable Query Run a single SELECT query via GET request—useful for read-only operations and shareable URLs. ``` const sql = btoa('SELECT * FROM users WHERE name = ?') const users = await client.sqlite.query.executeShareable('/data/myapp.db', sql) ``` ### 3. Key-Value Store: Basic CRUD Store, retrieve, update, and delete values using the KV store. ``` // Store a value await client.sqlite.kvStore.set('user:1', '/data/myapp.db', undefined, undefined, 3600, undefined, true, true, { value: { name: 'Alice', role: 'admin' } }) // Retrieve a value const user = await client.sqlite.kvStore.get({ key: 'user:1', db: '/data/myapp.db' }) // Check existence without retrieving const exists = await client.sqlite.kvStore.exists({ key: 'user:1', db: '/data/myapp.db' }) // Delete a key await client.sqlite.kvStore.delete({ key: 'user:1', db: '/data/myapp.db' }) ``` ### 4. Atomic Counters Increment and decrement numeric values atomically—ideal for rate limiting, counters, and inventory tracking. ``` // Initialize a counter await client.sqlite.kvStore.set('page:views', '/data/myapp.db', undefined, undefined, undefined, undefined, true, true, { value: 0 }) // Increment by 1 (default) await client.sqlite.kvStore.incr({ key: 'page:views', db: '/data/myapp.db' }) // Increment by 10 await client.sqlite.kvStore.incr('page:views', '/data/myapp.db', undefined, 10) // Decrement by 1 await client.sqlite.kvStore.decr({ key: 'page:views', db: '/data/myapp.db' }) // Read current value const views = await client.sqlite.kvStore.get({ key: 'page:views', db: '/data/myapp.db' }) ``` ### 5. Array Operations (Push, Pop, Remove) Manage arrays stored as JSON values—useful for queues, stacks, and lists. ``` // Initialize an array await client.sqlite.kvStore.set('queue:tasks', '/data/myapp.db', undefined, undefined, undefined, undefined, true, true, { value: [] }) // Push items to the end await client.sqlite.kvStore.push('queue:tasks', '/data/myapp.db', undefined, undefined, true, { value: 'task-1' }) await client.sqlite.kvStore.push('queue:tasks', '/data/myapp.db', undefined, undefined, true, { value: 'task-2' }) // Pop the last item const lastTask = await client.sqlite.kvStore.pop({ key: 'queue:tasks', db: '/data/myapp.db' }) // Remove by index await client.sqlite.kvStore.removeElement('queue:tasks', '/data/myapp.db', undefined, 0, true, { value: null }) ``` ### 6. Nested JSON Path Operations Access and modify nested values within JSON documents using path expressions. ``` // Store a nested document await client.sqlite.kvStore.set('config:app', '/data/myapp.db', undefined, undefined, undefined, undefined, true, true, { value: { database: { host: 'localhost', port: 5432 }, cache: { ttl: 300, maxSize: 1000 } } }) // Read a nested value using JSON path const dbPort = await client.sqlite.kvStore.get('config:app', '/data/myapp.db', undefined, 'database.port') // Update a nested value await client.sqlite.kvStore.set('config:app', '/data/myapp.db', 'cache.ttl', undefined, undefined, undefined, true, false, { value: 600 }) // Increment a nested counter await client.sqlite.kvStore.incr('config:app', '/data/myapp.db', undefined, 1, 'cache.maxSize') ``` ### 7. Batch Operations Perform multiple KV operations in a single transaction for efficiency. ``` // Batch set multiple keys await client.sqlite.kvStore.batchSet('/data/myapp.db', undefined, { items: { 'user:1': { name: 'Alice' }, 'user:2': { name: 'Bob' }, 'user:3': { name: 'Charlie' } } }) // Batch get multiple keys const users = await client.sqlite.kvStore.batchGet('/data/myapp.db', undefined, { keys: ['user:1', 'user:2', 'user:3'] }) // Batch delete multiple keys await client.sqlite.kvStore.batchDelete('/data/myapp.db', undefined, { keys: ['user:1', 'user:2', 'user:3'] }) ``` ### 8. List Keys with Filtering Enumerate keys with optional prefix filtering and pagination. ``` // List all keys const allKeys = await client.sqlite.kvStore.list({ db: '/data/myapp.db' }) // List keys with prefix filter const userKeys = await client.sqlite.kvStore.list('/data/myapp.db', undefined, 'user:') // List with pagination const page1 = await client.sqlite.kvStore.list('/data/myapp.db', undefined, undefined, 10, 0) const page2 = await client.sqlite.kvStore.list('/data/myapp.db', undefined, undefined, 10, 10) // Collect all pages automatically const allUserKeys = await client.sqlite.kvStore.listAll('/data/myapp.db', undefined, 'user:') ``` ### 9. Query History and Statistics Track and analyze SQL query execution patterns. ``` // Get recent query history const history = await client.sqlite.history.list('/data/myapp.db', 50) // Get aggregated statistics const stats = await client.sqlite.history.getStats({ db: '/data/myapp.db' }) // Delete a specific history entry await client.sqlite.history.deleteEntry(42, '/data/myapp.db') // Clear all history await client.sqlite.history.clear({ db: '/data/myapp.db' }) ``` ### 10. Health Monitoring Check service status and cache pressure. ``` // Full health check with memory, fd counters, and cache info const health = await client.sqlite.health.getHealth() // Lightweight cache-only health for dashboards const cacheHealth = await client.sqlite.health.getHealthCache() ``` --- ## Advanced Operations ### Time-Travel: Key Snapshots and Rollback Reconstruct historical states and undo operations. ``` // Get operation history for a key const keyHistory = await client.sqlite.kvStore.getHistory('user:1', '/data/myapp.db', undefined, 100) // Reconstruct a key's value at a specific operation number const snapshot = await client.sqlite.kvStore.getSnapshot('user:1', '/data/myapp.db', undefined, 5) // Rollback a key by N operations await client.sqlite.kvStore.rollback('user:1', '/data/myapp.db', undefined, 3) ``` ### Time-Travel: Table Snapshots and Diff Compare and restore entire table states across timestamps. ``` // Get the entire KV table at a specific timestamp const tableSnapshot = await client.sqlite.kvStore.getTableSnapshot( '/data/myapp.db', undefined, 1700000000 ) // Compare table state between two timestamps const diff = await client.sqlite.kvStore.compareSnapshots( '/data/myapp.db', undefined, 1700000000, 1700003600 ) // Dry-run a table rollback to preview changes const preview = await client.sqlite.kvStore.rollbackTable( '/data/myapp.db', undefined, 1700000000, true, undefined, {} ) // Confirm and execute the rollback await client.sqlite.kvStore.rollbackTable( '/data/myapp.db', undefined, 1700000000, false, 'yes', {} ) ``` ### Compare-and-Swap (CAS) Updates Prevent lost updates with optimistic concurrency control. ``` // Step 1: Read current value with metadata const current = await client.sqlite.kvStore.get({ key: 'counter:global', db: '/data/myapp.db' }) // Step 2: Update only if the value hasn't changed since read await client.sqlite.kvStore.set('counter:global', '/data/myapp.db', undefined, undefined, undefined, current.etag, true, false, { value: current.value + 1 }) ``` ### Error Recovery Patterns ``` async function safeKvSet(key: string, value: any, db: string) { try { await client.sqlite.kvStore.set(key, db, undefined, undefined, undefined, undefined, true, true, { value }) } catch (error: any) { if (error.status === 409) { // Conflict: key was modified concurrently, retry with fresh read const current = await client.sqlite.kvStore.get(key, db) await client.sqlite.kvStore.set(key, db, undefined, undefined, undefined, current.etag, true, false, { value }) } else if (error.status === 404) { // Database doesn't exist, create it first await client.sqlite.database.create(db, true) await client.sqlite.kvStore.set(key, db, undefined, undefined, undefined, undefined, true, true, { value }) } else { throw error } } } ``` ### Performance Considerations 1. **Use batch operations** for multiple keys—reduces HTTP round-trips significantly 2. **Set TTLs** on ephemeral data to prevent unbounded growth 3. **Disable history** (`history: false`) for high-frequency writes where audit trails aren't needed 4. **Use prefix filtering** when listing keys to reduce response payload 5. **Prefer KV store** over SQL for simple key lookups—lower overhead 6. **Use `listAll`** SDK helper for large key sets instead of manual pagination --- ## Quick Reference ### Essential Endpoints | Operation | SDK Method | HTTP | |-----------|-----------|------| | Create database | `client.sqlite.database.create(path)` | POST `/api/v1/sqlite/db/create` | | Execute SQL | `client.sqlite.database.executeTransaction(db, create, data)` | POST `/api/v1/sqlite/db` | | Shareable query | `client.sqlite.query.executeShareable(db, sql)` | GET `/api/v1/sqlite/query` | | Get value | `client.sqlite.kvStore.get(key, db)` | GET `/api/v1/sqlite/kv/{key}` | | Set value | `client.sqlite.kvStore.set(key, db, ..., data)` | PUT `/api/v1/sqlite/kv/{key}` | | Delete key | `client.sqlite.kvStore.delete(key, db)` | DELETE `/api/v1/sqlite/kv/{key}` | | List keys | `client.sqlite.kvStore.list(db, cursor?, prefix?, limit?, offset?)` | GET `/api/v1/sqlite/kv` | | Batch set | `client.sqlite.kvStore.batchSet(db, table, data)` | POST `/api/v1/sqlite/kv/batch/set` | | Batch get | `client.sqlite.kvStore.batchGet(db, table, data)` | POST `/api/v1/sqlite/kv/batch/get` | | Increment | `client.sqlite.kvStore.incr(key, db)` | POST `/api/v1/sqlite/kv/{key}/incr` | | Health | `client.sqlite.health.getHealth()` | GET `/api/v1/sqlite/health` | ### Required Parameters - **`db`** (string): Database file path—required on nearly every endpoint - **`key`** (string): KV store key name—required for all key-specific operations - **`sql`** (string): Base64-encoded SQL for shareable queries - **`path`** (string): JSON path for nested value access (e.g., `database.port`) - **`from`/`to`** (integer): Unix timestamps for snapshot comparison ### Typical Response Formats **KV Get** returns the stored value with metadata including `etag` for CAS operations. **List Keys** returns an array of key objects with pagination info (`total`, `offset`, `limit`). **Health** returns service identity, process memory/fd counters, and cache statistics. **Transaction** returns an array of result sets, one per SQL statement in the transaction.