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 an error describing the problem: usually 400, 403 on the KV routes for a path that leads outside the allowed area, and 500 when the filesystem itself fails.

A database has to exist before requests can use it: a request against a missing file returns 400 DATABASE_NOT_FOUND instead of creating it (when its parent directory is missing too, reads and some other routes answer INVALID_DB_PATH instead; a HEAD answers with the status alone; the maintenance endpoint returns 404). Create one with POST /api/v1/sqlite/db/create?path=... (add init_kv=true to create the KV table at the same time), or pass create_db_if_missing=true on POST /api/v1/sqlite/db or on a KV write such as a set, an increment, a batch set, expire or persist, which also creates any missing parent directories. Reads never create the database named by db, and a rollback creates one only if its parent directory already exists. In directory mode, a read does create the lookup cache described below. A database is never created at another database’s side-file name (<db>-wal, <db>-shm or <db>-journal), and a database whose side-file name holds a separate database is not opened: SQLite would treat that file as its own journal or log and destroy it. Both answer 400 INVALID_DB_PATH, as does initialising an existing empty file at a side-file name. A side file that cannot be read counts as possibly a separate database: the open or create answers 400 INVALID_DB_PATH, and deleting the database answers 500 DELETE_INCOMPLETE with files_removed: 0 and removes nothing.

A path that names an existing directory rather than a file switches the KV endpoints to directory mode.

/hoody/databases/ is the conventional location for databases inside a container, and hoody-sqlite resolves a bare name such as app to /hoody/databases/app.db there. 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. It is an ordinary directory in that container’s own filesystem, not a share: see the SQLite driver for what it does and does not give you.

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 - up to 100 keys per request, written all-or-nothing
  • Conditional writes - every key has an ETag, and If-Match or If-None-Match makes a write fail instead of overwriting another client’s change
  • Change feed - every change to a KV table, read by polling, long polling, or a Server-Sent Events stream
  • Safe retries - an Idempotency-Key makes a retried write apply once
  • 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 - the recorded history of KV changes, for compliance reporting

API Endpoints Summary

Section titled “API Endpoints Summary”

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

SQL Operations:

  • POST /api/v1/sqlite/db - Execute atomic SQL transactions
    • Supports: Multiple statements, parameterized queries, bulk inserts
    • Query params: db (required), create_db_if_missing (create the file if it does not exist)
  • GET /api/v1/sqlite/query - Read-only query via GET
    • Query params: db (database path) and sql (Base64-encoded SELECT statement), both required
  • POST /api/v1/sqlite/db/create - Create database
    • Query param: path, init_kv (auto-create KV table)
    • An existing database is 409 DATABASE_EXISTS, and any other entry at path is a 409 too; nothing is overwritten. A 409 without DATABASE_EXISTS can also mean another program put a file at path while the database was being created; that file is left alone, so check the path and retry
  • GET /api/v1/sqlite/db/list - List the databases in a directory
    • Query param: dir (absolute path, default /hoody/databases)
    • From the CLI: hoody db list
  • POST /api/v1/sqlite/maintenance - Run one out-of-transaction maintenance operation
    • Body: op is wal_checkpoint_truncate, vacuum_into (with dest_path), quick_check, restore (with src_path: copy a backup, such as a vacuum_into copy, into the database, then start a new change feed), or reset_changes (start a new change feed and invalidate every ETag)
    • Query params: db, timeout (seconds, clamped to 1-300)
    • vacuum_into answers 409 when dest_path already exists (a symlink counts) or when another program put a file there during the copy; that file is left alone, so check the path and retry, with another dest_path if needed
    • A 503 without Retry-After means creating the destination was interrupted and its outcome is unknown, now or in an earlier request: a file may be left at dest_path, and that path answers a final 503 until the server restarts, so use another dest_path (see Safe retries)
    • From the CLI: hoody db maintenance run. It prints the result the operation returned, so a quick_check that finds a problem says so rather than reporting success

Key-Value Store - Basic:

  • GET /api/v1/sqlite/kv/{key} - Get value by key
    • Query params: db, table, path (JSON path; ignored with at_timestamp, which returns the whole value), at_timestamp (time-travel, Unix timestamp integer; 0 means a live read)
    • Header: If-None-Match (304 while the key’s ETag matches; refused with a non-zero at_timestamp)
    • A time-travel answer is JSON: value for a JSON value, value_base64 for any other type, with content_type, and op_number and op_timestamp when the key has recorded history. It carries no ETag
  • PUT /api/v1/sqlite/kv/{key} - Set/update value
    • Query params: ttl (auto-expiry, in seconds counted from the moment the write runs, once it holds the database’s write lock), if_match (CAS), path (partial update; the stored value must have a JSON content type, so store it with Content-Type: application/json first, otherwise 400), history
    • Headers: If-Match (write only at this ETag), If-None-Match: * (create only), Idempotency-Key (safe retries)
  • DELETE /api/v1/sqlite/kv/{key} - Delete key
  • HEAD /api/v1/sqlite/kv/{key} - Check existence
  • GET /api/v1/sqlite/kv - List keys with prefix filtering
    • Query params: prefix, limit (default 100, values above 1000 are clamped to 1000; 0 returns no items and echoes after as next_after, except at a timestamp, where it returns every key), after (cursor), offset (default 0), at_timestamp (list the keys as they stood at that moment)
    • Results are ordered by key. Page with after: pass the previous page’s next_after, and stop when has_more is false. A key written or deleted between two pages never makes another key be skipped or repeated. offset paging is a sequence of independent reads, so on a table being written to, keys added or removed between requests shift what later pages contain. A point-in-time listing pages with offset only (after with at_timestamp is a 400), and one that reports it reached its discovery limit is telling you it could not see every candidate key.
    • On a database file, each item carries its key’s etag, and change_cursor is the change feed position read together with the page (null, with no item ETags, when the service could not read versions; list again). Directory-mode listings and listings at a timestamp carry neither

Key-Value Store - Versions, TTL, and Changes:

  • GET /api/v1/sqlite/kv/{key}/entry - The value with its content type, ETag, and created, updated and expiry times in one JSON body
    • Header: If-None-Match (304 while unchanged)
  • POST /api/v1/sqlite/kv/{key}/expire - Set or replace a key’s TTL
    • Query param: ttl (seconds from now, at least 1)
  • POST /api/v1/sqlite/kv/{key}/persist - Remove a key’s TTL
  • GET /api/v1/sqlite/changes - Page through a KV table’s changes, or wait for the next ones
    • Query params: db, table, since (cursor), prefix, limit (1-1000, default 100), wait (0-60 seconds), include_values
  • GET /api/v1/sqlite/changes/stream - The same changes as a Server-Sent Events stream
    • Query params: db, table, since, prefix, include_values; header Last-Event-ID to resume

Key-Value Store - Batch:

