Skip to content
Docs

SQL API

Send read-only SQL to api.transitlab.nyc and get rows back as JSON, CSV or Parquet. Free key. Also a remote MCP server.

The SQL API runs your query on our servers, against every open table, and sends back the answer. Nothing to download or install. It suits scripts, notebooks and small apps that can make HTTPS calls but can’t run DuckDB.

If you want whole tables, or queries that read years of trips, download the Parquet files instead. They need no key.

Enter your email address at transitlab.nyc/api-keys. We email you a key from hello@transitlab.nyc. It looks like tl_live_ and 32 letters and digits.

We keep only a fingerprint of the key, so we can’t send it again. If you lose it, ask for a new one. If it leaks, write to hello@transitlab.nyc and we’ll turn it off.

Send the key in the Authorization header and the query as JSON:

Terminal
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"}'

The answer:

JSON
{
"columns": [{"name": "route_id", "type": "VARCHAR"}, {"name": "stops", "type": "BIGINT"}],
"row_count": 5,
"truncated": false,
"elapsed_ms": 412,
"bytes_scanned": 3145728,
"rows": [["A", 41210], ["F", 38877], ...]
}

The body takes these fields:

FieldMeaning
sqlOne DuckDB SELECT or WITH statement. Required.
formatjson (the default), csv or parquet.
limitMost rows to return, up to 10,000. The default is 1,000. truncated says whether there were more.
from, toOptional, YYYY-MM-DD or YYYY-MM. Read only the files for those days or months.

You can also send the SQL itself as the body with Content-Type: text/plain.

CSV and Parquet answers carry the row count, truncation, time and bytes read in the X-Rows, X-Truncated, X-Elapsed-Ms and X-Bytes-Scanned headers.

Terminal
curl https://api.transitlab.nyc/v1/sql \
-H "Authorization: Bearer $TRANSITLAB_API_KEY" \
-H "Content-Type: application/json" \
-d '{"sql": "select * from bus_lane_streets", "format": "parquet"}' -o streets.parquet
Python
import os, requests
r = requests.post(
"https://api.transitlab.nyc/v1/sql",
headers={"Authorization": f"Bearer {os.environ['TRANSITLAB_API_KEY']}"},
json={"sql": "select count(*) from taxi_yellow_trips where year = 2026 and month = 8"},
timeout=60,
)
r.raise_for_status()
print(r.json()["rows"])

These two need no key:

Terminal
curl https://api.transitlab.nyc/v1/tables
curl https://api.transitlab.nyc/v1/tables/taxi_yellow_trips

The first lists every table with its rows, size and the days it covers. The second gives one table’s columns, their meaning, and its files. The datasets pages say the same at more length.

Each query stops after 30 seconds. Big tables (trips, bus positions, stop events) are one file a month or a day, with year and month columns. Filter on them, and the engine opens only those files:

SQL
select date_trunc('day', pickup_datetime) as day, count(*) as trips
from fhv_trips
where year = 2026 and month = 5
group by 1 order by 1

A query over a whole trip table reads gigabytes and won’t finish in time. Count, average and group in SQL, and fetch raw rows only when you need them.

Only one read-only statement is allowed per request. Functions that read other files or servers (read_csv, read_parquet, glob and the like) are refused; name the tables instead.

LimitFree key
Requests60 a minute, to any endpoint
Queries1,000 a day (UTC)
Query time5 minutes a day (UTC)
At once2 queries per key
Per query30 seconds, 10,000 rows, 10 MB

The same query (ignoring spaces and comments) sent again within 10 minutes comes from a cache. It is fast and doesn’t count against the daily limits.

Every answer has RateLimit-Policy and RateLimit headers. Over a limit, you get 429 with a Retry-After header in seconds. The engine runs one query pool for everyone. A query waits up to 5 seconds for a free slot; if none opens you get 503 with Retry-After. The engine sleeps after 3 minutes without a query, so the first query after a quiet spell takes longer (about 5 to 15 seconds).

The free API runs on one small engine shared by all keys, and it has a fixed number of engine hours each calendar month (150, counted from when the engine wakes until it sleeps again). engine_budget in /v1/health shows the hours left and when the month resets. When they run out, SQL (/v1/sql and the MCP run_sql tool) answers 503 with Retry-After until the first of next month. Repeat queries still come from the cache, and /v1/tables, health and the Parquet files keep working. If you need more, write to us.

Errors come back as JSON with an error code and a message in plain words.

StatusWhen
400The query isn’t one SELECT, reads something other than the tables, or has a mistake (sql_error; the message is DuckDB’s)
401No key, or a key we don’t know or have turned off. The body links to the key page.
408The query ran past 30 seconds
422The query needed more memory than the engine allows
429Over the per-minute limit (rate_limited), the daily limits (daily_queries, daily_compute) or two queries at once (concurrency)
503The engine is busy or starting (busy, engine_unavailable). Wait the Retry-After seconds and try again.
503The free service has used its engine time for the month (budget_exhausted). Retry-After is the seconds to the first of next month (UTC).

The same key works with a remote MCP server at https://api.transitlab.nyc/mcp. Its queries count against the key’s limits like any other. Setup and the list of tools are on the CLI and MCP page.

If a query fails and the error doesn’t say why, a doc or a table looks wrong, the data look wrong, or you need something we don’t have, tell us. It needs no key, so you can also report a key that doesn’t work. A person reads every report. Nothing comes back to you unless you leave an email.

Terminal
curl https://api.transitlab.nyc/v1/feedback \
-H "Content-Type: application/json" \
-d '{"message": "count(*) on bus_lanes returned a 500. I expected one number.", "context": "POST /v1/sql: select count(*) from bus_lanes. Error: 500 server"}'

The answer is {"ok": true, "id": 12, "message": "..."}. The body takes these fields:

FieldMeaning
messageWhat happened and what you expected. Required, 10 to 4,000 characters.
contextOptional. The page, endpoint, table or query this is about, and any error text. Up to 2,000 characters.
contactOptional. An email address, only if you want a reply.

Send one report per problem and leave out keys and other secrets. Each address and each key may send 10 reports an hour; the same words twice count once. We store your report, the key id if you sent a valid key, the country and a salted hash of your IP address, never the address itself. The hash lets us limit abuse and nothing else. A 400 says which field to fix. A 429 has a Retry-After header.

In a terminal, run transitlab feedback "message" --kind data. Every page of these docs ends with a Yes or No button that sends a report too.

The API reference has a page for every endpoint, with its parameters, responses and samples in curl, Python and JavaScript. It is built from the OpenAPI description. GET https://api.transitlab.nyc/v1/health says whether the API is up, which catalog it serves, and how many engine hours are left this month in engine_budget.

Data from the API is under the same terms as the files: CC BY 4.0 for our tables. Credit Transit Lab and the original source. See license and credit.