# SQLite: SQL Operations

**Page:** api/sqlite/sql-operations

[Download Raw Markdown](./api/sqlite/sql-operations.md)

---

{/* AUTO-GENERATED — Do not edit manually. Regenerate with: npm run docs:api:generate */}



# 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

### `GET /api/v1/sqlite/openapi.json`

Redirects to the YAML specification endpoint.

This endpoint takes no parameters.

**Response**



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



**SDK Usage**

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

---

### `GET /api/v1/sqlite/openapi.yaml`

Retrieve the complete OpenAPI specification in YAML format.

This endpoint takes no parameters.

**Response**



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



**SDK Usage**

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

---

## Shareable Queries

### `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

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

**SDK Usage**

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

**Response**



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


```json
{
  "error": "INVALID_DB_PATH",
  "message": "The provided database path is invalid or inaccessible"
}
```

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


```json
{
  "error": "DATABASE_ERROR",
  "message": "An internal database error occurred"
}
```

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

### `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

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

**SDK Usage**

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

**Response**



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


```json
{
  "error": "INVALID_DB_PATH",
  "message": "The provided database path is invalid"
}
```

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


```json
{
  "error": "Database already exists",
  "path": "/hoody/databases/my-database.db"
}
```


```json
{
  "error": "DATABASE_ERROR",
  "message": "An internal database error occurred"
}
```

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

| 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

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

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| `resultFormat` | string | No | Format for query results |
| `transaction` | array | No | Array of transaction items to execute in order |

Each transaction item supports:

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| `noFail` | boolean | No | If true, continue executing remaining statements even if this one fails |
| `query` | string | No | SQL query string (alias for `statement`) |
| `statement` | string | No | SQL statement string (preferred over `query`) |
| `values` | array | No | Positional parameter values for the statement |
| `valuesBatch` | array | No | Batch of parameter value arrays for bulk operations |

**SDK Usage**

```ts
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**



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


```json
{
  "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 |


```json
{
  "error": "DATABASE_ERROR",
  "reqIdx": 0
}
```

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