# Query

Source: https://www.hotdata.dev/docs/api-reference/query
Site index: https://www.hotdata.dev/llms.txt

Execute SQL queries against connected data sources. Use standard Postgres-compatible SQL syntax to query and join across multiple connections. Results are returned inline and also persisted asynchronously for later retrieval.

## Execute SQL query

`POST /v1/query`

Execute a SQL query scoped to a database. A database is the only window into catalogs: the query sees only that database's auto `default` catalog plus any catalogs explicitly attached to it. Select the database with EITHER the `X-Database-Id` header OR the `database_id` body field (exactly one must be given; if both are sent and disagree, that's a 400). Use standard Postgres-compatible SQL; reference the default catalog as `default.<schema>.<table>` (or just `<schema>.<table>` / `<table>`) and attached catalogs by their alias. Results are returned inline and a `result_id` is provided for later retrieval via the Results API.

Set `async: true` to execute asynchronously — returns a query run ID for polling. Optionally set `async_after_ms` to attempt synchronous execution first, falling back to async if the query exceeds the timeout.

**Headers**

- `X-Database-Id` `string,null` — Database id to scope the query to. Required unless the `database_id` body field is set; if both are present they must match. Only that database's catalogs are visible during planning. A malformed value is a 400; an unknown database id is a 404.

**Request body**

- `async` `boolean` — When true, execute the query asynchronously and return a query run ID for polling via GET /query-runs/{id}. The query results can be retrieved via GET /results/{id} once the query run status is "succeeded". Default: `false`
- `async_after_ms` `integer,null` — If set (requires `async` = true), first attempt the query synchronously and wait up to this many milliseconds: if it finishes in time the full result is returned, otherwise an async response (a run id to poll) is returned. Must be at least 1000 and at most the server's configured maximum; a value out of that range, or set without `async` = true, is rejected with 400. Min: `1000`
- `database_id` `string,null` — Database to scope the query to (its id). Alternative to the `X-Database-Id` header — exactly one source must be provided. If both this field and the header are set and they disagree, the request is rejected with a 400.
- `default_catalog` `string,null` — Catalog that unqualified table references resolve against within the query's database scope. Must name a catalog visible in the database (`default`, an attached catalog alias, or a system catalog). Defaults to `default` when omitted.
- `default_schema` `string,null` — Schema that unqualified table references resolve against within the query's database scope. Defaults to `main` when omitted. Existence is not validated up front — an unknown schema surfaces as a "table not found" error at planning time.
- `dialect` `string,null` — SQL dialect the `sql` field is written in. One of `hotsql` (the default), `duckdb`, `postgres`, or `snowflake`. When set to anything other than `hotsql`, the query is translated to HotSQL before it runs, so you can use idioms from that dialect (for example Snowflake `IFF(...)` or Postgres `MOD(a, b)`). Only read-only queries are accepted. An unrecognized value is rejected with a 400.
- `sql` `string` — **required**

```json
{
  "async": false,
  "database_id": "dbid6lguax1dxn9y1xj5gxnameyywl",
  "default_catalog": "default",
  "default_schema": "main",
  "dialect": "hotsql",
  "sql": "SELECT customer_id, sum(amount) AS total FROM orders GROUP BY customer_id ORDER BY total DESC LIMIT 10"
}
```

**Response** `200` — Query executed successfully

- `columns` `string`[] — **required**
- `execution_time_ms` `integer` — **required**. Min: `0`
- `nullable` `boolean`[] — **required**. Nullable flags for each column (parallel to columns vec). True if the column allows NULL values, false if NOT NULL.
- `preview_row_count` `integer` — **required**. Number of rows in *this* response body. Always present. For a large result this is a bounded preview, not the grand total — see `total_row_count` and `truncated`.
- `query_run_id` `string` — **required**. Unique identifier for the query run record (qrun...).
- `result_id` `string,null` — Unique identifier for retrieving this result via GET /results/{id}. When non-null, the result is being persisted asynchronously. Null only when the result fit entirely in this response (`truncated: false`) but could not be persisted for later retrieval — see the `warning` field. A `truncated: true` response ALWAYS carries a non-null, resolvable `result_id`: a truncated result that cannot be persisted fails the request with a retryable HTTP 503 (`PERSISTENCE_UNAVAILABLE`, with a `Retry-After` header) rather than returning a partial body with a dead ticket.
- `row_count` `integer` — **required**. **Deprecated** — use `preview_row_count` (rows in this body) and `total_row_count` (grand total) instead. Retained as a back-compat alias and always equal to `preview_row_count`; for a truncated result it is the preview count, *not* the grand total — read `total_row_count` for that. Will be removed in a future release once clients migrate. Min: `0`
- `rows` `any`[][] — **required**. Array of rows, where each row is an array of column values. Values can be strings, numbers, booleans, or null.
- `total_row_count` `integer,null` — Grand total rows in the full result. Present (and equal to `preview_row_count`) when the whole result fit in this response; `null` while a truncated result is still being persisted. When `null`, read the authoritative total from `GET /v1/query-runs/{id}` (`row_count`) or the `X-Total-Row-Count` header on `GET /v1/results/{id}`.
- `truncated` `boolean` — **required**. True when `rows` is a bounded preview of a larger result. Fetch the full result via `result_id`.
- `warning` `string,null` — Warning message if result persistence could not be initiated. Present only when the full result is returned inline (`truncated: false`) but could not be persisted: `result_id` is then null and the result cannot be re-fetched later, though every row is in this response. A truncated result never carries a warning — if it cannot be persisted the request fails with a retryable HTTP 503 (`PERSISTENCE_UNAVAILABLE`, with a `Retry-After` header) instead.

```json
{
  "columns": [
    "string"
  ],
  "execution_time_ms": 0,
  "nullable": [
    true
  ],
  "preview_row_count": 0,
  "query_run_id": "string",
  "result_id": "string",
  "row_count": 0,
  "rows": [
    [
      null
    ]
  ],
  "total_row_count": 0,
  "truncated": true,
  "warning": "string"
}
```

**Response** `202` — Query submitted asynchronously

- `query_run_id` `string` — **required**. Unique identifier for the query run.
- `reason` `string,null` — Human-readable reason why the query went async (e.g., caching tables for the first time).
- `status` `string` — **required**. Current status of the query run.
- `status_url` `string` — **required**. URL to poll for query run status. Requires the same `X-Database-Id` header used to submit the query.

```json
{
  "query_run_id": "string",
  "reason": "string",
  "status": "string",
  "status_url": "string"
}
```

**Errors**

| Status | Description |
| ------ | ----------- |
| `400` | Invalid request (no database specified, or header/body database_id conflict) |
| `404` | Database not found |
| `429` | The engine was too busy to run this query right now — too many concurrent queries, or not enough memory available (often because of other queries running at the same time). Retry after the Retry-After delay; if it persists, narrowing the query (add a filter or LIMIT) may help. |
| `500` | Internal server error |
| `503` | Result store temporarily unavailable (a truncated result could not be persisted); retry after the Retry-After delay |