# NSQLite

> SQLite over the network, with Litestream support.

This file contains the LLM-readable Markdown content for the full site.

---

Source: https://nsqlite.varavel.com/
Markdown: https://nsqlite.varavel.com/index.md
Content file: content/_index.md

# NSQLite

> SQLite over the network, with Litestream support.

Canonical HTML: https://nsqlite.varavel.com/
Markdown: https://nsqlite.varavel.com/index.md

---

Source: https://nsqlite.varavel.com/docs/
Markdown: https://nsqlite.varavel.com/docs/index.md
Content file: content/docs/_index.md

# Documentation

Canonical HTML: https://nsqlite.varavel.com/docs/
Markdown: https://nsqlite.varavel.com/docs/index.md

---

Source: https://nsqlite.varavel.com/docs/guides/
Markdown: https://nsqlite.varavel.com/docs/guides/index.md
Content file: content/docs/guides/_index.md

# Guides

Canonical HTML: https://nsqlite.varavel.com/docs/guides/
Markdown: https://nsqlite.varavel.com/docs/guides/index.md

---

Source: https://nsqlite.varavel.com/docs/guides/configuration/
Markdown: https://nsqlite.varavel.com/docs/guides/configuration/index.md
Content file: content/docs/guides/configuration.md

# Container Configuration

> This project is designed to be used with Docker and aims to ship with sensible defaults that work...

Canonical HTML: https://nsqlite.varavel.com/docs/guides/configuration/
Markdown: https://nsqlite.varavel.com/docs/guides/configuration/index.md

This project is designed to be used with Docker and aims to ship with sensible defaults that work out of the box. You shouldn't need to change much. But when you do, everything is here.

All configuration is performed through **environment variables**.

## Server Configuration

| Environment Variable          | Description                                                       | Default   |
| ----------------------------- | ----------------------------------------------------------------- | --------- |
| `NSQLITE_DATA_DIR`            | Directory where NSQLite stores its SQLite database files.         | `/data`   |
| `NSQLITE_LISTEN_HOST`         | Host address for the HTTP server to bind to.                      | `0.0.0.0` |
| `NSQLITE_LISTEN_PORT`         | TCP port for the HTTP server. Valid range: `1`–`65535`.           | `9876`    |
| `NSQLITE_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    | Role           | Description                                                                                                                                        |
| ----------------------- | -------------- | -------------------------------------------------------------------------------------------------------------------------------------------------- |
| `NSQLITE_AUTH_TOKEN`    | **Admin**      | Full access to all endpoints, including `/stats`, `/version`, and all query types (read, write, transactions).                                     |
| `NSQLITE_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` | **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`).

---

Source: https://nsqlite.varavel.com/docs/guides/transactions/
Markdown: https://nsqlite.varavel.com/docs/guides/transactions/index.md
Content file: content/docs/guides/transactions.md

# Transactions

> NSQLite supports SQLite transactions over the RPC API. A transaction lets you group multiple writ...

Canonical HTML: https://nsqlite.varavel.com/docs/guides/transactions/
Markdown: https://nsqlite.varavel.com/docs/guides/transactions/index.md

NSQLite supports SQLite transactions over the RPC API. A transaction lets you group multiple writes atomically: either all succeed or all are rolled back.

## Lifecycle

A transaction follows four steps:

1. **BEGIN** — Start a transaction and get a transaction ID (`txId`)
2. **Execute queries** — Run read/write queries with the `txId`
3. **COMMIT** — Persist all changes
4. **ROLLBACK** — Discard all changes (instead of COMMIT)

```
┌───────┐
│ BEGIN │──→ returns txId
└───┬───┘
    │
    ▼
┌──────────┐
│ Query 1  │── uses txId
└──────────┘
    │
    ▼
┌──────────┐
│ Query 2  │── uses txId
└──────────┘
    │
    ├──────────────┐
    ▼              ▼
┌────────┐   ┌──────────┐
│ COMMIT │   │ ROLLBACK │── uses txId
└────────┘   └──────────┘
```

## Starting a Transaction

Send a `BEGIN TRANSACTION` query. The response includes a `txId` that you use in subsequent queries.

```bash
curl -X POST http://localhost:9876/rpc/Database/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer my-token" \
  -d '{"queries": [{"query": "BEGIN TRANSACTION"}]}'
```

Response:

```json
{
  "ok": true,
  "output": {
    "time": 0.002,
    "results": [
      {
        "type": "begin",
        "time": 0.002,
        "txId": "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
      }
    ]
  }
}
```

The `txId` is a UUID that identifies your transaction session.

## Executing Queries Within a Transaction

Pass the `txId` in each query you want to execute inside the transaction.

```bash
curl -X POST http://localhost:9876/rpc/Database/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer my-token" \
  -d '{
    "queries": [
      {
        "query": "INSERT INTO users(name) VALUES(?)",
        "params": [{"value": {"text": "fiona"}}],
        "txId": "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
      },
      {
        "query": "UPDATE accounts SET balance = balance - 100 WHERE user_id = 1",
        "txId": "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
      }
    ]
  }'
```

