Run a SQL query
Runs one read-only DuckDB query (SELECT or WITH) on the open tables and returns the rows as JSON, CSV or Parquet.
https://api.transitlab.nyc/v1/sql API keyNeeds a key. Limits per key: 60 requests a minute, 1,000 queries and 5 minutes of query time a day (UTC), two queries at a time; each query stops after 30 seconds and returns at most 10,000 rows. The same query (ignoring spacing and comments) within 10 minutes comes from a cache and costs no quota. The free service also has a monthly allowance of engine time shared by all keys; when it is spent, SQL answers 503 (budget_exhausted) with Retry-After set to the first of next month, while cached answers, tables and health still work. Functions that read files or other servers are refused. A plain-text body (Content-Type: text/plain) is taken as the SQL itself.
Request
Section titled “Request”curl https://api.transitlab.nyc/v1/sql \ -H "Authorization: Bearer $TRANSITLAB_API_KEY" \ -H "Content-Type: application/json" \ -d '{"sql":"select route_id, count(*) as stops from subway_stop_events where year = 2026 and month = 10 group by 1 order by 2 desc limit 5"}'import osimport requests
r = requests.post( "https://api.transitlab.nyc/v1/sql", headers={"Authorization": f"Bearer {os.environ['TRANSITLAB_API_KEY']}"}, json={ "sql": "select route_id, count(*) as stops from subway_stop_events where year = 2026 and month = 10 group by 1 order by 2 desc limit 5", }, timeout=60,)r.raise_for_status()print(r.json())const res = await fetch("https://api.transitlab.nyc/v1/sql", { method: "POST", headers: { Authorization: `Bearer ${process.env.TRANSITLAB_API_KEY}`, "Content-Type": "application/json", }, body: JSON.stringify({ "sql": "select route_id, count(*) as stops from subway_stop_events where year = 2026 and month = 10 group by 1 order by 2 desc limit 5" }),})if (!res.ok) throw new Error(`HTTP ${res.status}`)console.log(await res.json())Authorization
Section titled “Authorization”Needs an API key, sent as Authorization: Bearer <key>. Get a free key.
Request body
Section titled “Request body”application/json, required.
-
sqlstring requiredOne DuckDB SELECT or WITH statement. Name tables as /v1/tables lists them; they resolve to the public Parquet files. Big tables have year and month columns: filter on them to read fewer files. Up to 20000 characters.
-
formatstringAnswer format. CSV and Parquet carry the row count, truncation, time and bytes read in X- headers. One of
json,csv,parquet. Default"json". -
limitintegerMost rows to return. Larger values are cut to 10,000. Default
1000. From 1 to 10000. -
fromstringOptional. Skip files that end before this day (YYYY-MM-DD) or month (YYYY-MM). Picks files only; add a WHERE to trim rows.
-
tostringOptional. Skip files that start after this day or month.
Also accepted:
text/plain: The SQL itself.
Responses
Section titled “Responses”200 OK
Section titled “200 OK”The answer. X-Cache says whether it came from the cache.
Headers:
-
RateLimit-PolicystringThe key’s limits: requests a minute and queries a day (IETF RateLimit headers).
-
RateLimitstringQueries left today and seconds until the count resets.
-
X-RowsintegerRows returned.
-
X-Truncatedstringtrue if cut at the limit.
-
X-Elapsed-MsintegerEngine time in milliseconds.
-
X-Bytes-ScannedintegerBytes read from the files.
-
X-Cachestringhit or miss.
Body:
-
columnsobject[] requiredThe answer’s columns, in order.
-
columns[].namestring requiredColumn name.
-
columns[].typestring requiredDuckDB type, e.g. BIGINT, DOUBLE, VARCHAR, TIMESTAMP.
-
rowsany[][] requiredOne array per row, values in column order. Dates and times are ISO 8601 strings; decimals are numbers.
-
row_countinteger requiredRows in
rows. -
truncatedboolean requiredTrue if the answer had more rows than the limit (or than 10 MB) and was cut.
-
elapsed_msinteger requiredTime the query took in the engine, in milliseconds.
-
bytes_scannedinteger requiredBytes the engine read from the Parquet files for this query. Parts it already had in memory from earlier queries count as zero.
{ "columns": [ { "name": "route_id", "type": "VARCHAR" }, { "name": "stops", "type": "BIGINT" } ], "row_count": 5, "truncated": false, "elapsed_ms": 310, "bytes_scanned": 198821, "rows": [ ["7", 50012], ["4", 28813], ["6", 27615], ["1", 23035], ["2", 22199] ]}Also sent as:
text/csv: CSV with a header row.application/vnd.apache.parquet: A Parquet file.
Errors
Section titled “Errors”| Status | When |
|---|---|
400 | Bad request. The query is not one read-only statement, reads something other than the tables, or has an error. |
401 | Unauthorized. No key, or the key is unknown or revoked. The body links to https://transitlab.nyc/api-keys. |
408 | Timeout. The query ran longer than 30 seconds. |
413 | Too large. The body is over 64 KB. |
415 | Unsupported media type. The body is not JSON or plain text. |
422 | Unprocessable. The query needed more memory than the engine allows. |
429 | Too many requests. Over the per-minute limit, the daily quota, or the running-query limit. |
503 | Unavailable. The engine is busy or starting, the service’s daily limit is spent, or the free service’s monthly engine time is spent (budget_exhausted; Retry-After is the seconds to the first of next month, UTC). |
Error responses carry this body:
-
errorstring requiredA short code: unauthorized, rate_limited, daily_queries, daily_compute, concurrency, busy, budget_exhausted, engine_unavailable, bad_sql, not_read_only, sql_error, not_allowed, timeout, out_of_memory, bad_format, bad_limit, bad_date, no_such_table, not_found.
-
messagestring requiredWhat went wrong and what to do, in plain words.
-
get_a_keystring (uri)On 401: where to get a key.
-
docsstring (uri)On 401: the API guide.
{ "error": "sql_error", "message": "Catalog Error: Table with name subway_stop_event does not exist!\nDid you mean \"subway_stop_events\"?\n\nLINE 1: select route_id, count(*) from subway_stop_event group by 1\n ^"}Headers on 401, 429, 503:
-
WWW-AuthenticatestringBearer.
-
Retry-AfterintegerSeconds to wait before trying again.
-
RateLimit-PolicystringThe key’s limits: requests a minute and queries a day (IETF RateLimit headers).
-
RateLimitstringQueries left today and seconds until the count resets.