REST API
Data API
Run one or more SQL statements on an existing SQLite database. Each request is one transaction.
Authentication
The base URL is https://z8data.com. Use your own service URL for another deployment. Send Authorization: Bearer <db-key> on every query.
The token is case-sensitive; the Bearer scheme is not. Use one space before the token, with no extra text, whitespace, commas, or second key.
There are three shared service keys and no keys per user or database. The server reads them from process environment variables. See key settings and rotation for .env setup. Keep keys in private client configuration, not in public browser code.
Authentication runs before JSON parsing or database access. Missing or invalid keys return 401. A valid key with the wrong API role returns 403. Both DB keys pass route authentication; SQLite then checks whether each SQL statement is allowed.
Create the database file first with the Admin API. DB keys cannot create or delete database files. A query for a missing database returns 404 and creates no file. Old dataset and user keys are not accepted.
Database identity
1{"dir":"prod","filename":"app.db"}The pair identifies one database. There is no generated ID or per-database key. Both strings must contain valid Unicode. Names are not trimmed or repaired. File name case follows the server filesystem.
Use / between folder names. Absolute paths, leading or trailing slashes, empty path parts, ., .., backslashes, colons, and NUL characters are rejected. A filename cannot contain a slash. Filenames ending in -wal, -shm, or -journal are reserved, with any letter case. This suffix rule does not apply to folder names.
Paths must stay inside the database root. Symbolic links in database paths and hard links to database or sidecar files are rejected. Text such as ?mode=ro inside a filename stays filename text; it is not a SQLite connection option.
Send the identity as JSON fields in the query body. Do not URL-encode these JSON strings. See storage rules for the database root and file ownership.
/api/db/v1/queryRun a batch in order. Use the same body for one statement or many. Success returns 200 after commit.
Send Content-Type: application/json and no URL query parameters. The top-level object has exactly dir, filename, and statements.
Unknown fields in request or statement objects are rejected. Use positional ? placeholders only. Named placeholders such as :name, @name, and $name, and numbered placeholders such as ?1, are rejected. The number of values must match the placeholders. Placeholder-like text inside quoted strings or comments is not a binding.
Parameters bind values only. They cannot stand for table names, column names, SQL keywords, or a list of values. For IN (?, ?, ?), send three separate values. Choose any dynamic identifiers from an allowed list in your client code.
The handler checks the top-level object first, then the database identity, then statement input in array order. A body that is not an object, does not have three fields, or has no statements field returns 400 INVALID_REQUEST. Once that shape check passes, missing or invalid dir or filename values return 400 INVALID_DATABASE_PATH. A statements value that is not a nonempty array returns 400 INVALID_REQUEST. These errors have a null statementIndex. An invalid item in the statements array returns 400 INVALID_REQUEST with that item's zero-based index.
SQL and values stay separate. Never join user values into SQL. Empty SQL, comment-only SQL, SQL containing NUL, and multiple statements in one item are invalid. A final semicolon and trailing comments are allowed. SQL is not trimmed, rewritten, truncated, or split on semicolons. Semicolons inside quoted text and trigger bodies are kept.
The service prepares or reuses each SQL statement when the batch reaches it. SQL syntax, access rules, and the single-statement check can fail after earlier statements have run; the service then rolls back the batch. Input validation does not prepare all SQL in advance. This lets a later statement use a table created earlier in the same batch.
This example needs an empty database. It creates a table, inserts a row, and reads that row in one request:
1curl -X POST 'https://z8data.com/api/db/v1/query' \
2 -H 'Authorization: Bearer <db-read-write-key>' \
3 -H 'Content-Type: application/json' \
4 -d '{"dir":"prod","filename":"app.db","statements":[
5 {"sql":"CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE)"},
6 {"sql":"INSERT INTO users (name) VALUES (?) RETURNING id, name","params":["Alice"]},
7 {"sql":"SELECT id, name FROM users ORDER BY id"}
8 ]}'1{"results":[
2 {"columns":[],"rows":[],"changes":0},
3 {"columns":["id","name"],"rows":[[1,"Alice"]],"changes":1},
4 {"columns":["id","name"],"rows":[[1,"Alice"]]}
5]}A read-only request uses the same shape and the read-only key:
1curl -X POST 'https://z8data.com/api/db/v1/query' \
2 -H 'Authorization: Bearer <db-read-only-key>' \
3 -H 'Content-Type: application/json' \
4 -d '{"dir":"prod","filename":"app.db","statements":[{"sql":"SELECT id, name FROM users WHERE id = ?","params":[1]}]}'Parameter and result values
Integer text uses decimal digits with an optional minus sign. Spaces, a plus sign, exponents, leading zeros, and -0 are invalid. 0 is valid. Tagged integers within the safe number range are accepted too. BLOBs must use the standard alphabet, not URL-safe base64; whitespace, missing required padding, and noncanonical encodings are rejected.
Tags have exactly the two fields shown above. Extra tag fields, malformed tags, booleans, arrays, other objects, non-finite numbers, and integers outside the allowed range are rejected. Use 0 or 1 for booleans. Parameters are validated before the batch starts.
1{"dir":"prod","filename":"app.db","statements":[{
2 "sql":"SELECT ? AS large_integer, ? AS bytes, ? AS empty_bytes, ? AS missing",
3 "params":[{"type":"integer","value":"9223372036854775807"},{"type":"blob","base64":"AQID"},{"type":"blob","base64":""},null]
4}]}1{"results":[{
2 "columns":["large_integer","bytes","empty_bytes","missing"],
3 "rows":[[{"type":"integer","value":"9223372036854775807"},{"type":"blob","base64":"AQID"},{"type":"blob","base64":""},null]]
4}]}Results use JSON strings, finite numbers, null, and the same tags. Safe SQLite integers become JSON numbers; larger SQLite integers use the integer tag. SQLite REAL values stay JSON numbers. BLOBs always use the BLOB tag. A value that cannot be encoded returns UNSUPPORTED_RESULT_VALUE and rolls back the batch. It is never silently replaced with null.
Results and direct write counts
A read with no matching rows still has its column names and rows: []. Reads omit changes. An INSERT, UPDATE, or DELETE without RETURNING has empty columns and rows, plus its write count. With RETURNING, it has rows and a write count.
The service uses SQLite authorizer actions to decide whether to include changes. Statements marked as schema writes report zero changes. Setting application_id or user_version also reports zero changes. EXPLAIN and EXPLAIN QUERY PLAN for permitted SQL return rows and omit changes, even when explaining a write. Treat changes as optional; do not infer its presence from the first SQL keyword.
1{"results":[
2 {"columns":["name"],"rows":[]},
3 {"columns":["value","value"],"rows":[[1,2]]},
4 {"columns":[],"rows":[],"changes":2}
5]}This example shows three independent result shapes. Rows are arrays so duplicate column names cannot overwrite each other. Use RETURNING when you need inserted IDs; there is no separate last-insert-ID response field.
TypeScript JSON types
These types describe the JSON sent over HTTP. Validate values against the rules above before sending them. TypeScript alone does not check integer ranges, finite numbers, base64, or extra object fields.
1type DatabaseIdentity = { dir: string; filename: string }
2type IntegerTag = { type: "integer"; value: string }
3type BlobTag = { type: "blob"; base64: string }
4type SqlValue = null | string | number | IntegerTag | BlobTag
5
6type Statement = { sql: string; params?: SqlValue[] }
7type QueryRequest = DatabaseIdentity & {
8 statements: [Statement, ...Statement[]]
9}
10type StatementResult = {
11 columns: string[]
12 rows: SqlValue[][]
13 changes?: number | IntegerTag
14}
15type QuerySuccess = { results: StatementResult[] }
16type DatabaseFailure = {
17 error: {
18 code: string
19 message: string
20 statementIndex: number | null
21 }
22}Send large integers as decimal strings inside IntegerTag. Do not convert them to JavaScript numbers first. Native bigint values cannot be sent directly with JSON.stringify. Encode byte arrays as BlobTag. Dates have no separate API type; choose a text or numeric format for your schema.
Check the HTTP status before using QuerySuccess. A handled error uses DatabaseFailure. A proxy or framework error may be plain text or a different JSON shape. Keep rows as arrays unless you know the column names are unique.
Transactions and rollback
After authentication and input validation, the service opens the existing database or reuses a cached connection for the same key role. All statements run in order on one connection in one transaction. Later statements see earlier changes in that batch. Read/write-key batches use BEGIN IMMEDIATE, even for SELECT-only batches. Read-only-key batches use BEGIN. Concurrent read/write batches for the same database are serialized. The service does not promise an order for separate concurrent HTTP requests. Await one response before sending a request that depends on it.
Commit happens only after every statement succeeds and the full result can be encoded as JSON. A 200 response means commit succeeded. A statement, deferred constraint, result encoding, or commit failure rolls back the full batch, including schema changes. There are no partial results. Statement errors have an index; begin and commit errors have a null index.
Caller transaction commands are denied: BEGIN, COMMIT, END, ROLLBACK, SAVEPOINT, and RELEASE. A statement that cannot run inside the service transaction fails; it is never moved outside the transaction. For example, VACUUM cannot be used as a maintenance escape.
Rollback finishes before a statement error is returned. If rollback itself fails, the connection is discarded and the response is INTERNAL_ERROR. Connections and statement caches keep a fixed DB role. Separate requests may reuse a connection or use another worker. Connection state is not reset for each request. Keep temporary-table work and calls that depend on connection state, such as last_insert_rowid(), in the same batch.
One batch targets one database. There is no transaction across database files or across HTTP requests. Run schema migrations as statement arrays without BEGIN or COMMIT. You can update PRAGMA user_version in the same batch as the schema changes.
Do not retry write batches automatically. The API has no idempotency-key handling. If a connection drops before you receive a response, the batch may already have committed. Check the data before sending the write again. A busy response or queue timeout is not a partial success response.
PRAGMAs and SQL rules
Use SQLite SQL. The connection keeps Node SQLite's default that disables double-quoted string literals. Use single quotes for SQL text and double quotes for identifiers. Prefer bound parameters for values. See the Node SQLite connection defaults.
Both roles may use these read PRAGMAs, with an optional argument where SQLite supports one:
1table_info, table_xinfo, table_list,
2index_info, index_xinfo, index_list,
3foreign_key_list, foreign_key_check,
4integrity_check, quick_checkBoth roles may read these settings without an assignment:
1application_id, user_version, schema_version,
2page_count, freelist_count, page_size, encoding,
3foreign_keys, synchronous, journal_mode, query_only,
4trusted_schema, busy_timeout, cache_size, auto_vacuum,
5compile_options, pragma_list, function_list, module_listTo inspect your schema, send a permitted PRAGMA such as PRAGMA table_info(users) through this query endpoint. Admin get and list return file identities only. Names returned by pragma_list, function_list, or module_list describe the SQLite build; they do not grant permission to use blocked features.
Only the read/write key may assign application_id and user_version. These changes are part of the batch transaction. All other PRAGMAs are denied, including changes to connection settings. These examples each belong in a separate statement item:
1PRAGMA table_info(users);
2PRAGMA user_version;
3PRAGMA user_version = 2; -- read/write key onlyWAL, synchronous=FULL, foreign keys, defensive mode, and trusted_schema=OFF stay enabled as configured by the service. SQLite's busy timeout is 5,000 ms. Extension loading, ATTACH, DETACH, and the functions load_extension, readfile, writefile, and fts3_tokenizer are blocked.
Read-only connections open with mode=ro, readOnly: true, and query_only=ON. An engine authorizer also blocks data and schema writes, temporary writes, and unsafe PRAGMAs. Read/write connections use mode=rw so queries cannot create missing files. If a protection step fails, the connection closes and the operation fails.
Limits and waiting
HTTP input validation has no separate body-size, SQL-length, or statement-count cap. Accepted input must still fit the shared task budgets below before SQL can run. There is no application result-row cap, pagination, or streaming: all rows are collected in memory and returned in one JSON response. Use SQL LIMIT to reduce results. SQLite, available memory, and your network or proxy can still limit a request.
A single large task can exceed the byte budget and be rejected even when the queue is empty. Exceeding either admission budget returns 500 WORKER_QUEUE_FULL. Waiting too long returns 500 WORKER_QUEUE_TIMEOUT. Admin database work shares these budgets. Do not split a batch automatically: that would change its transaction boundary.
There is no SQL execution timeout or query-cancel endpoint. The handler does not pass a client disconnect or abort signal to the worker. Aborting a fetch does not cancel an accepted batch or guarantee rollback. The 5,000 ms SQLite busy timeout limits waits for locks, not the full query time.
HTTP, caching, and CORS
Query success and handled errors are JSON with Cache-Control: no-store. Send a JSON body; the handler parses it as JSON without checking the Content-Type header. Invalid or empty JSON returns 400 INVALID_REQUEST after authentication.
The route accepts POST. OPTIONS is a public preflight: it returns 204 with no body, does not run SQL, and needs no key. GET, HEAD, PUT, PATCH, and DELETE return the framework's 405 response before the query handler runs. Framework errors do not use the database JSON error contract or the query handler's CORS headers.
These CORS headers are included on query responses, including handled errors, and preflight responses. Cookie credentials are not enabled. Admin routes do not enable cross-origin access. Use a private server-side client when a key must stay hidden from browser users.
Errors
1{"error":{"code":"SQLITE_CONSTRAINT_UNIQUE","message":"SQLite constraint failed","statementIndex":1}}Handled DB query and Admin database errors have exactly these three fields inside error. Use code to handle an error. Messages use fixed text and omit SQL, keys, parameter values, stack traces, and server file paths. There are no partial results. Framework and proxy failures can have a different body. Check the HTTP status and response shape before reading results or error fields.
statementIndex starts at zero. It identifies the failed statement, including invalid statement input. It is null for request, auth, path, queue, database open, transaction begin or commit, and Admin errors. A rollback failure also returns a null index with INTERNAL_ERROR.
* above includes the primary code and its extended codes. A known extended code is kept, such as SQLITE_CONSTRAINT_UNIQUE. An unknown extended code falls back to its primary code. Telemetry uses the separate string error format shown on the Admin API page.
When the service authorizer denies a statement, the batch returns 403 SQLITE_AUTH with that statement's index. A failure while setting up connection protections returns 500 INTERNAL_ERROR with a null index.