sqlite-server

A small, multi-threaded TCP server that exposes SQLite databases over a length-prefixed JSON protocol. Clients open a socket, send a JSON request, and receive a JSON response. The protocol is simple enough to talk to from any language.

• Transport: raw TCP, one 4-byte length header + UTF-8 JSON body per message.
• Commands: QUERY, LIST, DELETE_DB.
• Concurrency: a configurable pool of worker threads on a shared Boost.Asio io_context.
• Storage: one SQLite database file per name, kept in a configured folder.

A Rust port is available: sqlite-server-rs. The two are drop-in interchangeable — same wire protocol, same config file format (identical JSON keys), and the same on-disk SQLite database files. You can switch a deployment from this server to the Rust one (or back) with no migration: point either binary at the same databases folder (and the same config) and it just works. Existing databases and clients keep working unchanged.

---

Building

The build uses CMake with FetchContent, so Boost, nlohmann/json and fmt are downloaded and built automatically — you only need a C++17 compiler, CMake ≥ 3.11, Git, and a build tool (Ninja or Make).

cmake -S . -B build           # configure (fetches Boost/json/fmt on first run)
cmake --build build           # compile -> build/sqlite3-server

Bundled/declared dependencies:

Dependency	Version	Purpose
Boost	1.91	asio, system, regex, filesystem, thread, program_options
nlohmann/json	3.12	JSON parsing / serialization
fmt	12.1	logging / formatting
SQLite3	bundled (sqlite3/)	database engine

SQLite is compiled with SQLITE_THREADSAFE=1 (plus several size/feature trims — see CMakeLists.txt).

---

Running

./build/sqlite3-server --port 3333 --databases-folder ./data

Command-line options

Option	Default	Description
-h, --help	—	Show usage and exit
-v, --version	—	Show git branch + commit and exit
-c, --config <path>	—	Load settings from a JSON config file (see below)
--ip <ip>	localhost	Listen address
-p, --port <port>	3333	Listen port
-a, --auth <password>	"" (disabled)	Require clients to authenticate with this password (see Authentication)
--ip-whitelist <list>	"" (allow all)	Comma-separated IPs/CIDRs allowed to connect (see IP whitelist)
-d, --databases-folder <dir>	sqlite	Folder holding the database files (must exist)
-w, --workers <n>	CPU cores	Number of worker threads
--client-max-packet-size <bytes>	16777216 (16 MiB)	Max request size; larger requests cause the connection to be closed

Config file

When --config is given, all settings come from a JSON file and the other options are ignored:

{
  "client_max_packet_size": 16777216,
  "workers": 4,
  "listen_ip": "127.0.0.1",
  "listen_port": 3333,
  "databases_folder": "./data",
  "auth": "",
  "ip_whitelist": ["127.0.0.1", "10.0.0.0/8"]
}

The databases folder must already exist — the server will not create it (individual database files inside it are created on demand). Shutdown is graceful on SIGINT/SIGTERM.

auth and ip_whitelist are optional: omit them (or leave auth empty / ip_whitelist empty) to disable each feature. See Access control below.

Access control

The server is still intended to run inside a trusted network (there is no transport encryption — see SECURITY.md), but two optional, independent layers let you restrict who can talk to it.

Authentication

Set a password with --auth <password> (or "auth" in the config file). When set, every connection must authenticate before any command is processed:

1. As the first message on the connection, send { "auth": "<password>" }.
   • On success the server replies { "result": "ok" } and marks that connection as authenticated.
   • On a wrong/missing password it replies { "result": "error" }.
2. Any command (QUERY, LIST, DELETE_DB) sent before authenticating is rejected with { "result": "error" }.

Notes:

• Authentication is per-connection — each new socket must authenticate again.
• The auth message is handled on its own; send it first, read the ok, then send commands. (A combined { "auth": "...", "cmd": "..." } only performs the authentication.)
• Leaving auth empty disables the check entirely (the default).
• The password is sent in clear text over the connection, so this only protects against unauthenticated access on a network you already trust — it is not a substitute for TLS.

IP whitelist

Restrict which peers may connect at all with --ip-whitelist (comma-separated) or the "ip_whitelist" JSON array. Each entry is either a CIDR range or a bare address:

• CIDR, e.g. 10.0.0.0/8, 192.168.1.0/24, 2001:db8::/32.
• A bare address, e.g. 127.0.0.1 (treated as /32) or ::1 (treated as /128).

Both IPv4 and IPv6 are supported. A connection from an address that is not in the list is dropped at accept time — the socket is closed immediately and no request is read. An empty list disables the check (all peers allowed). An invalid entry is a fatal config error and the server refuses to start.

This is an application-level allow-list, not a firewall: a rejected client still completes the TCP handshake before the connection is closed, and the peer address is the direct connecting address (put no NAT/proxy in front, or whitelist the proxy). For hard network filtering, pair it with a real firewall.

Running as a service (systemd)

The server runs in the foreground and logs to stdout, and it shuts down cleanly on SIGTERM (the default signal systemd sends), so it works well as a Type=simple service.

1. Install the binary and create a dedicated user + data directory:

sudo install -m 0755 build/sqlite3-server /usr/local/bin/sqlite3-server
sudo useradd --system --no-create-home --shell /usr/sbin/nologin sqlite-server
sudo mkdir -p /var/lib/sqlite-server
sudo chown sqlite-server:sqlite-server /var/lib/sqlite-server

2. Create /etc/systemd/system/sqlite-server.service:

[Unit]
Description=sqlite-server (SQLite over TCP/JSON)
After=network.target

[Service]
Type=simple
ExecStart=/usr/local/bin/sqlite3-server --ip 127.0.0.1 --port 3333 --databases-folder /var/lib/sqlite-server
User=sqlite-server
Group=sqlite-server
Restart=on-failure
RestartSec=2

# Hardening — even with the optional password/IP whitelist there is no TLS, so keep
# it bound to localhost (or a trusted interface) and lock the process down.
NoNewPrivileges=true
ProtectSystem=strict
ProtectHome=true
PrivateTmp=true
PrivateDevices=true
ProtectControlGroups=true
ProtectKernelModules=true
ProtectKernelTunables=true
RestrictAddressFamilies=AF_INET AF_INET6
ReadWritePaths=/var/lib/sqlite-server

[Install]
WantedBy=multi-user.target

3. Enable and start it:

sudo systemctl daemon-reload
sudo systemctl enable --now sqlite-server

4. Check status and follow logs:

systemctl status sqlite-server
journalctl -u sqlite-server -f

To use a config file instead of flags, point ExecStart at ... --config /etc/sqlite-server/config.json and add that path to ReadOnlyPaths=.

Tip: on systemd ≥ 235 you can skip the manual useradd/mkdir by using DynamicUser=yes together with StateDirectory=sqlite-server (which creates and owns /var/lib/sqlite-server for you) and pointing --databases-folder at it.

---

Communication protocol

Framing

Every message — in both directions — is:

+---------------------------+-------------------------------+
| 4 bytes  little-endian    |  N bytes  UTF-8 JSON          |
| uint32 length = N         |  payload                      |
+---------------------------+-------------------------------+

The header is the byte length of the JSON payload that follows. Read the 4 bytes, decode the length, then read exactly that many bytes.

Connection lifecycle

A connection is persistent: after the server replies it waits for the next request on the same socket, so you can send many requests over one connection. Requests on a single connection are processed sequentially (send a request, read its full response, then send the next).

If the server was started with a password, the connection must first authenticate with a { "auth": "..." } message before any command is accepted — see Authentication.

Request format

A request is a JSON object with a cmd field (matched case-insensitively) plus command-specific fields.

Command	Required fields	Purpose
QUERY	db, query	Run a SQL statement against database db
LIST	—	List available database files
DELETE_DB	db	Delete the database file db

Tolerance: the server will also accept lightly-malformed JSON where object keys are unquoted (e.g. {cmd:"LIST"}), repairing it before parsing.

---

QUERY

{ "cmd": "QUERY", "db": "mydb", "query": "SELECT id, name FROM users WHERE id = 1" }

Success response — columns is the column list in SELECT order, data is an array of row objects:

{
  "columns": ["id", "name"],
  "data": [ { "id": 1, "name": "Alice" } ]
}

For statements that return no result set (INSERT, UPDATE, CREATE, …) both arrays are empty: { "columns": [], "data": [] }.

Value encoding

SQLite values map to JSON as follows:

SQLite type	JSON representation
INTEGER	number (64-bit)
FLOAT	number
TEXT	string
NULL	null
BLOB	string "X'<hex>'" (lowercase hex), e.g. "X'00ff'"

A client that needs the raw bytes of a BLOB decodes the X'<hex>' literal itself.

Column ordering

Each row is a JSON object, and the server serializes object keys alphabetically (not in SELECT order). Use the top-level columns array when you need the original column order; it is also present when data is empty.

Statement scope

Each QUERY compiles and runs a single SQL statement (the first one in the string). To run several statements, send them as separate requests. Wrap multi-step work in an explicit transaction by issuing BEGIN / COMMIT as their own requests on the same connection when atomicity matters.

---

LIST

{ "cmd": "LIST" }

Returns the regular files in the databases folder, excluding SQLite sidecar files (-wal, -shm, -journal):

{ "list": ["mydb", "sales", "logs"] }

---

DELETE_DB

{ "cmd": "DELETE_DB", "db": "mydb" }

Response:

{ "result": "ok" }

"result" is "ok" if the file was removed, "error" otherwise (e.g. it did not exist).

---

Database name rules

db must be a plain file name inside the configured folder. The server resolves the full path (following symlinks for the existing part) and confirms it stays directly inside the databases folder. Anything that escapes — path separators, absolute paths, ./.. traversal, or a symlink pointing outside — is rejected. A non-existent database is created on first QUERY.

---

Error responses

Command/validation errors carry a numeric generic_error code and echo the request:

{ "generic_error": 3, "request": { "cmd": "QUERY", "db": "../etc/passwd", "query": "..." } }

Code	Name	Meaning
0	INVALID_FORMAT	Request body was not valid JSON (also includes a message)
1	NO_COMMAND_SPECIFIED	Missing cmd
2	UNKNOWN_COMMAND	cmd is not one of the supported commands
3	NO_DATABASE_SPECIFIED	Missing db, or db is not a safe/valid name
4	ERROR_READING_FROM_CLIENT	Missing query on a QUERY request

SQL errors (raised while preparing/running a QUERY) carry the SQLite error code, its message, and the original request:

{ "error_code": 1, "error_message": "no such table: ghosts",
  "query": { "cmd": "QUERY", "db": "mydb", "query": "SELECT * FROM ghosts" } }

An empty/whitespace/comment-only query is reported as error_code 21 (SQLITE_MISUSE) with message "empty query".

---

Python client

Reference library

A ready-to-use client lives at examples/python/sqlite.py. It is a single, dependency-free module (standard library only) that handles framing, parameter binding with client-side escaping, and a typed result wrapper. Copy it into your project and use it directly:

from sqlite import Sqlite

# Defaults to 127.0.0.1:3333 with no auth; override per connection:
#   Sqlite("mydb", ip="10.0.0.5", port=3333, auth="my-password")
with Sqlite("mydb") as db:
    db.send_query("CREATE TABLE IF NOT EXISTS users(id INTEGER, name TEXT)")
    db.send_query("INSERT INTO users VALUES(?, ?)", [1, "Alice"])   # ? params are escaped

    result = db.query("SELECT id, name FROM users WHERE id = ?", [1])
    for row in result:
        print(row.id, row.name)          # rows support both row["id"] and row.id

    n = db.query("SELECT COUNT(*) AS n FROM users").scalar()   # first column of first row -> 1

If the server was started with --auth, pass the matching auth= (or set the module's _SQLITE_AUTH default). The client sends the { "auth": "..." } handshake on connect; see Authentication.

Highlights of the wrapper:

• Parameter binding — ? and ?N placeholders are escaped client-side (SELECT … WHERE id = ?, [1]); bytes values become X'..' BLOB literals.
• QueryResult — iterable/sized/truthy; .first(), .scalar(), .column(name), .rows, .columns (true SELECT order). Never None.
• Row — a dict subclass with attribute access (row.name) and row.blob("col") to decode a BLOB column back to bytes.

Minimal raw protocol

If you would rather speak the protocol directly, the framing is just a 4-byte length plus JSON:

import json, socket, struct

def call(host, port, request):
    payload = json.dumps(request).encode("utf-8")
    with socket.create_connection((host, port)) as sock:
        sock.sendall(struct.pack("<I", len(payload)) + payload)   # 4-byte LE length + body

        def recv_exactly(n):
            buf = b""
            while len(buf) < n:
                chunk = sock.recv(n - len(buf))
                if not chunk:
                    raise ConnectionError("connection closed")
                buf += chunk
            return buf

        size = struct.unpack("<I", recv_exactly(4))[0]
        return json.loads(recv_exactly(size).decode("utf-8"))

print(call("127.0.0.1", 3333, {"cmd": "LIST"}))
print(call("127.0.0.1", 3333,
           {"cmd": "QUERY", "db": "mydb", "query": "SELECT 1 AS one"}))

---

GUI client (letos / SQLiteStudio)

letos — the free, multi-platform SQLite database manager by Pawel Salawa (formerly SQLiteStudio) — can browse and query databases served by this server, including from Android. It speaks this server's protocol natively (the request parser even tolerates SQLiteStudio's {cmd:"LIST"} quirk — see parse_request in RequestHandler.cpp).

To connect:

1. Add a database and set Database type to Android SQLite.
2. Open the Android database URL editor and choose Connection method → Network (IP address).
3. Enter the server's IP address and Port — the port you started the server with (e.g. 3333). The plugin defaults to 12121, so change it to match.
4. Remote access password: if you started the server without --auth, leave this unchecked. If you set an auth password, tick Remote access password and enter the same value — the GUI authenticates using this server's { "auth": "..." } handshake (see Authentication). Either way there is no TLS, so keep the server on a trusted network.
5. Under Database, add the database name (a file in the server's databases folder, as returned by LIST).
6. Give it a Name, optionally tick Permanent, click Test connection, then OK.

1. Pick the database type	2. Configure the network connection

---

Project layout

File	Responsibility
main.cpp	CLI/config parsing, starts the network worker
Config.{h,cpp}	Singleton holding runtime configuration
Network.h	NetworkWorker: io_context, thread pool, signal handling
ListenSocket.h	Accepts incoming connections
Socket.{h,cpp}, SQLiteSocket.{h,cpp}	Per-connection read/write + framing
RequestHandler.{h,cpp}	Parses requests, dispatches commands, builds responses
Response.{h,cpp}, IResponse.h	Serializes a JSON response with its length header
sqlite3_wrapper/	RAII wrappers: SQLDatabase, SQLStatement, SQLException
sqlite3/	Bundled SQLite amalgamation
Logger.h	Timestamped console logging (debug logs only in debug builds)
examples/python/	Reference Python client (sqlite.py)

---

License

Released under the MIT License. The bundled SQLite amalgamation in sqlite3/ is public domain.