All queries in a transaction execute on the **single read-write connection** and are serialized.

## Committing

```bash
curl -X POST http://localhost:9876/rpc/Database/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer my-token" \
  -d '{"queries": [{"query": "COMMIT", "txId": "a1b2c3d4-e5f6-7890-abcd-ef1234567890"}]}'
```

Response:

```json
{
  "ok": true,
  "output": {
    "time": 0.003,
    "results": [
      {
        "type": "commit",
        "time": 0.003
      }
    ]
  }
}
```

## Rolling Back

```bash
curl -X POST http://localhost:9876/rpc/Database/query \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer my-token" \
  -d '{"queries": [{"query": "ROLLBACK", "txId": "a1b2c3d4-e5f6-7890-abcd-ef1234567890"}]}'
```

## Idle Timeout

If a transaction has no activity within the configured idle window, it is automatically rolled back by the server.

| Variable                  | Default | Description                            |
| ------------------------- | ------- | -------------------------------------- |
| `NSQLITE_TX_IDLE_TIMEOUT` | `10s`   | Maximum idle time before auto-rollback |

Set this to a longer duration for long-running transactional workflows:

```bash
NSQLITE_TX_IDLE_TIMEOUT=60s
```

Valid time units: `ns`, `us`, `µs`, `ms`, `s`, `m`, `h`.

When a transaction times out, subsequent queries with the old `txId` receive:

```json
{
  "ok": true,
  "output": {
    "results": [
      {
        "type": "error",
        "time": 0.001,
        "error": "transaction not found or timed out, check your settings"
      }
    ]
  }
}
```

## Concurrency Model

NSQLite manages concurrency through two locks:

| Lock      | Scope       | Purpose                                     |
| --------- | ----------- | ------------------------------------------- |
| `txMu`    | Transaction | Ensures only one transaction runs at a time |
| `writeMu` | Write query | Ensures write queries are serialized        |

- **Only one transaction** can be active at any time. Other clients that attempt `BEGIN` will queue until the current transaction finishes (COMMIT or ROLLBACK) or times out.
- Read queries outside a transaction can run in parallel across multiple read-only connections.
- While a transaction is open, all queries (including reads) within it go through the single read-write connection.

## Error Messages

| Error                                                            | Meaning                                                |
| ---------------------------------------------------------------- | ------------------------------------------------------ |
| `transaction not found or timed out, check your settings`        | The `txId` is invalid, already completed, or timed out |
| `transaction ID does not match the currently active transaction` | The `txId` does not match the open transaction         |
| `cannot start a transaction within a transaction`                | Called `BEGIN` while a transaction is already open     |
| `transaction ID is required for this operation`                  | Used COMMIT, ROLLBACK, or END without a `txId`         |

## Best Practices

- **Always use a `txId`** with COMMIT and ROLLBACK queries. The server needs it to identify which transaction to finalize.
- **Keep transactions short.** Idle transactions block other writers.
- **Always COMMIT or ROLLBACK.** Unclosed transactions hold resources and block other clients until the idle timeout fires.
- **Include the `txId` on every query** inside the transaction. Read queries without a `txId` execute outside the transaction on the read pool and if they are write operations, they wait until the active transaction finishes.

---

Source: https://nsqlite.varavel.com/docs/guides/cors/
Markdown: https://nsqlite.varavel.com/docs/guides/cors/index.md
Content file: content/docs/guides/cors.md

# CORS & Browser Access

> NSQLite is built for server-to-server communication. The HTTP API expects other servers as client...

Canonical HTML: https://nsqlite.varavel.com/docs/guides/cors/
Markdown: https://nsqlite.varavel.com/docs/guides/cors/index.md

NSQLite is built for server-to-server communication. The HTTP API expects other servers as clients, not browsers.

If you ever need to hit it from a browser (e.g. from a web-based tool) you'll run into CORS. That's normal, and there's a simple fix.

Only do this if you have a real need and a good reason. Ideally, NSQLite shouldn't be accessible from the outside at all, keep it behind a VPN or restricted to your internal network. If you're exposing it, take security seriously.

## Why a Reverse Proxy?

Browsers block cross-origin requests unless the server explicitly allows them via CORS headers. NSQLite doesn't ship with CORS support:

- **It's out of scope.** NSQLite's job is to give you access to your database. CORS is a concern for the layer that exposes services to the web, and specialized tools handle it better.
- **Server clients don't need it.** Backend services calling NSQLite don't enforce CORS, so shipping those headers would be dead weight.
- **You should opt in deliberately.** Browser access to your database shouldn't happen by accident.

The idiomatic way to handle this is a reverse proxy. It's the right layer for CORS, and since NSQLite is Docker-first, adding one takes two minutes.

