SQLite: SQL Operations

The SQLite service exposes HTTP endpoints for executing transactional SQL, creating databases, running shareable queries, and performing maintenance operations such as checkpoint, vacuum, and integrity check. Use these endpoints when you need programmatic access to a SQLite container outside of the WebSocket interface — for example, embedding a query in a URL, automating migrations, or running long-running VACUUMs that need a custom timeout.

All endpoints below run on the SQLite container host:

https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com

OpenAPI specification

The SQLite service publishes its own OpenAPI document. The JSON endpoint redirects to the YAML form so the YAML is always the canonical source.

GET /api/v1/sqlite/openapi.json

Redirects to the YAML specification endpoint.

This endpoint takes no parameters.

cURL

curl -L "https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite/openapi.json"

TypeScript SDK

import { HoodyClient } from 'hoody-sdk';

const client = new HoodyClient({ baseURL: 'https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com', token: process.env.HOODY_TOKEN });

await client.sqlite.docs.getJson();

Response (307)

{
  "description": "Redirects to YAML specification"
}

GET /api/v1/sqlite/openapi.yaml

Retrieve the complete OpenAPI specification in YAML format.

This endpoint takes no parameters.

cURL

curl "https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite/openapi.yaml"

TypeScript SDK

import { HoodyClient } from 'hoody-sdk';

const client = new HoodyClient({ baseURL: 'https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com', token: process.env.HOODY_TOKEN });

await client.sqlite.docs.getYaml();

Response (200)

{
  "description": "OpenAPI specification in YAML format",
  "schema": {
    "type": "string"
  }
}

Shareable queries

GET /api/v1/sqlite/query

Execute a SQL query using base64-encoded SQL for easy sharing via URL. The sql parameter accepts a base64-encoded SQL string; the result is returned in the body.

Parameters

Name	In	Type	Required	Description
db	query	string	Yes	Database file path
sql	query	string	Yes	Base64-encoded SQL query

cURL

curl -G "https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite/query" \
  --data-urlencode "db=app.db" \
  --data-urlencode "sql=U0VMRUNUICogRlJPTSB1c2Vycw=="

TypeScript SDK

import { HoodyClient } from 'hoody-sdk';

const client = new HoodyClient({ baseURL: 'https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com', token: process.env.HOODY_TOKEN });

await client.sqlite.query.executeShareable({ db: 'app.db', sql: 'U0VMRUNUICogRlJPTSB1c2Vycw==' });

Response (200)

{
  "results": [
    {
      "success": true,
      "rowsUpdated": 0,
      "resultHeaders": ["id", "name", "email"],
      "resultSet": [
        { "id": 1, "name": "Ada Lovelace", "email": "ada@example.com" },
        { "id": 2, "name": "Alan Turing", "email": "alan@example.com" }
      ]
    }
  ]
}

Response (400)

{
  "error": "INVALID_DB_PATH"
}