Key-Value Store - Atomic:

  • POST /api/v1/sqlite/kv/{key}/incr - Atomic increment (thread-safe)
    • Query param: delta (amount to add, a positive integer, default: 1; zero or negative is a 400)
  • POST /api/v1/sqlite/kv/{key}/decr - Atomic decrement
    • Query param: delta (amount to subtract, a positive integer, default: 1)
  • POST /api/v1/sqlite/kv/{key}/push - Atomic array push
    • Body: the JSON value to append (an object, array, string, number, boolean or null)
  • POST /api/v1/sqlite/kv/{key}/pop - Atomic array pop (LIFO)
    • Returns and removes last element
  • POST /api/v1/sqlite/kv/{key}/remove - Remove array element
    • To remove by value, send {"value": <the element to match>}. The element goes inside a value envelope, unlike push, which takes the element as the whole body
    • To remove by position, pass the index query parameter and send no body
    • Supplying neither is 400 Either index or value must be provided. Supplying both, index wins

Key-Value Store - Time-Travel:

  • GET /api/v1/sqlite/kv/{key}/history - Change history, newest first
    • Query params: limit (default 50, maximum 1000)
  • GET /api/v1/sqlite/kv/{key}/snapshot - Value at operation number
    • Query param: op_number (integer, required; take it from the /history response)
  • GET /api/v1/sqlite/kv/diff - Compare time periods
    • Query params: from, to (Unix timestamps)
  • POST /api/v1/sqlite/kv/{key}/rollback - Undo changes
    • Query param: steps (number of changes to undo, default: 1)
  • GET /api/v1/sqlite/kv/snapshot - Snapshot entire KV table at timestamp
    • Query params: timestamp (Unix timestamp integer), prefix, limit (default 100, at most 1000, 0 for every key found)
    • The snapshot is complete only when count equals total_keys_at_snapshot (otherwise limit cut it), has_gaps is false, and candidate_truncated is false
  • POST /api/v1/sqlite/kv/rollback - Rollback entire table
    • Query params: to_timestamp (Unix timestamp integer, required), confirm=yes (required to apply), dry_run=true (preview without applying)

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:

  • GET /api/v1/sqlite/health - Service health check
    • Query param: verbose (default false). Without it the answer is {"status": "ok"} and nothing else; with it you also get service identity, the features the server supports (such as kv_etag and kv_idempotency), process memory and file-descriptor counts, and the cache and counter snapshots. Use the short form for liveness probes and the verbose form when you are diagnosing something.
  • GET /api/v1/sqlite/health/cache - Cache health snapshot
    • From the CLI: hoody db health and hoody db cache stats, matching the two endpoints

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. create_db_if_missing=true creates the database file but not the tables in it, so the examples create the users table first:

Terminal window
# Execute SQL statements in a transaction
hoody db transactions run -c $CONTAINER --db /data/app.db --create-db-if-missing \
  --transaction '[{"statement": "CREATE TABLE IF NOT EXISTS users (id INTEGER)"}, {"statement": "INSERT INTO users (id) VALUES (?)", "values": [1]}, {"query": "SELECT * FROM users WHERE id = ?", "values": [1]}]'

The response carries one result object per item: rows and their headers (resultHeaders, resultSet) for a query, or for a statement that produces result columns (SELECT, ... RETURNING); rowsUpdated for a statement without them; rowsUpdatedBatch for a valuesBatch item:

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

values is an array for positional placeholders (?, ?NNN, and $NNN with only digits after the $), or an object for named ones (:name, @name, $name). An object against positional placeholders is refused rather than guessed at, in valuesBatch rows too: 400 for the request, or, with "noFail": true on the item, a failed entry inside a 200 response. A named placeholder with no matching key in the object is refused with 400 SQL_BIND_ERROR.

Each name in a values object must start with a letter; {"1": 7} is refused with 400 SQL_BIND_ERROR. A conflict that makes SQLite roll back the whole transaction itself (INSERT OR ROLLBACK, or RAISE(ROLLBACK, ...) in a trigger) ends the request even under "noFail": true: the answer is that item’s 409 SQL_CONSTRAINT, and nothing is committed. A database that stays locked by another process answers 503 DATABASE_BUSY with Retry-After.

Date and time text in a column declared DATE, DATETIME or TIMESTAMP comes back in SQLite’s own form: 2026-01-02T03:04:05Z reads back as 2026-01-02 03:04:05. Values in those columns that are not recognised as date text, and every TEXT column, are returned exactly as stored.

When two result columns share a name, the later ones are given suffixed keys (name:2, then name:3), skipping any name already in the row, so no column overwrites another. The resultHeaders array lists the keys actually used. A BLOB comes back as a base64 string.

With "noFail": true on an item, a failure of that item is reported inside a 200 response and only that item is rolled back; the other items still commit. A failure of the request as a whole still rolls back every item, noFail or not: a response over the size limit (413 RESPONSE_TOO_LARGE), the deadline passing, or a failed commit. In a valuesBatch item, value sets that are malformed or of the wrong shape (an object for positional placeholders, say) are skipped and listed in rowErrors; a value set that fails when it runs fails and rolls back the whole item. values and its alias params may both be sent only when they hold the same value: numbers must be spelt alike, so [1] and [1.0] conflict (400 VALUES_CONFLICT). ATTACH and DETACH are refused with 400: a request works on the one database named by db.

Foreign keys are enforced. A row that references a missing parent is refused with 409 SQL_CONSTRAINT, and the ON DELETE and ON UPDATE actions you declare (CASCADE, SET NULL, SET DEFAULT) run. Deleting a parent that still has children whose key has no action (NO ACTION or RESTRICT) is refused with 409 SQL_CONSTRAINT. Rows already stored are not checked: run PRAGMA foreign_key_check to list rows whose parent is missing. SQLite cannot change foreign_keys inside a transaction, and every request runs in one, so a request that contains PRAGMA foreign_keys = OFF (or 0, false, no) runs with enforcement off from its first item to its last, on a connection of its own. The next request has enforcement on again. Use this for a table rebuild: with enforcement on, DROP TABLE on a parent table first deletes its rows, which runs the children’s ON DELETE actions. To keep enforcement on but check only at the commit, send PRAGMA defer_foreign_keys = ON instead.

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 increment "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 request, and a batch set or delete applies all of them or none:

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


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

One batch replaces up to 100 round trips. Batch set and batch delete are transactional: every key is written, or none is. Batch get reads every key in one read transaction, so the results are consistent with each other; it returns one result per key, with null for keys that do not exist.

Versions and conditional writes

Section titled “Versions and conditional writes”

Every key in a database file has an ETag: a version tag that changes with every write to the key, TTL changes included. Live reads and writes return it in the ETag header; the responses of a set, the atomic and array operations and the TTL endpoints also carry it as etag, and so do the items of a key listing and of a batch get. A batch set has no ETag header; it returns each key’s tag in items[].etag. A delete, a batch delete and a rollback return none, and neither does a time-travel read or a read of a table the change feed cannot follow (see Change feed). While the service cannot read a key’s version (the database is busy, or a schema change made with SQL is still being absorbed), a plain read answers without an ETag and the key entry shows "etag": null; a conditional read answers 503 instead, so retry it. Treat an ETag as an opaque string.

