Query API¶
Run read-only SQL against a registered data source.
Run a query¶
Requires authentication (Authorization: Bearer <token>).
Request body:
{
"source_id": "orders",
"sql": "SELECT id, status, total FROM orders WHERE status = 'shipped'",
"limit": 100
}
| Field | Type | Required | Default | Description |
|---|---|---|---|---|
source_id |
string | Yes | — | Registered source name (e.g. orders) or UUID |
sql |
string (1–10,000 chars) | Yes | — | SQL SELECT statement |
limit |
integer, min 1 | No | 100 |
Maximum rows to return. The ceiling is the deployment's TDB_MAX_ROWS (default 1,000); a higher value is rejected with 400, never silently reduced. |
Use the source name
source_id accepts either the source's registered name (e.g. orders) or its
UUID. Names are easier to read, write, and remember for daily operations.
UUIDs remain useful in automation scripts and audit-trail lookups.
Response (200):
{
"source_id": "a1b2c3d4-...",
"sql": "SELECT id, status, total FROM orders WHERE status = 'shipped'",
"columns": ["id", "status", "total"],
"rows": [
{"id": 1001, "status": "shipped", "total": 149.99},
{"id": 1002, "status": "shipped", "total": 59.00}
],
"rows_returned": 2,
"truncated": false,
"executed_at": "2026-05-22T09:15:00Z"
}
Table name in queries¶
The table name to use in your SQL depends on the connector — there is no universal
data alias across connector types:
- CSV (Community): always use
data. TDB loads the file into an in-memory table under that fixed name. - PostgreSQL / MySQL / SQL Server / Snowflake: use the actual table name from the
source's
connection.table. Your SQL is passed through to the database unchanged (apart from an appendedLIMIT).
-- CSV source — always 'data'
SELECT COUNT(*) AS total FROM data
-- Database source registered with "table": "orders"
SELECT * FROM orders WHERE status = 'shipped' LIMIT 20
Row limit behaviour¶
TDB applies the row limit at three levels:
- Request-level — the
limitfield caps rows returned in this response. It may not exceed the deployment'sTDB_MAX_ROWS; a larger value is rejected with400. - SQL injection — if your SQL doesn't contain a
LIMITclause, TDB appendsLIMIT limit + 1. If your SQL already has one, it is left alone. The extra row is never returned; it exists so TDB can tell a result that exactly fills the limit from one that was cut. - After fetching — the result is cut to
limitregardless of what the SQL asked for, andtruncatedis set totruewhen rows were dropped. This is the ceiling that actually holds: aLIMIT 100000in your own SQL does not raise it.
truncated: true means you did not receive the whole result
Check the flag before treating a response as complete. Narrow the query — filter,
aggregate, or page with LIMIT/OFFSET in your own SQL against a database
source — rather than assuming the rows you got are all of them.
On 0.7.0 / community 0.4.6 and earlier, truncated under-reported
Those versions appended LIMIT limit rather than limit + 1, so a query with
no LIMIT of its own could not distinguish "exactly limit rows exist" from
"the result was cut" — and always reported truncated: false. A 5-row table
queried with limit: 2 returned 2 rows and truncated: false.
Only queries carrying their own larger LIMIT set the flag correctly. If you
are on an affected version, do not treat truncated: false as proof of
completeness — compare rows_returned against your limit instead, and treat
equality as "possibly more". Fixed in 0.7.1 and community 0.4.7.
Enterprise deployments can raise the ceiling by setting TDB_MAX_ROWS
(reference). Every row of a response is held
in memory, so raise it deliberately. Community is fixed at 1,000. Cursor-based
streaming and pagination remain a post-launch feature.
What SQL is accepted¶
The statement must begin with SELECT or WITH, ignoring leading
whitespace and leading comments. That rule is stricter than "read-only", and the
difference catches people out:
| SQL | Accepted |
|---|---|
SELECT * FROM orders |
✅ |
select * from orders (any case) |
✅ |
SELECT * FROM orders -- comment |
✅ trailing comments are fine |
SELECT * FROM (SELECT …) t |
✅ subqueries |
SELECT … UNION SELECT … |
✅ |
SELECT * FROM t WHERE note = 'update pending' |
✅ keywords inside string literals |
WITH x AS (…) SELECT * FROM x |
✅ CTEs, from 0.6.0 / 0.10.0 |
WITH RECURSIVE t(n) AS (…) SELECT * FROM t |
✅ recursive CTEs |
/* comment */ SELECT 1 |
✅ a leading comment, from 0.6.0 / 0.10.0 |
-- commentSELECT 1 |
✅ a leading line comment |
WITH x AS (DELETE FROM t RETURNING *) SELECT * FROM x |
❌ Blocked keyword: DELETE |
(SELECT 1) |
❌ parenthesised |
EXPLAIN SELECT 1 |
❌ |
SHOW TABLES, VALUES (1), TABLE orders |
❌ |
Rejections return HTTP 400. A statement that opens with the wrong token gives
SQL validation failed: Only SELECT and WITH statements are allowed; one
containing a write keyword gives Blocked keyword: <keyword> instead, and that
check runs first.
CTEs are accepted from community 0.6.0 / enterprise 0.10.0
Earlier versions refused WITH … SELECT — the validator required the
statement to start with SELECT. If you are on an older release, rewrite
the CTE as a subquery:
-- accepted everywhere
SELECT customer, COUNT(*)
FROM (SELECT * FROM orders WHERE created_at > '2026-01-01') recent
GROUP BY customer
A data-modifying CTE is still refused on every version, by the
write-keyword scan rather than by the opening token:
WITH w AS (INSERT INTO t … RETURNING *) SELECT * FROM w returns 400 with
Blocked keyword: INSERT.
On SQL Server the row ceiling is not pushed into a CTE as a TOP
clause, because TOP has to sit on the statement's final SELECT. The
response is capped and truncated is still accurate — the source simply
does more work before the rows are cut. Every other source pushes a LIMIT
down as usual.
Multiple statements¶
One statement per request, from community 0.7.0 / enterprise 0.11.0.
SELECT 1; SELECT 2 returns 400 with Only one statement per query is
allowed, and is audited as sql_validation_failed. A ; inside a string
literal or comment does not count, and a single trailing ; is fine. (Before
community 0.7.1 / enterprise 0.11.1 a trailing ; on a query with no LIMIT
of its own failed with a 500, because the row cap was appended after it.)
Earlier releases accepted several statements, ran them all and returned only the last result set. Only the first statement's opening keyword was checked, so a second statement could be something the write-keyword list does not name — which is why this is now refused rather than documented.
Files are not readable from SQL¶
A CSV query can read only files in the source's data directory
(TDB_ALLOWED_DATA_DIR, or the CSV's own directory when that is unset), from
community 0.7.0 / enterprise 0.11.0. SELECT * FROM read_csv('/etc/passwd')
returns 403 and is audited as sql_file_access. Earlier releases let DuckDB
open any path written in the SQL — see the
security advisory.
Read-only enforcement¶
TDB rejects SQL containing a write keyword:
Expected response (HTTP 400):
Even if the SQL validator is somehow bypassed, the Postgres connection is opened
with read_only = True — Postgres itself will reject write operations.
Audit log¶
Every query writes a line to tdb_audit.jsonl:
{
"event": "query",
"source_id": "a1b2c3d4-...",
"sql": "SELECT ...",
"rows_returned": 2,
"key_hint": "tdbk_a...",
"ts": "2026-05-22T09:15:00.123456+00:00"
}
Rejected queries — SQL validation failures, auth failures, unknown sources — write
an event: "denied" entry carrying the action and a machine-readable reason:
{
"event": "denied",
"action": "query",
"reason": "sql_validation_failed",
"source_id": "a1b2c3d4-...",
"sql": "DROP TABLE data",
"key_hint": "tdbk_a...",
"ts": "2026-05-22T09:15:02.884101+00:00"
}
See Audit Log for the full action and reason sets.
Error responses¶
| Status | Meaning |
|---|---|
| 400 | SQL validation failed (not a SELECT) |
| 401 | Missing or invalid auth token |
| 404 | source_id (name or UUID) not found |
| 429 | Rate limit exceeded (DB-managed keys only) |
| 500 | Query execution error — check that the source is reachable |
Examples¶
Count rows (by source name):
Expected response:
{
"source_id": "a1b2c3d4-...",
"sql": "SELECT COUNT(*) AS total FROM orders",
"columns": ["total"],
"rows": [{"total": 4821}],
"rows_returned": 1,
"truncated": false,
"executed_at": "2026-05-22T09:15:00Z"
}
Aggregate with GROUP BY:
Filter with a date range:
Invoke-RestMethod -Uri "http://localhost:8000/v1/query" `
-Method POST `
-ContentType "application/json" `
-Headers @{ Authorization = "Bearer <YOUR_KEY>" } `
-Body '{
"source_id": "orders",
"sql": "SELECT * FROM orders WHERE created_at >= ''2026-01-01'' AND created_at < ''2026-02-01''",
"limit": 500
}'