Skip to content
Hoody Documentation
Hoody.com
Download Page (.md)

SQLite

Section titled “SQLite”

Every Hoody container runs hoody-sqlite, which exposes SQLite databases over HTTP. You execute SQL transactions, read and write key-value pairs, query values as they were at an earlier point in time, and run atomic operations, all as HTTP requests to the container’s SQLite URL. There is no database server to install and no connection string to configure.

Database paths

Section titled “Database paths”

The ?db= parameter accepts three forms:

  • Absolute path: ?db=/data/app.db is used verbatim. Examples throughout this page use this form.
  • Bare name: ?db=notes is shorthand resolved to /hoody/databases/notes.db.
  • ./name shorthand: ?db=./notes is likewise resolved to /hoody/databases/notes.db.

hoody-sqlite rejects an invalid or inaccessible path with a 400 error describing the problem.

Databases stored in /hoody/databases/ are shared through SQLite Drive and are safe for concurrent writes from several containers. You can query one over HTTP through hoody-sqlite or open the same file directly with a SQLite library such as Python sqlite3 or Node better-sqlite3, and mix both approaches in one application.

Feature summary

Section titled “Feature summary”

One service exposes both a SQL interface and a key-value interface over the same HTTP surface:

  • Web database UI - a SQL query interface in the browser, and the main entry point for exploring a database
  • SQL operations - queries and transactions over HTTP POST
  • Key-value store - GET and SET operations on individual keys
  • Atomic operations - increment, decrement, push, and pop
  • Batch operations - 100 keys in one atomic request
  • Time-travel - read historical values and snapshot the table at a past point in time
  • Rollback - undo changes to a key, or restore the table to a previous state
  • Shareable queries - read-only query URLs carrying Base64-encoded SQL
  • No server to run - no database daemon, connection pool, or driver
  • Audit trail - a complete change history, for compliance reporting

API Endpoints Summary

Section titled “API Endpoints Summary”

Full parameter, response, and example documentation lives in the API reference.

SQL Operations:

Key-Value Store - Basic:

Key-Value Store - Batch:

Key-Value Store - Atomic:

Key-Value Store - Time-Travel:

Query History:

Web Interface:

  • GET / - Web-based SQL query interface
    • Browse the database in a visual client
    • Run queries interactively
    • Read results in tabular form
    • Inspect the database schema

System Monitoring:

Capabilities

Section titled “Capabilities”

Web database interface

Section titled “Web database interface”

Open the container’s SQLite URL in a browser:

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

The page provides:

  • SQL query editor - write and run queries
  • Schema browser - view tables, indexes, and structure
  • Result viewer - see query results in table format
  • Database selector - switch between .db files
  • Query history - review past queries

Use it to explore a database, test a query, read table contents, or debug a data problem without leaving the browser. The interface is the same on a phone, a tablet, and a laptop.

Serverless SQL via HTTP

Section titled “Serverless SQL via HTTP”

For automation, post SQL over HTTP instead of connecting a client library:

Terminal window
# Execute SQL statements in a transaction
hoody db exec-transaction -c $CONTAINER --db /data/app.db \
  --transaction '[{"statement": "INSERT INTO users (name, email) VALUES (?, ?)", "values": ["Alice", "alice@example.com"]}, {"query": "SELECT * FROM users WHERE email = ?", "values": ["alice@example.com"]}]'

The response carries one result object per statement:

{
  "results": [
    {"success": true, "rowsUpdated": 1},
    {"success": true, "rowsUpdated": 0}
  ]
}

Because the interface is HTTP, the same database is reachable from:

  • AI agents (standard HTTP calls)
  • Mobile devices (fetch from a phone)
  • Other containers (cross-container data access)
  • Embedded iframes (live data in dashboards)
  • Any HTTP client (no database driver needed)

Key-value store interface

Section titled “Key-value store interface”

Read and write individual keys without writing SQL:

Terminal window
# Set a key-value pair
hoody kv set "user:1" --db /data/app.db -c $CONTAINER --body '{"name": "Alice", "role": "editor"}'


# Get value by key
hoody kv get "user:1" --db /data/app.db -c $CONTAINER


# Delete a key
hoody kv delete "user:1" --db /data/app.db -c $CONTAINER --yes


# Atomic increment
hoody kv incr "views:homepage" --db /data/app.db -c $CONTAINER

