# SQLite

**Page:** kit/sqlite

[Download Raw Markdown](./kit/sqlite.md)

---

# SQLite

**Your database is a URL.** Execute SQL via HTTP, store key-value data, travel through time, perform atomic operations—all serverless, all through HTTP endpoints.

Every Hoody container includes **hoody-sqlite**, providing serverless database access with both traditional SQL and a powerful Key-Value store interface.

### Absolute-path DB files

`hoody-sqlite` has two modes for the `?db=` parameter:

- **Bare name mode** (default on the standalone binary): `?db=notes` resolves to `./notes.db` relative to the server's working directory. Absolute paths like `?db=/data/app.db` are **rejected** with `INVALID_DB_PATH`.
- **Any-path mode**: pass `--allow-any-absolute-db-path` when starting the binary. Absolute paths (`/data/app.db`) are then accepted and used verbatim.

**In Hoody containers this flag is ON by default**, which is why examples throughout this page use `?db=/data/*.db`. If you're running the binary yourself, either drop the absolute path or add the flag.

**Stacks perfectly with [SQLite Drive →](/foundation/storage/sqlite-drive/):** Store databases in `/hoody/databases/` for multi-container access (concurrent write-safe). Query via HTTP through hoody-sqlite OR directly with traditional SQLite libraries (Python sqlite3, Node better-sqlite3, etc.)—mix both approaches freely.

---

## What You Can Do

**hoody-sqlite** provides dual database interfaces through HTTP:

- **🌐 Web Database UI** - Visual SQL query interface in your browser—main entry point for exploration
- **📊 SQL Operations** - Execute queries and transactions via HTTP POST
- **🗂️ Key-Value Store** - NoSQL-style GET/SET operations
- **⚛️ Atomic Operations** - Thread-safe increment, decrement, push, pop
- **📦 Batch Operations** - 100 keys in one atomic request
- **⏱️ Time-Travel** - Query historical data, view snapshots at any point in time
- **🔄 Rollback** - Undo changes instantly, restore to previous state
- **🔗 Shareable Queries** - Create read-only query URLs (Base64-encoded SQL)
- **🎯 Zero Configuration** - No database server, no connection pooling, just HTTP
- **📝 Audit Trail** - Complete change history for compliance


**Shared Databases & Access Control**:
- **[SQLite Drive →](/foundation/storage/sqlite-drive/)** - Store databases in `/hoody/databases/` for multi-container access with no locking issues
- **[Proxy Permissions →](/foundation/proxy/permissions/)** - Control who can query databases (IP whitelist, passwords, JWT)


---

## API Endpoints Summary

**Official Technical Reference:**

For complete endpoint documentation with all parameters, responses, and examples:

**SQL Operations:**
- **[POST /api/v1/sqlite/db](/api/sqlite/sql-operations/#post-apiv1sqlitedb)** - Execute atomic SQL transactions
  - Supports: Multiple statements, parameterized queries, bulk inserts
- **[GET /api/v1/sqlite/query](/api/sqlite/sql-operations/#get-apiv1sqlitequery)** - Read-only query via GET
  - Query param: `sql` (Base64-encoded SELECT statement)
- **[POST /api/v1/sqlite/db/create](/api/sqlite/sql-operations/#post-apiv1sqlitedbcreate)** - Create database
  - Query param: `path`, `init_kv` (auto-create KV table)

**Key-Value Store - Basic:**
- **[GET /api/v1/sqlite/kv/\{key\}](/api/sqlite/kv-basic/#get-apiv1sqlitekvkey)** - Get value by key
  - Query params: `db`, `table`, `path` (JSON path), `at_timestamp` (time-travel, Unix timestamp integer)
- **[PUT /api/v1/sqlite/kv/\{key\}](/api/sqlite/kv-basic/#put-apiv1sqlitekvkey)** - Set/update value
  - Query params: `ttl` (auto-expiry), `if_match` (CAS), `path` (partial update), `history`
- **[DELETE /api/v1/sqlite/kv/\{key\}](/api/sqlite/kv-basic/#delete-apiv1sqlitekvkey)** - Delete key
- **[HEAD /api/v1/sqlite/kv/\{key\}](/api/sqlite/kv-basic/#head-apiv1sqlitekvkey)** - Check existence
- **[GET /api/v1/sqlite/kv](/api/sqlite/kv-store/)** - List keys with prefix filtering

**Key-Value Store - Batch:**
- **[POST /api/v1/sqlite/kv/batch/get](/api/sqlite/kv-batch/)** - Get up to 100 keys atomically
- **[POST /api/v1/sqlite/kv/batch/set](/api/sqlite/kv-batch/)** - Set up to 100 keys atomically
- **[POST /api/v1/sqlite/kv/batch/delete](/api/sqlite/kv-batch/)** - Delete up to 100 keys atomically

**Key-Value Store - Atomic:**
- **[POST /api/v1/sqlite/kv/\{key\}/incr](/api/sqlite/kv-atomic/)** - Atomic increment (thread-safe)
  - Query param: `delta` (amount to add, default: 1)
- **[POST /api/v1/sqlite/kv/\{key\}/decr](/api/sqlite/kv-atomic/)** - Atomic decrement
  - Query param: `delta` (amount to subtract, default: 1)
- **[POST /api/v1/sqlite/kv/\{key\}/push](/api/sqlite/kv-atomic/)** - Atomic array push
  - Body: `{"value": item}` (add to end of array)
- **[POST /api/v1/sqlite/kv/\{key\}/pop](/api/sqlite/kv-atomic/)** - Atomic array pop (LIFO)
  - Returns and removes last element
- **[POST /api/v1/sqlite/kv/\{key\}/remove](/api/sqlite/kv-atomic/)** - Remove array element
  - Body: `{"value": item}` or query param `index` (position)

**Key-Value Store - Time-Travel:**
- **[GET /api/v1/sqlite/kv/\{key\}/history](/api/sqlite/kv-time-travel/)** - Complete change history
  - Query params: `limit` (pagination)
- **[GET /api/v1/sqlite/kv/\{key\}/snapshot](/api/sqlite/kv-time-travel/)** - Value at operation number
  - Query param: `op_number` (integer, required — get from `/history` response)
- **[GET /api/v1/sqlite/kv/diff](/api/sqlite/kv-time-travel/)** - Compare time periods
  - Query params: `from`, `to` (Unix timestamps)
- **[POST /api/v1/sqlite/kv/\{key\}/rollback](/api/sqlite/kv-time-travel/)** - Undo changes
  - Query param: `steps` (number of changes to undo, default: 1)
- **[GET /api/v1/sqlite/kv/snapshot](/api/sqlite/kv-time-travel/)** - Snapshot entire KV table at timestamp
  - Query param: `timestamp` (Unix timestamp integer)
- **[POST /api/v1/sqlite/kv/rollback](/api/sqlite/kv-time-travel/)** - Rollback entire table
  - Query params: `to_timestamp` (Unix timestamp integer, required), `confirm=yes` (required to apply — omit for dry-run preview)

**Query History:**
- **[GET /api/v1/sqlite/history](/api/sqlite/kv-time-travel/)** - Get query history
- **[DELETE /api/v1/sqlite/history](/api/sqlite/kv-time-travel/)** - Clear query history
- **[DELETE /api/v1/sqlite/history/\{index\}](/api/sqlite/kv-time-travel/)** - Delete specific history entry
- **[GET /api/v1/sqlite/history/stats](/api/sqlite/kv-time-travel/)** - Get history statistics

**Web Interface:**
- **[GET /](/api/sqlite/sql-operations/)** - Web-based SQL query interface
  - Visual database browser in your browser
  - Execute queries interactively
  - View results in tabular format
  - Browse database schema

**System Monitoring:**
- **[GET /api/v1/sqlite/health](/api/sqlite/sql-operations/)** - Service health check
- **[GET /api/v1/sqlite/health/cache](/api/sqlite/sql-operations/)** - Cache health snapshot

---

## Core Capabilities

### 1. Web Database Interface (Main Entry Point)

**Open the container SQLite URL in your browser for visual database management:**

```
https://{project}-{container}-sqlite-1.{server}.containers.hoody.icu
```

**Interactive web interface with:**
- 📊 **SQL query editor** - Write and execute queries visually
- 📋 **Schema browser** - View tables, indexes, structure
- 📈 **Result viewer** - See query results in table format
- 💾 **Database selector** - Switch between different .db files
- 🔍 **Query history** - Review past queries

**Perfect for:** Exploring databases, testing queries, viewing table contents, debugging data issues—all without leaving the browser.

**Works on any device:** Phone, tablet, laptop—same interface everywhere.

### 2. Serverless SQL via HTTP (For Automation)

**No database server needed. Just HTTP requests:**


  
    ```bash
    # Execute a SQL transaction
    hoody db exec-transaction --transaction '{
      "transaction": [
        {
          "statement": "INSERT INTO users (name, email) VALUES (?, ?)",
          "values": ["Alice", "alice@example.com"]
        },
        {
          "query": "SELECT * FROM users WHERE email = ?",
          "values": ["alice@example.com"]
        }
      ]
    }'
    ```
  
  
    ```typescript
    import { HoodyClient } from '@hoody-ai/hoody-sdk';

    const client = new HoodyClient({ baseURL: 'https://api.hoody.icu', token: process.env.HOODY_TOKEN });
    const containerClient = await client.withContainer({ id: CONTAINER_ID, project_id: PROJECT_ID, server: SERVER });

    const result = await containerClient.sqlite.database.executeTransaction({
      transaction: [
        { statement: 'INSERT INTO users (name, email) VALUES (?, ?)', values: ['Alice', 'alice@example.com'] },
        { query: 'SELECT * FROM users WHERE email = ?', values: ['alice@example.com'] },
      ],
    });
    console.log(result.data); // { results: [{ rowsUpdated: 1 }, { resultSet: [{ id: 1, name: "Alice", ... }] }] }
    ```
  
  
    ```bash
    curl -X POST "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.icu/api/v1/sqlite/db?db=/data/app.db" \
      -H "Content-Type: application/json" \
      -d '{
        "transaction": [
          {
            "statement": "INSERT INTO users (name, email) VALUES (?, ?)",
            "values": ["Alice", "alice@example.com"]
          },
          {
            "query": "SELECT * FROM users WHERE email = ?",
            "values": ["alice@example.com"]
          }
        ]
      }'
    ```
  




**Response includes query results:**
```json
{
  "results": [
    {"rowsUpdated": 1},
    {"resultSet": [{"id": 1, "name": "Alice", "email": "alice@example.com"}]}
  ]
}
```

**The breakthrough:** Your database is accessible via HTTP from:
- AI agents (standard HTTP calls)
- Mobile devices (fetch from phone)
- Other containers (cross-container data access)
- Embedded iframes (live data in dashboards)
- Any HTTP client (no database driver needed)

### 3. Key-Value Store Interface

**NoSQL-style operations on SQLite:**


  
    ```bash
    # Set a key-value pair
    hoody kv set "user:1" --body '{"name": "Alice", "role": "editor"}'

    # Get value by key
    hoody kv get "user:1"

    # Delete a key
    hoody kv delete "user:1"

    # Atomic increment
    hoody kv incr "views:homepage"
    ```
  
  
    ```typescript
    // Set a key-value pair
    await containerClient.sqlite.kvStore.set('user:1', JSON.stringify({ name: 'Alice', role: 'editor' }));

    // Get value by key
    const user = await containerClient.sqlite.kvStore.get('user:1');
    console.log(user); // { name: "Alice", role: "editor" }

    // Delete a key
    await containerClient.sqlite.kvStore.delete('user:1');

    // Atomic increment (thread-safe)
    await containerClient.sqlite.kvStore.incr('views:homepage');
    ```
  
  
    ```bash
    # Set a key-value pair
    curl -X PUT "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.icu/api/v1/sqlite/kv/user:1?db=/data/app.db" \
      -H "Content-Type: application/json" \
      -d '{"name": "Alice", "role": "editor"}'

    # Get value by key
    curl "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.icu/api/v1/sqlite/kv/user:1?db=/data/app.db"

    # Delete a key
    curl -X DELETE "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.icu/api/v1/sqlite/kv/user:1?db=/data/app.db"

    # Atomic increment
    curl -X POST "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.icu/api/v1/sqlite/kv/views:homepage/incr?db=/data/stats.db"
    ```
  




Get the value back:



**Perfect for:**
- Configuration storage
- Session management
- Caching API responses
- Feature flags
- User preferences

### 4. Atomic Operations (No Race Conditions)

**Thread-safe operations for shared state:**



Multiple clients calling simultaneously? No problem - each increment is atomic. Counter goes: 5 -> 6 -> 7 -> 8 (not 5 -> 6 -> 6 -> 6)

**Atomic operations:**


  
```bash
# Increment counter
POST /api/v1/sqlite/kv/{key}/incr?delta=1

# Decrement inventory
POST /api/v1/sqlite/kv/inventory:item1/decr?delta=1

# Perfect for: views, likes, credits, inventory
```
  
  
  
```bash
# Add item to shopping cart
POST /api/v1/sqlite/kv/cart:user1/push
{"value": "product-123"}

# Remove last item
POST /api/v1/sqlite/kv/cart:user1/pop

# Remove specific item
POST /api/v1/sqlite/kv/cart:user1/remove
{"value": "product-123"}
```
  


**No race conditions. No locks needed. Just atomic HTTP operations.**

### 5. Batch Operations

**Operate on 100 keys in one atomic request:**

```javascript
// Set multiple keys atomically
await fetch('.../kv/batch/set?db=/data/app.db', {
  method: 'POST',
  body: JSON.stringify({
    batch: [
      { key: 'user:1', value: { name: 'Alice' } },
      { key: 'user:2', value: { name: 'Bob' } },
      { key: 'user:3', value: { name: 'Carol' } }
    ]
  })
});

// Get multiple keys in one request
await fetch('.../kv/batch/get?db=/data/app.db', {
  method: 'POST',
  body: JSON.stringify({
    keys: ['user:1', 'user:2', 'user:3']
  })
});
```

**100x faster than individual requests.** All operations succeed or all fail (atomic).

### 6. Time-Travel and Audit

**Every change is recorded:**

```bash
# View complete change history (returns op_number for each entry)
GET /api/v1/sqlite/kv/config:timeout/history?db=/data/app.db

# Get value at a specific operation number (op_number from history)
GET /api/v1/sqlite/kv/config:timeout/snapshot?op_number=42&db=/data/app.db

# Undo last change
POST /api/v1/sqlite/kv/config:timeout/rollback?db=/data/app.db

# Restore entire database to 2 hours ago (dry-run preview — add &confirm=yes to apply)
timestamp=$(date -d '2 hours ago' +%s)
POST /api/v1/sqlite/kv/rollback?to_timestamp=$timestamp&db=/data/app.db
```

**Perfect for:**
- Auditing (compliance requirements)
- Debugging (what changed and when?)
- Recovery (undo bad changes instantly)
- Testing (rollback after experiments)

### 7. Shareable Query URLs

**Create read-only query links:**

```javascript
// 1. Define query
const sql = "SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC";

// 2. Encode as Base64
const encoded = btoa(sql);

// 3. Create shareable URL
const queryUrl = `/api/v1/sqlite/query?db=/data/app.db&sql=${encoded}`;

// Share this URL - anyone can view live data
// No write access, perfectly safe
```

**Use cases:**
- Embed live data in dashboards
- Share reports with stakeholders
- Public API endpoints from private data
- Documentation with live queries

---

## Why This Changes Everything

### Traditional Databases

```
Database Server (install) → Connection Pool (configure) → Client Library (install) → Query (finally)
```

**Problems:**
- Server installation and management
- Connection string complexity
- Language-specific drivers
- Connection pooling configuration
- Port exposure and firewalls
- AI needs database-specific SDKs

### Hoody SQLite

```
HTTP Request → SQLite File → Response (immediately)
```

**Advantages:**
- ✅ Zero server setup (just files)
- ✅ Zero connection config (HTTP everywhere)
- ✅ Universal access (HTTP is the driver)
- ✅ AI-native (standard HTTP requests)
- ✅ Observable (all queries logged)
- ✅ Embeddable (query results in iframes)
- ✅ Serverless (no daemon processes)
- ✅ Simple backup (copy the .db file)

### HTTP Unlocks Databases

**Because databases are HTTP:**

1. **Your phone can query databases:**
   ```javascript
   // From mobile browser
   const users = await fetch(
     'https://{project}-{container}-sqlite-1.{server}.containers.hoody.icu/api/v1/sqlite/db?db=/data/app.db',
     {
       method: 'POST',
       body: JSON.stringify({
         transaction: [{ query: 'SELECT * FROM users LIMIT 10' }]
       })
     }
   ).then(r => r.json());
   ```

2. **AI agents have native database access:**
   ```javascript
   // AI makes standard HTTP request
   await fetch(sqliteUrl + '/api/v1/sqlite/db?db=/data/app.db', {
     method: 'POST',
     body: JSON.stringify({
       transaction: [{ query: 'SELECT COUNT(*) FROM orders WHERE status = "pending"' }]
     })
   });
   // No database driver. No connection string. Just HTTP.
   ```

3. **Embed live data in pages:**
   ```html
   <iframe src="https://{project}-{container}-sqlite-1.{server}.containers.hoody.icu/api/v1/sqlite/query?db=/data/stats.db&sql=U0VMRUNUIC4uLg==" />
   ```

---

## Common Workflows

### Application Configuration

**Store and retrieve config via KV store:**

```javascript
// Set configuration
await fetch('.../kv/config:api?db=/data/app.db', {
  method: 'PUT',
  body: JSON.stringify({
    timeout: 30,
    retries: 3,
    endpoint: 'https://api.example.com'
  })
});

// Get configuration
const config = await fetch('.../kv/config:api?db=/data/app.db')
  .then(r => r.json());

// Use in app
const response = await fetch(config.endpoint, { timeout: config.timeout });
```

### Distributed Counter

**Track metrics across multiple clients:**

```javascript
// Each page view increments atomically
await fetch('.../kv/views:homepage/incr?db=/data/stats.db', {
  method: 'POST'
});

// Get current count
const count = await fetch('.../kv/views:homepage?db=/data/stats.db')
  .then(r => r.json());

console.log(`Homepage views: ${count}`);
```

**No race conditions.** 1000 simultaneous requests = counter increments exactly 1000.

### Session Management

**Store user sessions with auto-expiry:**

```javascript
// Create session with 1-hour TTL
await fetch('.../kv/session:abc123?db=/data/sessions.db&ttl=3600', {
  method: 'PUT',
  body: JSON.stringify({
    user_id: 'user-456',
    ip: '203.0.113.50',
    created_at: Date.now()
  })
});

// Session automatically expires after 1 hour
// No cleanup jobs needed
```

### Shopping Cart

**Atomic cart operations:**

```javascript
// Add item to cart
await fetch('.../kv/cart:user1/push?db=/data/store.db', {
  method: 'POST',
  body: JSON.stringify({
    value: {
      product_id: 'prod-xyz',
      quantity: 2,
      price: 29.99
    }
  })
});

// Remove item
await fetch('.../kv/cart:user1/remove?db=/data/store.db', {
  method: 'POST',
  body: JSON.stringify({
    value: { product_id: 'prod-xyz' }
  })
});

// Get entire cart
const cart = await fetch('.../kv/cart:user1?db=/data/store.db')
  .then(r => r.json());
```

### SQL + KV Combination

**Use both interfaces in one database:**

```javascript
// SQL for complex queries
const analytics = await fetch('.../api/v1/sqlite/db?db=/data/app.db', {
  method: 'POST',
  body: JSON.stringify({
    transaction: [{
      query: 'SELECT product_id, COUNT(*) as purchases FROM orders GROUP BY product_id ORDER BY purchases DESC LIMIT 10'
    }]
  })
}).then(r => r.json());

// KV for simple config
const config = await fetch('.../kv/config:featured?db=/data/app.db')
  .then(r => r.json());

// Combine results
const featured = analytics.results[0].resultSet.filter(item => 
  config.featured_products.includes(item.product_id)
);
```

### Recovery from Bad Changes

**Rollback when things go wrong:**

**View history to find when config was correct:**



**Rollback to before deletion (undo last 2 changes):**



**Or restore entire table to 1 hour ago:**


**Rollback requires `?confirm=yes` to apply.** Without it, the endpoint only returns a dry-run preview. This is a safety rail — rollback is destructive. Use `&dry_run=true` explicitly for dry-runs, or `&confirm=yes` to actually apply.




---

## Use Cases

### AI Agent Memory

**Persistent memory for AI conversations:**

```javascript
// AI stores context
await fetch('.../kv/agent:conversation:123?db=/data/agent.db', {
  method: 'PUT',
  body: JSON.stringify({
    user_request: 'Build a todo app',
    plan: ['Create database', 'Build API', 'Create frontend'],
    progress: { completed: 0, total: 3 }
  })
});

// AI retrieves context later
const memory = await fetch('.../kv/agent:conversation:123?db=/data/agent.db')
  .then(r => r.json());

// AI continues from where it left off
```

### Feature Flags

**Control features via KV store:**

```javascript
// Set feature flags
await fetch('.../kv/batch/set?db=/data/app.db', {
  method: 'POST',
  body: JSON.stringify({
    batch: [
      { key: 'feature:new-ui', value: true },
      { key: 'feature:beta-api', value: false },
      { key: 'feature:dark-mode', value: true }
    ]
  })
});

// Check feature in app
const newUiEnabled = await fetch('.../kv/feature:new-ui?db=/data/app.db')
  .then(r => r.json());

if (newUiEnabled) {
  // Show new UI
}
```

### Rate Limiting

**Track API calls per user:**

```javascript
// Increment user's API call count
await fetch(`.../kv/api-calls:user-${userId}/incr?db=/data/limits.db`, {
  method: 'POST'
});

// Get current count
const calls = await fetch(`.../kv/api-calls:user-${userId}?db=/data/limits.db`)
  .then(r => r.json());

if (calls > 1000) {
  return { error: 'Rate limit exceeded' };
}
```

**Atomic increment prevents double-counting.** Works correctly under high concurrency.

### Cache Layer

**Cache expensive API responses:**

```javascript
const cacheKey = `cache:api:${endpoint}`;

// Check cache
const cached = await fetch(`.../kv/${cacheKey}?db=/data/cache.db`)
  .then(r => r.json())
  .catch(() => null);

if (cached) {
  return cached;
}

// Fetch from API
const data = await fetchFromAPI(endpoint);

// Store with 10-minute TTL
await fetch(`.../kv/${cacheKey}?db=/data/cache.db&ttl=600`, {
  method: 'PUT',
  body: JSON.stringify(data)
});

return data;
```

### Audit Trail for Compliance

**Track every data change:**

```javascript
// Make change
await fetch('.../kv/user:1:email?db=/data/users.db', {
  method: 'PUT',
  body: JSON.stringify('new-email@example.com')
});

// Later: Generate compliance report
const history = await fetch('.../kv/user:1:email/history?db=/data/users.db')
  .then(r => r.json());

// Shows: who changed what, when, from what value to what value
// Complete audit trail automatically
```

---

## Best Practices

### Use KV Store for Simple Data

**Don't overcomplicate:**

```javascript
// ❌ Overkill - SQL for simple config
const sql = 'SELECT value FROM config WHERE key = "timeout"';

// ✅ Simple - KV for simple data
const timeout = await fetch('.../kv/config:timeout?db=/data/app.db')
  .then(r => r.json());
```

**Use SQL when you need:** JOINs, complex queries, relationships  
**Use KV when you have:** Simple key-value pairs, config, caching

### Batch for Performance

**100x faster than individual requests:**

```javascript
// ❌ Slow - 100 individual requests
for (const user of users) {
  await fetch(`.../kv/user:${user.id}`, {
    method: 'PUT',
    body: JSON.stringify(user)
  });
}

// ✅ Fast - 1 batch request
await fetch('.../kv/batch/set?db=/data/app.db', {
  method: 'POST',
  body: JSON.stringify({
    batch: users.map(user => ({
      key: `user:${user.id}`,
      value: user
    }))
  })
});
```

### Use Atomic Operations for Counters

**Prevent race conditions:**

```javascript
// ✅ Correct - Atomic
POST /api/v1/sqlite/kv/counter/incr

// ❌ Wrong - Race condition
const current = await GET /api/v1/sqlite/kv/counter;
await PUT /api/v1/sqlite/kv/counter (current + 1);
// Two clients do this simultaneously = counter wrong
```

### Set TTL for Temporary Data

**Auto-expire sessions, cache, tokens:**

```javascript
// Session expires in 24 hours
await fetch('.../kv/session:xyz?db=/data/sessions.db&ttl=86400', {
  method: 'PUT',
  body: JSON.stringify({ user_id: '123' })
});

// No cleanup job needed - automatic expiry
```

### Snapshot Before Risky Changes

**KV store has built-in time-travel:**

Before bulk update, note the current timestamp. Make changes, then if something went wrong:

**Rollback entire table to timestamp:**



**Or rollback specific keys:**



---

## Useful Questions

### Can I use hoody-sqlite like a traditional database server?

Yes! Use the SQL operations endpoint to execute standard SQL. The difference: instead of `mysql -u user -p` or `psql`, you make HTTP POST requests. Same SQL, different transport. Perfect for existing applications that need HTTP-native databases.

### How does the KV store differ from Redis?

Similar concepts (key-value, atomic operations, TTL), different implementation. Redis is in-memory with persistence options. hoody-sqlite KV is SQLite-backed (disk-first). Redis has pub/sub. hoody-sqlite has time-travel and history. Both accessible via HTTP in Hoody containers.

### Can multiple containers share one SQLite database?

Yes! Use [SQLite Drive →](/foundation/storage/sqlite-drive/) by storing databases in `/hoody/databases/`. This special directory is automatically shared across containers with built-in locking and concurrency management. Multiple containers can safely read and write simultaneously.

Alternatively: One container owns a database in `/data/`, exposes it via hoody-sqlite HTTP endpoints, other containers query via HTTP. Both patterns work.

### Does time-travel history consume a lot of space?

Each change stores: key, old value, new value, timestamp. For most use cases, overhead is minimal (5-10% of data size). You can disable history with `?history=false` for ephemeral data like session tracking.

### Can AI agents query my database?

Absolutely! AI makes standard HTTP POST requests to execute SQL or KV operations. No database driver needed. LLMs understand HTTP natively—they can query data, analyze results, make decisions, all through HTTP.

### What's the maximum database size?

SQLite supports databases up to 281 TB theoretically. Practically: keep databases under 100GB for optimal performance. For larger datasets, split across multiple databases or use container with more storage.

### How do atomic operations prevent race conditions?

They execute at the database level in one step. `incr` does read-modify-write atomically—no other operation can interrupt between reading and writing. Multiple simultaneous requests are serialized by SQLite's locking mechanism.

### Can I backup SQLite databases?

Yes! SQLite databases are single files. Copy the `.db` file via hoody-files service, container snapshots, or standard file operations. For hot backup (database in use), use SQLite's VACUUM INTO or backup API.

### Do I need to create the KV table manually?

No. When creating a database, use `?init_kv=true` parameter. The KV table and indexes are created automatically. Or use the first KV operation and it creates the table if missing.

---

## Troubleshooting

### Database Locked Errors

**Problem:** "Database is locked" errors during writes

**Cause:** SQLite allows one writer at a time per database

**BEST Solution - Use SQLite Drive:**

Hoody provides **[SQLite Drive →](/foundation/storage/sqlite-drive/)**, a shared database system that eliminates locking issues for multi-container applications.

**Store databases in `/hoody/databases/` for shared access:**

```bash
# Instead of: /data/app.db (single-container, can lock under concurrency)
# Use: /hoody/databases/app.db (multi-container safe, no locking)
```

**Why `/hoody/databases/` is better:**
- ✅ Multiple containers can write simultaneously without locks
- ✅ Automatic sharing across all containers in your project
- ✅ Built-in connection pooling and conflict resolution
- ✅ Zero configuration - just use the path
- ✅ Perfect for distributed applications

**See:** [SQLite Drive →](/foundation/storage/sqlite-drive/) for complete setup and cross-container database access patterns.

**Alternative Solutions (for single-container databases in `/data/`):**

1. **Use batch operations to reduce requests:**
   ```bash
   # Instead of 100 individual SETs
   # Use 1 batch SET
   POST /api/v1/sqlite/kv/batch/set
   ```

2. **Retry with exponential backoff:**
   ```javascript
   async function retryWrite(url, body, maxRetries = 3) {
     for (let i = 0; i < maxRetries; i++) {
       try {
         return await fetch(url, { method: 'PUT', body });
       } catch (e) {
         if (i < maxRetries - 1) {
           await new Promise(r => setTimeout(r, 100 * Math.pow(2, i)));
         }
       }
     }
   }
   ```

3. **Use writeahead logging (WAL mode):**
   ```sql
   -- Enable WAL for better concurrency
   PRAGMA journal_mode=WAL;
   ```

### Key Not Found (null response)

**Problem:** GET returns null for existing key

**Check:**

1. **Verify key spelling (case-sensitive):**
   ```bash
   # ❌ Wrong: user:1
   # ✅ Correct: User:1  (if that's what you SET)
   ```

2. **Check key hasn't expired:**
   ```bash
   # If TTL was set, key auto-deletes on expiry
   ```

3. **List all keys to verify:**
   ```sql
   -- Via SQL
   SELECT key FROM kv_store LIMIT 100;
   ```

### Rollback Doesn't Restore Data

**Problem:** Rollback operation completes but data unchanged

**Possible causes:**

1. **Missing `confirm=yes` on table rollback:**
   ```bash
   # Without confirm=yes, POST /kv/rollback returns a dry-run preview only
   # Add &confirm=yes to the query string to actually apply:
   POST /api/v1/sqlite/kv/rollback?to_timestamp=...&db=...&confirm=yes
   ```

2. **History disabled when data was written:**
   ```bash
   # If original SET had ?history=false
   # No history = can't rollback
   ```

3. **Timestamp too far back:**
   ```bash
   # Rollback only works if history exists for that time
   # Check history first:
   GET /api/v1/sqlite/kv/{key}/history
   ```

4. **Wrong database file:**
   ```bash
   # Verify ?db= parameter points to correct file
   ```

### Batch Operation Partially Fails

**Problem:** Some keys in batch succeed, others fail

**Reality:** Batch operations are atomic—all succeed or all fail together. If you see partial success, you're making multiple requests instead of one batch.

**Verify:**
```javascript
// ✅ Correct - Single atomic batch
POST /api/v1/sqlite/kv/batch/set
{
  batch: [
    { key: 'k1', value: v1 },
    { key: 'k2', value: v2 }
  ]
}

// ❌ Wrong - Two separate requests
POST /api/v1/sqlite/kv/k1 (value: v1)
POST /api/v1/sqlite/kv/k2 (value: v2)
```

### Query Returns Unexpected Results

**Check SQL syntax and parameters:**

```javascript
// Use parameterized queries
{
  query: 'SELECT * FROM users WHERE id = ?',
  values: [userId]  // Not: `WHERE id = ${userId}` (SQL injection risk)
}
```

**Verify database state:**
```bash
# List tables
SELECT name FROM sqlite_master WHERE type='table';

# Check schema
PRAGMA table_info(users);
```

---

## What's Next

**Explore other data services:**


  
    Access files across local storage and 60+ cloud providers through HTTP.
    
    [Explore Files →](./files/)
  
  
  
    Turn scripts into HTTP endpoints—your code becomes an API automatically.

    [Explore Exec →](./exec/)
  


**Master SQLite operations:**
- **[SQL Operations →](/api/sqlite/sql-operations/)** - Execute transactions, create databases
- **[KV Store →](/api/sqlite/kv-store/)** - NoSQL-style interface
- **[KV Basic →](/api/sqlite/kv-basic/)** - GET, SET, DELETE operations
- **[KV Atomic →](/api/sqlite/kv-atomic/)** - Thread-safe operations
- **[KV Time-Travel →](/api/sqlite/kv-time-travel/)** - History and rollback

---

> **Your database is a URL.**  
> **SQL and KV in one.**  
> **Time-travel built-in.**  
> **Atomic by default.**  
> **Serverless forever.**

**This is how databases work in the HTTP era.**