Below is an example with Caddy because it's the simplest option with a simple config and automatic TLS (very important). You can achieve the same with Nginx or any other reverse proxy.

Create a `Caddyfile`:

```
nsqlite.example.com {
	@preflight method OPTIONS

	header {
		Access-Control-Allow-Origin "*"
		Access-Control-Allow-Methods "GET, POST, PUT, PATCH, DELETE, OPTIONS"
		Access-Control-Allow-Headers "Authorization, Content-Type, Accept, Origin, X-Requested-With"
		Access-Control-Allow-Credentials "true"
		Vary "Origin"
	}

	respond @preflight 204

	reverse_proxy nsqlite:9876
}
```

Tie it together in `docker-compose.yml`:

```yaml
services:
  nsqlite:
    image: varavel/nsqlite:latest
    environment:
      NSQLITE_AUTH_TOKEN: ${NSQLITE_AUTH_TOKEN}
    volumes:
      - nsqlite_data:/data
    networks:
      - internal
    restart: unless-stopped

  caddy:
    image: caddy:alpine
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - ./Caddyfile:/etc/caddy/Caddyfile
      - caddy_data:/data
    networks:
      - internal
    restart: unless-stopped

volumes:
  nsqlite_data:
  caddy_data:

networks:
  internal:
```

## Security Checklist

| Thing                | Why                                                                   |
| -------------------- | --------------------------------------------------------------------- |
| Pin your origin      | Replace `*` with your actual domain. Don't skip this.                 |
| Use auth tokens      | Set `NSQLITE_AUTH_TOKEN` (or `_RW` / `_RO`) on the NSQLite container. |
| Terminate TLS        | Let the reverse proxy handle HTTPS. Caddy does this out of the box.   |
| Keep NSQLite private | Only the proxy should be publicly reachable, not NSQLite itself.      |

That's it. A reverse proxy + CORS headers + auth tokens and you're good to go.

---

Source: https://nsqlite.varavel.com/docs/guides/rqlite-compat/
Markdown: https://nsqlite.varavel.com/docs/guides/rqlite-compat/index.md
Content file: content/docs/guides/rqlite-compat.md

# RQLite Compatibility

> NSQLite provides an additive compatibility layer for clients that use the rqlite HTTP API format....

Canonical HTML: https://nsqlite.varavel.com/docs/guides/rqlite-compat/
Markdown: https://nsqlite.varavel.com/docs/guides/rqlite-compat/index.md

NSQLite provides an additive compatibility layer for clients that use the [rqlite HTTP API](https://rqlite.io/docs/api/api/) format. This layer translates rqlite-style requests into NSQLite's native query execution path and translates responses back to the rqlite JSON shape.

The native NSQLite RPC API remains unchanged and is the preferred way to use NSQLite.

## Routes

| Method | Route         | Purpose                                      |
| ------ | ------------- | -------------------------------------------- |
| `GET`  | `/db/query`   | Execute read queries from the `q` parameter. |
| `POST` | `/db/query`   | Execute read queries from the request body.  |
| `POST` | `/db/execute` | Execute write statements.                    |
| `POST` | `/db/request` | Execute mixed read/write statements.         |

## Authentication

The compatibility routes support NSQLite Bearer tokens and rqlite-style Basic auth.

When Basic auth is used, NSQLite ignores the username and treats the password as the NSQLite token. For example, `ignored-user:rw-token` authenticates with `rw-token`.

## Request Formats

JSON statement arrays are supported:

```json
[
  "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)",
  ["INSERT INTO users(name) VALUES(?)", "fiona"]
]
```

Named parameters are supported:

```json
[
  ["INSERT INTO users(name) VALUES(:name)", { "name": "fiona" }]
]
```

Plain-text single statements are supported for `POST` requests with `Content-Type: text/plain`:

```sql
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)
```

## Query Parameters

| Parameter     | Behavior                                                                      |
| ------------- | ----------------------------------------------------------------------------- |
| `q`           | SQL query for `GET /db/query`.                                                |
| `transaction` | Executes all statements in one transaction and rolls back on the first error. |
| `timings`     | Includes per-result and total response timings.                               |
| `associative` | Returns read rows as objects keyed by column name.                            |
| `blob_array`  | Returns BLOB values as byte arrays instead of base64 strings.                 |

Other rqlite cluster-specific parameters, such as `redirect`, `retries`, `raft_index`, and read-consistency controls, are accepted as no-ops because NSQLite is not a Raft cluster.

## Response Shape

Responses follow the rqlite `results` shape:

```json
{
  "results": [
    {
      "last_insert_id": 1,
      "rows_affected": 1
    },
    {
      "columns": ["id", "name"],
      "types": ["integer", "text"],
      "values": [[1, "fiona"]]
    }
  ]
}
```

Database-level errors are returned inside individual result objects using the rqlite `error` key while preserving HTTP `200`, matching rqlite's documented behavior.