Send it back to make a write conditional:

  • If-Match: <etag> applies the write only while the key’s ETag is still that one. Another client’s write in between makes the request fail with 412, write nothing, and return the key’s current_etag (null if the key is gone). When current_etag is not null, a retry that sends it succeeds unless the key changes again. When it is null, the key is missing or expired: recreate it with If-None-Match: * instead. If-Match: * means “the key exists”.
  • If-None-Match: * makes a write create-only: it fails with 412 if the key already exists.
  • If-None-Match: <etag> on GET, HEAD or the key entry answers 304 Not Modified, with no body, while the key is unchanged. With path, a path the value does not contain is 404 even while the ETag matches.
Terminal window
# Read the value and its ETag in one JSON body
curl "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.com/api/v1/sqlite/kv/settings/entry?db=/data/app.db"
# {"content_type":"application/json","created_at":1791121775,"etag":"\"484ba0af68916b8d29a87ddc737fdac6.32b18d4a6c44e25b6185e95a68eef6d7.8\"","expire_at":null,"key":"settings","updated_at":1791121775,"value":{"lang":"en","theme":"dark"}}


# Write the new value only if nobody changed the key since
curl -X PUT "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.com/api/v1/sqlite/kv/settings?db=/data/app.db" \
  -H "Content-Type: application/json" \
  -H 'If-Match: "484ba0af68916b8d29a87ddc737fdac6.32b18d4a6c44e25b6185e95a68eef6d7.8"' \
  -d '{"lang":"en","theme":"light"}'
# {"etag":"\"484ba0af68916b8d29a87ddc737fdac6.32b18d4a6c44e25b6185e95a68eef6d7.9\"","key":"settings","size":29,"success":true}

If-Match works on PUT and DELETE, the atomic and array operations, and the TTL endpoints. Batch set items take if_match and if_none_match fields, and batch delete takes items with if_match: every condition is checked first, and one failure writes nothing and lists each failing key with its current_etag. An If-Match or If-None-Match header on a batch write or a rollback is refused with 400 rather than ignored. The if_match query parameter compares values instead; an ETag compares versions, so it also catches a write that stored the same value again. A value CAS that writes back the value it compared against is still a write: the ETag changes, history records it, and, like any set of a whole value without ttl, it removes the key’s expiry (a set with path keeps the expiry unless it sends ttl).

A schema change made with SQL through POST /api/v1/sqlite/db, such as CREATE TABLE, ALTER TABLE or adding or dropping an index on a KV table, changes every ETag in that database. On a table created with SQL whose key column is case-insensitive (COLLATE NOCASE), spellings that differ only in case reach the same key, which keeps the spelling it was first stored with. Clients holding old ETags get 412 or a full read, never a stale 304.

Key entry and TTL changes

Section titled “Key entry and TTL changes”

GET /api/v1/sqlite/kv/{key}/entry returns the value with its content type, ETag and created, updated and expiry times in one JSON body. A JSON value comes back parsed in value; anything else comes back base64-encoded in value_base64.

POST /api/v1/sqlite/kv/{key}/expire?ttl=3600 makes an existing key expire an hour from now, replacing any TTL it had. POST /api/v1/sqlite/kv/{key}/persist removes the TTL. Neither touches the value or updated_at. Setting an expiry, or removing one the key had, changes the ETag and is recorded in history, so a rollback restores the earlier TTL. With history=false the entry records only that the write happened, not the TTL it set. persist on a key that has no TTL answers 200 and changes nothing: same ETag, no history entry, no change event.

Terminal window
curl -X POST "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.com/api/v1/sqlite/kv/session:abc123/expire?db=/data/app.db&ttl=3600"
# {"etag":"\"484ba0af68916b8d29a87ddc737fdac6.32b18d4a6c44e25b6185e95a68eef6d7.6\"","expire_at":1791125375,"key":"session:abc123","success":true}

Safe retries

Section titled “Safe retries”

A write whose response was lost may or may not have committed. Send an Idempotency-Key header (1 to 255 visible ASCII characters) with the write, and send the identical request again after a failure: if the first one committed, the server answers with the stored response and Idempotent-Replayed: true instead of applying it twice.

Terminal window
curl -i -X POST "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.com/api/v1/sqlite/kv/orders:count/incr?db=/data/app.db" \
  -H "Idempotency-Key: checkout-7f3a"
# The same request again answers with the first response:
# HTTP/1.1 200 OK
# Idempotent-Replayed: true
#
# {"etag":"\"484ba0af68916b8d29a87ddc737fdac6.32b18d4a6c44e25b6185e95a68eef6d7.12\"","key":"orders:count","success":true,"value":1}

The stored response is written in the same transaction as the write, so it exists exactly when the write committed. Reusing a key for a different request (method, route, key, query parameters other than timeout, conditional headers, content type or body) is 422 IDEMPOTENCY_KEY_MISMATCH; the query must also keep its parameters in the same order, so build the retry from the same URL. A request refused before it wrote anything, such as a 400 or a 412, stores nothing. A 404 from a delete, expire or persist of a missing key is a stored answer: retrying with the same key replays the 404 even after the key has been created, so use a new key for a new attempt. A response larger than 1 MiB cannot be stored, so that write is refused with 413 and not applied; retry it without the header. Stored responses are kept for 24 hours, and each database keeps at most 100,000 of them and 256 MiB, dropping the oldest first. The header works on every KV write: set, delete, the batch writes, the atomic and array operations, the TTL endpoints and both rollbacks.

A 503 with Retry-After: 1, and a 409 caused by another process holding the database, are temporary: wait that long, then retry. This includes file operation could not be started; retry, sent when creating, opening, reading or checking a database or value file could not start; nothing was done, so resend the request as it is.

A 503 without Retry-After is final. Usually it means a write was interrupted while it changed a file, and whether it was applied is unknown. Do not resend it automatically: read the current state first (the key, the database file, or the destination path), then decide. When POST /db/create, or the creation of a vacuum_into destination, ends this way, a file may be left at the path; the server cannot tell whether it is its own, so it leaves it as it is. A path refused after an unknown outcome answers a final 503 until the server restarts (its message says an earlier create was interrupted); do not retry it. That holds for creating, opening, copying to or deleting a database at that path, and for a GET, HEAD, PUT or DELETE of a directory-mode key whose value is stored there. Use another path or key; the path can be used again after a restart. A 503 with Retry-After that says nothing was created can be retried as it is.

The one exception is listing keys: GET /api/v1/sqlite/kv on a directory-mode store is not refused when a value’s path is held after an interrupted write. The listing answers 200 and leaves that key out of both items and total, while a GET, HEAD, PUT or DELETE of the key itself answers the final 503 until the server restarts. Nothing at that path is removed.

Change feed

Section titled “Change feed”