Common uses:

  • Configuration storage
  • Session management
  • Caching API responses
  • Feature flags
  • User preferences

Atomic operations

Section titled “Atomic operations”

Counters and array mutations run as one database operation, so shared state stays correct under concurrency:

When several clients call the endpoint at the same time, each increment applies on top of the last, so the counter runs 5 -> 6 -> 7 -> 8 rather than 5 -> 6 -> 6 -> 6.

The endpoints, by value type:

Terminal window
# Increment counter
POST /api/v1/sqlite/kv/{key}/incr?db=/data/app.db&delta=1


# Decrement inventory
POST /api/v1/sqlite/kv/inventory:item1/decr?db=/data/app.db&delta=1


# Typical uses: views, likes, credits, inventory

Each of these applies as a single database operation, so your client does not need its own locking.

Batch operations

Section titled “Batch operations”

Up to 100 keys move in one atomic request:

// Set multiple keys atomically
await fetch('.../kv/batch/set?db=/data/app.db', {
  method: 'POST',
  body: JSON.stringify({
    items: [
      { 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']
  })
});

One batch replaces up to 100 round trips. Every operation in it succeeds, or the whole batch fails.

Time-travel and audit

Section titled “Time-travel and audit”

Every write is recorded in the key’s history:

Terminal window
# View a key's change history, newest first (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 (preview with &dry_run=true; 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

Common uses:

  • Auditing against compliance requirements
  • Debugging a value that changed unexpectedly
  • Recovering from a bad write
  • Resetting state after an experiment

Shareable query URLs

Section titled “Shareable query URLs”

Encode a SELECT statement into a URL and share it:

// 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}`;


// Anyone with this URL can read live data
// The endpoint is read-only, so a shared URL cannot write

Typical uses:

  • Embed live data in dashboards
  • Share reports with stakeholders
  • Public API endpoints from private data
  • Documentation with live queries

Comparison with a database server

Section titled “Comparison with a database server”

Traditional database stack

Section titled “Traditional database stack”
Database Server (install) → Connection Pool (configure) → Client Library (install) → Query (finally)

A conventional setup involves:

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

Hoody SQLite

Section titled “Hoody SQLite”
HTTP Request → SQLite File → Response (immediately)

The HTTP interface removes those steps:

  • Databases are files, so there is nothing to install
  • No connection string or pool to configure
  • Any HTTP client works; the transport is the driver
  • AI agents call it with ordinary HTTP requests
  • Every query is logged
  • Query results embed directly in an iframe
  • No database daemon runs between requests
  • Backup is a file copy

Access from any HTTP client

Section titled “Access from any HTTP client”

Because the database speaks HTTP, all of these work without a driver.

  1. From a phone browser:

    // From mobile browser
    const users = await fetch(
      'https://{project}-{container}-sqlite-1.{server}.containers.hoody.com/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. From an AI agent:

    // 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"' }]
      })
    });
    // An ordinary fetch call, with no driver or connection string involved

  3. Embedded in a page:

    <iframe src="https://{project}-{container}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite/query?db=/data/stats.db&sql=U0VMRUNUIC4uLg==" />

Common workflows

Section titled “Common workflows”

Application configuration

Section titled “Application configuration”

Store configuration under one key and read it back at startup:

// 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

Section titled “Distributed counter”

Track a metric written by many clients at once:

// 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}`);

A thousand simultaneous requests increment the counter exactly one thousand times.

Session management

Section titled “Session management”

Store sessions with a TTL so they expire on their own:

// 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()
  })
});


// The key is removed an hour after it is written
// You do not need a cleanup job

Shopping cart

Section titled “Shopping cart”

Push, remove, and read cart items without read-modify-write races:

