<!--
hoody-sqlite Subskill (cli)
Auto-generated by Hoody Skills Generator
Generated: 2026-05-06T20:40:12.911Z
Model: mimo-v2.5-pro + fixer:mimo-v2.5-pro
Mode: cli


Tokens: 12201

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

# hoody-sqlite Subskill

## Overview

### What is hoody-sqlite?

hoody-sqlite provides **portable SQLite databases accessible from anywhere**. It combines full SQL database capabilities with a convenient key-value store interface, all accessible over HTTP through the Hoody CLI.

### When to Use hoody-sqlite

| Use Case | Recommended Approach |
|----------|---------------------|
| Simple key-value storage | `hoody kv` commands |
| Complex queries with joins | `hoody db` commands with SQL |
| Atomic counters | `hoody kv incr/decr` |
| Array operations | `hoody kv push/pop/remove` |
| Time-travel debugging | `hoody kv history/snapshot` |
| Batch operations | `hoody kv batch` commands |
| Transactional SQL | `hoody db exec-transaction` |

### How It Fits Into Hoody Philosophy

hoody-sqlite embodies the **"portable databases accessible from anywhere"** principle:

- **Zero configuration**: Databases are created and managed through CLI commands
- **Container-scoped**: Each database belongs to a container, ensuring isolation
- **Dual interface**: Use KV shortcuts for simple data, full SQL for complex queries
- **Time-travel built-in**: Every operation is tracked with history and snapshots
- **ACID guarantees**: All transactions maintain data integrity

### Architecture

```
┌─────────────────────────────────────────────────────────┐
│                    hoody CLI                             │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────┐ │
│  │  hoody kv   │  │  hoody db   │  │ hoody sqlite    │ │
│  └──────┬──────┘  └──────┬──────┘  └────────┬────────┘ │
└─────────┼────────────────┼──────────────────┼──────────┘
          │                │                  │
          ▼                ▼                  ▼
┌─────────────────────────────────────────────────────────┐
│              hoody-sqlite Service                        │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────┐ │
│  │  KV Store   │  │  SQL Engine │  │ History/Time    │ │
│  │  (fast ops) │  │  (full SQL) │  │ Travel          │ │
│  └─────────────┘  └─────────────┘  └─────────────────┘ │
└─────────────────────────────────────────────────────────┘
          │
          ▼
┌─────────────────────────────────────────────────────────┐
│              SQLite Database File                        │
└─────────────────────────────────────────────────────────┘
```

---

## Common Workflows

### Workflow 1: Database Lifecycle Management

#### Step 1: Create a New Database

```
# Create a new SQLite database
hoody db create --path myapp.db -c <container-id>
```

```
{
  "success": true,
  "path": "myapp.db",
  "created": "2025-01-15T10:30:00Z"
}
```

#### Step 2: Initialize with Schema

```
# Execute SQL transaction to create tables
hoody db exec-transaction --db myapp.db --sql "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE); CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT, FOREIGN KEY(user_id) REFERENCES users(id));" -c <container-id>
```

```
{
  "success": true,
  "changes": 0,
  "lastInsertRowid": 0
}
```

#### Step 3: Verify Database Health

```
# Check service health
hoody db stats --db myapp.db -c <container-id>
```

```
{
  "totalQueries": 1,
  "totalErrors": 0,
  "avgExecutionTime": 0.5,
  "lastQuery": "2025-01-15T10:30:01Z"
}
```

### Workflow 2: Key-Value Store Operations

#### Step 1: Store Configuration Values

```
# Set a simple string value
hoody kv set app:config:version --value "2.1.0" --db config.db -c <container-id>
```

```
{
  "key": "app:config:version",
  "value": "2.1.0",
  "created": "2025-01-15T10:35:00Z"
}
```

#### Step 2: Store Complex JSON Data

```
# Set a JSON object
hoody kv set app:settings --value '{"theme":"dark","language":"en","notifications":true}' --db config.db -c <container-id>
```

```
{
  "key": "app:settings",
  "value": "{\"theme\":\"dark\",\"language\":\"en\",\"notifications\":true}",
  "created": "2025-01-15T10:35:05Z"
}
```

#### Step 3: Retrieve and Verify

```
# Get the stored value
hoody kv get app:settings --db config.db -c <container-id>
```

```
{
  "key": "app:settings",
  "value": "{\"theme\":\"dark\",\"language\":\"en\",\"notifications\":true}",
  "created": "2025-01-15T10:35:05Z",
  "updated": "2025-01-15T10:35:05Z"
}
```

#### Step 4: Check Key Existence

```
# Check if key exists (HEAD request)
hoody kv exists app:settings --db config.db -c <container-id>
```

```
{
  "exists": true,
  "key": "app:settings"
}
```

### Workflow 3: Atomic Counter Operations

#### Step 1: Initialize Counter

```
# Set initial counter value
hoody kv set metrics:page_views --value "0" --db metrics.db -c <container-id>
```

```
{
  "key": "metrics:page_views",
  "value": "0",
  "created": "2025-01-15T11:00:00Z"
}
```

#### Step 2: Increment Counter

```
# Atomically increment
hoody kv incr metrics:page_views --db metrics.db -c <container-id>
```

```
{
  "key": "metrics:page_views",
  "value": "1",
  "operation": "increment"
}
```

#### Step 3: Decrement Counter

```
# Atomically decrement
hoody kv decr metrics:page_views --db metrics.db -c <container-id>
```

```
{
  "key": "metrics:page_views",
  "value": "0",
  "operation": "decrement"
}
```

### Workflow 4: Array Operations

#### Step 1: Create Array

```
# Initialize empty array
hoody kv set user:123:tags --value '[]' --db users.db -c <container-id>
```

```
{
  "key": "user:123:tags",
  "value": "[]",
  "created": "2025-01-15T11:10:00Z"
}
```

#### Step 2: Push Elements

```
# Append to array
hoody kv push user:123:tags --value '"admin"' --db users.db -c <container-id>
```

```
{
  "key": "user:123:tags",
  "value": "[\"admin\"]",
  "operation": "push"
}
```

```
# Push another element
hoody kv push user:123:tags --value '"editor"' --db users.db -c <container-id>
```

```
{
  "key": "user:123:tags",
  "value": "[\"admin\",\"editor\"]",
  "operation": "push"
}
```

#### Step 3: Pop Element

```
# Remove last element
hoody kv pop user:123:tags --db users.db -c <container-id>
```

```
{
  "key": "user:123:tags",
  "value": "[\"admin\"]",
  "popped": "editor",
  "operation": "pop"
}
```


### Workflow 5: Batch Operations

#### Step 1: Batch Set Multiple Keys

```
# Set multiple keys at once
hoody kv set --batch '[{"key":"user:1:name","value":"Alice"},{"key":"user:1:email","value":"alice@example.com"},{"key":"user:1:role","value":"admin"}]' --db users.db -c <container-id>
```

```
{
  "success": true,
  "keysSet": 3,
  "keys": ["user:1:name", "user:1:email", "user:1:role"]
}
```

#### Step 2: Batch Get Multiple Keys

```
# Get multiple keys at once
hoody kv get --batch '["user:1:name","user:1:email","user:1:role"]' --db users.db -c <container-id>
```

```
{
  "results": [
    {"key": "user:1:name", "value": "Alice"},
    {"key": "user:1:email", "value": "alice@example.com"},
    {"key": "user:1:role", "value": "admin"}
  ]
}
```

#### Step 3: Batch Delete Multiple Keys

```
# Delete multiple keys at once
hoody kv delete --batch '["user:1:name","user:1:email","user:1:role"]' --yes --db users.db -c <container-id>
```

```
{
  "success": true,
  "keysDeleted": 3,
  "keys": ["user:1:name", "user:1:email", "user:1:role"]
}
```

### Workflow 6: Query History Management

#### Step 1: Execute Some Queries

```
# Execute SQL queries
hoody db exec-transaction --db myapp.db --sql "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');" -c <container-id>
```

```
{
  "success": true,
  "changes": 1,
  "lastInsertRowid": 1
}
```

#### Step 2: View Query History

```
# List recent queries
hoody db list --db myapp.db -c <container-id>
```

```
{
  "history": [
    {
      "index": 1,
      "query": "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');",
      "timestamp": "2025-01-15T11:30:00Z",
      "duration": 0.8
    }
  ],
  "total": 1
}
```

#### Step 3: View History Statistics

```
# Get aggregated stats
hoody db stats --db myapp.db -c <container-id>
```

```
{
  "totalQueries": 5,
  "totalErrors": 0,
  "avgExecutionTime": 0.6,
  "slowestQuery": "SELECT * FROM users JOIN posts ON users.id = posts.user_id;",
  "fastestQuery": "SELECT 1;"
}
```

#### Step 4: Delete Specific History Entry

```
# Delete history entry by index
hoody db delete 1 --yes --db myapp.db -c <container-id>
```

```
{
  "success": true,
  "deleted": 1
}
```

#### Step 5: Clear All History

```
# Clear entire history
hoody db clear --db myapp.db -c <container-id>
```

```
{
  "success": true,
  "deleted": 5
}
```

---

## Advanced Operations

### Advanced Workflow 1: Time-Travel Debugging

#### Step 1: Track Key History

```
# View operation history for a key
hoody kv history user:123:balance --db accounts.db -c <container-id>
```

```
{
  "key": "user:123:balance",
  "history": [
    {"op": 1, "value": "1000", "timestamp": "2025-01-15T09:00:00Z", "operation": "set"},
    {"op": 2, "value": "1100", "timestamp": "2025-01-15T10:00:00Z", "operation": "incr"},
    {"op": 3, "value": "900", "timestamp": "2025-01-15T11:00:00Z", "operation": "decr"}
  ]
}
```

#### Step 2: Get Snapshot at Specific Operation

```
# Get value at operation 2
hoody kv get-key user:123:balance --op 2 --db accounts.db -c <container-id>
```

```
{
  "key": "user:123:balance",
  "value": "1100",
  "op_number": 2,
  "timestamp": "2025-01-15T10:00:00Z"
}
```

#### Step 3: Rollback Key to Previous State

```
# Rollback last 2 operations
hoody kv rollback user:123:balance --steps 2 --db accounts.db -c <container-id>
```

```
{
  "key": "user:123:balance",
  "value": "1000",
  "rolledBack": 2,
  "currentOp": 4
}
```

### Advanced Workflow 2: Table-Level Time Travel

#### Step 1: Get Table Snapshot at Timestamp

```
# Get entire KV table state at specific time
hoody kv get-table --timestamp "2025-01-15T10:00:00Z" --db config.db -c <container-id>
```

```
{
  "timestamp": "2025-01-15T10:00:00Z",
  "snapshot": {
    "app:version": "1.0.0",
    "app:theme": "light",
    "user:count": "42"
  }
}
```

#### Step 2: Compare Table Snapshots

```
# Compare two timestamps
hoody kv compare-table --from "2025-01-15T09:00:00Z" --to "2025-01-15T12:00:00Z" --db config.db -c <container-id>
```

```
{
  "from": "2025-01-15T09:00:00Z",
  "to": "2025-01-15T12:00:00Z",
  "created": ["app:new_feature"],
  "modified": ["app:version", "user:count"],
  "deleted": ["app:deprecated_flag"]
}
```

#### Step 3: Rollback Entire Table

```
# Rollback table to specific timestamp (requires confirmation)
hoody kv rollback-table --to "2025-01-15T09:00:00Z" --yes --db config.db -c <container-id>
```

```
{
  "success": true,
  "rolledBackTo": "2025-01-15T09:00:00Z",
  "keysAffected": 5
}
```

### Advanced Workflow 3: JSON Path Operations

#### Step 1: Store Nested JSON

```
# Store complex nested object
hoody kv set user:123:profile --value '{"name":"Alice","address":{"city":"NYC","zip":"10001"},"scores":[95,87,92]}' --db users.db -c <container-id>
```

```
{
  "key": "user:123:profile",
  "value": "{\"name\":\"Alice\",\"address\":{\"city\":\"NYC\",\"zip\":\"10001\"},\"scores\":[95,87,92]}",
  "created": "2025-01-15T12:00:00Z"
}
```

#### Step 2: Extract Nested Value with JSON Path

```
# Get nested value using JSON path
hoody kv get user:123:profile --path "address.city" --db users.db -c <container-id>
```

```
{
  "key": "user:123:profile",
  "path": "address.city",
  "value": "NYC"
}
```

#### Step 3: Increment Nested Numeric Value

```
# Increment nested counter
hoody kv incr user:123:profile --path "scores[0]" --db users.db -c <container-id>
```

```
{
  "key": "user:123:profile",
  "path": "scores[0]",
  "value": "96",
  "operation": "increment"
}
```

### Advanced Workflow 4: Shareable SQL Queries

#### Step 1: Create Shareable Query URL

```
# Execute SQL with base64-encoded query (generates shareable URL)
hoody db exec-shareable --db myapp.db --sql "SELECT u.name, COUNT(p.id) as post_count FROM users u LEFT JOIN posts p ON u.id = p.user_id GROUP BY u.id ORDER BY post_count DESC LIMIT 10;" -c <container-id>
```

```
{
  "query": "SELECT u.name, COUNT(p.id) as post_count FROM users u LEFT JOIN posts p ON u.id = p.user_id GROUP BY u.id ORDER BY post_count DESC LIMIT 10;",
  "results": [
    {"name": "Alice", "post_count": 15},
    {"name": "Bob", "post_count": 12},
    {"name": "Charlie", "post_count": 8}
  ],
  "shareableUrl": "https://{projectId}-{containerId}-sqlite-{serviceId}.{node}.containers.hoody.icu/api/v1/sqlite/query?db=myapp.db&sql=U0VMRUNUIHUubmFtZSwgQ09VTlQocC5pZCkgYXMgcG9zdF9jb3VudCBGUk9NIHVzZXJzIHUgTEVGVCBKT0lOIHBvc3RzIHAgT04gdS5pZCA9IHAudXNlcl9pZCBHUk9VUCBCWSB1LmlkIE9SREVSIEJZIHBvc3RfY291bnQgREVTQyBMSU1JVCAxMDs="
}
```

### Error Recovery Patterns

#### Pattern 1: Transaction Rollback on Failure

```
# If transaction fails, check history and retry
hoody db exec-transaction --db myapp.db --sql "BEGIN; INSERT INTO orders (user_id, total) VALUES (1, 100); UPDATE inventory SET stock = stock - 1 WHERE product_id = 42; COMMIT;" -c <container-id>
```

If this fails, the transaction is automatically rolled back. Verify with:

```
# Check if changes were applied
hoody db list --db myapp.db -c <container-id>
```

#### Pattern 2: Key Recovery from History

```
# If a key was accidentally deleted, check history
hoody kv history important:config --db config.db -c <container-id>
```

```
{
  "key": "important:config",
  "history": [
    {"op": 1, "value": "production_value", "timestamp": "2025-01-15T09:00:00Z", "operation": "set"},
    {"op": 2, "value": null, "timestamp": "2025-01-15T12:00:00Z", "operation": "delete"}
  ]
}
```

```
# Restore from snapshot
hoody kv get-key important:config --op 1 --db config.db -c <container-id>
```

```
{
  "key": "important:config",
  "value": "production_value",
  "op_number": 1
}
```

```
# Re-set the value
hoody kv set important:config --value "production_value" --db config.db -c <container-id>
```

### Performance Considerations

#### 1. Use Batch Operations for Bulk Data

```
# Instead of 100 individual sets:
# ❌ hoody kv set key1 --value "val1" ...
# ❌ hoody kv set key2 --value "val2" ...
# ✅ Use batch:
hoody kv set --batch '[{"key":"key1","value":"val1"},{"key":"key2","value":"val2"}]' --db mydb.db -c <container-id>
```

#### 2. Limit Query History

```
# Get only recent history (limit to 10 entries)
hoody db list --limit 10 --db myapp.db -c <container-id>
```

#### 3. Use JSON Paths for Partial Updates

```
# Instead of reading, modifying, and writing entire object:
# ❌ hoody kv get user:123 ...
# ❌ (modify in memory)
# ❌ hoody kv set user:123 ...
# ✅ Use JSON path for targeted update:
hoody kv incr user:123:profile --path "login_count" --db users.db -c <container-id>
```

#### 4. Clear Old History Periodically

```
# Clear history to free up storage
hoody db clear --db myapp.db -c <container-id>
```

---

## Quick Reference

### Essential Commands

| Operation | Command |
|-----------|---------|
| Create database | `hoody db create --path <path> -c <id>` |
| Execute SQL | `hoody db exec-transaction --db <db> --sql "<sql>" -c <id>` |
| List keys | `hoody kv list --db <db> -c <id>` |
| Get value | `hoody kv get <key> --db <db> -c <id>` |
| Set value | `hoody kv set <key> --value "<val>" --db <db> -c <id>` |
| Delete key | `hoody kv delete <key> --yes --db <db> -c <id>` |
| Check exists | `hoody kv exists <key> --db <db> -c <id>` |
| Increment | `hoody kv incr <key> --db <db> -c <id>` |
| Decrement | `hoody kv decr <key> --db <db> -c <id>` |
| Push to array | `hoody kv push <key> --value "<val>" --db <db> -c <id>` |
| Pop from array | `hoody kv pop <key> --db <db> -c <id>` |
| View history | `hoody kv history <key> --db <db> -c <id>` |
| Get snapshot | `hoody kv get-key <key> --op <n> --db <db> -c <id>` |
| Rollback key | `hoody kv rollback <key> --steps <n> --db <db> -c <id>` |
| Batch set | `hoody kv set --batch '<json>' --db <db> -c <id>` |
| Batch get | `hoody kv get --batch '<json>' --db <db> -c <id>` |
| Batch delete | `hoody kv delete --batch '<json>' --yes --db <db> -c <id>` |
| Table snapshot | `hoody kv get-table --timestamp "<ts>" --db <db> -c <id>` |
| Table diff | `hoody kv compare-table --from "<ts>" --to "<ts>" --db <db> -c <id>` |
| Table rollback | `hoody kv rollback-table --to "<ts>" --yes --db <db> -c <id>` |
| Query history | `hoody db list --db <db> -c <id>` |
| History stats | `hoody db stats --db <db> -c <id>` |
| Clear history | `hoody db clear --db <db> -c <id>` |
| Delete history entry | `hoody db delete <index> --yes --db <db> -c <id>` |
| Shareable query | `hoody db exec-shareable --db <db> --sql "<sql>" -c <id>` |

### Common Parameters

| Parameter | Description | Example |
|-----------|-------------|---------|
| `-c <id>` | Container ID (required) | `-c abc123` |
| `--db <path>` | Database file path | `--db myapp.db` |
| `--key <key>` | KV store key | `--key user:123` |
| `--value <val>` | Value to store | `--value "hello"` |
| `--path <jsonpath>` | JSON path for nested values | `--path "address.city"` |
| `--sql <query>` | SQL statement | `--sql "SELECT * FROM users"` |
| `--batch <json>` | JSON array for batch ops | `--batch '[{"key":"k1","value":"v1"}]'` |
| `--op <number>` | Operation number for snapshots | `--op 5` |
| `--steps <n>` | Number of operations to rollback | `--steps 3` |
| `--timestamp <ts>` | ISO 8601 timestamp | `--timestamp "2025-01-15T10:00:00Z"` |
| `--from <ts>` | Start timestamp for diff | `--from "2025-01-15T09:00:00Z"` |
| `--to <ts>` | End timestamp for diff/rollback | `--to "2025-01-15T12:00:00Z"` |
| `--limit <n>` | Limit results | `--limit 10` |
| `--yes` | Skip confirmation prompts | `--yes` |
| `-o json` | JSON output format | `-o json` |

### Response Formats

**Success Response:**
```
{
  "success": true,
  "key": "example_key",
  "value": "example_value",
  "created": "2025-01-15T10:00:00Z"
}
```

**Error Response:**
```
{
  "success": false,
  "error": "Key not found",
  "code": "KEY_NOT_FOUND"
}
```

**Batch Response:**
```
{
  "success": true,
  "keysSet": 3,
  "keys": ["key1", "key2", "key3"]
}
```

**History Response:**
```
{
  "key": "example_key",
  "history": [
    {"op": 1, "value": "initial", "timestamp": "2025-01-15T09:00:00Z", "operation": "set"},
    {"op": 2, "value": "updated", "timestamp": "2025-01-15T10:00:00Z", "operation": "set"}
  ]
}
```

### Service Health

```
# Check service health
hoody db stats --db <db> -c <container-id> -o json
```

```
{
  "totalQueries": 150,
  "totalErrors": 2,
  "avgExecutionTime": 0.45,
  "lastQuery": "2025-01-15T12:30:00Z"
}
```

### Container Targeting

All commands require container targeting via one of:

1. **CLI flag**: `-c <container-id>`
2. **Environment variable**: `export HOODY_CONTAINER=<container-id>`

```
# Using flag
hoody kv list --db mydb.db -c abc123

# Using environment variable
export HOODY_CONTAINER=abc123
hoody kv list --db mydb.db
```

### Output Formats

Use `-o` flag for different output formats:

```
# Table format (default)
hoody kv list --db mydb.db -c abc123

# JSON format (machine-readable)
hoody kv list --db mydb.db -c abc123 -o json

# YAML format
hoody kv list --db mydb.db -c abc123 -o yaml

# Wide format (more columns)
hoody kv list --db mydb.db -c abc123 -o wide

# Raw format (no formatting)
hoody kv list --db mydb.db -c abc123 -o raw
```