Skip to content
Hoody Documentation
Download Page (.md)

SQLite: SQL Operations

Section titled “SQLite: SQL Operations”

The SQLite SQL Operations API lets you execute SQL transactions, create databases, and run shareable queries against SQLite database files. Use these endpoints to manage database lifecycle, execute multi-statement transactions with ACID guarantees, and share queries via URL-safe base64 encoding. Database paths accept absolute paths, bare names, or ./name shorthand (resolved to /hoody/databases/*.db).

OpenAPI Specification

Section titled “OpenAPI Specification”

GET /api/v1/sqlite/openapi.json

Section titled “GET /api/v1/sqlite/openapi.json”

Redirects to the YAML specification endpoint.

This endpoint takes no parameters.

Response

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

SDK Usage

const result = await client.sqlite.docs.getJson();

GET /api/v1/sqlite/openapi.yaml

Section titled “GET /api/v1/sqlite/openapi.yaml”

Retrieve the complete OpenAPI specification in YAML format.

This endpoint takes no parameters.

Response

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

SDK Usage

const result = await client.sqlite.docs.getYaml();

Shareable Queries

Section titled “Shareable Queries”

GET /api/v1/sqlite/query

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

Execute a SQL query using base64-encoded SQL for easy sharing via URL. The sql parameter must be a base64-encoded SQL string, making queries safe to embed in URLs.

Parameters

Section titled “Parameters”
NameInTypeRequiredDescription
dbquerystringYesDatabase file path
sqlquerystringYesBase64-encoded SQL query

SDK Usage

const result = await client.sqlite.query.executeShareable({
  db: "my-database.db",
  sql: "U0VMRUNUIHRpbWVzdGFtcCwgY291bnQoKik="
});

Response

{
  "columns": ["timestamp", "count"],
  "rows": [
    ["2024-01-15 10:30:00", 42],
    ["2024-01-15 10:31:00", 17]
  ]
}

Database Operations

Section titled “Database Operations”

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. When init_kv is true, the database is pre-configured with key-value store tables for directory mode usage.

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

SDK Usage

const result = await client.sqlite.database.create({
  path: "my-database.db",
  init_kv: true,
  kv_table: "kv_store"
});

Response

{
  "status": "created",
  "path": "/hoody/databases/my-database.db"
}

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 transaction item accepts statement (preferred) or query (alias) for the SQL string.

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. Default: false

Request Body

Section titled “Request Body”

Transaction request containing queries and statements. Each transaction item accepts statement (preferred) or query (alias) for statements.

FieldTypeRequiredDescription
resultFormatstringNoFormat for query results
transactionarrayNoArray of transaction items to execute in order

Each transaction item supports:

FieldTypeRequiredDescription
noFailbooleanNoIf true, continue executing remaining statements even if this one fails
querystringNoSQL query string (alias for statement)
statementstringNoSQL statement string (preferred over query)
valuesarrayNoPositional parameter values for the statement
valuesBatcharrayNoBatch of parameter value arrays for bulk operations

SDK Usage

const result = await client.sqlite.database.executeTransaction({
  db: "my-database.db",
  create_db_if_missing: true,
  data: {
    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: [1, 2]
      }
    ]
  }
});

Response

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