Skip to content
Docs

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.

POST https://api.transitlab.nyc/v1/sql API key

Needs 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.

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 os
import 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())

Needs an API key, sent as Authorization: Bearer <key>. Get a free key.

application/json, required.

  • sql string required

    One 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.

  • format string

    Answer format. CSV and Parquet carry the row count, truncation, time and bytes read in X- headers. One of json, csv, parquet. Default "json".

  • limit integer

    Most rows to return. Larger values are cut to 10,000. Default 1000. From 1 to 10000.

  • from string

    Optional. Skip files that end before this day (YYYY-MM-DD) or month (YYYY-MM). Picks files only; add a WHERE to trim rows.

  • to string

    Optional. Skip files that start after this day or month.

Also accepted:

  • text/plain: The SQL itself.

The answer. X-Cache says whether it came from the cache.

Headers:

  • RateLimit-Policy string

    The key’s limits: requests a minute and queries a day (IETF RateLimit headers).

  • RateLimit string

    Queries left today and seconds until the count resets.

  • X-Rows integer

    Rows returned.

  • X-Truncated string

    true if cut at the limit.

  • X-Elapsed-Ms integer

    Engine time in milliseconds.

  • X-Bytes-Scanned integer

    Bytes read from the files.

  • X-Cache string

    hit or miss.

Body:

  • columns object[] required

    The answer’s columns, in order.

  • columns[].name string required

    Column name.

  • columns[].type string required

    DuckDB type, e.g. BIGINT, DOUBLE, VARCHAR, TIMESTAMP.

  • rows any[][] required

    One array per row, values in column order. Dates and times are ISO 8601 strings; decimals are numbers.

  • row_count integer required

    Rows in rows.

  • truncated boolean required

    True if the answer had more rows than the limit (or than 10 MB) and was cut.

  • elapsed_ms integer required

    Time the query took in the engine, in milliseconds.

  • bytes_scanned integer required

    Bytes the engine read from the Parquet files for this query. Parts it already had in memory from earlier queries count as zero.

Example
{
"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.
StatusWhen
400Bad request. The query is not one read-only statement, reads something other than the tables, or has an error.
401Unauthorized. No key, or the key is unknown or revoked. The body links to https://transitlab.nyc/api-keys.
408Timeout. The query ran longer than 30 seconds.
413Too large. The body is over 64 KB.
415Unsupported media type. The body is not JSON or plain text.
422Unprocessable. The query needed more memory than the engine allows.
429Too many requests. Over the per-minute limit, the daily quota, or the running-query limit.
503Unavailable. 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:

  • error string required

    A 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.

  • message string required

    What went wrong and what to do, in plain words.

  • get_a_key string (uri)

    On 401: where to get a key.

  • docs string (uri)

    On 401: the API guide.

Example (400)
{
"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-Authenticate string

    Bearer.

  • Retry-After integer

    Seconds to wait before trying again.

  • RateLimit-Policy string

    The key’s limits: requests a minute and queries a day (IETF RateLimit headers).

  • RateLimit string

    Queries left today and seconds until the count resets.