API reference¶

Base URL when running locally: http://127.0.0.1:5000. The same information is available interactively at /docs and as an OpenAPI document at /openapi.json.

If the server has any key configured - SQL2API_API_KEY or a scoped key created through /api_keys - send it with every request as X-API-Key: <key> (/health, /docs, /ui, /openapi.json and /metrics are always public; /openapi.json's saved-query section still varies with who's asking). See Authentication and permissions.

Endpoint	Method	Purpose
`/execute_sql`	POST	Run ad-hoc SQL
`/q/<name>`	GET, POST	Run a saved query as an endpoint
`/execute_sql_from_file`	POST	Run a saved query by file path
`/execute_sql_with_parameters_from_file`	POST	Same as above (kept for compatibility)
`/save_sql_to_file`	PATCH	Save a query / add a version
`/list_files`	GET	List saved queries
`/saved_sql/<name>`	DELETE	Delete a saved query or one version
`/view_file_content`	GET	Raw saved-query file
`/connections`	GET, PATCH	List / add / update connections
`/connections/<name>`	DELETE	Delete a connection
`/connections/<name>/schema`	GET	List a connection's tables/views and their columns
`/api_keys`	GET, POST	List / create scoped API keys
`/api_keys/<name>`	PATCH, DELETE	Update / revoke a scoped API key
`/health`	GET	`{"status": "ok", "version": "..."}`
`/metrics`	GET	Prometheus text-format metrics

Common query parameters¶

These apply to every endpoint that returns rows.

Parameter	Default	Description
`format`	`json`	`json`, `ndjson`, `csv`, `tsv`, `xml`, `yaml` or `xlsx`. For the POST endpoints it may also be given in the JSON body.
`page`	`1`	1-based page number.
`page_size`	`10`	Rows per page, at most `SQL2API_MAX_PAGE_SIZE` (default 1000).
`timeout`	server limit	Seconds the query may run before it is cancelled with a 504. It can lower the server limit (`SQL2API_QUERY_TIMEOUT`, default 30; `0` disables it) but never raise it. For the POST endpoints it may also be given in the JSON body.

Any trailing LIMIT/OFFSET in the SQL is replaced by the requested page. Responses carry X-Page, X-Page-Size and X-Has-More (true when another page exists). A query that returns no rows answers {"message": "No results returned"}.

Execute SQL¶

POST /execute_sql?format=json&page=1&page_size=5

{
    "sql": "SELECT * FROM actor WHERE actor_id > :min AND first_name LIKE :name",
    "params": {"min": 10, "name": "A%"},
    "connection_name": "sakila-sqlite"
}

:name markers are bound parameters: values are sent to the database separately from the SQL. Markers inside string literals, comments and Postgres :: casts are ignored. Every marker needs a value in params.
Unless the server sets SQL2API_ALLOW_WRITES=1, only a single SELECT, WITH, SHOW, DESCRIBE, EXPLAIN or VALUES statement is accepted (403 otherwise; 400 for several statements).

Streaming exports¶

page_size is capped (SQL2API_MAX_PAGE_SIZE, default 1000) - there is normally no way to pull a full result larger than that in one call. ?stream=true lifts that cap: the whole result is streamed straight from the database cursor as it comes in, rather than built up in memory first, so an export far larger than fits in memory can still be downloaded. It works on both POST /execute_sql and GET/POST /q/<name>:

curl -X POST 'http://127.0.0.1:5000/execute_sql?stream=true&format=csv' \
     -H 'Content-Type: application/json' \
     -d '{"sql": "SELECT * FROM actor", "connection_name": "sakila-sqlite"}' -o actor.csv

Only format=csv, tsv or ndjson are streamable (400 for json/xml/yaml/xlsx - those formats all need the whole document structure in memory to write correctly, so paging still applies to them normally).
page/page_size are rejected together with stream=true (400) - the whole point is that there is no page.
Always read-only, regardless of SQL2API_ALLOW_WRITES or the calling key's own write permission - a large export has no business mutating data. A write statement gets the usual 403 from the SQL guard.
The response carries Content-Disposition: attachment with a filename, so a browser hitting the URL directly downloads it rather than navigating in-page.
For a saved query, the run is still appended to execution_history once the stream finishes, but cache_ttl is ignored - caching would mean building the whole body in memory first, exactly what streaming avoids.
If the connection or SQL itself is invalid, that surfaces as the usual JSON error before any data is sent. A failure partway through an already-started stream cannot change the response's status or body shape any more, though - the client just sees the download end early; check the server log for what actually happened.
How much this bounds the server's memory, not just removing the cap, varies by database - MySQL, PostgreSQL and ClickHouse stream from the server without buffering the whole result client-side first; SQLite, H2, the generic jdbc type and DuckDB still bound this project's own memory to one batch at a time, but the underlying engine or driver may materialise more than that internally - see Connection pooling for the per-dialect detail and what it means for how long a pooled connection stays checked out.
SQL2API_STREAM_MAX_ROWS, if set, caps how many rows a single export returns - unlike page_size, nothing bounds stream=true by default, since the whole point is not buffering the result to know its size up front. A malformed value is rejected at startup, the same as SQL2API_RATE_LIMIT. Past the cap, the export ends early (whatever HTTP headers and rows already went out stand; there's no way to retroactively mark an in-progress 200 as partial) and a WARNING is logged naming the connection and the limit hit. GET /metrics's sql2api_stream_exports_total counts a capped export under status="truncated", distinct from "success"; a saved query's execution_history still records it as "success" with the truncated row count - it did succeed, just not to completion.

Save a query¶

PATCH /save_sql_to_file

{
    "filename": "actor_by_id",
    "sql_query": "SELECT * FROM actor WHERE actor_id = :id",
    "query_parameters": {"id": "int"},
    "connection_name": "sakila-sqlite",
    "author": "anantha",
    "description": "Look up an actor",
    "tags": ["example"]
}

filename may contain letters, digits, spaces, ., _ and -. Saving to an existing name creates the next version.
query_parameters declares the query's parameters and their rules. The definitions are checked when you save (400 with an errors map if any is invalid), and every parameter declared must be used in sql_query.
connection_name (optional) is the default connection when a run does not name one.
cache_ttl (optional, seconds) caches a response - see Response caching.
Response: {"message": "...", "filename": "actor_by_id", "uuid": "...", "version": 2}.

Run a saved query¶

GET /q/actor_by_id?id=7&format=csv - query-string arguments other than format, page, page_size, connection_name and version become parameters.

POST /q/actor_by_id - the JSON body may contain params, connection_name, version and format:

{"params": {"id": 7}, "connection_name": "sakila-sqlite", "version": 1}

The latest version runs unless version is given.
connection_name from the request wins over the saved default.
Each run is appended to that version's execution_history (last 50 runs: time, connection, status, rows, duration, plus request_id/key_name - see Observability).

The older endpoints POST /execute_sql_from_file and POST /execute_sql_with_parameters_from_file do the same thing with the query named in the body:

{
    "filepath": "saved_sql/actor_by_id.json",
    "connection_name": "sakila-sqlite",
    "placeholders": {"id": 7},
    "format": "csv"
}

filepath may be a bare name (actor_by_id), a path relative to the data folder, or an absolute path - but it must resolve to a .json file inside saved_sql/.

Response caching¶

Opt-in, per saved query: set cache_ttl (seconds) when saving it. A cached response is only ever served for that exact name, version, connection, resolved parameter values, format and page - anything else is a separate entry. It is never used for a query whose SQL is a write (INSERT/UPDATE/DELETE/DDL), regardless of cache_ttl: caching such a query would silently skip the write on every call after the first.

A fresh response carries ETag, Cache-Control: max-age=<cache_ttl> and X-Cache: MISS. A request within the TTL gets the same body with X-Cache: HIT; send back If-None-Match: <ETag> to get 304 Not Modified with no body instead. A cache hit is not appended to execution_history - nothing ran against the database. The cache is kept in this one process's memory (see the note on /metrics' sql2api_pool_idle_connections for what that means for a multi-process deployment) and is shared across every API key that can use the connection - it stores nothing an authorized caller could not already see by running the query itself.

Parameter rules¶

query_parameters maps each parameter name to its type, or to an object of rules:

{
    "rating":     {"type": "str", "enum": ["G", "PG", "R"], "default": "PG", "description": "MPAA rating"},
    "max_length": {"type": "int", "min": 1, "max": 600, "default": 120},
    "title":      {"type": "str", "required": false, "min_length": 2, "pattern": "[A-Za-z ]+"},
    "id":         "int"
}

Rule	Applies to	Meaning
`type`	any	`int`, `float`, `str` or `bool` (aliases `integer`, `number`, `string`, `boolean`). Query-string text is converted; JSON values must already have the right type. Untyped parameters accept any single value.
`required`	any	Defaults to `true`, or to `false` when a `default` is given. An optional parameter with no value and no default is bound as `NULL`, so `(:title IS NULL OR title LIKE :title)` works.
`default`	any	Used when the request supplies nothing. It must itself satisfy the other rules. Cannot be combined with `"required": true`.
`enum`	any	The value must be one of these.
`min`, `max`	numbers	Inclusive bounds.
`min_length`, `max_length`	text	Length bounds.
`pattern`	text	A regular expression the whole value must match (at most 500 characters).
`description`	any	Shown in `/docs`.

Requests that break a rule are rejected with 400 before anything reaches the database, with every problem listed:

{
    "error": "Invalid parameters: rating must be one of: G, PG, R; max_length must be at most 600",
    "errors": {"rating": "must be one of: G, PG, R", "max_length": "must be at most 600"}
}

Parameters used in the SQL but not declared still work: they are required and passed through as supplied. Requests rejected this way are not recorded in the query's execution_history.

Text placeholders (legacy)¶

Saved SQL may also contain {name} placeholders, which are substituted as text before the query runs. Because the value becomes part of the SQL it must be a number, a boolean, or a string made only of letters, digits, whitespace and . , : @ % + / -; anything else is rejected. Prefer bound :name parameters.

List saved queries¶

GET /list_files?sort_by=name&sort_order=desc - sort_by is name (default) or modified, sort_order is asc (default) or desc. Returns every query with its versions' metadata (not the SQL text).

Delete a saved query¶

DELETE /saved_sql/actor_by_id removes the whole query; DELETE /saved_sql/actor_by_id?version=2 removes one version (the file goes with its last version).

View a saved query file¶

GET /view_file_content?filename=actor_by_id returns {"content": "<raw file text>"}. Only files in saved_sql/ can be read.

Connections¶

GET /connections lists connections; stored passwords are shown as ******** (values that are ${ENV_VAR} references are shown as written). Each entry also carries a live usage object - {"queries": ..., "errors": ..., "rows": ..., "avg_duration_ms": ...} - aggregated from the same in-process counters /metrics renders (see Observability), so the admin UI's Connections tab can show how much a connection has actually been used without a separate Prometheus query. avg_duration_ms is null until at least one query has run against it since this process started; the numbers reset on restart - they are a live view of this process, not a durable history (see a saved query's own execution_history for that).

PATCH /connections adds or replaces connections. Sending the ******** mask back for an existing connection keeps its stored password.

{
    "connections": {
        "reporting": {
            "db": "postgres",
            "host": "db.internal",
            "port": 5432,
            "database": "reports",
            "user": "readonly",
            "password": "${REPORTING_PASSWORD}",
            "active": true
        }
    }
}

DELETE /connections/reporting removes one. See DATABASE_CONNECTION_CONFIGURATION.md for the connection fields.

A ${VAR} password reference is expanded from the environment at connection time and never written to disk as plaintext. A literal password is encrypted at rest when SQL2API_SECRET_KEY is set (see Encryption at rest below); without that variable it is stored as given, in db_connections.json on disk, not just masked in API responses. The server logs a startup warning naming any connection whose password is still a literal string with no protection at all, so a deployment that hasn't adopted either convention finds out - nothing blocks it, this is a nudge, not an enforcement.

Encryption at rest for connection passwords¶

Set SQL2API_SECRET_KEY to a Fernet key (python -c "from cryptography.fernet import Fernet; print(Fernet.generate_key().decode())") and every literal connection password - existing ones immediately at startup, new ones the moment they're saved - is encrypted before it touches disk, decrypted only in memory at the instant a connection is actually opened. Needs the cryptography package (pip install "sql2api[encryption]", included in [all]); a clear error at startup names the missing package if SQL2API_SECRET_KEY is set without it. A ${VAR} reference is untouched either way - it was never a secret stored in the file to begin with.

An encrypted password is masked the same as a literal one in GET /connections and the audit log (********) - the stored ciphertext itself is never returned to a client. Losing or rotating SQL2API_SECRET_KEY fails clearly rather than quietly: a connection whose password can't be decrypted returns a 500 naming the problem, and the server logs a startup warning if encrypted passwords exist on disk but no key is configured to read them - the same "fail closed, say why" precedent expires_at and rate_limit already follow for a key's own malformed data. There is no way to recover an encrypted password without the key that encrypted it; keep SQL2API_SECRET_KEY itself somewhere safe, outside db_connections.json and outside version control, the same way you would any other credential.

GET /connections/reporting/schema lists its tables and views for self-service query writing:

{
    "tables": [
        {"name": "orders", "type": "table", "columns": [
            {"name": "id", "type": "integer", "nullable": false, "position": 1},
            {"name": "customer_id", "type": "integer", "nullable": false, "position": 2}
        ]}
    ],
    "truncated": false
}

truncated is true only if the connection has more than 5000 columns across all its tables and views combined, in which case the list was cut off.

Authentication and permissions¶

SQL2API_API_KEY, if set, is a full-access admin key - unrestricted, exactly as before this section existed. Scoped keys are additive, managed through /api_keys (admin only), and can only run queries: a list of connection names they may use (or every connection), and whether they may write at all. A scoped key can never do more than the server-wide settings already allow - allow_writes on a key can only narrow SQL2API_ALLOW_WRITES, never widen it - and can never manage connections, saved queries or other API keys; only the admin key can. Creating your first scoped key turns on authentication for the whole server immediately, even without SQL2API_API_KEY set - and since only the admin key can manage the server, doing that without also setting SQL2API_API_KEY locks configuration changes out until you do (the server logs a warning at startup in that state).

A key's secret is never stored - only its SHA-256 hash, in api_keys.json (SQL2API_HOME). It is generated by the server and returned exactly once, when the key is created; there is no way to recover it afterwards, only to revoke it (DELETE /api_keys/<name>) and create a new one.

POST /api_keys creates a key:

{"name": "reporting", "connections": ["reporting-db"], "allow_writes": false}

{"name": "reporting", "key": "sk_...", "message": "Store this key now - it can't be shown again."}

connections may be omitted (or "*") for every connection, or an empty list to block all of them. GET /api_keys lists keys (name, connections, allow_writes, active, created_at, created_from_role - never the hash or secret). Each entry also carries a live usage object - {"queries": ..., "errors": ..., "rows": ...} - the same live, in-process aggregation GET /connections carries (see above), giving a key's activity alongside its grants; unlike a connection's, a key's usage never includes avg_duration_ms - the underlying latency histograms aren't split by key, to keep /metrics' bucketed output from growing with the number of keys (see Observability). PATCH /api_keys/reporting changes connections, allow_writes or active (false revokes it immediately) without rotating the secret. DELETE /api_keys/reporting removes it outright. Creating several keys with the same grants repeatedly? See Permission roles below for a reusable template - POST /api_keys with "role": "<name>" instead of these fields.

Per-saved-query access (external clients)¶

connections grants a key everything on a connection - every saved query on it, plus ad-hoc SQL if allow_writes and the server allow it. That fits an internal caller, but not an external one who should only ever reach a specific, curated list of saved queries and nothing else on the connection behind them.

A key's queries grant covers that case: a list of saved-query names it may run regardless of connections, independent of and additive with whatever connections already allows - never a narrower version of it. A key can have connections: [] (no connection access at all) and still run every query named in queries, but it can never reach ad-hoc SQL through this grant, since queries only ever authorizes the specific named saved query, not the connection behind it:

{"name": "acme-corp", "connections": [], "queries": ["monthly_revenue", "active_users"], "allow_writes": false}

queries may also be "*" for every saved query by name (still never ad-hoc SQL) - a middle tier between a single-connection key and a full-access one, for a caller that should see the whole curated catalogue but never write raw SQL. Omitted (or []) grants nothing extra beyond connections, unchanged from before this field existed. /openapi.json and /docs reflect a key's actual reach: a queries-scoped key sees only its own approved queries in the catalogue, not the full internal list.

Per-query write curation¶

A queries entry can be an object instead of a plain name, adding write access to that one query specifically - on top of, never instead of, whatever allow_writes already grants:

{"name": "partner", "connections": [], "allow_writes": false,
 "queries": ["read_orders", {"name": "submit_order", "allow_writes": true}]}

Here partner can run read_orders read-only and submit_order (a write) - a single curated write endpoint - with no blanket write access, no connection access, and no ad-hoc SQL of any kind. A plain string entry stays exactly what it always was: read access only. "*" can never carry write access, by design - a key wanting write access to a specific query must enumerate its queries list explicitly rather than hiding a write grant behind a wildcard picked for unrelated read access. Still subject to the usual ceiling: never wider than server-wide SQL2API_ALLOW_WRITES, and the query's SQL still has to pass the normal guard (a single statement, actually a write, and - if the key has allowed_write_ops - one of the permitted keywords).

Key expiry¶

A key can carry an optional expires_at (YYYY-MM-DD) for time-boxed access - a trial integration, a partner engagement with a known end date - that stops authenticating on its own once the date passes, without anyone having to remember to come back and revoke it:

{"name": "trial-partner", "connections": ["reporting-db"], "expires_at": "2026-12-31"}

Valid through the end of that date (23:59:59), not from its start. Checked live on every request, the same way active already is - there is no background sweep, so nothing to schedule or fail silently. Omit it (or leave it unset) for a key that never expires; PATCH /api_keys/<name> with {"expires_at": null} clears an existing expiry without rotating the secret, and PATCH without the field at all leaves whatever expiry (or lack of one) the key already had untouched.

Last used¶

GET /api_keys reports last_used_at for a key once it has authenticated at least one request - useful for noticing a stale key nobody has called in months (a candidate to revoke) or confirming a newly-issued one actually got wired up on the other end. Updated at most once a minute per key regardless of how often it's actually used, so a busy key doesn't turn every request into a disk write - read it as "roughly how recently," not an exact timestamp. A key that has never been used has no last_used_at field at all.

Per-key rate limiting¶

SQL2API_RATE_LIMIT (see Rate limiting and CORS) applies server-wide, by client IP, shared by every caller. Hand scoped keys to several external clients and they all draw from the same budget - one noisy integration can exhaust it for everyone else. A key's own rate_limit gives it an individual quota instead:

{"name": "acme-corp", "connections": [], "queries": ["monthly_revenue"], "rate_limit": "100/minute"}

Same N/period grammar as SQL2API_RATE_LIMIT (second, minute, hour or day). Checked in addition to the server-wide limit, never instead of it - a key can never use its own quota to exceed the ceiling every caller already sits under, and a per-key limit still applies even when SQL2API_RATE_LIMIT is unset entirely, since throttling one specific external caller is a reasonable ask on its own. Omitted (or null) means no limit of this key's own - PATCH /api_keys/<name> with an explicit {"rate_limit": null} clears an existing one, the same pattern expires_at uses.

IP allowlisting¶

A key can also be pinned to allowed_ips, a list of IP addresses or CIDR ranges (IPv4 or IPv6, mixed freely) it may authenticate from - real defense in depth for a key handed to an external party with known, stable infrastructure, since even a leaked key then only works from an expected address:

{"name": "trial-partner", "connections": ["reporting-db"], "allowed_ips": ["203.0.113.5", "198.51.100.0/24"]}

Checked against the same client address SQL2API_TRUST_PROXY/ProxyFix already establish as trustworthy for rate limiting, not re-derived here - set SQL2API_TRUST_PROXY correctly behind a reverse proxy, or every caller looks like the proxy's own address. This restricts who may use a key at all, independent of per-key rate limiting above, which restricts how much a caller who is already allowed may do. A request from an address outside the list fails exactly like a wrong key (401), not a distinct error - a caller learns nothing about why a key didn't work. Omitted (or null) means no restriction - the admin key is never restricted by this at all. PATCH /api_keys/<name> with an explicit {"allowed_ips": null} clears an existing restriction, the same pattern expires_at and rate_limit use.

Write operation granularity¶

A key with allow_writes on can be narrowed further with allowed_write_ops, a list of the specific SQL statement keywords it may actually perform - INSERT but not DELETE/DROP, for example - rather than every write keyword being equally permitted once writes are on at all:

{"name": "ingest-bot", "connections": ["events-db"], "allow_writes": true, "allowed_write_ops": ["insert"]}

Only ever narrows write access, never widens it, and never restricts a read-only statement - a key with no allow_writes still can't write regardless of this list. Checked in sqltools.validate_sql() against the statement's own leading keyword (case-insensitive); a rejected statement gets 403 naming the operations the key is permitted. Omitted (or null) means every write keyword is equally permitted, exactly today's behaviour. PATCH /api_keys/<name> with an explicit {"allowed_write_ops": null} clears an existing restriction, the same pattern expires_at/rate_limit/allowed_ips use.

Permission roles (templates)¶

Creating several keys with the same shape of grants - the same connections, the same curated queries, the same rate limit - means repeating that shape by hand each time. A named role, managed through /roles (admin only, stored separately from keys), is a reusable template for exactly that: connections, allow_writes, queries, rate_limit, allowed_ips and allowed_write_ops, the same fields a key itself carries (deliberately excluding expires_at, which is inherently per-key, not something a shared template should dictate).

{"name": "reporting", "connections": ["reporting-db"], "allow_writes": false, "rate_limit": "200/hour"}

POST /api_keys with "role": "reporting" instead of specifying grants directly copies that role's fields onto the new key once, at creation time:

{"name": "acme-corp", "role": "reporting"}

This is a template, not a live link - a key created from a role is a fully independent copy from that moment on. authenticate() reads only the key's own stored entry on every request; the role is never consulted again. Editing or deleting a role afterward has no effect whatsoever on a key already created from it - there is no blast radius to updating a role once keys already exist from it, and no dangling reference to worry about when deleting one. A key still records which role (if any) it was created from, in created_from_role - purely informational, visible in GET /api_keys, never consulted by any permission check.

role cannot be combined with any explicit grant field (connections, allow_writes, queries, rate_limit, allowed_ips or allowed_write_ops) in the same POST /api_keys request - that combination is rejected with 400, naming the conflicting fields. Create the key from the role, then PATCH it afterward to customize it away from the template. expires_at is the one field that can still be set alongside role, since it's per-key by nature rather than part of the shared template.

GET /roles lists roles; PATCH /roles/<name> updates one (the same explicit-null-to-clear convention as PATCH /api_keys/<name> for rate_limit, allowed_ips and allowed_write_ops); DELETE /roles/<name> removes it - again, with zero effect on any key already created from it.

Observability¶

Every response carries X-Request-Id (12 hex characters); log lines written while handling that request carry the same ID and the name of the API key that made it (admin for SQL2API_API_KEY, a scoped key's own name, or - when no key is configured at all), so a request - and who made it - can be traced through the logs even under concurrent traffic. Plain text by default; SQL2API_JSON_LOGS=1 switches to one JSON object per line (time, level, logger, request_id, key, message). In JSON mode, several log lines also carry extra structured fields alongside message rather than only inside it - the per-query line (connection, dialect, limit, offset, timeout, sql_hash), the streaming-start line (connection, dialect, sql_hash), the slow-query warning (connection, dialect, duration_ms), and the per-request access log line (method, path, status, duration_ms, and serialization_ms when the response went through the paged JSON/CSV/TSV/XML/YAML/XLSX formatter) - so a log aggregator can filter or aggregate on those directly instead of parsing the message text. sql_hash is a full SHA-256 hex digest of the SQL text, logged alongside the full text (never instead of it) - useful for spotting "did this same query run elsewhere/before" without a log aggregator having to store or search the SQL itself.

A query that takes at least SQL2API_SLOW_QUERY_THRESHOLD seconds (default 1; 0 disables it) is logged as a WARNING with the connection, dialect and elapsed time.

A saved query's execution_history entries (see Save a query) also carry request_id, key_name and serialization_ms, so a slow or failed run visible in the admin UI's History tab can be traced back to the exact structured log line (and caller) that produced it, and so response-formatting time can be told apart from duration_ms (query execution time) - useful for XLSX or other large-page exports, where encoding cost can rival query time but was previously invisible, folded into "whatever's left over" between total request latency and query latency.

GET /metrics (always public, like /health) serves Prometheus text exposition format:

sql2api_requests_total - HTTP requests by method, endpoint, status and the calling key's name. sql2api_request_duration_seconds - the same latency, by method and endpoint only (not by key, to keep the bucketed output from growing with the number of keys).
sql2api_queries_total - SQL queries by connection, dialect, status (success/error) and the calling key's name. sql2api_query_duration_seconds - the same latency, by connection and dialect only.
sql2api_rows_returned_total - total rows actually returned, by connection, dialect and the calling key's name: the trimmed page for a paged query, or however many rows made it out of a streaming export before it finished or failed partway through (a partial count on failure is still counted - that data already left the server).
sql2api_active_queries - a gauge of SQL queries currently executing right now, paged or mid-stream. For a streaming export this stays incremented for as long as the client keeps reading, not just for the initial query dispatch, since the underlying connection stays checked out the whole time.
sql2api_serialization_duration_seconds - response body serialization latency (JSON/CSV/TSV/XML/YAML/ XLSX encoding), by output format, for paged responses only - streaming formats row by row as it goes, so there's no equivalent single span to measure there.
sql2api_pool_idle_connections - idle pooled database connections currently held.
sql2api_rate_limit_rejections_total - requests rejected by the rate limiter.

Metrics are kept in memory for this one process. This is correct for the image this project ships (a single gunicorn worker - see the comment next to --workers 1 in the Dockerfile); running several worker processes would need a shared backing store instead, which nothing here provides.

Seeing it: a built-in view, or a real dashboard¶

The admin UI's Metrics tab reads /metrics itself and renders it as stat tiles, a couple of bar charts (requests by status, queries by connection) and a per-connection table (queries, errors, average latency, rows) - a zero-setup live view for a deployment with no Prometheus/Grafana stack in front of it at all. It's deliberately a snapshot, not a dashboard: the numbers are this process's own totals since it started, with no history and no trends, the same limits above already describe. It never needs an API key - /metrics is public - and reading it again just re-fetches the current numbers; there's no polling or auto-refresh.

For real history, trends and alerting, scrape /metrics with Prometheus and import documentation/grafana-dashboard.json into Grafana (Dashboards → New → Import, then upload the file or paste its contents) - it builds on exactly the metric names listed above, with panels for request/query rate and latency (p50/p95/p99), error rate, rows returned, active queries, pool occupancy and rate-limit rejections. Each SQL2API process needs its own scrape target; the dashboard doesn't aggregate across instances, matching the in-memory, per-process nature of the metrics themselves.

Audit log¶

GET /audit_log (admin only) is a durable record of administrative changes - distinct from Observability above, which covers live request/query traffic, not configuration changes. Every create, update or delete of an API key, role, connection or saved query appends one entry, newest first:

{
  "entries": [
    {"timestamp": "2026-09-24 10:03:11", "actor": "admin", "action": "update_key", "target": "acme-corp",
     "changes": {"allow_writes": {"from": false, "to": true}}},
    {"timestamp": "2026-09-24 10:01:47", "actor": "admin", "action": "create_connection", "target": "reporting",
     "changes": {"db": "postgres", "host": "db.internal", "password": "********", "active": true}}
  ]
}

An update's changes is a diff of only the fields that actually changed ({"field": {"from": ..., "to": ...}}); a create or delete records a full snapshot of the entry instead, since there's no prior or remaining state to diff against. A connection's password is never included as a value in either form - masked as ******** in a snapshot (the same mask GET /connections already uses) and reported only as the literal string "changed" in a diff, so the audit log itself never becomes a second place a real password leaks from. An API key's entry never includes its secret or hash, the same fields GET /api_keys already omits. Capped at the 500 most recent entries; older ones roll off, the same way a saved query's execution_history is capped per version.

The admin UI's Audit Log tab filters this client-side over what it already fetched - an action dropdown (populated from whatever actions actually appear) and a search box matching actor, target or timestamp - and renders a snapshot's changes (a create/delete) with any unset field (null, "", []) omitted rather than always listing every field, so a key or connection with few grants set doesn't read as a long, mostly empty list. A diff (an update) is unaffected by this - it never had unset fields in it to begin with, only whatever actually changed.

Rate limiting and CORS¶

Both are off unless the server enables them (SQL2API_RATE_LIMIT, SQL2API_CORS_ORIGINS).

Rate limit. When enabled, every response carries X-RateLimit-Limit (the quota) and X-RateLimit-Remaining. A client over its limit receives 429 with a Retry-After header (seconds) and {"error": "Rate limit exceeded", "retry_after": 12}. /health is never limited.
Per-key rate limit. An API key can also carry its own rate_limit (same grammar, e.g. "100/minute" - see Per-key rate limiting), checked in addition to the server-wide limit above, never instead of it - a response carries both header pairs when both apply (X-RateLimit-Limit/X-RateLimit-Remaining for the server-wide one, X-RateLimit-Key-Limit/X-RateLimit-Key-Remaining for the key's own), and a rejection from the key's own limit reads {"error": "Rate limit exceeded for this API key", ...} - distinguishable from the server-wide rejection's plain "Rate limit exceeded".
CORS. For listed origins the server answers preflight (OPTIONS) requests and adds Access-Control-Allow-Origin to responses, exposing X-Page, X-Page-Size, X-Has-More, X-RateLimit-* and Retry-After to the page's JavaScript. Allowed methods are GET, POST, PATCH, DELETE, OPTIONS; allowed request headers are Content-Type and X-API-Key. Credentials (cookies) are not used.

Admin UI¶

/ui is a small, self-contained admin page (no build step, no external dependency) for managing connections, saved queries and API keys, running ad-hoc SQL, reviewing the audit log, and viewing a live snapshot of /metrics - a client of the API above, adding no server-side logic of its own. Loading the page needs no API key; the requests it makes are gated exactly like any other client, so a scoped (non-admin) key sees the same "only the admin key" message on the API Keys and Audit Log tabs as it does on Connections and Saved Queries (the Metrics tab is the one exception - /metrics is public, so it works with no key at all). It shares its API-key storage with /docs (the same browser-tab-only sessionStorage entry), so entering the key on one page covers both.

Both the Connections and API Keys tabs have a "Usage" column showing each row's live activity (queries run, failures, and - for a connection - average latency), sourced from the usage object both endpoints now carry; a row with no activity since the server started reads "No activity yet" rather than showing zeros.

The API Keys tab creates, edits and revokes scoped keys: its "All connections" checkbox toggles between the "*" wildcard and a specific set of connections, and a freshly created key's secret is shown once, in place, with a copy button - the same one-time-only reveal the API itself enforces, since the page never receives the secret again after that response. Its "Create from" field lists any permission roles that exist; picking one dims the grant fields below (they'll be copied from the role instead) and creates the key with {"name": ..., "role": ...} - only expires_at stays editable alongside it.

The Roles tab manages roles themselves - the same connections/queries/write-access/rate-limit/allowed-IPs form as the API Keys tab, minus expires_at and active, which don't apply to a template. Its "New key from this" button jumps straight to the API Keys tab's "New API key" drawer with that role pre-selected.

A saved query's History tab shows execution_history with a "Caller" and "Request ID" column alongside the existing time/connection/rows/duration/error ones, and a status filter (all/success/failed) plus a search box over connection, caller, request ID and error text - useful once a version has accumulated more than a handful of runs and you're looking for the failures, or everything one particular key did. Filtering happens client-side, over the run history the page already has, so it applies instantly with no extra request.

Any JSON result with at least one numeric column gets a "Chart" toggle next to "Copy as TSV" in the Run SQL and saved-query Run tabs: a quick bar chart, off by default, of the columns on screen. It charts the current page only - up to 50 rows of it - with a visible reminder that it isn't the full result, since a chart of page 3 of 50 can look complete without being one. Label and value columns are pickable from dropdowns (value defaults to the first all-numeric column, label to the first column that isn't); rendered as inline SVG with no charting library, the same no-dependency approach the SQL editor's own syntax highlighting already uses.