// Add item to cart
await fetch('.../kv/cart:user1/push?db=/data/store.db', {
  method: 'POST',
  body: JSON.stringify({
    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({ product_id: 'prod-xyz' })
});


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

SQL and KV in one database

Section titled “SQL and KV in one database”

One .db file serves both interfaces:

// 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 a bad change

Section titled “Recovery from a bad change”

Read the key’s history to find the operation where the value was still correct, then undo the last two changes:

Terminal window
# Change history, newest first, with an op_number for each entry
hoody kv history "config:api" --db /data/app.db -c $CONTAINER


# Undo the last 2 changes to the key
hoody kv rollback "config:api" --db /data/app.db --steps 2 -c $CONTAINER

Or restore the whole table to an hour ago:

Terminal window
timestamp=$(date -d '1 hour ago' +%s)


# Preview the changes first
hoody kv rollback-table --db /data/app.db --to-timestamp $timestamp --dry-run -c $CONTAINER


# Apply
hoody kv rollback-table --db /data/app.db --to-timestamp $timestamp --confirm yes -c $CONTAINER

Use Cases

Section titled “Use Cases”

AI agent memory

Section titled “AI agent memory”

Keep conversation state across agent runs:

// 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

Section titled “Feature flags”

Write flags as keys and read them at runtime:

// Set feature flags
await fetch('.../kv/batch/set?db=/data/app.db', {
  method: 'POST',
  body: JSON.stringify({
    items: [
      { 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

Section titled “Rate limiting”

Track API calls per user:

// 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 when requests overlap, so the count stays right under high concurrency.

Cache layer

Section titled “Cache layer”

Cache expensive API responses:

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

Section titled “Audit trail for compliance”

Track every data change:

// 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());


// Each entry shows what changed, when, and the values before and after
// The history is recorded without extra instrumentation

Best Practices

Section titled “Best Practices”

Use the KV store for simple data

Section titled “Use the KV store for simple data”

A single configuration value does not need a SQL query:

// Heavier than needed: SQL for one config value
const sql = 'SELECT value FROM config WHERE key = "timeout"';


// Simpler: the same value from the KV store
const timeout = await fetch('.../kv/config:timeout?db=/data/app.db')
  .then(r => r.json());

Use SQL for JOINs, complex queries, and relationships. Use the KV store for plain key-value pairs, configuration, and caching.

Batch for performance

Section titled “Batch for performance”

One request instead of one request per key:

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


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

Use atomic operations for counters

Section titled “Use atomic operations for counters”

Increment on the server rather than in the client:

// Correct: the read and the write happen in one operation
POST /api/v1/sqlite/kv/counter/incr


// Wrong: the read and the write are separate
const current = await GET /api/v1/sqlite/kv/counter;
await PUT /api/v1/sqlite/kv/counter (current + 1);
// Two clients doing this at once lose one of the increments

Set TTL for temporary data

Section titled “Set TTL for temporary data”

Give sessions, cached responses, and tokens an expiry:

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


// The key is deleted at expiry, with no cleanup job

Snapshot before risky changes

Section titled “Snapshot before risky changes”

Before a bulk update, note the current Unix timestamp. If the result is wrong, the KV store’s history lets you undo it: see Recovery from a bad change for the dry-run-then-confirm flow.

Useful Questions

Section titled “Useful Questions”

Can I use hoody-sqlite like a database server?

Section titled “Can I use hoody-sqlite like a database server?”

Yes. The SQL operations endpoint runs standard SQL. Instead of connecting with mysql -u user -p or psql, you send an HTTP POST. The SQL is the same; only the transport differs.

How does the KV store differ from Redis?

Section titled “How does the KV store differ from Redis?”

The concepts overlap: key-value access, atomic operations, TTL. The implementations do not. Redis is in-memory with persistence options; the hoody-sqlite KV store is SQLite-backed and disk-first. Redis has pub/sub; hoody-sqlite has history and time-travel. Both are reachable over HTTP inside a Hoody container.

Can multiple containers share one SQLite database?

Section titled “Can multiple containers share one SQLite database?”

Yes. Store the database in /hoody/databases/ and SQLite Drive shares it. That directory is shared across every container in your project, with locking and concurrency handled for you, so several containers can read and write the same database at the same time.

The other pattern: one container owns a database in /data/ and exposes it through its hoody-sqlite HTTP endpoints, and other containers query over HTTP. Both work.

Does time-travel history consume a lot of space?

Section titled “Does time-travel history consume a lot of space?”

Each change stores the key, the old value, the new value, and a timestamp. In most cases that adds 5-10% to the size of the data. For ephemeral data such as session tracking, turn it off with ?history=false.

Can AI agents query my database?

Section titled “Can AI agents query my database?”

Yes. An agent runs SQL or KV operations with the same HTTP POST requests as any other client, and needs no database driver. Querying, reading results, and acting on them all happen over HTTP.

What’s the maximum database size?

Section titled “What’s the maximum database size?”

SQLite’s format allows databases up to 281 TB. In practice, keep a database under 100GB for good performance. Beyond that, split the data across several databases or move to a container with more storage.

How do atomic operations prevent race conditions?

Section titled “How do atomic operations prevent race conditions?”

Each one runs at the database level in a single step. incr performs its read, modify, and write without another operation interrupting between the read and the write. hoody-sqlite serializes writes to each database behind a per-database lock, and each operation runs in a single transaction.

Can I backup SQLite databases?

Section titled “Can I backup SQLite databases?”

Yes. A SQLite database is a single file. Copy the .db through the hoody-files service, a container snapshot, or ordinary file operations. To back up a database that is in use, use SQLite’s VACUUM INTO or its backup API.

Do I need to create the KV table manually?

Section titled “Do I need to create the KV table manually?”

No. Pass ?init_kv=true when you create the database and the KV table and its indexes are created for you. If you skip that, the first KV operation creates the table.

Troubleshooting

Section titled “Troubleshooting”

Database locked errors

Section titled “Database locked errors”

Problem: “Database is locked” errors during writes

Cause: SQLite allows one writer at a time per database

Preferred fix: move the database to SQLite Drive, the shared database layer that removes locking issues for applications spread over several containers.

Store the database in /hoody/databases/ for shared access:

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

What that path gives you:

  • Several containers can write at the same time, without locking each other out
  • The directory is shared across every container in your project
  • Connection pooling and conflict resolution are handled for you
  • Nothing to configure beyond the path
  • Suited to applications spread over several containers

See: SQLite Drive → for setup and cross-container database access patterns.

If the database stays in /data/:

  1. Use batch operations to reduce requests:

    Terminal window
    # Instead of 100 individual SETs
    # Use 1 batch SET
    POST /api/v1/sqlite/kv/batch/set

  2. Retry with exponential backoff:

    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)));
          }
        }
      }
    }

Write-ahead logging is not something you need to turn on: every database hoody-sqlite opens is already in WAL mode, with a 30-second busy timeout.

Key not found (null response)

Section titled “Key not found (null response)”

Problem: GET returns null for a key you expect to exist

Check:

  1. Verify key spelling (case-sensitive):

    Terminal window
    # Wrong: user:1
    # Correct: User:1  (if that's what you SET)

  2. Check key hasn’t expired:

    Terminal window
    # If TTL was set, key auto-deletes on expiry

  3. List all keys to verify:

    -- Via SQL
    SELECT key FROM kv_store LIMIT 100;

Rollback with no effect

Section titled “Rollback with no effect”

Problem: the rollback call completes but the data is unchanged

Possible causes:

  1. Missing confirm=yes on table rollback:

    Terminal window
    # Without confirm=yes, POST /kv/rollback does not apply the rollback
    # Preview with &dry_run=true, then add &confirm=yes to actually apply:
    POST /api/v1/sqlite/kv/rollback?to_timestamp=...&db=...&confirm=yes

  2. History disabled when data was written:

    Terminal window
    # If original SET had ?history=false
    # No history = can't rollback

  3. Timestamp too far back:

    Terminal window
    # Rollback only works if history exists for that time
    # Check history first:
    GET /api/v1/sqlite/kv/{key}/history

  4. Wrong database file:

    Terminal window
    # Verify ?db= parameter points to correct file

Partial batch failures

Section titled “Partial batch failures”

Problem: some keys in a batch succeed while others fail

Cause: batch operations are atomic, so every key succeeds or the whole batch fails. Partial success means the writes went out as separate requests rather than one batch.

Verify:

// Correct: one atomic batch
POST /api/v1/sqlite/kv/batch/set
{
  items: [
    { 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)

Unexpected query results

Section titled “Unexpected query results”

Check SQL syntax and parameters:

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

Verify database state:

Terminal window
# List tables
SELECT name FROM sqlite_master WHERE type='table';


# Check schema
PRAGMA table_info(users);

What’s Next

Section titled “What’s Next”

Other data services:

Files

Access files across local storage and 60+ cloud providers through HTTP.

Explore Files →

Exec

Serve a script as an HTTP endpoint, with no framework or deploy step.

Explore Exec →

SQLite reference pages: