SQL Queries
Write real SQL against your data. SELECT, JOIN, GROUP BY — the full language.
Send a SQL SELECT query to the /records/sql endpoint and get JSON back. Use the full power of PostgreSQL — WHERE, ORDER BY, GROUP BY, HAVING, CASE, subqueries, DISTINCT — without leaving the csv-api ecosystem.
What it does
The SQL Query endpoint accepts a raw SQL SELECT statement via the q parameter and executes it against a virtual 'data' table that maps to your dataset's columns using their display names. The query is parsed with a real PostgreSQL parser, validated to ensure it's a single read-only SELECT, and executed inside a sandboxed, read-only transaction with a statement timeout. Results come back as JSON, just like the rest of the API. If no LIMIT is supplied, a default cap of 1,000 rows is applied automatically.
How it works
-
1
Write your SQL
Query a virtual table called 'data' using your dataset's display column names. SELECT name, city FROM data WHERE age > 30 ORDER BY name.
-
2
Send it to the endpoint
GET or POST /api/v1/datasets/:id/records/sql?q=YOUR_SQL with a private API key. The query is parsed and validated before anything touches the database.
-
3
Get JSON results
Results come back as a JSON data array with a meta object containing the row count. Column names in the response match the names you used in the query.
See it in action
# Top 5 cities by average age, only where we have 3+ people
curl -G "https://csv-api.com/api/v1/datasets/d_a8f3bc91/records/sql" \
-H "Authorization: Bearer sk_YOUR_KEY" \
--data-urlencode "q=SELECT city, COUNT(*) AS cnt, AVG(age) AS avg_age
FROM data
GROUP BY city
HAVING COUNT(*) >= 3
ORDER BY avg_age DESC
LIMIT 5"
# → 200 OK
{
"data": [
{ "city": "Portland", "cnt": 14, "avg_age": 33.2 },
{ "city": "Denver", "cnt": 8, "avg_age": 31.5 },
{ "city": "Seattle", "cnt": 9, "avg_age": 29.8 }
],
"meta": { "row_count": 3 }
}
Why it matters
-
Full query language
Go beyond filter[col][op]=val. Write JOINs against the same table, window functions, CASE expressions, nested subqueries — anything PostgreSQL SELECT supports.
-
Display-name columns
You query with the same column names you see in the dashboard, not internal database identifiers. A CTE handles the mapping transparently.
-
Sandboxed and safe
Queries are validated, sandboxed, and time-limited. Only SELECT statements against your own data are allowed. Writes and cross-table access are rejected before they reach the database.
The problem it solves
The filter and aggregation endpoints cover common cases, but sometimes you just need SQL. Percentiles, self-joins, CASE-based bucketing, correlated subqueries — these are easy to express in SQL and painful (or impossible) to shoehorn into a REST filter syntax. The SQL endpoint gives you the full language when you need it, behind the same authentication and rate-limiting you already trust.
Common use cases
-
Ad-hoc analytics that go beyond the built-in filter and aggregation endpoints
-
Generating reports with GROUP BY, HAVING, and complex WHERE clauses
-
Data exploration during development — try queries before committing to a dashboard
-
Power users and BI tools that want to speak SQL instead of query parameters
Try SQL Queries for yourself
Create a free csv-api account, upload a file, and see your API live in under a minute.