# hoody-sqlite Subskill **Service**: hoody-sqlite **Purpose**: Portable SQLite databases accessible from anywhere via SQL over HTTP with key-value shortcuts **Last Updated**: 2025-11-05 --- ## Overview hoody-sqlite provides two complementary capabilities for data persistence inside Hoody containers: 1. **SQL Execution** — Run full SQLite transactions with ACID guarantees against named databases 2. **Key-Value Store** — High-level shortcuts for storing, retrieving, and manipulating structured data without writing SQL ### When to Use - **Configuration storage** — Store application settings as KV pairs with TTL support - **Session/state tracking** — Maintain mutable application state with rollback capability - **List management** — Append/remove items from arrays using push/pop/remove operations - **Atomic counters** — Track metrics with atomic increment/decrement - **Batch operations** — Read/write multiple keys in a single transaction - **Time travel** — Snapshot and rollback individual keys or entire tables to past states - **Raw SQL** — Execute arbitrary SQL statements when KV shortcuts are insufficient ### How It Fits the Hoody Philosophy Every database lives inside a container and is accessed through the Hoody proxy. No external database servers, no connection strings, no DNS setup. Point any tool at the container, authenticate, and your data is there — portable and isolated per project. ### Prerequisites - A running Hoody container (create via `hoody container create` — see core SKILL.md) - Container ID (set via `-c ` or `HOODY_CONTAINER` env var) - A database name (create with `hoody db create`, then pass via `--db ` on all subsequent commands) --- ## Common Workflows ### Workflow 1: Create a Database and Run SQL ``` # Step 1: Create a new SQLite database inside a container hoody db create -c my-container --path app.db -o json # Step 2: Execute a transaction (CREATE TABLE + INSERT) hoody db exec-transaction -c my-container --db app.db \ --sql "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);" \ --sql "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');" \ -o json # Step 3: Query data back with a shareable URL hoody db exec-shareable -c my-container --db app.db \ --sql "SELECT * FROM users;" \ -o json # Step 4: Check query history hoody db list -c my-container --db app.db -o json # Step 5: View history statistics hoody db stats -c my-container --db app.db -o json ``` ### Workflow 2: Key-Value Store Basics Set, retrieve, and manage simple values: ``` # Set a string value hoody kv set -c my-container --db app.db --key "app:name" --value '"My App"' -o json # Set a numeric value hoody kv set -c my-container --db app.db --key "app:version" --value '42' -o json # Get a value hoody kv get -c my-container --db app.db --key "app:name" -o json # Check existence without retrieving hoody kv exists -c my-container --db app.db --key "app:name" # List all keys (with optional prefix filter) hoody kv list -c my-container --db app.db --prefix "app:" -o json # Delete a key (requires --yes confirmation or interactive prompt) hoody kv delete -c my-container --db app.db --key "app:version" --yes -o json ``` ### Workflow 3: Atomic Counters ``` # Set initial counter hoody kv set -c my-container --db app.db --key "metrics:page_views" --value '0' -o json # Increment by 1 (default) hoody kv incr -c my-container --db app.db --key "metrics:page_views" -o json # Increment by a custom amount hoody kv incr -c my-container --db app.db --key "metrics:page_views" --amount 10 -o json # Decrement hoody kv decr -c my-container --db app.db --key "metrics:page_views" -o json # Read current value hoody kv get -c my-container --db app.db --key "metrics:page_views" -o json ``` ### Workflow 4: Array Operations ``` # Initialize an array key hoody kv set -c my-container --db app.db --key "tags:post-1" --value '[]' -o json # Push items to the end hoody kv push -c my-container --db app.db --key "tags:post-1" --value '"tutorial"' -o json hoody kv push -c my-container --db app.db --key "tags:post-1" --value '"sqlite"' -o json hoody kv push -c my-container --db app.db --key "tags:post-1" --value '"hoody"' -o json # Verify the array hoody kv get -c my-container --db app.db --key "tags:post-1" -o json # Pop the last element hoody kv pop -c my-container --db app.db --key "tags:post-1" -o json ``` ### Workflow 5: Batch Operations Operate on multiple keys in a single transaction (max 100 keys per batch): ``` # Batch set multiple keys hoody kv set -c my-container --db app.db --batch \ --items '{"user:1":"Alice","user:2":"Bob","user:3":"Carol"}' -o json # Batch get multiple keys hoody kv get -c my-container --db app.db --batch \ --keys '["user:1","user:2","user:3"]' -o json # Batch delete multiple keys (requires confirmation) hoody kv delete -c my-container --db app.db --batch \ --keys '["user:1","user:2","user:3"]' --yes -o json ``` ### Workflow 6: Key History and Rollback ``` # View operation history for a specific key hoody kv history -c my-container --db app.db --key "app:name" -o json # Rollback a key to undo the last N operations hoody kv rollback -c my-container --db app.db --key "app:name" --steps 2 -o json # Get a key's value at a specific operation number hoody kv get-key -c my-container --db app.db --key "app:name" --op 5 -o json ``` --- ## Advanced Operations ### Time-Travel: Table Snapshots and Diff Reconstruct the entire KV table at a past point in time, or compare two points: ``` # Snapshot the entire KV table at a specific timestamp hoody kv get-table -c my-container --db app.db \ --timestamp "2025-11-01T00:00:00Z" -o json # Compare KV table between two timestamps hoody kv compare-table -c my-container --db app.db \ --from "2025-11-01T00:00:00Z" \ --to "2025-11-05T00:00:00Z" -o json ``` **Response includes**: `created` (new keys), `modified` (changed values), `deleted` (removed keys). ### Full Table Rollback Roll the entire KV table back to a previous timestamp. Requires confirmation: ``` # Rollback entire table (destructive — requires --yes) hoody kv rollback-table -c my-container --db app.db \ --to-timestamp "2025-11-01T00:00:00Z" --yes -o json ``` **Recovery pattern**: Always snapshot or diff first before rolling back. If rollback goes wrong, the operation itself is recorded in history, so you can check `hoody kv history` to diagnose. ### Error Recovery Patterns **Scenario: Accidental key deletion** ``` # 1. Check what the key contained before deletion hoody kv history -c my-container --db app.db --key "critical:config" -o json # 2. Restore by rolling back one step hoody kv rollback -c my-container --db app.db --key "critical:config" --steps 1 -o json # 3. Verify restoration hoody kv get -c my-container --db app.db --key "critical:config" -o json ``` **Scenario: Bad batch write** ``` # 1. Diff to see what changed hoody kv compare-table -c my-container --db app.db \ --from "2025-11-04T23:59:00Z" \ --to "2025-11-05T00:01:00Z" -o json # 2. Roll back the table to before the bad write hoody kv rollback-table -c my-container --db app.db \ --to-timestamp "2025-11-04T23:59:00Z" --yes -o json ``` ### Query History Management ``` # Clear all query history for a database hoody db clear -c my-container --db app.db --yes -o json # Delete a specific history entry by index hoody db delete -c my-container --db app.db --index 42 --yes -o json ``` ### Performance Considerations - **Batch over individual**: Use `hoody kv set/get/delete --batch` for multiple keys instead of looping individual commands - **Use TTL**: When setting values, use `--ttl` to auto-expire ephemeral data (sessions, caches) - **Prefix filtering**: Use `hoody kv list --prefix` to narrow results instead of listing all keys - **Shareable queries**: Use `hoody db exec-shareable` for read-only queries that can be shared via URL --- ## Quick Reference ### Essential Commands | Task | Command | |------|---------| | Create database | `hoody db create -c --path ` | | Run SQL transaction | `hoody db exec-transaction -c --db --sql "..."` | | Set a value | `hoody kv set -c --db --key --value ` | | Get a value | `hoody kv get -c --db --key ` | | Check existence | `hoody kv exists -c --db --key ` | | List keys | `hoody kv list -c --db ` | | Delete a key | `hoody kv delete -c --db --key --yes` | | Increment counter | `hoody kv incr -c --db --key ` | | Push to array | `hoody kv push -c --db --key --value ` | | Pop from array | `hoody kv pop -c --db --key ` | | Batch get | `hoody kv get -c --db --batch --keys '[...]'` | | Key history | `hoody kv history -c --db --key ` | | Key rollback | `hoody kv rollback -c --db --key --steps ` | | Table snapshot | `hoody kv get-table -c --db --timestamp ` | | Table diff | `hoody kv compare-table -c --db --from --to ` | ### Required Flags - **Every command**: `-c ` (or set `HOODY_CONTAINER` env var) - **Database-scoped commands**: `--db ` - **Machine output**: `-o json` - **Destructive operations**: `--yes` to confirm ### Typical Response Envelope ``` { "ok": true, "data": { } } ``` ### Key-Value Types Supported - **Strings**: `'\"hello\"'` - **Numbers**: `'42'` or `'3.14'` - **Booleans**: `'true'` or `'false'` - **Arrays**: `'[1, 2, 3]'` - **Objects**: `'{"a": 1}'` - **Null**: `'null'`