Every KV table in a database file has a change feed: an ordered log of the changes made to its rows, whoever made them. Writes through the KV API, SQL sent to POST /api/v1/sqlite/db, and other programs that open the file all appear in it. Read it in pages, wait for the next change with a long poll, or receive it as a Server-Sent Events stream.

Each event names the key and an op:

opMeaning
setThe row was written
ttlOnly the expiry changed
deleteThe row was deleted
expireThe service deleted a row whose TTL had passed. This happens later than the expiry time: during a later write to the table, or within about ten seconds while a long poll or stream is following the table

Events describe rows, so a key renamed with SQL appears as a delete and a set, and a row with another key removed by a REPLACE (INSERT OR REPLACE meeting it on the rowid or on a UNIQUE index) appears as its own delete, or expire if it had already expired, before the new set, whoever ran it. Replacing a row that has the same key is one set (a program writing with SQLite’s recursive_triggers on also logs a delete, or expire if the replaced row had already expired, before it).

Some tables changed with SQL have no ETags and no change feed: a table with a trigger of your own on it, or one that a trigger on another table refers to in its body (a mention in a string, a comment or after AS does not count), a table with a foreign key to itself whose ON DELETE or ON UPDATE action is CASCADE, SET NULL or SET DEFAULT, a table with a UNIQUE index made only of expressions, such as lower(value) (one that mixes plain columns and expressions is fine), and a rowid table whose columns hide rowid, _rowid_ and oid all at once. On such a table, every KV write and every change feed request answers 409 CONFLICT, naming the table and the reason, and nothing is written; reading a key still works, without an ETag. A cursor issued before the trigger was added gets the same 409. Remove what caused it and the table works again, with new ETags and cursors: an old cursor gets 410 and the client resyncs. A TEMP trigger lives only on the connection that made it: a POST /db transaction that changes the TEMP schema and also writes a KV table starts every KV table in the database over when it commits (old cursors get 410 table_changed, old ETags get 412), and a TEMP trigger made by another program is invisible to the service, so do not let TEMP triggers write KV tables. Writes made with history=false appear like any other. Delivery is in order and at least once, so apply events idempotently.

Terminal window
# The current position (no events yet)
curl "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.com/api/v1/sqlite/changes?db=/data/app.db"
# {"events":[],"next_cursor":"c1.5e6c0727f4fd948a66e8658eb9ec3e86.db28154bd8020871613a2a3e9c703a57.13","cursor":"c1.5e6c0727f4fd948a66e8658eb9ec3e86.db28154bd8020871613a2a3e9c703a57.13","oldest_cursor":"c1.5e6c0727f4fd948a66e8658eb9ec3e86.db28154bd8020871613a2a3e9c703a57.0","has_more":false}


# Changes after that cursor; with wait, the request returns as soon as one arrives
curl "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.com/api/v1/sqlite/changes?db=/data/app.db&since=$CURSOR&wait=25"


# The same changes as a stream
curl -N "https://$PROJECT-$CONTAINER-sqlite-1.$SERVER.containers.hoody.com/api/v1/sqlite/changes/stream?db=/data/app.db"

GET /changes returns up to limit events (1 to 1000, default 100); continue from next_cursor. prefix keeps only matching keys; a long poll with prefix reads past the changes it skips before it waits, so a match already in the log comes back at once. include_values=true attaches the value to set and ttl events while it is still current and at most 256 KiB: a JSON value in value, any other type base64-encoded in value_base64 (otherwise value_omitted says why).

The stream sends ready, then one change event per change with its cursor as the event id, and a checkpoint every 15 seconds. To resume after a disconnect, send the last id in Last-Event-ID; a browser EventSource does this when it reconnects. A stream lasts at most an hour by default (the server’s --change-stream-max-lifetime) and then ends with event: end and reason max_lifetime; reconnect with the last id. Reason database_deleted means the database file is gone. When the server is at its stream limit (1024 by default), it answers 503 with Retry-After.

A cursor is an opaque string. Pass back exactly what the service returned. The service answers 410 when a cursor cannot be continued:

  • CHANGE_CURSOR_EXPIRED: the changes after the cursor have been trimmed from the log (it is behind oldest_cursor). See the change log row under Limits.
  • CHANGE_CURSOR_INVALID: the database file was replaced or restored, the reset_changes maintenance operation ran, or the table or the schema was changed outside the KV API.

In both cases, read the keys again and continue from the change_cursor that the first page of the key listing returns. If that change_cursor is null, list again: following the feed without a cursor starts at the current position and misses what changed during the listing. A stream reports the same conditions as an event: reset before it closes.

To restore a backup, run POST /api/v1/sqlite/maintenance with {"op": "restore", "src_path": "<backup>"}. The backup is copied in as one write transaction and only read; a restore that fails before the copy commits, or runs out of time, changes nothing. A 500 whose message says the database was restored means the copy committed but the new change feed was not started: run reset_changes then, and do not repeat the restore. Every change cursor and every ETag issued before it then stops matching, so clients re-read instead of trusting state from before the restore.

Do not copy a backup over a database file the service has open. The service keeps using the old -wal file and its cached pages, so it goes on serving the old data, or a mix. If the file was replaced while the service did not have it open (after DELETE /api/v1/sqlite/db, for example), run the reset_changes maintenance operation ({"op": "reset_changes"}) before clients use it again.

Time-travel and audit

Section titled “Time-travel and audit”

By default, every write is recorded in the key’s history; pass history=false on a write to opt out of storing its value.

The write is still noted. It appears in the history as an UNRECORDED entry holding the time an untracked write happened and nothing else — no value, no content type, no expiry. Untracked writes that follow one another collapse into a single entry covering the whole span, so this does not grow with the number of writes. A recorded write between them starts a new entry.

That entry is what lets time travel tell you it cannot answer. Asked for a moment covered by an untracked write, the time-travel endpoints return 409 TIME_TRAVEL_CHAIN_GAP (or list the key under gap_keys) rather than the value from before it. Without the entry, a write made with history=false that was later deleted or allowed to expire left nothing behind, and a reconstruction would confidently return the older value — or report a key that did exist as never having existed.

A recorded write that directly follows the untracked span stores the value it replaced, which is the value the last untracked write left. So a moment after the last untracked write and before that recorded write gets an answer again. A moment between two untracked writes still returns 409.

History is recorded by the KV endpoints only. Changes made to a KV table with SQL through POST /api/v1/sqlite/db, and anything a SQL trigger does to it, are not recorded and leave no UNRECORDED entry, so time travel cannot account for them. If you rely on a table’s history, change its data through the KV endpoints.

The change feed does record those SQL changes.

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 the entire KV table to 2 hours ago (to preview, send &dry_run=true instead of &confirm=yes)
timestamp=$(date -d '2 hours ago' +%s)
POST /api/v1/sqlite/kv/rollback?to_timestamp=$timestamp&db=/data/app.db&confirm=yes
{}

Common uses:

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

Directory mode

Section titled “Directory mode”

Point ?db= at an existing directory instead of a database file and the KV endpoints keep each key as a file inside it. PUT /api/v1/sqlite/kv/users%2Falice?db=/data/kv sent with Content-Type: application/json writes /data/kv/kv_store/users/alice/data.json: / in a key becomes a subdirectory, and ?table= picks the top-level folder, kv_store by default. The extension comes from the request’s Content-Type header (matched in any letter case), not from the bytes: common types have fixed extensions, .json and .txt among them, anything else takes whatever extension the system maps that type to, and a type with no known extension yields a plain data file. The files are ordinary files, so you can read, edit, or version them with any tool. A key has at most 32 segments, and some names a database file accepts are refused here (see Limits). The service keeps a lookup cache in a hidden .hoody_sqlite folder in the directory, and notices an edit made outside the API from the file’s size and modification time. An edit that keeps both, such as a copy made with cp -p over a file of the same size, is not noticed until you read the key with ?rebuild=true. A value file that the service has open as a SQLite database (one created inside the directory, or a hard link to one) is not a value: reading, writing or deleting that key answers 409 CONFLICT and changes nothing until the service has closed the database. The same holds while a backup is being written to a value file (a vacuum_into destination, or a scheduled backup’s copy, directly or through a hard link): retry once the backup has finished.

Directory mode supports get, set, delete, existence checks, and listing with prefix and after. Atomic operations, batch requests, history and time travel, the key entry, the TTL endpoints and the change feed need a database file, and return 400 against a directory. So do ttl, if_match, and the If-Match, If-None-Match and Idempotency-Key headers on a set; ttl=0, which means no expiry, is accepted. To follow changes to files, use hoody-watch.

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 Base64URL: the endpoint reads the URL-safe alphabet,
// so a plain btoa() breaks on any SQL whose encoding contains + or /
const encoded = btoa(sql).replace(/\+/g, '-').replace(/\//g, '_');


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

The read-only check is conservative. After any leading comments, the SQL must be a single SELECT or WITH statement with no ; anywhere, not even a trailing one or one inside a string, and a WITH query is refused when its text contains a write keyword such as INSERT, DELETE or PRAGMA, even inside a string literal. Send such a query with POST /api/v1/sqlite/db instead.

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
  • Queries are logged to a per-database history that keeps roughly the most recent 1,000
  • Query results are JSON that any page can fetch and render
  • No database daemon runs between requests
  • Backup is one call: the vacuum_into maintenance operation writes a consistent copy (copying only the .db file of a database in use can miss committed data still in its write-ahead log)

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',
        headers: { 'Content-Type': 'application/json' },
        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',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        transaction: [{ query: 'SELECT COUNT(*) FROM orders WHERE status = "pending"' }]
      })
    });
    // An ordinary fetch call, with no driver or connection string involved

  3. Shown as raw JSON in a page:

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

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',
  headers: { 'Content-Type': 'application/json' },
  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. fetch has no timeout option, so turn the stored seconds
// into an abort signal.
const response = await fetch(config.endpoint, {
  signal: AbortSignal.timeout(config.timeout * 1000),
});

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 increments that all succeed move the counter by exactly one thousand: none of them overwrites another. A request that fails leaves the outcome unknown rather than undone: the increment may have committed before the response was lost. Send each increment with an Idempotency-Key and retry with the same key: a committed increment is answered from its stored response instead of counting twice.

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',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    user_id: 'user-456',
    ip: '203.0.113.50',
    created_at: Date.now()
  })
});


// The key stops being readable an hour after it is written. Its row is
// cleaned up later, best-effort: after a subsequent write, or in a cleanup
// tried about every ten seconds while a change-feed reader follows the table

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',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    product_id: 'prod-xyz',
    quantity: 2,
    price: 29.99
  })
});


// Remove item. Two things differ from push: the "value" envelope, because
// remove matches on the whole element, and the Content-Type, because remove
// parses the body. fetch labels a string body text/plain, which remove
// rejects with a 400; push reads the raw bytes and does not care.
await fetch('.../kv/cart:user1/remove?db=/data/store.db', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({ value: { product_id: 'prod-xyz', quantity: 2, price: 29.99 } })
});


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

Process changes as they happen

Section titled “Process changes as they happen”

Follow the change feed with a long poll and act on each order that changes, by any client, using its value at the time the worker reads it:

const base = 'https://{project}-{container}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite';
const db = encodeURIComponent('/data/app.db');
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));


// Retry what is transient (a network error, a 503 with Retry-After: 1). Any other error
// would come back the same (a 503 without Retry-After is final too), so stop with the server's message (a 409 after someone adds
// a trigger to the table, say). `expect` lists statuses the caller handles itself.
async function get(url, expect = []) {
  for (;;) {
    const res = await fetch(url).catch(() => null);
    if (res && (res.ok || expect.includes(res.status))) return res;
    if (res && !(res.status === 503 && res.headers.has('Retry-After'))) throw new Error(`${res.status}: ${await res.text()}`);
    await sleep(1000);
  }
}


// Start from the current position, or from a cursor saved by an earlier run
let cursor = (await (await get(`${base}/changes?db=${db}`)).json()).cursor;


for (;;) {
  const res = await get(`${base}/changes?db=${db}&prefix=order:&include_values=true&wait=25&since=${encodeURIComponent(cursor)}`, [410]);
  if (res.status === 410) {
    throw new Error('the cursor cannot continue: rescan the orders, then follow from the listing\'s change_cursor');
  }
  const page = await res.json();
  for (const e of page.events) {
    if (e.op !== 'set') continue;
    let item = e; // include_values put value (JSON) or value_base64 (other types) here
    if (e.value_omitted) {
      // Not attached (superseded, expired or too_large): read the key's current entry
      const entry = await get(`${base}/kv/${encodeURIComponent(e.key)}/entry?db=${db}`, [404]);
      if (entry.status === 404) continue; // deleted since
      item = await entry.json();
    }
    await handleOrder(e.key, 'value' in item ? item.value : item.value_base64); // must tolerate the same order twice
  }
  cursor = page.next_cursor; // save it to resume here after a restart
}

Events arrive at least once, so handleOrder must be idempotent. Store cursor durably after each page to resume where the worker stopped. As long as the worker reads again before its cursor falls behind the oldest kept change (oldest_cursor), it walks every kept event and handles each value still available. An order written twice in quick succession may be handled once, with its newer value: when an event’s value is not attached, the entry read returns the current value, not the one that event wrote. An order deleted or expired before the worker reads it can be skipped altogether. The log is trimmed by age (24 hours by default) and by size (1,000,000 changes per database), so under heavy traffic that window can be much shorter than a day; a 410 means rescanning, as above.

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',
  headers: { 'Content-Type': 'application/json' },
  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 list "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 table rollback --db /data/app.db --to-timestamp $timestamp --dry-run -c $CONTAINER


# Apply
hoody kv table rollback --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',
  headers: { 'Content-Type': 'application/json' },
  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',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    items: [
      { key: 'feature:new-ui', value: JSON.stringify(true), content_type: 'application/json' },
      { key: 'feature:beta-api', value: JSON.stringify(false), content_type: 'application/json' },
      { key: 'feature:dark-mode', value: JSON.stringify(true), content_type: 'application/json' }
    ]
  })
});


// Check feature in app. A missing key is a 404 whose body is a JSON error
// object, so check the status first and decide what a missing flag means.
const res = await fetch('.../kv/feature:new-ui?db=/data/app.db');
const newUiEnabled = res.ok ? await res.json() : false;


if (newUiEnabled === true) {
  // Show new UI
}

Rate limiting

Section titled “Rate limiting”

Track API calls per user:

// Increment user's API call count; the response carries the new count
const res = await fetch(`.../kv/api-calls:user-${userId}/incr?db=/data/limits.db`, {
  method: 'POST'
});
if (!res.ok) throw new Error(`rate-limit count failed: ${res.status}`);
const { value: calls } = await res.json();


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

Atomic increment prevents lost updates when requests overlap: each successful increment counts once, so the count stays right under high concurrency. Reading the count from the increment’s own response, rather than a separate GET, keeps the check tied to that increment.

Cache layer

Section titled “Cache layer”

Cache expensive API responses:

// The key is one path segment, so encode it: an endpoint containing / or ?
// would otherwise split the key and swallow the db parameter.
const cacheKey = encodeURIComponent(`cache:api:${endpoint}`);


// Check cache. A miss is a 404 whose body is a JSON error object, so check
// the status: parsing first would treat that object as a hit. Anything
// other than a hit or a miss is a real failure worth seeing.
const res = await fetch(`.../kv/${cacheKey}?db=/data/cache.db`);
if (res.ok) {
  return await res.json();
}
if (res.status !== 404) {
  throw new Error(`cache read failed: ${res.status}`);
}


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


// Store with 10-minute TTL. A failed write only costs a cache entry, but
// silence here hides a broken cache for as long as it keeps failing.
const stored = await fetch(`.../kv/${cacheKey}?db=/data/cache.db&ttl=600`, {
  method: 'PUT',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify(data)
});
if (!stored.ok) {
  console.warn(`cache write failed: ${stored.status}`);
}


return data;

Live updates in the browser

Section titled “Live updates in the browser”

Show values that other clients change, without polling for them. The page lists the keys once, then follows the change feed from the position that listing was read at, so no write in between is lost:

const base = 'https://{project}-{container}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite';
const db = encodeURIComponent('/data/app.db');
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
let stream = null;
let seen = new Map(); // key -> number of its latest event, so a slow read never overwrites a newer event
let restarting = false;


async function getJSON(url, init) {
  const res = await fetch(url, init);
  if (!res.ok) throw new Error(`${res.status}: ${await res.text()}`);
  return res.json();
}


// Show every scoreboard key, and return the feed position the listing was read at
async function reload() {
  const board = new Map();
  let cursor = null;
  let after = null;
  do {
    const page = await getJSON(`${base}/kv?db=${db}&prefix=scoreboard:&limit=100` + (after ? `&after=${encodeURIComponent(after)}` : ''));
    if (!cursor) {
      if (!page.change_cursor) throw new Error('no feed position yet'); // busy: list again
      cursor = page.change_cursor; // the first page's: following from it covers every later page
    }
    const keys = (page.items ?? []).map((item) => item.key); // an empty page has `items: null`
    if (keys.length > 0) {
      const { results } = await getJSON(`${base}/kv/batch/get?db=${db}`, {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ keys }),
      });
      for (const key of keys) {
        if (results[key]?.error) throw new Error(results[key].error);
        if (results[key]) board.set(key, results[key].value); // null: deleted since the listing
      }
    }
    after = page.has_more ? page.next_after : null;
  } while (after);
  show(board); // replace everything on screen
  return cursor;
}


// On any failure: close the stream, list again (backing off while that fails), reopen from its cursor
async function restart() {
  if (restarting) return;
  restarting = true;
  stream?.close();
  seen = new Map(); // reads still in flight belong to the old map and are dropped
  for (let delay = 1000; ; delay = Math.min(delay * 2, 30000)) {
    try {
      open(await reload());
      break;
    } catch (err) {
      console.warn('scoreboard: reload failed, retrying', err);
      await sleep(delay);
    }
  }
  restarting = false;
}


function open(cursor) {
  const s = new EventSource(`${base}/changes/stream?db=${db}&prefix=scoreboard:&include_values=true&since=${encodeURIComponent(cursor)}`);
  const map = seen;
  stream = s;
  s.addEventListener('change', (msg) => apply(JSON.parse(msg.data), map));
  // The cursor cannot continue (reset), or the server refused a reconnect, for example a cursor
  // that expired while disconnected (a JSON 410, which sends no reset event): start over.
  s.addEventListener('reset', restart);
  s.addEventListener('error', () => { if (s.readyState === EventSource.CLOSED) restart(); });
}


async function apply(event, map) {
  if (map !== seen || event.op === 'ttl') return; // an old stream, or only the expiry changed
  const n = (map.get(event.key) ?? 0) + 1;
  map.set(event.key, n);
  if (event.op !== 'set') return render(event.key, undefined); // delete or expire
  if ('value' in event) return render(event.key, event.value);
  // No value attached (superseded, expired or too large): read the key's current entry
  try {
    const res = await fetch(`${base}/kv/${encodeURIComponent(event.key)}/entry?db=${db}`);
    const entry = res.ok ? await res.json() : null;
    if (map !== seen || map.get(event.key) !== n) return; // a newer event or a restart came first
    if (res.status === 404) return render(event.key, undefined); // deleted since
    if (!entry) throw new Error(`entry read failed: ${res.status}`);
    render(event.key, entry.value);
  } catch {
    if (map === seen && map.get(event.key) === n) restart();
  }
}


restart(); // the first load

The scoreboard values are JSON, so they arrive in value; a value of another type arrives base64-encoded in value_base64 instead. show(board) and render(key, value) are the page’s own drawing functions (undefined removes a key). Following the feed from the first listing page’s cursor replays every change made while the later pages and values were read, so the board ends up current even when a value was read after the change that wrote it. EventSource reconnects on its own after a network drop, sending the last event id, so it resumes without losing a change while that cursor is still kept; anything it cannot resume from leads to a fresh listing. The example assumes scoreboard keys have no TTL: a key that expires stops being readable at its expire_at, and its expire event arrives only when the row is cleaned up later, so remove such a key at expire_at (from the listing and from set and ttl events) instead of waiting for that event.

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',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify('new-email@example.com')
});