Both the Run SQL tab and the New saved query drawer have a Schema panel next to their SQL editor, backed by GET /connections/<name>/schema: it lists the selected connection's tables, expands to show a table's columns (with type and nullability as a tooltip), and clicking a table or column inserts its name at the cursor. It updates automatically when the connection changes, and a connection whose schema isn't available (a jdbc connection, or one the caller isn't permitted to use) shows that message in the panel itself rather than the page's error banner, since browsing the schema is optional, not the action the user took.

Interactive documentation¶

/docs (Swagger UI, backed by /openapi.json) documents the generic API and also lists every saved query as its own endpoint, generated from its latest version: its parameters with types, defaults, ranges and descriptions, and whether a connection must be named. The SQL text is never included.

The generic part is public. When the server sets SQL2API_API_KEY, the saved-query part is only included for requests that carry the key - paste it into the box at the top of /docs (kept in that browser tab only) or send X-API-Key to /openapi.json.

Since /openapi.json is a standard OpenAPI 3.0 document, Postman and Insomnia can both import it directly by URL (Postman: Import → Link) to get a ready-made collection of every endpoint, including saved queries once you've supplied a key - no separate export step.

The API catalogue¶

GET /catalog answers a different question than /openapi.json: not just how to call a saved query (parameters, types, connection) but under what terms - whether its response can be cached, whether the calling key specifically can write through it, and what rate limit governs the calling key itself. That information already exists (cache_ttl on the saved query, a key's own rate_limit, per-query write curation - see Per-query write curation) but was otherwise only visible on admin-only screens a scoped key can never reach:

{
  "queries": [
    {"name": "top_rented_films", "version": 1, "description": "Films ranked by number of rentals",
     "tags": ["reporting", "films"], "connection_name": "rental_db", "parameters": {},
     "cache_ttl": 60, "can_write": false}
  ],
  "caller": {
    "name": "acme-corp", "admin": false, "allow_writes": false, "allowed_write_ops": null,
    "rate_limit": "200/hour", "server_rate_limit": null
  }
}

queries is scoped exactly like /openapi.json's saved-query list - a query this caller cannot reach through /q/<name> is never listed here either, so the catalogue never shows a caller something it can't actually use. cache_ttl is null when the query isn't cached; can_write reflects this specific caller (a query with no per-query write curation for them still reads false even if their key has blanket allow_writes, since blanket access is already visible on their own key). caller.rate_limit is this key's own additional limit (null if it has none of its own); caller.server_rate_limit is SQL2API_RATE_LIMIT, checked in addition to it, never instead of it. Unlike /openapi.json, /catalog is never public - it requires authentication like any other functional endpoint, since the whole point is answering "what can I use," which needs a resolved caller to mean anything.

Errors¶

Errors are returned as {"error": "..."}; failed queries also include "detail" with the database's message.

Status	Meaning
400	Missing or invalid input (SQL, paging, format, filename, parameters, several statements). Parameter-rule violations add an `errors` map keyed by parameter name.
401	Missing or wrong `X-API-Key` (only when a key is configured)
429	Rate limit exceeded; wait `Retry-After` seconds
403	Inactive connection, write statement while writes are disabled, a file outside `saved_sql/`, a connection this API key isn't scoped to, or a management action a scoped (non-admin) key can't perform
404	Unknown connection, saved query, version or file
500	The database rejected the query or could not be reached
504	The query exceeded its time limit and was cancelled (the response includes `"timeout"`)