# NSQLite

> SQLite over the network, with Litestream support.

SQLite over the network, with Litestream support.

# Reference

# NSQLite API Reference

Base URL: `http://<host>:<port>` (default port: `9876`)

All responses include the header `X-Server: NSQLite`.

______________________________________________________________________

## Authentication

Endpoints marked **🔒 Admin** require a valid `Authorization: Bearer <token>` header with an admin-level token.

Endpoints marked **🔒 Query** require a valid `Authorization: Bearer <token>` header. The role (admin, read/write, or read-only) determines which SQL operations are allowed — see the [`/query`](#post-query) endpoint for details.

If no authentication tokens are configured (`NSQLITE_AUTH_TOKEN`, `NSQLITE_AUTH_TOKEN_RW`, and `NSQLITE_AUTH_TOKEN_RO` are all empty), the server runs in **unauthenticated mode** and all requests are treated as admin.

**Error format** (all error responses):

```
{
  "id": "a1b2c3d4-...",
  "error": "Unauthorized",
  "message": "Unauthorized"
}
```

- `id` — Unique error identifier (UUID v4), useful for cross-referencing server logs.
- `error` — HTTP status text.
- `message` — Human-readable description of the error.

______________________________________________________________________

## Endpoints

### `GET /health` — Health Check

**Auth:** None

**Description:** Verifies the server is alive and can successfully execute a simple SQL query (`SELECT 1`) against the underlying SQLite database.

**Response** `200 OK`:

```
OK
```

**Response** `500 Internal Server Error` — returned when the database is unreachable or the query fails.

______________________________________________________________________

### `GET /version` — Version Info

**Auth:** 🔒 Admin

**Description:** Returns the current NSQLite version string.

**Response** `200 OK`:

```
v0.1.0
```

*(Version format depends on the build; plain text body.)*

______________________________________________________________________

### `GET /stats` — Server Statistics

**Auth:** 🔒 Admin

**Description:** Returns live server and database statistics. The exact fields depend on the stats implementation but typically include HTTP request counters, queue depth, and database connection pool metrics.

**Response** `200 OK`:

```
{
  "httpRequests": 42,
  "httpRequestsQueued": 3,
  "httpRequestsQueuedMax": 10,
  "dbTotalTime": 1.234,
  "dbTotalTimeMax": 0.567,
  "dbAveTime": 0.029
}
```

**Response Fields** (subject to change based on build):

| Field                   | Description                                                    |
| ----------------------- | -------------------------------------------------------------- |
| `httpRequests`          | Total number of HTTP requests handled since server start.      |
| `httpRequestsQueued`    | Currently queued HTTP requests awaiting a database connection. |
| `httpRequestsQueuedMax` | Maximum number of requests ever queued simultaneously.         |
| `dbTotalTime`           | Cumulative time (in seconds) spent on database operations.     |
| `dbTotalTimeMax`        | Longest single database operation time (in seconds).           |
| `dbAveTime`             | Average database operation time (in seconds).                  |

______________________________________________________________________

### `POST /query` — Execute SQL Queries

**Auth:** 🔒 Query (Admin = all operations, Read/Write = queries + transactions, Read-Only = read queries only)

**Description:** Submit one or more SQL statements for execution. The endpoint supports batching — send an array of queries in a single request.

**Request:**

```
[
  {
    "query": "SELECT * FROM users WHERE id = ?",
    "params": [1],
    "txId": ""
  },
  {
    "query": "INSERT INTO users (name) VALUES (?)",
    "params": ["Alice"],
    "txId": ""
  }
]
```

**Request Fields:**

| Field    | Type     | Required | Description                                                                                      |
| -------- | -------- | -------- | ------------------------------------------------------------------------------------------------ |
| `query`  | `string` | ✅       | The SQL statement to execute.                                                                    |
| `params` | `array`  | ❌       | Parameters for parameterized queries (`?` placeholders).                                         |
| `txId`   | `string` | ❌       | Transaction ID obtained from a `BEGIN` query. Omit or leave empty for non-transactional queries. |

**Response** `200 OK`:

```
{
  "time": 0.0023,
  "results": [
    {
      "type": "read",
      "time": 0.0011,
      "columns": ["id", "name"],
      "types": ["integer", "text"],
      "rows": [[1, "Alice"]]
    },
    {
      "type": "write",
      "time": 0.0012,
      "lastInsertId": 2,
      "rowsAffected": 1,
      "columns": [],
      "types": [],
      "rows": []
    }
  ]
}
```

**Response Fields (top-level):**

| Field     | Type    | Description                                                 |
| --------- | ------- | ----------------------------------------------------------- |
| `time`    | `float` | Total execution time for all queries in seconds.            |
| `results` | `array` | Array of per-query results, in the same order as the input. |

**Per-Result Fields (`results[]`):**

| Field          | Type       | Applies To       | Description                                                              |
| -------------- | ---------- | ---------------- | ------------------------------------------------------------------------ |
| `type`         | `string`   | All              | Result type: `read`, `write`, `begin`, `commit`, `rollback`, or `error`. |
| `time`         | `float`    | All              | Execution time for this single query in seconds.                         |
| `error`        | `string`   | `error` results  | Error message if the query failed.                                       |
| `txId`         | `string`   | `begin` results  | Transaction ID to use in subsequent queries within the same transaction. |
| `lastInsertId` | `int`      | `write` results  | The `rowid` of the last inserted row.                                    |
| `rowsAffected` | `int`      | `write` results  | Number of rows modified by the statement.                                |
| `columns`      | `[string]` | `read` / `write` | Column names of the result set.                                          |
| `types`        | `[string]` | `read` / `write` | SQLite column type affinity names.                                       |
| `rows`         | `[array]`  | `read` / `write` | Result set rows, each row is an ordered array of values.                 |

**Query Types:**

| Type       | Description                                     | Allowed Roles                    |
| ---------- | ----------------------------------------------- | -------------------------------- |
| `read`     | SELECT, EXPLAIN, etc.                           | Admin, Read/Write, **Read-Only** |
| `write`    | INSERT, UPDATE, DELETE, DDL                     | Admin, Read/Write                |
| `begin`    | Start a transaction (`BEGIN`), returns a `txId` | Admin, Read/Write                |
| `commit`   | Commit a transaction (`COMMIT`)                 | Admin, Read/Write                |
| `rollback` | Rollback a transaction (`ROLLBACK`)             | Admin, Read/Write                |

**Transaction Handling:**

1. Send `BEGIN` as a query — the response includes a `txId`.
1. Include that `txId` in subsequent query objects within the same request **or** across separate requests.
1. Send `COMMIT` or `ROLLBACK` with the same `txId` to finish the transaction.

> **Idle timeout:** If no activity occurs on the transaction within `NSQLITE_TX_IDLE_TIMEOUT` (default `10s`), it is automatically rolled back.

______________________________________________________________________

### Error Responses (All Endpoints)

| Status Code                    | Meaning                                                                                                     |
| ------------------------------ | ----------------------------------------------------------------------------------------------------------- |
| `400 Bad Request`              | Malformed request body (e.g., invalid JSON, empty query).                                                   |
| `401 Unauthorized`             | Missing or invalid `Authorization` header.                                                                  |
| `403 Forbidden`                | Authenticated but the token's role does not permit the requested operation.                                 |
| `413 Request Entity Too Large` | Request body exceeds `NSQLITE_MAX_REQUEST_SIZE_MB`.                                                         |
| `500 Internal Server Error`    | Unexpected server error. The response body contains a unique error ID that can be found in the server logs. |

# NSQLite Configuration Reference

All configuration is performed through **environment variables** or **CLI flags**. Both are equivalent — the CLI flag `--listen-port` can also be set via `NSQLITE_LISTEN_PORT`.

> **Note on precedence:** CLI flags take precedence over environment variables when both are provided.

______________________________________________________________________

## Table of Contents

- [Server Configuration](#server-configuration)
- [Authentication](#authentication)
- [SQLite Tuning](#sqlite-tuning)
- [Litestream (Container Only)](#litestream-container-only)

______________________________________________________________________

## Server Configuration

| Environment Variable          | CLI Flag                | Description                                                       | Default   |
| ----------------------------- | ----------------------- | ----------------------------------------------------------------- | --------- |
| `NSQLITE_DATA_DIR`            | `--data-dir`            | Directory where NSQLite stores its SQLite database files.         | `/data`   |
| `NSQLITE_LISTEN_HOST`         | `--listen-host`         | Host address for the HTTP server to bind to.                      | `0.0.0.0` |
| `NSQLITE_LISTEN_PORT`         | `--listen-port`         | TCP port for the HTTP server. Valid range: `1`–`65535`.           | `9876`    |
| `NSQLITE_MAX_REQUEST_SIZE_MB` | `--max-request-size-mb` | Maximum HTTP request body size (in MB) for the `/query` endpoint. | `100`     |

> **Validation:** `NSQLITE_LISTEN_HOST` must be a valid host address. `NSQLITE_LISTEN_PORT` must be within `1`–`65535`.

______________________________________________________________________

## Authentication

NSQLite supports three tiers of access control. Each variable accepts **one or more tokens** separated by whitespace. Tokens can be:

- **Plaintext** (e.g., `my-secret-token`)
- **bcrypt hashes** (e.g., `$2a$...`)
- **Argon2id hashes** (e.g., `$argon2id$...`)

| Environment Variable    | CLI Flag          | Role           | Description                                                                                                                                        |
| ----------------------- | ----------------- | -------------- | -------------------------------------------------------------------------------------------------------------------------------------------------- |
| `NSQLITE_AUTH_TOKEN`    | `--auth-token`    | **Admin**      | Full access to all endpoints, including `/stats`, `/version`, and all query types (read, write, transactions).                                     |
| `NSQLITE_AUTH_TOKEN_RW` | `--auth-token-rw` | **Read/Write** | Access to `/query` only. Can execute read queries, write queries, and transactions. Cannot access `/stats` or `/version`.                          |
| `NSQLITE_AUTH_TOKEN_RO` | `--auth-token-ro` | **Read-Only**  | Access to `/query` only. Can only execute read queries (SELECT, etc.). Cannot execute write queries (INSERT, UPDATE, DELETE, DDL) or transactions. |

> **When all three are empty/unset:** Authentication is **disabled** — every request is treated as Admin. **Do not use in production without auth.**
>
> **Performance note:** Tokens are resolved on first use and cached in memory via a SHA-256 derived key, so repeated authentication (including bcrypt/Argon2 verification) happens only once per unique token.

______________________________________________________________________

## SQLite Tuning

| Environment Variable      | CLI Flag            | Description                                                                                                                                                                                                             | Default |
| ------------------------- | ------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------- |
| `NSQLITE_TX_IDLE_TIMEOUT` | `--tx-idle-timeout` | Maximum idle duration for an open transaction. If a transaction has no activity within this window, it is automatically rolled back. Valid time units: `ns`, `us`/`µs`, `ms`, `s`, `m`, `h`. Must be greater than zero. | `10s`   |
| `NSQLITE_MAX_READ_CONNS`  | `--max-read-conns`  | Maximum number of concurrent read-only SQLite connections. Higher values improve parallel read throughput but consume more memory.                                                                                      | `10`    |
| `NSQLITE_CACHE_SIZE_KB`   | `--cache-size-kb`   | SQLite page cache size **per connection**, in kilobytes. Specify the positive KB value (it is converted internally to SQLite's negative page-count representation).                                                     | `20000` |
| `NSQLITE_BUSY_TIMEOUT`    | `--busy-timeout`    | Amount of time SQLite waits for the database lock when another writer is active. Valid time units: `ns`, `us`/`µs`, `ms`, `s`, `m`, `h`. Must be greater than zero.                                                     | `5s`    |

______________________________________________________________________

## Litestream (Container Only)

These variables are **only evaluated by the Docker entrypoint** (`cmd/entrypoint/main.go`). They configure automatic continuous backup of the SQLite database to an S3-compatible object store via [Litestream](https://litestream.io/).

Litestream is an optional runtime wrapper. When disabled, the container runs NSQLite directly. When enabled, the container generates a Litestream config file and execs Litestream with NSQLite as a child process.

### Enabling Litestream

| Variable                     | Description                                                                                       | Default |
| ---------------------------- | ------------------------------------------------------------------------------------------------- | ------- |
| `NSQLITE_LITESTREAM_ENABLED` | Set to `true`, `1`, `yes`, or `on` to enable Litestream replication. Any other value disables it. | `false` |

### S3 Replica (Required when Litestream is enabled)

| Variable                                  | Description                                                    | Example                              |
| ----------------------------------------- | -------------------------------------------------------------- | ------------------------------------ |
| `NSQLITE_LITESTREAM_S3_BUCKET`            | S3 bucket name for the replica destination.                    | `my-nsqlite-backups`                 |
| `NSQLITE_LITESTREAM_S3_PATH`              | Object key prefix (path) within the bucket.                    | `production/db/`                     |
| `NSQLITE_LITESTREAM_S3_ENDPOINT`          | S3-compatible endpoint URL. Use the full URL including scheme. | `https://s3.us-east-1.amazonaws.com` |
| `NSQLITE_LITESTREAM_S3_REGION`            | AWS region of the target bucket.                               | `us-east-1`                          |
| `NSQLITE_LITESTREAM_S3_ACCESS_KEY_ID`     | AWS access key ID with write permission on the bucket.         | `AKIA...`                            |
| `NSQLITE_LITESTREAM_S3_SECRET_ACCESS_KEY` | AWS secret access key corresponding to the access key ID.      | `...`                                |
| `NSQLITE_LITESTREAM_S3_SESSION_TOKEN`     | Optional AWS session token (for temporary credentials).        | `...`                                |

### Litestream Tuning

| Variable                                 | Description                                                                 | Default               |
| ---------------------------------------- | --------------------------------------------------------------------------- | --------------------- |
| `NSQLITE_LITESTREAM_CONFIG_PATH`         | Filesystem path where the auto-generated Litestream YAML config is written. | `/tmp/litestream.yml` |
| `NSQLITE_LITESTREAM_LOG_LEVEL`           | Litestream log level.                                                       | `info`                |
| `NSQLITE_LITESTREAM_LOG_FORMAT`          | Litestream log format (`text` or `json`).                                   | `text`                |
| `NSQLITE_LITESTREAM_SNAPSHOT_INTERVAL`   | Interval between full database snapshots.                                   | `24h`                 |
| `NSQLITE_LITESTREAM_SNAPSHOT_RETENTION`  | How long to retain old snapshots before pruning.                            | `168h` (7 days)       |
| `NSQLITE_LITESTREAM_SYNC_INTERVAL`       | How often Litestream syncs WAL changes to S3.                               | `1s`                  |
| `NSQLITE_LITESTREAM_VALIDATION_INTERVAL` | How often Litestream validates the integrity of the replica.                | `5m`                  |

> **Important:** When Litestream is enabled, the entrypoint writes the generated config file and then **execs Litestream**, which in turn spawns NSQLite as a managed child process. The `NSQLITE_DATA_DIR` variable controls where the database file is created (joined with `database.sqlite`).