// Later: the key's most recent operations, newest first
// (50 by default, up to 1000 with &limit=1000; older ones are not paged)
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.
// It is recorded without extra instrumentation, for writes that go through
// the KV endpoints with history left on.

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}?db=/data/app.db`, {
    method: 'PUT',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(user)
  });
}


// Faster: 1 batch request. Give each item its content_type: the request's
// own header covers the envelope, and an item without one is stored as
// application/octet-stream, which reads back differently.
await fetch('.../kv/batch/set?db=/data/app.db', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    items: users.map(user => ({
      key: `user:${user.id}`,
      value: JSON.stringify(user),
      content_type: 'application/json'
    }))
  })
});

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?db=/data/app.db


// 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',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({ user_id: '123' })
});


// The key stops being readable at expiry, with no cleanup job of your own

Update shared keys with If-Match

Section titled “Update shared keys with If-Match”

When several clients edit the same key, read it with the key entry, change the value, and write it back with If-Match set to the ETag you read. On 412, read the entry again and redo the change. No write is lost, and the client needs no lock. For counters and arrays, the atomic operations are simpler.

Retry writes with an Idempotency-Key

Section titled “Retry writes with an Idempotency-Key”

Generate one key per logical write (an order ID, a request UUID) and send it with every attempt of that write. A retry after a timeout then applies at most once while the server still keeps the stored response: 24 hours by default, less when the database’s limit of stored responses (100,000 or 256 MiB by default) pushes out older ones. After that the key counts as unused. Do not reuse the key for a different write: that is a 422.

Snapshot before risky changes

Section titled “Snapshot before risky changes”

Before a bulk update, note the current Unix timestamp and wait for the next second before starting: history is kept in whole seconds, and a rollback to a timestamp keeps the writes made during that second. 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 SQLite SQL. Instead of connecting with mysql -u user -p or psql, you send an HTTP POST. What differs from a database server is the transport, the SQLite dialect, and that the service owns the transaction: it wraps each request, so your SQL does not issue BEGIN or COMMIT.

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 and keyspace notifications; hoody-sqlite has a resumable change feed per KV table, plus 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?”

Not the file itself. /hoody/databases/ is an ordinary directory inside each container, so another container cannot open the file at all, and putting it on a network share is worse than unsupported: those filesystems do not honor the locks SQLite relies on.

The pattern that works: one container owns the database and exposes it through its hoody-sqlite HTTP endpoints, and the others query over HTTP. The file stays on the owning container’s local filesystem, where SQLite coordinates access from hoody-sqlite and any native clients in that container. See Can two containers share one database file?.

Does time-travel history consume a lot of space?

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

It can. Each recorded write stores the key’s full value before and after the change, plus a timestamp, and there is no age or count limit on how much is kept, so a key rewritten often keeps growing its history long after its current value stops changing size. Rollbacks are what remove history through the KV API: a single-key rollback deletes the operations it reverses, and a whole-table rollback deletes post-target history for the keys it reconstructs, leaving the keys it skips or you exclude untouched. Deleting a key, a TTL expiry and the maintenance operations all leave its earlier history in place, and SQL — including scheduled maintenance SQL — can delete history outright. For ephemeral or frequently rewritten data such as session tracking, turn recording off with ?history=false.

That still keeps a small entry recording that untracked writes happened and over what span: one per unbroken run of them, however many writes the run contains, so history stops growing with the data rather than stopping entirely. A recorded write in between starts a new run, and so a new entry. Values, content types and expiry times are not kept.

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 requests as any other client — POST for SQL and the atomic operations, GET, PUT and DELETE for ordinary keys — 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.

For a database that is in use, do not copy the file underneath it. POST /api/v1/sqlite/maintenance?db=/hoody/databases/production.db with {"op": "vacuum_into", "dest_path": "backup.db"} writes a consistent copy, /hoody/databases/backup.db, through the service itself; the destination follows the same path rules as db and must not already exist. The same endpoint runs wal_checkpoint_truncate to fold the WAL back into the main file, and quick_check to verify integrity. vacuum_into and wal_checkpoint_truncate cannot go through POST /api/v1/sqlite/db, whose transaction wrapper SQLite refuses for them, which is why they have their own route; quick_check also runs as an ordinary query there. Long vacuums can raise the deadline with ?timeout= up to 300 seconds.

Does the change feed see changes made with SQL?

Section titled “Does the change feed see changes made with SQL?”

Yes. The feed records every change to the rows of a KV table, whether it came through the KV endpoints, through SQL sent to POST /api/v1/sqlite/db, or from another program that opened the file. History is different: it records only the KV endpoints’ writes.

Why did my ETags and cursors stop working?

Section titled “Why did my ETags and cursors stop working?”

A schema change made outside the KV API (for example CREATE TABLE through POST /api/v1/sqlite/db), reset_changes, or replacing the database file changes every ETag in the database and ends every change cursor. The service cannot tell what such a change did to the KV tables, so it makes clients re-read instead of trusting a version that might be stale. Expect a 412 on the next conditional write and a 410 on the next feed read, then read again.

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 on a key creates the live table; the history endpoints create only the history table they need.

The database file itself is created only when you ask: a KV request against a missing file returns 400 unless it is a write carrying create_db_if_missing=true. See Database paths.

Limits

Section titled “Limits”
WhatLimitPast it
Request body4 MiB as sent; 32 MiB after decompression when sent with Content-Encoding gzip, deflate or br (in any letter case)413. A body that fails to decompress is a 400
SQL requests10,000 items per POST /db request; 100,000 valuesBatch value sets per item and 1,000,000 per request413. An empty valuesBatch is a 400
SQL results10,000 rows per result set; 16 MiB of JSON per POST /db request (all its queries together) or per GET /queryRows past 10,000 are left out and the result says truncated: true. Past 16 MiB: 413, and a POST /db transaction is rolled back. Page with LIMIT
Batch requests1 to 100 keys, none empty400
Directory-mode keys32 /-separated segments; a segment must not be empty, ., .. or .hoody_sqlite, nor contain \ < > : " | ? * or a NUL byte. A PUT also refuses a segment named data or data.<anything>, the name that holds a value400. A key such as user:alice works on a database file but not in directory mode. A key stored under a data name before that rule can still be read and deleted
Bare database names (?db=notes)Start with a letter or digit, then letters, digits, ., - and _; 128 characters at most400
Key listinglimit up to 1000Clamped to 1000. limit=0 returns no keys on a live listing, and every key found on a listing at a timestamp
Key history listinglimit up to 1000Clamped to 1000
Replaying one key’s history50,000 history entries for that key, UNRECORDED markers includedGET /kv/{key}/snapshot, the table snapshot, diff and whole-table rollback (its dry run included) answer 413 PAYLOAD_TOO_LARGE, naming the key; a rollback writes nothing. Reads with at_timestamp, and single-key rollback, have no such limit
Single-key rollback steps50,000400
Table snapshot, diff, and listing at a timestampDiscovery of candidate keys stops around 10,000, across live rows and history together, with a separate ceiling on how much history it scansThe answer covers what was found and says candidate_truncated: true. It is a discovery bound, not a limit on how many keys come back. Naming keys explicitly on a diff skips discovery
Whole-table rollback10,000 keys413, dry run included; name the keys in the request body instead. A separate limit on how much history the scan reads reports discovery_truncated in a dry run, and applying that rollback is refused
KV ?table= names1 to 63 letters, digits and underscores, not starting with a digit. On a database file, names are case-insensitive: users and Users are the same table and share their keys. Must not end in _history, in any case. On a SQLite database, also not an SQL keyword SQLite refuses as a bare table name (order, select, group, …), not sqlite, and not starting with sqlite_400. SQL can still create such a table. In directory mode these names are ordinary folder names and work
Change feed pagelimit 1 to 1000 events, default 100; wait up to 60 seconds400
Inline values in the change feed256 KiB per valueThe event carries value_omitted: "too_large"; read the key
Change logTrimmed to the last 24 hours and to 1,000,000 changes per database. These are targets: trimming runs in the background in bounded steps, so the log can briefly hold moreA cursor behind the oldest kept change (oldest_cursor) gets 410 CHANGE_CURSOR_EXPIRED. A cursor has no lifetime of its own: an up-to-date one survives a quiet day
Change streamsBy default 1 hour each (--change-stream-max-lifetime, at least 1 second) and 1024 open at once per server (--max-change-streams, 0 for no limit)The stream ends with event: end; over the cap, 503 with Retry-After
Idempotency-Key1 to 255 visible ASCII characters; stored responses up to 1 MiB, kept 24 hours by default, and the oldest dropped earlier past 100,000 responses or 256 MiB per database (the retention time and both budgets are configurable)400 for a malformed key; 413 IDEMPOTENT_RESPONSE_TOO_LARGE for a larger response, with nothing written
Request time30 seconds by default; ?timeout= sets 1 to 300 secondsDatabase work is cancelled and the request normally fails with 503 once the handler returns. Health checks are exempt, and a request waiting on a lock can run past the deadline. A request waiting for maintenance on the same database (a vacuum, a checkpoint, reset_changes, or SQL that drops or alters a table) gives up at its deadline with 503 REQUEST_TIMEOUT; retry it. A SQL or KV write that already committed keeps its success response — but a timed-out request having changed nothing is never guaranteed

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: set PRAGMA journal_mode = WAL and PRAGMA busy_timeout = 30000 on your own connections, and keep write transactions short. See how hoody-sqlite opens a database.

Requests through hoody-sqlite already get this: the pooled connections it opens database files with use WAL, synchronous=NORMAL, busy_timeout=30000 and _txlock=immediate, so readers do not block the writer and a second writer waits its turn instead of failing at once. Writes still serialize — one writer at a time is SQLite’s model, and no location changes that.

Across containers, let one container own the file and have the others query it over this HTTP API. Do not put the file on a share both containers mount: network filesystems do not reliably honor SQLite’s locks, and that corrupts a database rather than merely locking it.

Whichever container owns it:

  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?db=/data/app.db

  2. Retry with exponential backoff:

    // A busy database answers with a status, not a thrown fetch, so retry on
    // the response as well as on a network error. This retries network failures
    // and 5xx responses; a request refused as invalid (400), such as a bad
    // database path, is not retried. Check `ok` on what comes back.
    async function retryWrite(url, body, maxRetries = 3) {
      for (let i = 0; i < maxRetries; i++) {
        const last = i === maxRetries - 1;
        try {
          const res = await fetch(url, {
            method: 'PUT',
            headers: { 'Content-Type': 'application/json' },
            body,
          });
          if (res.ok || res.status < 500 || last) return res;
        } catch (e) {
          if (last) throw e;
        }
        await new Promise(r => setTimeout(r, 100 * Math.pow(2, i)));
      }
    }

Key not found (404 response)

Section titled “Key not found (404 response)”

Problem: GET returns 404 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
    # A key with a TTL stops answering at expiry; its row is cleaned up later,
    # so it can still turn up in a SQL query of the table

  3. List all keys to verify:

    -- Via SQL, skipping rows that have expired but are not cleaned up yet
    SELECT key FROM kv_store
    WHERE expire_at IS NULL OR expire_at > unixepoch()
    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. A dry run, or no confirm=yes, on table rollback:

    Terminal window
    # &dry_run=true only previews. With neither dry_run=true nor confirm=yes,
    # POST /kv/rollback returns 400 and changes nothing. To apply:
    POST /api/v1/sqlite/kv/rollback?to_timestamp=...&db=...&confirm=yes

  2. History disabled when data was written:

    Terminal window
    # If the original write used ?history=false, its value was never stored,
    # so there is nothing to roll back to. Rolling back across such a write
    # is refused with 409 rather than silently undoing it.
    #
    # Whole-table rollback is the exception: a key written only with
    # history=false and since deleted or expired has nothing to restore,
    # so it is skipped and listed under untouched_keys instead of
    # blocking the rollback.

  3. Timestamp too far back:

    Terminal window
    # The history has to establish the state at that moment — which can be
    # "the key did not exist yet", so a target before the key was created works
    # Check history first:
    GET /api/v1/sqlite/kv/{key}/history?db=/data/app.db

  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: a batch set or delete is atomic, so every key succeeds or the whole batch fails. Partial success means the writes went out as separate requests rather than one batch. (A batch get is different by design: it answers per key, with null for a key that does not exist.)

Verify:

// Correct: one atomic batch (each value is a string; label JSON with content_type)
await fetch('.../kv/batch/set?db=/data/app.db', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    items: [
      { key: 'k1', value: JSON.stringify(v1), content_type: 'application/json' },
      { key: 'k2', value: JSON.stringify(v2), content_type: 'application/json' }
    ]
  })
});


// Wrong: two separate requests
PUT /api/v1/sqlite/kv/k1 (value: v1)
PUT /api/v1/sqlite/kv/k2 (value: v2)

412 Precondition Failed

Section titled “412 Precondition Failed”

Cause: an If-Match or If-None-Match condition did not hold. Another client wrote the key after you read it, the key does not exist (If-Match), or it already exists (If-None-Match: *). A schema change made with SQL also changes every ETag.

Fix: current_etag in the response is the key’s ETag now. Read the key again, reapply your change, and retry with that ETag. A null current_etag means the key does not exist: If-Match cannot match it, so recreate the key with If-None-Match: * instead. For a batch, failed lists each key whose condition did not hold; nothing was written.

410 from the change feed

Section titled “410 from the change feed”

Cause: the cursor cannot be continued. CHANGE_CURSOR_EXPIRED means it is older than the kept log. CHANGE_CURSOR_INVALID gives a reason: database_replaced (the file was replaced or restored, or reset_changes ran), table_changed (the table or the schema changed outside the KV API), or future_cursor (the file was overwritten with an older copy of itself).

Fix: read the state again, for example by listing the keys, and follow the feed from the listing’s change_cursor. A cursor is opaque: pass back exactly what the service returned.

422 on a retried write

Section titled “422 on a retried write”

Cause: the Idempotency-Key was already used for a request that differs in its method, route, key, query parameters, conditional headers, content type or body. A different timeout alone does not count.

Fix: resend the original request unchanged, or use a new key for a new write.

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 49 cloud storage and protocol backends through HTTP.

Explore Files →

Exec

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

Explore Exec →

SQLite reference pages: