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

SQLite: SQL Operations

Section titled “SQLite: SQL Operations”

Run SQL against your SQLite database, create new database files, execute transactional batches with ACID guarantees, and run maintenance operations such as WAL checkpoints, VACUUM INTO, and integrity checks. Use these endpoints when you need to mutate schema or data, share a query via a URL, or perform offline-friendly compaction on a database file inside your container.

All endpoints in this group target the container’s SQLite service at https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com.

List databases in a directory

Section titled “List databases in a directory”

GET /api/v1/sqlite/db/list

Section titled “GET /api/v1/sqlite/db/list”

List the files in one directory whose extension is .db, .sqlite, or .sqlite3, newest first. Subdirectories, symlinks, and other non-regular entries are skipped. is_valid reports whether the file starts with a SQLite header. Without the --allow-any-absolute-db-path flag, only /hoody/databases and directories under it can be listed. The databases field is null when nothing matches.

Parameters

Section titled “Parameters”
NameInTypeRequiredDescription
dirquerystringNoAbsolute path of the directory to list. Default: "/hoody/databases"
timeoutqueryintegerNoDeadline for this request, in seconds, clamped to [1, 300]. It sets the request context deadline that handlers check between steps, so a long operation stops at the next checkpoint rather than being cut off mid-step; work already in progress, such as a filesystem scan or a wait for another writer, runs to completion. This is a server-side deadline, not a client transport timeout. Omitted, the server default applies (30 seconds unless the deployment overrides it). Default: 30

Response

Section titled “Response”
{
  "success": true,
  "count": 2,
  "dir": "/hoody/databases",
  "databases": [
    {
      "name": "my-database.db",
      "path": "/hoody/databases/my-database.db",
      "size": 24576,
      "size_str": "24.0 KB",
      "modified": "2026-01-15T10:30:00Z",
      "mod_unix": 1736937000,
      "is_valid": true
    },
    {
      "name": "archive.sqlite3",
      "path": "/hoody/databases/archive.sqlite3",
      "size": 1048576,
      "size_str": "1.0 MB",
      "modified": "2026-01-12T08:15:00Z",
      "mod_unix": 1736667300,
      "is_valid": true
    }
  ]
}

SDK usage

Section titled “SDK usage”
Terminal window
curl -G "https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite/db/list" \
  --data-urlencode "dir=/hoody/databases"

Execute shareable SQL query

Section titled “Execute shareable SQL query”

GET /api/v1/sqlite/query

Section titled “GET /api/v1/sqlite/query”

Run one read-only query passed in the URL, so the link can be shared: a single SELECT or WITH statement, without a semicolon. Anything else is refused with 400; use POST /api/v1/sqlite/db for it. SQL holding a NUL byte is refused with 400 too (code SQL_ERROR): SQLite would stop reading at the NUL and ignore the rest. The sql value is base64url-encoded (with or without = padding); plain SQL text is also accepted.

Parameters

Section titled “Parameters”
NameInTypeRequiredDescription
dbquerystringYesDatabase file path
sqlquerystringYesThe SQL query, base64url-encoded (with or without = padding). Plain SQL text is also accepted.
timeoutqueryintegerNoDeadline for this request, in whole seconds, clamped to [1, 300]. Once it passes, a long operation stops at its next checkpoint rather than being cut off mid-step; a step already running, such as a filesystem scan or a wait for another writer, finishes first. A request that has not finished by then answers 503 REQUEST_TIMEOUT, except that a write which has already committed still returns its success. A value that is not a whole number is ignored and the default applies. This is a server-side deadline, not a client transport timeout. Omitted, the server default applies (30 seconds unless the deployment overrides it). Default: 30

Response

Section titled “Response”
{
  "columns": ["id", "name", "email"],
  "query": "SELECT id, name, email FROM users",
  "resultSet": [
    { "id": 1, "name": "Ada Lovelace", "email": "ada@example.com" },
    { "id": 2, "name": "Alan Turing", "email": "alan@example.com" }
  ],
  "rowCount": 2,
  "success": true,
  "truncated": false
}

SDK usage

Section titled “SDK usage”
Terminal window
curl -G "https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite/query" \
  --data-urlencode "db=my-database.db" \
  --data-urlencode "sql=U0VMRUNUICogRlJPTSB1c2Vycw=="

Execute SQL transaction

Section titled “Execute SQL transaction”

POST /api/v1/sqlite/db

Section titled “POST /api/v1/sqlite/db”

Execute multiple SQL queries or statements in a single transaction with full ACID guarantees. Each item sends its SQL as query or statement (alias sql) and its bind values as values (alias params). A statement whose SQL produces result columns (SELECT, WITH, PRAGMA, INSERT/UPDATE/DELETE ... RETURNING) returns its rows exactly like a query; one that produces none returns rowsUpdated. An unknown key on an item is refused with 400 UNKNOWN_FIELD, and values and params with different contents with 400 VALUES_CONFLICT.

Parameters

Section titled “Parameters”
NameInTypeRequiredDescription
dbquerystringYesDatabase path (absolute path, bare name, or ./name shorthand resolved to /hoody/databases/*.db)
create_db_if_missingquerybooleanNoCreate database file if it is missing. The legacy alias auto_create is still accepted and means the same thing; the two must agree when both are sent. Default: false
timeoutqueryintegerNoDeadline for this request, in whole seconds, clamped to [1, 300]. Once it passes, a long operation stops at its next checkpoint rather than being cut off mid-step; a step already running, such as a filesystem scan or a wait for another writer, finishes first. A request that has not finished by then answers 503 REQUEST_TIMEOUT, except that a write which has already committed still returns its success. A value that is not a whole number is ignored and the default applies. This is a server-side deadline, not a client transport timeout. Omitted, the server default applies (30 seconds unless the deployment overrides it). Default: 30

Request body

Section titled “Request body”
FieldTypeRequiredDescription
resultFormatstringNoResult format hint (e.g. json)
transactionarrayNoOrdered list of statements to execute inside the transaction

Each entry in transaction accepts the following fields:

FieldTypeRequiredDescription
querystringNoSQL that returns rows (SELECT, WITH, PRAGMA, ... RETURNING). The item answers resultHeaders and resultSet (or resultSetList).
statementstringNoSQL statement to execute (preferred field)
sqlstringNoAlias for statement (backward compatibility)
valuesarray or objectNoBind parameters: a JSON array for positional (?) parameters, or a JSON object for named (:name) parameters. Elements must be JSON scalars (string, number, boolean, null); numbers must fit int64/float64 (else 400). A nested array or object cannot be bound: the request fails with 400 SQL_BIND_ERROR, or, for an item with noFail, that item reports the error and the rest of the request goes on.
paramsarray or objectNoAlias for values. Sending both is accepted when they hold the same value; different contents are refused with 400 VALUES_CONFLICT, and an explicit null counts as a value.
valuesBatcharrayNoBatch bind parameters: one entry per execution of the statement; each entry follows the same rules as values.
noFailbooleanNoWhen true, a failure on this statement does not abort the transaction.

Response

Section titled “Response”
{
  "results": [
    {
      "success": true,
      "rowsUpdated": 0,
      "resultHeaders": [],
      "resultSet": []
    },
    {
      "success": true,
      "rowsUpdated": 1,
      "resultHeaders": [],
      "resultSet": []
    }
  ]
}

SDK usage

Section titled “SDK usage”
Terminal window
curl -X POST "https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite/db?db=my-database.db&create_db_if_missing=true" \
  -H "Content-Type: application/json" \
  -d '{
    "resultFormat": "json",
    "transaction": [
      { "statement": "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)", "noFail": true },
      { "statement": "INSERT INTO users (name, email) VALUES (?, ?)", "values": ["Ada", "ada@example.com"] }
    ]
  }'

Create new SQLite database

Section titled “Create new SQLite database”

POST /api/v1/sqlite/db/create

Section titled “POST /api/v1/sqlite/db/create”

Create a new empty SQLite database with optional KV store initialization.

Parameters

Section titled “Parameters”
NameInTypeRequiredDescription
pathquerystringYesDatabase path (absolute path, bare name, or ./name shorthand resolved to /hoody/databases/*.db)
init_kvquerybooleanNoInitialize KV store tables. Default: false
kv_tablequerystringNoCustom KV table name. Default: "kv_store"
timeoutqueryintegerNoDeadline for this request, in whole seconds, clamped to [1, 300]. Once it passes, a long operation stops at its next checkpoint rather than being cut off mid-step; a step already running, such as a filesystem scan or a wait for another writer, finishes first. A request that has not finished by then answers 503 REQUEST_TIMEOUT, except that a write which has already committed still returns its success. A value that is not a whole number is ignored and the default applies. This is a server-side deadline, not a client transport timeout. Omitted, the server default applies (30 seconds unless the deployment overrides it). Default: 30

Response

Section titled “Response”
{
  "success": true,
  "path": "my-database.db",
  "message": "Database created successfully",
  "size": 16384,
  "kv_table": "kv_store",
  "tables": ["_query_history", "kv_store", "kv_store_history"]
}

SDK usage

Section titled “SDK usage”
Terminal window
curl -X POST "https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite/db/create?path=my-database.db&init_kv=true&kv_table=kv_store"

Delete SQLite database

Section titled “Delete SQLite database”

DELETE /api/v1/sqlite/db

Section titled “DELETE /api/v1/sqlite/db”

Permanently delete a SQLite database file, together with its -wal, -shm and -journal files and everything stored in it (tables, KV stores, query and KV history). Requests already running against the database finish first; new requests wait until the deletion is done and then find no database. Only SQLite database files can be deleted: a directory-mode KV store (a directory) is refused, and so is any file without a SQLite header. Cannot be undone.

Parameters

Section titled “Parameters”
NameInTypeRequiredDescription
dbquerystringYesDatabase path (absolute path, bare name, or ./name shorthand resolved to /hoody/databases/*.db)
timeoutqueryintegerNoDeadline for this request, in whole seconds, clamped to [1, 300]. Once it passes, a long operation stops at its next checkpoint rather than being cut off mid-step; a step already running, such as a filesystem scan or a wait for another writer, finishes first. A request that has not finished by then answers 503 REQUEST_TIMEOUT, except that a write which has already committed still returns its success. A value that is not a whole number is ignored and the default applies. This is a server-side deadline, not a client transport timeout. Omitted, the server default applies (30 seconds unless the deployment overrides it). Default: 30

Response

Section titled “Response”
{
  "success": true,
  "path": "my-database.db",
  "files_removed": 4,
  "message": "Database deleted successfully"
}

SDK usage

Section titled “SDK usage”
Terminal window
curl -X DELETE "https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite/db?db=my-database.db"

Run database maintenance

Section titled “Run database maintenance”

POST /api/v1/sqlite/maintenance

Section titled “POST /api/v1/sqlite/maintenance”

Run a maintenance operation that cannot execute inside the transactional /db endpoint: wal_checkpoint_truncate (PRAGMA wal_checkpoint(TRUNCATE)), vacuum_into (VACUUM INTO dest_path; dest_path is jailed like the db parameter and must not already exist; missing parent folders of dest_path are created), quick_check (PRAGMA quick_check; result carries the first result row, ok on a healthy database), restore (replace the whole content of the database with the SQLite database at src_path, such as a vacuum_into copy, then start a new KV change-capture epoch as reset_changes does; it runs as one write transaction, so open readers and later requests see either the old or the restored content, never a mix; a restore refused or failed before its copy commits, or out of time, changes nothing; src_path is jailed like the db parameter and is only read; a source whose page size differs from the database’s is refused with 400), or reset_changes (start a new KV change-capture epoch: every change cursor and every KV ETag of the database stops matching, so clients re-read). To restore a backup, use restore: a file copied over a database that is open here is not seen consistently, because its old -wal file and the cached pages stay in use. The operation runs directly on the database connection, fenced from concurrent query handlers. The database is never created; a missing file returns 404. Long VACUUMs can extend the request deadline via the ?timeout= query parameter (clamped to 5m).

Parameters

Section titled “Parameters”
NameInTypeRequiredDescription
dbquerystringYesDatabase path (absolute path, bare name, or ./name shorthand resolved to /hoody/databases/*.db)
timeoutqueryintegerNoDeadline for this request, in whole seconds, clamped to [1, 300]. Once it passes, a long operation stops at its next checkpoint rather than being cut off mid-step; a step already running, such as a filesystem scan or a wait for another writer, finishes first. A request that has not finished by then answers 503 REQUEST_TIMEOUT, except that a write which has already committed still returns its success. A value that is not a whole number is ignored and the default applies. This is a server-side deadline, not a client transport timeout. Omitted, the server default applies (30 seconds unless the deployment overrides it). Default: 30

Request body

Section titled “Request body”
FieldTypeRequiredDescription
opstringYesWhich maintenance operation to run. One of wal_checkpoint_truncate, vacuum_into, quick_check, reset_changes, restore.
dest_pathstringNoDestination file for vacuum_into. Required for that operation and ignored by the others. Jailed like the db parameter, and it must not already exist.
src_pathstringNoSource file for restore: an existing SQLite database, such as a vacuum_into copy. Required for that operation and ignored by the others. Jailed like the db parameter, and it must not be the database itself. It is only read.

Response

Section titled “Response”
{
  "op": "wal_checkpoint_truncate",
  "result": "ok"
}

SDK usage

Section titled “SDK usage”
Terminal window
curl -X POST "https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite/maintenance?db=my-database.db&timeout=60" \
  -H "Content-Type: application/json" \
  -d '{
    "op": "wal_checkpoint_truncate"
  }'