# Database

> Run SQL, atomic batches, inspect tables and manage branches.

Source: https://useprimitives.com/docs/api/database

These routes apply to a primitive of type `db`. See [Database](/docs/primitives/database) for how databases, branches and SQL rules work. Calling one of them on a primitive of another type answers `400 wrong_type`.

| Method | Path | Description |
| --- | --- | --- |
| `POST` | `/v1/primitives/{id}/query` | Run one statement |
| `POST` | `/v1/primitives/{id}/exec` | Alias of `query` |
| `POST` | `/v1/primitives/{id}/batch` | Run statements in one transaction |
| `GET` | `/v1/primitives/{id}/tables` | List tables and views |
| `GET` | `/v1/primitives/{id}/tables/{table}` | Get the columns and SQL of one table |
| `POST` | `/v1/primitives/{id}/branches` | Create a branch |
| `GET` | `/v1/primitives/{id}/branches` | List branches |
| `POST` | `/v1/primitives/{id}/branches/{branch}/promote` | Promote a branch over the main database |
| `DELETE` | `/v1/primitives/{id}/branches/{branch}` | Discard a branch |
| `GET` | `/v1/primitives/{id}/export` | Download the database as a SQLite file |

Every route answers `404 not_found` for a primitive that doesn't exist or is outside the API key's group, and `423 paused` while the primitive is paused. Timestamps are milliseconds since the Unix epoch. See [Errors](/docs/api/errors) for the full list of codes.

## Run a statement

`POST /v1/primitives/{id}/query`

Runs exactly one SQL statement in its own transaction and returns a QueryResult. `POST /v1/primitives/{id}/exec` is an alias with the same body and response.

At most 10,000 rows are returned. Beyond that the rest are dropped and `truncated` is `true`. A write with `RETURNING` still runs to completion. `rows_affected` and `last_insert_rowid` are only set by writes; reads return `0` and `null`.

Parameters are a positional array for `?` placeholders, or an object for named ones (`:name`, `@name` or `$name`; the key may leave out the prefix). The count must match the statement's placeholders. Values map to SQLite as described in [Values](/docs/primitives/database#values): a blob is `{ "$blob": "<base64>" }`, both in parameters and in returned rows.

Errors: `400 invalid_request` for a syntax error, an empty `sql` or mismatched parameters; `403 forbidden` for SQL the engine rejects (`ATTACH`, `BEGIN`, `_primitives_*` tables, and the others listed in [What SQL is allowed](/docs/primitives/database#what-sql-is-allowed)), or for a write through a key without `db_exec`; `404 not_found` if `branch` doesn't exist; `409 conflict` for a constraint violation.

Key tool: any db tool (`db_query`, `db_exec` or `db_branch`). Statements that write need `db_exec`.

**Body**

| Parameter | Type | Description |
| --- | --- | --- |
| `sql` | string | Required. One SQL statement. |
| `params` | array \| object | Positional values for `?`, or an object of named values. |
| `branch` | string | Run on this branch instead of the main database. |

```bash
curl -X POST https://api.useprimitives.com/v1/primitives/01jbz3k8m5q2w7x9v4n6t0r1ce/query \
  -H "Authorization: Bearer $PRIMITIVES_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql": "select id, title from tasks where status = ?", "params": ["open"]}'
```

```json
{
  "columns": ["id", "title"],
  "rows": [
    { "id": 1, "title": "Write docs" },
    { "id": 4, "title": "Ship branches" }
  ],
  "rows_affected": 0,
  "last_insert_rowid": null,
  "truncated": false
}
```

A write with named parameters:

```bash
curl -X POST https://api.useprimitives.com/v1/primitives/01jbz3k8m5q2w7x9v4n6t0r1ce/exec \
  -H "Authorization: Bearer $PRIMITIVES_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql": "insert into tasks (title, status) values (:title, :status)", "params": {"title": "Review PR", "status": "open"}}'
```

```json
{
  "columns": [],
  "rows": [],
  "rows_affected": 1,
  "last_insert_rowid": 5,
  "truncated": false
}
```

## Run a batch

`POST /v1/primitives/{id}/batch`

Runs up to 1,000 statements in one transaction. If any statement fails, none are applied, and the error message starts with the failing statement's zero-based index, such as `statement 1: …` for the second one. Returns one QueryResult per statement, in order.

An empty `statements` array, or more than 1,000, is `400 invalid_request`. Otherwise the errors are the same as for a single statement.

Key tool: any db tool. A batch with a statement that writes needs `db_exec`.

**Body**

| Parameter | Type | Description |
| --- | --- | --- |
| `statements` | array | Required. 1–1,000 objects of `{ sql, params? }`, with the same meaning as in [Run a statement](#run-a-statement). |
| `branch` | string | Run on this branch instead of the main database. |

```bash
curl -X POST https://api.useprimitives.com/v1/primitives/01jbz3k8m5q2w7x9v4n6t0r1ce/batch \
  -H "Authorization: Bearer $PRIMITIVES_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "statements": [
      { "sql": "update accounts set balance = balance - ? where id = ?", "params": [25, 1] },
      { "sql": "update accounts set balance = balance + ? where id = ?", "params": [25, 2] }
    ]
  }'
```

```json
{
  "results": [
    { "columns": [], "rows": [], "rows_affected": 1, "last_insert_rowid": null, "truncated": false },
    { "columns": [], "rows": [], "rows_affected": 1, "last_insert_rowid": null, "truncated": false }
  ]
}
```

## List tables

`GET /v1/primitives/{id}/tables`

Lists the tables and views of the main database or a branch, sorted by name. Engine tables (`_primitives_*`) and SQLite's own (`sqlite_*`) are left out. `rows` stops counting at 1,000,000 and is `null` for views.

The response uses the [list envelope](/docs/api#pagination) but always comes in one page: `next_cursor` is `null`.

Key tool: any db tool.

**Query parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `branch` | string | List the tables of this branch instead of the main database. |

```bash
curl https://api.useprimitives.com/v1/primitives/01jbz3k8m5q2w7x9v4n6t0r1ce/tables \
  -H "Authorization: Bearer $PRIMITIVES_API_KEY"
```

```json
{
  "data": [
    {
      "name": "open_tasks",
      "type": "view",
      "sql": "CREATE VIEW open_tasks AS SELECT * FROM tasks WHERE status = 'open'",
      "rows": null
    },
    {
      "name": "tasks",
      "type": "table",
      "sql": "CREATE TABLE tasks (id integer primary key, title text not null, status text default 'open')",
      "rows": 42
    }
  ],
  "next_cursor": null
}
```

## Get a table

`GET /v1/primitives/{id}/tables/{table}`

Returns the columns and SQL of one table or view as a TableSchema. The name matches case-insensitively, and the response carries its stored spelling. A table that doesn't exist, or an engine or SQLite table, answers `404 not_found`.

Key tool: any db tool.

**Query parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `branch` | string | Read the table from this branch instead of the main database. |

```bash
curl https://api.useprimitives.com/v1/primitives/01jbz3k8m5q2w7x9v4n6t0r1ce/tables/tasks \
  -H "Authorization: Bearer $PRIMITIVES_API_KEY"
```

```json
{
  "name": "tasks",
  "type": "table",
  "columns": [
    { "name": "id", "type": "INTEGER", "notnull": false, "default": null, "pk": true },
    { "name": "title", "type": "TEXT", "notnull": true, "default": null, "pk": false },
    { "name": "status", "type": "TEXT", "notnull": false, "default": "'open'", "pk": false }
  ],
  "sql": "CREATE TABLE tasks (id integer primary key, title text not null, status text default 'open')"
}
```

`type` on a column is the declared type as written in the `CREATE TABLE`, and `default` is the default's SQL text.

## Create a branch

`POST /v1/primitives/{id}/branches`

Copies the main database into a new branch. Query the branch by passing its name as `branch` to the routes above.

Branch names are 1–63 characters from `a-z`, `0-9`, `-`, `_` and `.`, starting with a letter or digit; another name is `400 invalid_request`. A branch with that name already existing is `409 already_exists`.

Key tool: `db_branch`.

**Body**

| Parameter | Type | Description |
| --- | --- | --- |
| `name` | string | Required. The branch name. |

```bash
curl -X POST https://api.useprimitives.com/v1/primitives/01jbz3k8m5q2w7x9v4n6t0r1ce/branches \
  -H "Authorization: Bearer $PRIMITIVES_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"name": "try-migration"}'
```

Returns `201` with the BranchInfo:

```json
{
  "name": "try-migration",
  "created_at": 1760486400000,
  "base": 17
}
```

## List branches

`GET /v1/primitives/{id}/branches`

Lists the database's branches, oldest first. The response uses the [list envelope](/docs/api#pagination) but always comes in one page.

Key tool: `db_branch`.

```bash
curl https://api.useprimitives.com/v1/primitives/01jbz3k8m5q2w7x9v4n6t0r1ce/branches \
  -H "Authorization: Bearer $PRIMITIVES_API_KEY"
```

```json
{
  "data": [
    { "name": "try-migration", "created_at": 1760486400000, "base": 17 }
  ],
  "next_cursor": null
}
```

## Promote a branch

`POST /v1/primitives/{id}/branches/{branch}/promote`

Replaces the main database with the branch. The branch is removed afterwards. The body is optional.

If the main database was written after the branch was created, promote fails with `409 branch_conflict` and nothing changes. Pass `force: true` to promote anyway, discarding those writes. A branch that doesn't exist is `404 not_found`.

Key tool: `db_branch`.

**Body**

| Parameter | Type | Description |
| --- | --- | --- |
| `force` | boolean | Promote even if the main database changed since the branch was created. Default `false`. |

```bash
curl -X POST https://api.useprimitives.com/v1/primitives/01jbz3k8m5q2w7x9v4n6t0r1ce/branches/try-migration/promote \
  -H "Authorization: Bearer $PRIMITIVES_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"force": false}'
```

```json
{ "promoted": "try-migration" }
```

## Discard a branch

`DELETE /v1/primitives/{id}/branches/{branch}`

Deletes the branch without touching the main database. A branch that doesn't exist is `404 not_found`.

Key tool: `db_branch`.

```bash
curl -X DELETE https://api.useprimitives.com/v1/primitives/01jbz3k8m5q2w7x9v4n6t0r1ce/branches/try-migration \
  -H "Authorization: Bearer $PRIMITIVES_API_KEY"
```

Returns `204` with no body.

## Export a database

`GET /v1/primitives/{id}/export`

Downloads a consistent copy of the main database as a SQLite file you can open with any SQLite tool. Branches aren't included. The file is named `<group>-<name>.sqlite`. When too many exports are already running, the call answers `429 rate_limited`; retry shortly. This route also serves KV and storage primitives; see [Primitives](/docs/api/primitives).

Key tool: `db_query`.

```bash
curl -o tasks.sqlite https://api.useprimitives.com/v1/primitives/01jbz3k8m5q2w7x9v4n6t0r1ce/export \
  -H "Authorization: Bearer $PRIMITIVES_API_KEY"
```

The response is the raw file, not JSON:

```http
HTTP/1.1 200 OK
Content-Type: application/vnd.sqlite3
Content-Disposition: attachment; filename="agent-42-tasks.sqlite"
Content-Length: 245760
```

## Shapes

| Shape | Fields |
| --- | --- |
| QueryResult | `{ columns, rows, rows_affected, last_insert_rowid, truncated }`. `columns` is an array of names. `rows` is an array of objects keyed by column name. `rows_affected` is a number. `last_insert_rowid` is a number, or `null` unless the statement inserted a row. `truncated` is `true` when more than 10,000 rows matched |
| TableInfo | `{ name, type, sql, rows }`. `type` is `"table"` or `"view"`. `sql` is the `CREATE` statement, or `null`. `rows` is a count capped at 1,000,000, or `null` for a view |
| TableSchema | `{ name, type, columns, sql }`. `columns` is an array of ColumnInfo, in table order |
| ColumnInfo | `{ name, type, notnull, default, pk }`. `type` is the declared type (may be empty). `notnull` and `pk` are booleans. `default` is the default's SQL text, or `null` |
| BranchInfo | `{ name, created_at, base }`. `base` is the main database's write counter when the branch was created; promote compares it to the current counter |

Scoped API keys need the tool named in each section on their allowlist; see [Tool allowlists](/docs/api/authentication#tool-allowlists). For creating, snapshotting, forking and pausing the database itself, see [Primitives](/docs/api/primitives). See [Limits](/docs/api/limits) for how operations are counted.