Reference
This page is the short version. The full reference lives with the code: SQL, server and shell and file format, plus the README, which is the manual.
Engine API
| Call | What it does |
|---|---|
Db::open(path) |
Open or create a database file. A second process gets Error::Locked |
db.get(ks, key), db.scan(ks, from, to, limit) |
Read by key, or a key range in order, on a snapshot |
db.put(ks, key, value), db.delete(ks, key) |
One change, committed on its own |
db.begin_write() |
A write transaction: get, scan, put, delete, drop_keyspace, execute, put_row, delete_row, then commit |
db.begin_read() |
A snapshot that sees nothing committed after it began: get, scan, query, get_row |
db.execute(sql, params) |
SQL in its own transaction. Reads run on a snapshot; writes commit only if every statement succeeds |
db.get_row(table, pk) |
A table row by primary key, straight from storage, no SQL |
db.verify() |
Check every page, every row against its table and every index entry |
When commit returns, the transaction is on disk. A crash or power cut during a commit leaves the whole commit or none of it. If a commit fails part way, the handle refuses further use; reopen the file to continue from the last good commit.
Limits
- Keys are 1 to 512 bytes. Values go up to 1 GiB.
- Primary keys are INTEGER, TEXT or BLOB, one column per table. TEXT and BLOB keys are 1 to 512 bytes.
- An indexed TEXT or BLOB value and the row’s key must fit in 512 bytes together.
- An expression nests at most 250 levels, with at most 64 levels of parentheses and calls. AND and OR lists and IN lists have no such limit.
- One writer at a time. Readers never wait for it.
SQL
KvSQL runs a subset of SQL close to SQLite with STRICT tables. Inside that subset, it gives the answers SQLite gives, which the tests check on random statements.
- Statements: CREATE and DROP TABLE, CREATE and DROP INDEX, INSERT, UPDATE, DELETE, and SELECT with DISTINCT, INNER, LEFT and CROSS joins, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT with OFFSET.
- Types: INTEGER, REAL, TEXT, BLOB and ANY. Values are converted only when nothing is lost.
- Expressions: arithmetic,
||, comparisons, IS and IS NOT, IN, BETWEEN, LIKE, CASE and CAST. - Functions: length, lower, upper, abs, coalesce, ifnull, nullif, substr, typeof, round, min and max, hex, instr, replace, trim, ltrim and rtrim. Aggregates count, sum, total, avg, min, max and group_concat, each with DISTINCT.
- Comparisons between text and numbers follow SQLite’s affinity rules, so
id = '7'finds row 7 in an INTEGER column. - Not supported yet: subqueries, UNION, multi-column indexes and primary keys, ALTER TABLE, DEFAULT values, UNIQUE and CHECK constraints, and prepared statements (parsed SQL is cached by its text instead).
A table is a keyspace with the same name, and every row sits under its primary key. A keyspace with no table definition reads in SQL as a table with two BLOB columns, key and value.
HTTP API of kvsqld
| Method and path | What it does |
|---|---|
GET /health |
Status, version and the last commit id |
GET /verify |
Run verify on the whole file |
GET /kv |
List keyspaces |
GET /kv/{ks}?from=&to=&limit= |
Keys in order |
GET, PUT, DELETE /kv/{ks}/{key} |
One key, raw bytes in and out |
GET /rows/{table}/{pk} |
A row by primary key, no SQL |
PUT /rows/{table} |
Insert or replace a whole row, sent as a JSON array |
DELETE /rows/{table}/{pk} |
Delete a row by primary key |
POST /sql |
{"sql": "...", "params": [...]} or plain SQL text; several statements in one request succeed or fail together |
BLOB values travel as {"$blob": "<base64>"}. Errors come back as {"error": "...", "kind": "..."} with a matching status: 400 for bad SQL or input, 401 for a missing token, 404, 409 for a broken constraint, 503 when every connection slot is busy.
Shell Commands
kvsql runs SQL ending in ; and these dot commands: .keyspaces, .get KS KEY, .put KS KEY VALUE, .del KS KEY, .scan KS [FROM] [LIMIT], .row TABLE PK, .verify, .help and .quit. It talks to a server with --url, or opens a file with --file.