Error Code	Title	Description	Resolution
INVALID_DB_PATH	Invalid database path	The provided database path is invalid or inaccessible	Provide a valid absolute path, or use bare name / ./name shorthand (resolved to /hoody/databases/*.db)
INVALID_PARAMETERS	Invalid request parameters	One or more request parameters are invalid or malformed	Check parameter types and values against the API specification
INVALID_SQLITE_HEADER	Not a valid SQLite database	The file exists but is not a valid SQLite database	Ensure the file is a valid SQLite database with proper header
PATH_IS_DIRECTORY	Path is a directory	Expected a .db file but got a directory (use table parameter for directory mode)	Use a .db file path or add table parameter for directory mode KV store

Response (500)

{
  "error": "DATABASE_ERROR"
}

Error Code	Title	Description	Resolution
DATABASE_ERROR	Database operation failed	An internal database error occurred	Check server logs for details. Database may be corrupted or locked.
FILE_SYSTEM_ERROR	File system error	Failed to read or write filesystem in directory mode	Check file permissions and disk space

Database management

POST /api/v1/sqlite/db/create

Create a new empty SQLite database. The database file is initialized at the given path; if init_kv=true, the schema for a KV store is also created so the database can be used with directory-style KV operations.

Parameters

Name	In	Type	Required	Description
path	query	string	Yes	Database path (absolute path, bare name, or ./name shorthand resolved to /hoody/databases/*.db)
init_kv	query	boolean	No	Initialize KV store tables. Default: false
kv_table	query	string	No	Custom KV table name. Default: "kv_store"

cURL

curl -X POST "https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite/db/create?path=app.db&init_kv=true&kv_table=kv_store"

TypeScript SDK

import { HoodyClient } from 'hoody-sdk';

const client = new HoodyClient({ baseURL: 'https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com', token: process.env.HOODY_TOKEN });

await client.sqlite.database.create({ path: 'app.db', init_kv: true, kv_table: 'kv_store' });

Response (201)

{
  "path": "app.db",
  "created": true,
  "init_kv": true,
  "kv_table": "kv_store"
}

Response (400)

{
  "error": "INVALID_DB_PATH"
}

Error Code	Title	Description	Resolution
INVALID_DB_PATH	Invalid database path	The provided database path is invalid or inaccessible	Provide a valid absolute path, or use bare name / ./name shorthand (resolved to /hoody/databases/*.db)
INVALID_PARAMETERS	Invalid request parameters	One or more request parameters are invalid or malformed	Check parameter types and values against the API specification
INVALID_SQLITE_HEADER	Not a valid SQLite database	The file exists but is not a valid SQLite database	Ensure the file is a valid SQLite database with proper header
PATH_IS_DIRECTORY	Path is a directory	Expected a .db file but got a directory (use table parameter for directory mode)	Use a .db file path or add table parameter for directory mode KV store

Response (409)

{
  "error": "DATABASE_EXISTS"
}

Response (500)

{
  "error": "FILE_SYSTEM_ERROR"
}

Error Code	Title	Description	Resolution
DATABASE_ERROR	Database operation failed	An internal database error occurred	Check server logs for details. Database may be corrupted or locked.
FILE_SYSTEM_ERROR	File system error	Failed to read or write filesystem in directory mode	Check file permissions and disk space

POST /api/v1/sqlite/maintenance

Run a maintenance operation that cannot execute inside the transactional POST /db endpoint. Supported operations are:

• wal_checkpoint_truncate — runs PRAGMA wal_checkpoint(TRUNCATE).
• vacuum_into — runs VACUUM INTO dest_path; the destination is jailed like the db parameter and must not already exist.
• quick_check — runs PRAGMA quick_check; result carries the first result row, which is "ok" on a healthy database.

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

Name	In	Type	Required	Description
db	query	string	Yes	Database path (absolute path, bare name, or ./name shorthand resolved to /hoody/databases/*.db)
timeout	query	integer	No	Request deadline in seconds (clamped to [1, 300])

Request Body

The body is an open JSON object describing the maintenance operation. It carries the op selector (wal_checkpoint_truncate, vacuum_into, or quick_check) and, for vacuum_into, the required dest_path.

cURL

curl -X POST "https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite/maintenance?db=app.db&timeout=300" \
  -H "Content-Type: application/json" \
  -d '{ "op": "wal_checkpoint_truncate" }'

TypeScript SDK

import { HoodyClient } from 'hoody-sdk';

const client = new HoodyClient({ baseURL: 'https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com', token: process.env.HOODY_TOKEN });

await client.sqlite.sql.runMaintenance({ op: 'wal_checkpoint_truncate' }, { db: 'app.db', timeout: 300 });

Response (200)

{
  "op": "wal_checkpoint_truncate",
  "result": "ok"
}

Response (400)

{
  "error": "INVALID_PARAMETERS"
}

Error Code	Title	Description	Resolution
INVALID_DB_PATH	Invalid database path	The provided database path is invalid or inaccessible	Provide a valid absolute path, or use bare name / ./name shorthand (resolved to /hoody/databases/*.db)
INVALID_PARAMETERS	Invalid request parameters	One or more request parameters are invalid or malformed	Check parameter types and values against the API specification
INVALID_SQLITE_HEADER	Not a valid SQLite database	The file exists but is not a valid SQLite database	Ensure the file is a valid SQLite database with proper header
PATH_IS_DIRECTORY	Path is a directory	Expected a .db file but got a directory (use table parameter for directory mode)	Use a .db file path or add table parameter for directory mode KV store

Response (404)

{
  "error": "DATABASE_NOT_FOUND"
}

Error Code	Title	Description	Resolution
KEY_NOT_FOUND	Key not found	The requested key does not exist in the KV store	Verify the key name and database/table parameters
DATABASE_NOT_FOUND	Database file does not exist	The specified database file was not found	Check the file path or use create_db_if_missing=true to create it
KEY_EXPIRED	Key expired	The key existed but has expired due to TTL	The key was automatically deleted. Store a new value if needed.

Response (409)

{
  "error": "MAINTENANCE_CONFLICT"
}

Response (500)

{
  "error": "DATABASE_ERROR"
}

Error Code	Title	Description	Resolution
DATABASE_ERROR	Database operation failed	An internal database error occurred	Check server logs for details. Database may be corrupted or locked.
FILE_SYSTEM_ERROR	File system error	Failed to read or write filesystem in directory mode	Check file permissions and disk space

Transaction execution

POST /api/v1/sqlite/db

Execute multiple SQL queries or statements in a single transaction with full ACID guarantees. Each entry in the transaction array runs in order; if any statement fails (and noFail is not set), the whole transaction rolls back.

Parameters

Name	In	Type	Required	Description
db	query	string	Yes	Database path (absolute path, bare name, or ./name shorthand resolved to /hoody/databases/*.db)
create_db_if_missing	query	boolean	No	Create database file if it is missing. Default: false

Request Body

The body is a sqlite_main.request object:

Field	Type	Required	Description
resultFormat	string	No	Controls the response shape; for example "json".
transaction	array	No	Ordered list of statements to run inside the transaction.

Each item in transaction is a sqlite_main.requestItem with the following fields:

Field	Type	Required	Description
statement	string	No	SQL statement to execute. Preferred field.
sql	string	No	Alias for statement.
query	string	No	Alias for statement.
noFail	boolean	No	When true, the transaction continues past a failure on this item.
values	array of integer	No	Positional bind values for the statement.
valuesBatch	array of array of integer	No	Multiple parameter sets; each inner array is one row for the same statement.

Tip

A request body is required but resultFormat and transaction are both optional in the schema. In practice, supply transaction whenever you want the server to execute statements — an empty transaction array is a valid no-op.

cURL

curl -X POST "https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com/api/v1/sqlite/db?db=app.db&create_db_if_missing=true" \
  -H "Content-Type: application/json" \
  -d '{
    "resultFormat": "json",
    "transaction": [
      {
        "statement": "CREATE TABLE IF NOT EXISTS counters (id INTEGER PRIMARY KEY, hits INTEGER, visits INTEGER)",
        "noFail": true
      },
      {
        "statement": "INSERT INTO counters (id, hits, visits) VALUES (?, ?, ?)",
        "values": [1, 42, 7]
      },
      {
        "statement": "SELECT id, hits, visits FROM counters ORDER BY id DESC LIMIT 1"
      }
    ]
  }'

TypeScript SDK

import { HoodyClient } from 'hoody-sdk';

const client = new HoodyClient({ baseURL: 'https://{projectId}-{containerId}-sqlite-1.{server}.containers.hoody.com', token: process.env.HOODY_TOKEN });

const result = await client.sqlite.database.executeTransaction(
  {
    resultFormat: "json",
    transaction: [
      { statement: "CREATE TABLE IF NOT EXISTS counters (id INTEGER PRIMARY KEY, hits INTEGER, visits INTEGER)", noFail: true },
      { statement: "INSERT INTO counters (id, hits, visits) VALUES (?, ?, ?)", values: [1, 42, 7] },
      { statement: "SELECT id, hits, visits FROM counters ORDER BY id DESC LIMIT 1" }
    ]
  },
  { db: "app.db", create_db_if_missing: true }
);

Response (200)

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

Response (400)

{
  "error": "INVALID_PARAMETERS",
  "reqIdx": 1
}

Error Code	Title	Description	Resolution
INVALID_DB_PATH	Invalid database path	The provided database path is invalid or inaccessible	Provide a valid absolute path, or use bare name / ./name shorthand (resolved to /hoody/databases/*.db)
INVALID_PARAMETERS	Invalid request parameters	One or more request parameters are invalid or malformed	Check parameter types and values against the API specification
INVALID_SQLITE_HEADER	Not a valid SQLite database	The file exists but is not a valid SQLite database	Ensure the file is a valid SQLite database with proper header
PATH_IS_DIRECTORY	Path is a directory	Expected a .db file but got a directory (use table parameter for directory mode)	Use a .db file path or add table parameter for directory mode KV store

Response (500)

{
  "error": "DATABASE_ERROR",
  "reqIdx": 2
}

Error Code	Title	Description	Resolution
DATABASE_ERROR	Database operation failed	An internal database error occurred	Check server logs for details. Database may be corrupted or locked.
FILE_SYSTEM_ERROR	File system error	Failed to read or write filesystem in directory mode	Check file permissions and disk space

Note

When a transaction item fails and noFail is true, the response item for that index carries success: false and the server continues with the next item. Otherwise the transaction is rolled back and a 400 is returned with reqIdx pointing at the failing item.