Skip to content

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.

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.

Pro only. A free account receives 403 PRO_REQUIRED. That response is not metered.

One accepted call counts as one metered read.

  • One SELECT statement. A statement that starts with WITH is rejected. A nested WITH is allowed only when every table it names is events or sessions. A recursive CTE is rejected. A subquery inside the SELECT is allowed.
  • Each of events and sessions may be referenced at most once. One join of events to sessions is allowed. A self join is not. A second reference is 400 INVALID_SQL, and the message says why.
  • After FROM, JOIN, or IN, only events, 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 appends LIMIT 1001, returns at most 1,000 rows, and sets truncated to true when that extra row existed. A user LIMIT must be a plain integer. LIMIT 1000+500 is 400 INVALID_SQL. A user LIMIT above 1,000 is capped to 1,000.
  • 10 /sql calls per account per minute, inside the Pro 1,000 requests per minute cap. The next call in that minute is 429 RATE_LIMITED with reason sql_rate_limit.
  • One running /sql per account on hosted. The lease expires after 15 seconds. If one is already running, the call is 429 SQL_BUSY with Retry-After.
  • When rows_read exceeds 5,000,000, that account’s /sql is blocked for 60 seconds. The next call is 429 SQL_COOLDOWN with Retry-After.
  • Monthly budget stays 100,000,000 rows read per account on /sql. The next call is 429 SQL_BUDGET.
  • The HTTP call times out after 10 seconds. That response is 504 SQL_TIMEOUT. The query may still finish inside the database.
  • Hosted returns 503 SQL_UNAVAILABLE when CF_D1_READ_TOKEN is not configured. /sql never uses the edit token. The database id comes from the authenticated account. The client does not send one.
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.

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.events or temp.sessions.
  • sqlite_master, sqlite_schema, sqlite_temp_master, or sqlite_temp_schema.
  • A table-valued function.
  • A non-ASCII character outside a string literal.
  • Any table other than events and sessions. A nested WITH is allowed only when every table it names is events or sessions.
  • A recursive CTE.
  • A second reference to events, or a second reference to sessions. One join of events to sessions is allowed. A self join is not.
  • After FROM, JOIN, or IN, a name other than events, sessions, a CTE name, or a parenthesized subquery or list.
  • A LIMIT that is not a plain integer.
  • randomblob or zeroblob.

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.

Terminal window
aa sql my-site --sql 'SELECT event, COUNT(*) AS n FROM events GROUP BY event'
aa sql my-site -f file.sql

Prefer query for a property group:

Terminal window
aa query my-site --metrics event_count --group-by properties.path --filter '[{"field":"event","op":"eq","value":"signup"}]'

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.