Read-only SQL
תוכן זה אינו זמין עדיין בשפה שלך.
Use POST /query first. POST /sql is the Pro escape hatch for a subquery or a join the query DSL cannot say.
Each account database runs one query at a time, so heavy SQL would delay incoming events.
Use query first
Section titled “Use query first”POST /query is the supported read. It stays Pro. group_by accepts event, date, user_id, session_id, country, and at most one properties.<key>.
<key> is 1 to 128 characters matching [A-Za-z0-9_], the same rule as a properties.<key> filter. The result row uses that string as the key. A null or missing property groups as null. Two properties.* entries are 400 INVALID_GROUP_BY. A bad key is 400 INVALID_PROPERTY_KEY. limit stays at most 1,000. Numeric metrics (sum, avg, p50, p75, p95) work with this group column.
GET /breakdown and the MCP tool analytics_breakdown still answer a single property’s counts. A funnel stays POST /funnel.
| Question | Call |
|---|---|
| Signups by country | POST /query with group_by: ["country"] |
Counts by properties.path |
POST /query with group_by: ["properties.path"] |
| Signups by path and day | POST /query with group_by: ["date", "properties.path"] |
| A two-step funnel | POST /funnel |
| A join or subquery the DSL cannot say | POST /sql |
POST /sql
Auth is the same account auth as other analytics reads. Agent session scope is analytics:read. On self-hosted OSS, auth is the read API key.
Body:
{ "project": "my-site", "sql": "SELECT event, COUNT(*) AS n FROM events GROUP BY event" }project is the project name. sql is one statement, at most 20,480 bytes (20 KB).
Optional days defaults to 7 and is at most 90. Or send date_from and date_to as YYYY-MM-DD, with a span of at most 90 days. Date parsing matches POST /query. Send days, or send the date pair. A window outside those rules is 400 INVALID_WINDOW.
The server always applies that window before the query runs, on the date column of both events and sessions: date >= ? AND date <= ?, inclusive UTC days. That uses idx_events_project_date and idx_sessions_project_date.
Response:
{ "columns": ["event", "n"], "rows": [{ "event": "page_view", "n": 12 }], "count": 1, "truncated": false, "window": { "date_from": "2026-11-07", "date_to": "2026-11-13" }, "statistics": { "elapsed_ms": 18, "rows_read": 1200, "expensive": false }}columns names the result columns. Each entry in rows is an object. Hosted and OSS both return row objects. Hosted sets columns to the keys of the first row. When rows is empty, columns is []. count is the number of rows returned. truncated is true when the server cut the result to 1,000 rows. window is the date_from and date_to the server applied. statistics.elapsed_ms is the database time in milliseconds. statistics.rows_read is how many rows the database read. On hosted, that number is D1 meta.rows_read. statistics.expensive is true when rows_read is over 1,000,000. The rows are still returned.
Plan and limits
Section titled “Plan and limits”Pro only. A free account receives 403 PRO_REQUIRED. That response is not metered.
One accepted call counts as one metered read.
- One
SELECTstatement. A statement that starts withWITHis rejected. A nestedWITHis allowed only when every table it names iseventsorsessions. A recursive CTE is rejected. A subquery inside theSELECTis allowed. - Each of
eventsandsessionsmay be referenced at most once. One join ofeventstosessionsis allowed. A self join is not. A second reference is400INVALID_SQL, and the message says why. - After
FROM,JOIN, orIN, onlyevents,sessions, a CTE name, or a parenthesized subquery or list is allowed. - At most 20,480 bytes.
- If the statement has no
LIMIT, the server appendsLIMIT 1001, returns at most 1,000 rows, and setstruncatedto true when that extra row existed. A userLIMITmust be a plain integer.LIMIT 1000+500is400INVALID_SQL. A userLIMITabove 1,000 is capped to 1,000. - 10
/sqlcalls per account per minute, inside the Pro 1,000 requests per minute cap. The next call in that minute is429RATE_LIMITEDwithreasonsql_rate_limit. - One running
/sqlper account on hosted. The lease expires after 15 seconds. If one is already running, the call is429SQL_BUSYwithRetry-After. - When
rows_readexceeds 5,000,000, that account’s/sqlis blocked for 60 seconds. The next call is429SQL_COOLDOWNwithRetry-After. - Monthly budget stays 100,000,000 rows read per account on
/sql. The next call is429SQL_BUDGET. - The HTTP call times out after 10 seconds. That response is
504SQL_TIMEOUT. The query may still finish inside the database. - Hosted returns
503SQL_UNAVAILABLEwhenCF_D1_READ_TOKENis not configured./sqlnever uses the edit token. The database id comes from the authenticated account. The client does not send one.
Errors
Section titled “Errors”| Status | Code | When |
|---|---|---|
| 400 | INVALID_SQL |
The statement breaks the rules below, or the database returns a SQL error. The message says why. This is not 500. |
| 400 | INVALID_WINDOW |
days or the date pair is outside the window rules. |
| 400 | MISSING_FIELDS |
project or sql is missing. |
| 403 | PRO_REQUIRED |
Free account. Not metered. |
| 429 | RATE_LIMITED |
More than 10 /sql calls for this account in a minute. The body includes reason sql_rate_limit. |
| 429 | SQL_BUSY |
A /sql query is already running for this account. Retry-After is set. The lease expires after 15 seconds. |
| 429 | SQL_COOLDOWN |
This account read more than 5,000,000 rows. /sql is blocked for 60 seconds. Retry-After is set. |
| 429 | SQL_BUDGET |
This account is over 100,000,000 rows read on /sql for the month. That budget is unchanged. |
| 429 | SQL_UPSTREAM_RATE_LIMITED |
Hosted only. The Cloudflare D1 API returned 429. The call is not retried. Retry-After is Cloudflare’s value, or 60. |
| 503 | SQL_UNAVAILABLE |
Hosted read-only D1 token CF_D1_READ_TOKEN is not configured. |
| 504 | SQL_TIMEOUT |
The HTTP call timed out after 10 seconds. The query may still finish inside the database. |
Tables
Section titled “Tables”The server exposes two tables, and only that project’s rows. The project name is a bound parameter. It is not interpolated into the SQL.
events columns, in order:
id, event, properties, user_id, session_id, timestamp, date, country
properties is a JSON string. Use json_extract in the statement. resolved_user_id is not a column. project_id is not a column of this events table.
sessions columns, in order:
session_id, user_id, project_id, start_time, end_time, duration, entry_page, exit_page, event_count, is_bounce, date
sessions.project_id is the project name.
This column list is a public contract. Adding a column later is allowed. Removing or renaming a column is a breaking change. Identity tables and the email lookup index are not part of this contract.
Rejected:
- More than one statement.
- Bind parameters (
?,:name,@name, or$name). - A write or DDL keyword outside a string literal:
INSERT,UPDATE,DELETE,REPLACE,ALTER,DROP,CREATE,ATTACH,DETACH,PRAGMA,VACUUM,REINDEX. The same word inside a string literal is allowed. - A schema-qualified name such as
main.eventsortemp.sessions. sqlite_master,sqlite_schema,sqlite_temp_master, orsqlite_temp_schema.- A table-valued function.
- A non-ASCII character outside a string literal.
- Any table other than
eventsandsessions. A nestedWITHis allowed only when every table it names iseventsorsessions. - A recursive CTE.
- A second reference to
events, or a second reference tosessions. One join ofeventstosessionsis allowed. A self join is not. - After
FROM,JOIN, orIN, a name other thanevents,sessions, a CTE name, or a parenthesized subquery or list. - A
LIMITthat is not a plain integer. randombloborzeroblob.
There is no GET /sql/schema. The column list above is the schema.
The hosted MCP tool is run_sql. readOnlyHint is true. It is not registered on the demo MCP.
The tool description lists events and sessions and the columns above, and it tells the agent to use query or analytics_breakdown first.
query group-by values are strings matching ^(event|date|user_id|session_id|country|properties\.[A-Za-z0-9_]{1,128})$.
Pass the statement with --sql or -f. Do not build the statement by concatenating untrusted shell input.
aa sql my-site --sql 'SELECT event, COUNT(*) AS n FROM events GROUP BY event'aa sql my-site -f file.sqlPrefer query for a property group:
aa query my-site --metrics event_count --group-by properties.path --filter '[{"field":"event","op":"eq","value":"signup"}]'Self-hosted
Section titled “Self-hosted”The OSS server exposes the same POST /sql route and the same column list. Auth is the read API key. OSS returns rows as objects, the same as hosted. OSS applies the time window, the join limit, and the 10 second timeout only. It does not apply SQL_BUSY, the 10 calls per minute cap, SQL_COOLDOWN, SQL_UPSTREAM_RATE_LIMITED, or the monthly rows read budget.
Node and SQLite open a read-only connection. Cloudflare Workers OSS checks the statement with the parser only, then runs it on the writable D1 binding. That Workers path is weaker. See Self-hosted OSS server.
