ngx_http_sqlite_module

ngx_http_sqlite_module is a standalone Nginx dynamic module that turns a location into a configured SQLite-backed HTTP operation. SQL stays in Nginx config, while clients can only provide values for declared bound parameters.

Version 1.0 runs HTTP database work on Nginx thread pools, uses independent per-worker connection lanes, validates every named binding at startup, and ships bounded JSON, JSON-array, NDJSON, CSV, or status-only responses. Query deadlines, client-disconnect cancellation, row/body/response limits, atomic batches, strict database policy, typed parameters, and stable HTTP error mappings are enabled without exposing raw SQL to clients.

This folder is self-contained so it can be built with:

./configure ... --with-compat --add-dynamic-module=/path/to/nginx_sql_module
make modules

Quick Start On Linux

cd nginx-sqlite-module
sudo make install

The installer supports hosts with apk, apt-get, dnf, or yum. It builds against the installed Nginx version and installs:

/usr/lib/nginx/modules/ngx_http_sqlite_module.so
/etc/nginx/modules/50-ngx-http-sqlite-module.conf
/usr/local/share/ngx_http_sqlite_module/

Then include module-backed locations in an Nginx server block:

sqlite_db "/var/lib/ngx_http_sqlite_module/app.sqlite";
sqlite_bearer_token "change-me";
sqlite_response json;
sqlite_busy_timeout 3000ms;
sqlite_query_timeout 5000ms;
sqlite_max_rows 10000;
sqlite_connections 2;
sqlite_max_response_size 8m;

location = /api/health-row {
    sqlite;

    sqlite_operation health;
    sqlite_method GET;
    sqlite_sql "SELECT 'ok' AS status";
    sqlite_read_only on;
    sqlite_allow_anon on;
}

Configuration Examples

Declare shared defaults once at server scope, then keep each location focused on one HTTP operation:

server {
    listen 8080;

    sqlite_db "/var/lib/app/app.sqlite";
    sqlite_bearer_token "change-me";
    sqlite_response json;
    sqlite_warmup on;

    location = /api/ready {
        sqlite;

        sqlite_operation ready;
        sqlite_method GET;
        sqlite_sql "SELECT 'ready' AS status";
        sqlite_read_only on;
        sqlite_allow_anon on;
    }
}

By default, workers strictly warm up two SQLite lanes per content location, execute HTTP work on the Nginx default thread pool, use a five-second query deadline, cap results at 10,000 rows, and cap successful bodies at 8m. Nginx must be built with --with-threads; sqlite_execution sync; is the explicit compatibility fallback. See MIGRATING-TO-1.0.md before upgrading a pre-1.0 configuration.

Public JSON Read

location = /api/things/detail {
    sqlite;

    sqlite_operation get_thing;
    sqlite_method GET;
    sqlite_sql "SELECT id, name, json(attrs) AS attrs, updated_at AS updatedAt FROM things WHERE id = :id";
    sqlite_param id arg:id required raw;
    sqlite_read_only on;
    sqlite_allow_anon on;
    sqlite_json_value attrs;
    sqlite_empty_status 404;
}

Example request:

curl 'http://localhost:8080/api/things/detail?id=thing-1'

JSON Write With Bearer Auth

location = /api/things {
    sqlite;
    sqlite_max_body_size 64k;

    sqlite_operation upsert_thing;
    sqlite_method POST PUT PATCH;
    sqlite_sql "INSERT INTO things (id, name, attrs, updated_at) VALUES (:id, :name, json(:attrs), strftime('%s','now')) ON CONFLICT(id) DO UPDATE SET name = excluded.name, attrs = excluded.attrs, updated_at = excluded.updated_at RETURNING id, name, json(attrs) AS attrs, updated_at AS updatedAt";
    sqlite_param id json:id required raw;
    sqlite_param name json:name required raw;
    sqlite_param attrs json:attrs "default:{}" raw;
    sqlite_json_value attrs;
    sqlite_invalid_json_status 422;
}

Example request:

curl -X POST http://localhost:8080/api/things \
  -H 'Authorization: Bearer change-me' \
  -H 'Content-Type: application/json' \
  -d '{"id":"thing-1","name":"Sensor","attrs":{"room":"lab"}}'

Argon2id Credentials

Use the argon2id transform to store password or API-key hashes, then verify submitted secrets with sqlite_argon2id_verify:

location = /api/login {
    sqlite;

    sqlite_operation login;
    sqlite_method POST;
    sqlite_sql "SELECT username FROM users WHERE username = :username AND sqlite_argon2id_verify(password_hash, :password) = 1";
    sqlite_param username json:username required raw;
    sqlite_param password json:password required raw;
    sqlite_empty_status 401;
}

Because Argon2id uses a fresh salt, do not look up credentials by rehashing the submitted secret. For API keys, store a public key id or prefix separately and use it to select candidate rows before calling sqlite_argon2id_verify.

Multi-Statement Batch

Use sqlite_batch when a route needs multiple configured statements, such as a transaction that writes and then returns the changed row:

location = /api/things/batch {
    sqlite;

    sqlite_operation upsert_thing_batch;
    sqlite_method POST;
    sqlite_batch "
        BEGIN;
        INSERT INTO things (id, name, attrs, updated_at)
        VALUES (:id, :name, json(:attrs), strftime('%s','now'))
        ON CONFLICT(id) DO UPDATE SET
            name = excluded.name,
            attrs = excluded.attrs,
            updated_at = excluded.updated_at;
        SELECT id, name, json(attrs) AS attrs, updated_at AS updatedAt
        FROM things
        WHERE id = :id;
        COMMIT;
    ";
    sqlite_param id json:id required raw;
    sqlite_param name json:name required raw;
    sqlite_param attrs json:attrs "default:{}" raw;
    sqlite_json_value attrs;
}

Configured batches are atomic by default: sqlite_batch_transaction auto; adds BEGIN IMMEDIATE, COMMIT, and rollback handling when the SQL does not already contain explicit transaction control.

Status-Only Delete

location = /api/things {
    sqlite;

    sqlite_operation delete_thing;
    sqlite_method DELETE;
    sqlite_sql "DELETE FROM things WHERE id = :id";
    sqlite_param id arg:id required raw;
    sqlite_require_changes on;
    sqlite_response status_only;
    sqlite_success_status 204;
    sqlite_empty_status 404;
}

Docker Compose Test Harness

The Compose system builds the dynamic module and third-party hook sample, applies every bundled migration, creates writable/create-mode/read-only example databases, provisions a file-backed demo token, and serves the complete feature reference on port 8080. The interactive API reference is at http://localhost:8080/ and the OpenAPI document is at http://localhost:8080/openapi.json.

Build the image, start it, and exercise the full feature/error matrix with one command:

cd nginx-sqlite-module
make docker-test

This leaves the healthy service running for exploration. Stop it with make docker-down, or remove its persisted example data with docker compose down -v.

The Dockerfile defaults to nginx:alpine, bootstraps make, and runs make install KEEP_BUILD_DEPS=true NO_RELOAD=true inside the image so the example uses the same installer path as a host install before compiling the hook sample. Pass --build-arg NGINX_IMAGE=nginx:stable-alpine to test another compatible Nginx Alpine image, or --build-arg NGINX_PACKAGE_SOURCE=alpine to force Alpine's native Nginx package.

To start and smoke-test separately:

make docker-up
make docker-smoke

See examples/reference/FEATURES.md for the directive, transform, policy, output, execution, and error-to-endpoint coverage map. The inline demo token is change-me; the file-backed demo token is file-secret. Both are example-only values and must be replaced in a deployment.

Benchmark one example response type:

scripts/bench-examples.sh --type json --requests 200
scripts/bench-examples.sh --type json_rows --requests 200
scripts/bench-examples.sh --type ndjson --requests 200
scripts/bench-examples.sh --type csv --requests 200
scripts/bench-examples.sh --type status --requests 200

For concurrent capacity, latency-percentile, real-write, and mixed-contention tests-including execution from a separate remote server-use the portable benchmark container:

make docker-benchmark

See BENCHMARKS.md for remote Docker commands, workload safety, configuration, and JSON reports.

Or run individual requests:

curl http://localhost:8080/examples/bootstrap
curl -X POST http://localhost:8080/examples/devices/json \
  -H 'Authorization: Bearer change-me' \
  -H 'Content-Type: application/json' \
  -d '{"id":"device-1","name":"Sensor","attrs":{"room":"lab"}}'
curl 'http://localhost:8080/examples/devices/detail?id=device-1'
curl 'http://localhost:8080/examples/devices/list?limit=10&offset=0'
curl 'http://localhost:8080/examples/devices/list.ndjson?limit=10&offset=0'
curl 'http://localhost:8080/examples/devices/list.csv?limit=10&offset=0'
curl -X POST http://localhost:8080/examples/projects/flags \
  -H 'Authorization: Bearer change-me' \
  -H 'Content-Type: application/json' \
  -d '{"projectId":"project-1","key":"checkout_v2","enabled":true,"rollout":100,"rules":{"plans":["pro"]}}'
curl 'http://localhost:8080/examples/projects/flags/list?projectId=project-1'
curl 'http://localhost:8080/examples/projects/flags/evaluate?projectId=project-1&key=checkout_v2&subject=user-1'

The example SQLite files are stored in the ngx_sqlite_module_data Docker volume. Remove the test data with:

docker compose down -v

Documentation

• CHANGELOG.md: release history.
• MIGRATING-TO-1.0.md: breaking defaults and upgrade checklist.
• COMPATIBILITY.md: supported Nginx and platform policy.
• SECURITY.md: security model and vulnerability reporting.
• BENCHMARKS.md: portable Docker load tests for local and remote servers.
• INSTALL.md: Linux install and manual build instructions.
• REPLICA.md: Litestream backup, read-replica, restore, and manual failover guide for S3-compatible storage and SFTP.
• DIRECTIVES.md: full directive reference.
• HOOKS.md: C hook API and semver compatibility checks for third-party Nginx modules.
• examples/reference/example-nginx.conf: JSON, query, auth, status-only, feature flag, third-party hook, and schema examples.
• examples/reference/FEATURES.md: complete runnable feature coverage map and Docker walkthrough.
• examples/reference/www/openapi.json: OpenAPI reference for the bundled example endpoints.
• examples/reference/www/index.html: Scalar API reference for the bundled OpenAPI document.
• examples/nanomq/README.md: isolated NanoMQ HTTP authentication, user-management UI, and MQTT ACL example.
• examples/expires_at/README.md: isolated relative-TTL example with exact read filtering and cron-based physical cleanup.
• scripts/migrate-sqlite.sh: optional ordered SQL migration runner.
• scripts/bench-examples.sh: curl-based example endpoint benchmark.

Development And Releases

make lint
make check
make test-sanitize
make test-fuzz
make release-check
make dist

make check covers static configuration contracts, the hook ABI, SQLite behavior, concurrent migration safety, OpenAPI structure, release metadata, and dry-run installation. make release-check adds formatting, shell lint, sanitizers, and fuzz smoke tests. make dist creates a reproducible source archive, SPDX SBOM, and SHA-256 checksums from a clean Git revision.

Security Model

Clients cannot submit SQL. Each route executes SQL configured with sqlite_sql or sqlite_batch, and user input is bound through explicit sqlite_param declarations. The default SQLite authorizer blocks database attachment, PRAGMAs, extension loading, and file helper functions. Prefer sqlite_bearer_token_file for deployed secrets, use sqlite_allow_anon on; only for intentional public routes, avoid secrets in URLs, and use Argon2id for stored API keys, access tokens, and passwords.