> ## Documentation Index > Fetch the complete documentation index at: https://docs.macro.com/llms.txt > Use this file to discover all available pages before exploring further. # QueryDatabase > Run SQL against the current user's Macro databases — the only way to read or change their rows. SELECT to answer a question, INSERT/UPDATE/DELETE to change data. One statement per call. **Every table the user can see is already in scope, across all of their databases.** The statement runs as the user against exactly what they are allowed to read: a table they cannot see simply does not exist, and a table they only have view access to is read-only. Always pass `databaseId` for the database the statement is about, so its tables win name ties. **Call DescribeDatabase first unless you already know the exact table and column names.** Names are the display names the user typed, so quote the ones with spaces. If a statement fails, the error names what was wrong and suggests the closest name — read it, fix it, retry. ## Dialect A small SQL subset, compiled by Macro rather than run by a SQL engine. What is listed here is everything there is: - **Reads:** `SELECT [DISTINCT] items FROM [database.]table [alias] {[LEFT] JOIN [database.]table [alias] ON a.col = b.col [AND ...]} [WHERE cond] [GROUP BY col] [ORDER BY col|alias|position [ASC|DESC], ...] [LIMIT n [OFFSET m]]`. Items are `*`, columns, or `COUNT(*)`, `COUNT(col)`, `SUM(col)`, `AVG(col)`, `MIN(col)`, `MAX(col)`, each optionally named with `AS name`; the alias names the result column and can be ordered by. GROUP BY takes one column, and a grouped SELECT lists only that column beside its aggregates. No other expressions or functions, no HAVING, and WHERE compares a column only with a literal. - **Joins** are how tables combine: - **What joins:** each `ON` pairs a column of the newly joined table with one of a table already in the query, by `=` (`HAS` means the same), several pairs joined by AND. Both sides are the same kind: text with text (exact and case-sensitive), number with number, date with date; a relation, person or other entity column with `row_id` or another entity column, such as `macro.people.id`. Select columns match only when they share options. Tables may come from different databases, a table may join itself under another alias, and joins chain: `SELECT t."Name", d."Name", p.email FROM "Ops"."Tasks" t JOIN "Sales"."Deals" d ON t."Deal" = d.row_id JOIN macro.people p ON d."Owner" = p.id`. - **What comes back:** JOIN keeps only rows with a match; LEFT JOIN keeps every row of the earlier tables, with NULL in the joined table's columns where nothing matched. A multi-valued relation or person cell matches once per value, so a task with two assignees gives two rows, and an empty cell matches nothing. Aggregates count the joined rows: `SELECT p.name AS person, COUNT(*) AS tasks FROM "Tasks" t JOIN macro.people p ON t."Assignees" = p.id GROUP BY p.name ORDER BY tasks DESC`. To filter on a joined table's columns, use JOIN rather than LEFT JOIN. - **Refused:** any `ON` test but `=` or `HAS`, OR in `ON`, an `ON` within one table, RIGHT, FULL and comma joins. - **Row cap:** each table a query reads stops at 20,000 rows and is then listed in `truncatedTables`, so any total over it is partial. Only `=`, IN and HAS tests on select, person, entity and relation columns (not `row_id`) narrow what a table reads; other conditions apply to the rows read. A joined table reads only the rows matching the values the earlier rows join on, up to 100 distinct values; past that it reads whole. - **No subqueries** (`IN (SELECT ...)`): SELECT the ids first, then use them as literals (`WHERE row_id IN ('', '')`), or JOIN. - **Conditions:** `col = | != | < | <= | > | >= literal`, `col [NOT] IN ('a', 'b')`, `col [NOT] LIKE '%pat%'` (case-insensitive), `col IS [NOT] NULL`, `col [NOT] HAS 'x'` (membership in a multi-valued column), combined with AND, OR and parentheses. - **Literals:** `'text'` (a quote inside is doubled: `'Wolf''s place'`), numbers, TRUE/FALSE, NULL; dates are `'2026-08-13'` or an ISO date-time. - **Writes:** `INSERT INTO table (col, ...) VALUES (...), (...)` or `INSERT INTO table DEFAULT VALUES`; `UPDATE table SET col = value, ... WHERE cond`; `DELETE FROM table WHERE cond`. The WHERE is required and takes any condition; `WHERE row_id = ''` or `row_id IN ('', ...)` names rows, and every id named must exist: read the ids first. `SET col = other_col` copies each row's own value of a column of the same kind. A multi-valued cell is written as a list, `['Urgent', 'Backend']`; `NULL` clears a cell. - **`row_id`** is every row's id. A row-shaped SELECT returns each result row's in `rowIds`, and an INSERT the new rows' in `insertedRowIds`; never invent one. A row's title is its first column, and a row the app shows as "Unnamed" has a NULL title: find it with `WHERE "Name" IS NULL`. - **Names are display names.** Double-quote a table or column name with spaces or punctuation (`FROM "Guest List" WHERE "Due Date" < '2026-09-01'`); names match case-insensitively, and a miss suggests the closest name. A table may be qualified by its database's name (`FROM "Offsite"."Guests"`). - **Select and tag columns take option labels** (`"Status" = 'Going'`), never option ids. Only labels the column carries are accepted; add new ones with AddColumnOptions. - **Relations hold the row ids of another table.** Write a list of row ids (`"Guests" = ['']`), test with `HAS ''`, and join with `ON i."Guest" = g.row_id`: `SELECT p."Name" AS party, COUNT(*) AS invites FROM "Invites" i JOIN "Parties" p ON i."Party" = p.row_id GROUP BY p."Name"`. Never compare a relation to a name. - **Person and other entity columns hold Macro ids,** never names: `'macro|sam@example.com'` for a person, a list for a multi-valued column. Respect each column's `specificEntityType`. - **People:** `macro.people` is everyone the user knows (contacts and teammates) with `id`, `name` and `email`, the viewer included: “me” is the row whose email is the signed-in user's. It is read-only. Find people there, then write their ids: `SELECT id, name, email FROM macro.people WHERE name LIKE '%julia%' OR email LIKE '%julia%'`, then `UPDATE "Parties" SET "Host" = 'macro|julia@example.com' WHERE row_id = ''`. Join to read names or emails: `JOIN macro.people p ON t."Owner" = p.id`. Never invent a person or an id; when a name matches several people or none, ask. - **Changing a column's type:** `ALTER TABLE table ALTER COLUMN col TYPE type`, the same change as ChangeColumnType, where type is text, number, boolean, date, link, select, select_number, tag, or entity(USER), entity(DOCUMENT), entity(TASK)…, with `[]` for several values (`select[]`). Pick from the column's `safeTypes` and `checkedTypes`. A value that does not fit, or a multi-valued cell a single-valued type would truncate, refuses the statement with counts and examples: fix those values with UPDATE, or add a new column. - **Other schema changes use tools, not SQL:** CreateDatabase, RenameDatabase, CreateTable, RenameTable, ReorderTables, DeleteTable, AddColumn, AddColumnOptions, RenameColumn, ChangeColumnType, DeleteColumn, ReorderColumns, SaveDatabaseView and DeleteDatabaseView. A board view groups by a single-select or single-person column. - Tables you only hold view access on are read-only. To change records, first SELECT the rows you mean (their ids are in `rowIds`), then UPDATE or DELETE exactly those with `WHERE row_id IN (...)`. After changing rows, SELECT the affected records to verify the actual result. On a connection failure, inspect before retrying an INSERT. To create a row and relate it in one go, INSERT it with the relation column set to the target row ids (`INSERT INTO invites (guest, status) VALUES ([''], 'Sent')`); the new row's id is in `insertedRowIds`. Results come back as columns and rows of typed cells (`{"type": "text", "value": "Sam"}`; `null` is an empty cell), with `rowIds`, the id of the row behind each result row of a row-shaped SELECT. Each column names its `kind`. A select column lists its `options`, and its cells hold option ids: read their labels there. An entity column names its `target`, which is how the app renders its ids as clickable chips — prefer selecting an entity column over stringifying it. A `row_id` column has kind `row`: its cells are `{"type": "row", "value": ""}`, rows of the table it names as `relatedTable`, not Macro entities. Writes report `changesApplied` and, for inserts, the `insertedRowIds` the server minted. To answer a question about the data or draw a chart for the user, check the SELECT here, then save it with SaveDatabaseQuery and paste the block it returns: it stays live, where a pasted result goes stale. # QueryDatabase Run SQL against the current user's Macro databases — the only way to read or change their rows. SELECT to answer a question, INSERT/UPDATE/DELETE to change data. One statement per call. **Every table the user can see is already in scope, across all of their databases.** The statement runs as the user against exactly what they are allowed to read: a table they cannot see simply does not exist, and a table they only have view access to is read-only. Always pass `databaseId` for the database the statement is about, so its tables win name ties. **Call DescribeDatabase first unless you already know the exact table and column names.** Names are the display names the user typed, so quote the ones with spaces. If a statement fails, the error names what was wrong and suggests the closest name — read it, fix it, retry. ## Dialect A small SQL subset, compiled by Macro rather than run by a SQL engine. What is listed here is everything there is: * **Reads:** `SELECT [DISTINCT] items FROM [database.]table [alias] {[LEFT] JOIN [database.]table [alias] ON a.col = b.col [AND ...]} [WHERE cond] [GROUP BY col] [ORDER BY col|alias|position [ASC|DESC], ...] [LIMIT n [OFFSET m]]`. Items are `*`, columns, or `COUNT(*)`, `COUNT(col)`, `SUM(col)`, `AVG(col)`, `MIN(col)`, `MAX(col)`, each optionally named with `AS name`; the alias names the result column and can be ordered by. GROUP BY takes one column, and a grouped SELECT lists only that column beside its aggregates. No other expressions or functions, no HAVING, and WHERE compares a column only with a literal. * **Joins** are how tables combine: * **What joins:** each `ON` pairs a column of the newly joined table with one of a table already in the query, by `=` (`HAS` means the same), several pairs joined by AND. Both sides are the same kind: text with text (exact and case-sensitive), number with number, date with date; a relation, person or other entity column with `row_id` or another entity column, such as `macro.people.id`. Select columns match only when they share options. Tables may come from different databases, a table may join itself under another alias, and joins chain: `SELECT t."Name", d."Name", p.email FROM "Ops"."Tasks" t JOIN "Sales"."Deals" d ON t."Deal" = d.row_id JOIN macro.people p ON d."Owner" = p.id`. * **What comes back:** JOIN keeps only rows with a match; LEFT JOIN keeps every row of the earlier tables, with NULL in the joined table's columns where nothing matched. A multi-valued relation or person cell matches once per value, so a task with two assignees gives two rows, and an empty cell matches nothing. Aggregates count the joined rows: `SELECT p.name AS person, COUNT(*) AS tasks FROM "Tasks" t JOIN macro.people p ON t."Assignees" = p.id GROUP BY p.name ORDER BY tasks DESC`. To filter on a joined table's columns, use JOIN rather than LEFT JOIN. * **Refused:** any `ON` test but `=` or `HAS`, OR in `ON`, an `ON` within one table, RIGHT, FULL and comma joins. * **Row cap:** each table a query reads stops at 20,000 rows and is then listed in `truncatedTables`, so any total over it is partial. Only `=`, IN and HAS tests on select, person, entity and relation columns (not `row_id`) narrow what a table reads; other conditions apply to the rows read. A joined table reads only the rows matching the values the earlier rows join on, up to 100 distinct values; past that it reads whole. * **No subqueries** (`IN (SELECT ...)`): SELECT the ids first, then use them as literals (`WHERE row_id IN ('', '')`), or JOIN. * **Conditions:** `col = | != | < | <= | > | >= literal`, `col [NOT] IN ('a', 'b')`, `col [NOT] LIKE '%pat%'` (case-insensitive), `col IS [NOT] NULL`, `col [NOT] HAS 'x'` (membership in a multi-valued column), combined with AND, OR and parentheses. * **Literals:** `'text'` (a quote inside is doubled: `'Wolf''s place'`), numbers, TRUE/FALSE, NULL; dates are `'2026-08-13'` or an ISO date-time. * **Writes:** `INSERT INTO table (col, ...) VALUES (...), (...)` or `INSERT INTO table DEFAULT VALUES`; `UPDATE table SET col = value, ... WHERE cond`; `DELETE FROM table WHERE cond`. The WHERE is required and takes any condition; `WHERE row_id = ''` or `row_id IN ('', ...)` names rows, and every id named must exist: read the ids first. `SET col = other_col` copies each row's own value of a column of the same kind. A multi-valued cell is written as a list, `['Urgent', 'Backend']`; `NULL` clears a cell. * **`row_id`** is every row's id. A row-shaped SELECT returns each result row's in `rowIds`, and an INSERT the new rows' in `insertedRowIds`; never invent one. A row's title is its first column, and a row the app shows as "Unnamed" has a NULL title: find it with `WHERE "Name" IS NULL`. * **Names are display names.** Double-quote a table or column name with spaces or punctuation (`FROM "Guest List" WHERE "Due Date" < '2026-09-01'`); names match case-insensitively, and a miss suggests the closest name. A table may be qualified by its database's name (`FROM "Offsite"."Guests"`). * **Select and tag columns take option labels** (`"Status" = 'Going'`), never option ids. Only labels the column carries are accepted; add new ones with AddColumnOptions. * **Relations hold the row ids of another table.** Write a list of row ids (`"Guests" = ['']`), test with `HAS ''`, and join with `ON i."Guest" = g.row_id`: `SELECT p."Name" AS party, COUNT(*) AS invites FROM "Invites" i JOIN "Parties" p ON i."Party" = p.row_id GROUP BY p."Name"`. Never compare a relation to a name. * **Person and other entity columns hold Macro ids,** never names: `'macro|sam@example.com'` for a person, a list for a multi-valued column. Respect each column's `specificEntityType`. * **People:** `macro.people` is everyone the user knows (contacts and teammates) with `id`, `name` and `email`, the viewer included: “me” is the row whose email is the signed-in user's. It is read-only. Find people there, then write their ids: `SELECT id, name, email FROM macro.people WHERE name LIKE '%julia%' OR email LIKE '%julia%'`, then `UPDATE "Parties" SET "Host" = 'macro|julia@example.com' WHERE row_id = ''`. Join to read names or emails: `JOIN macro.people p ON t."Owner" = p.id`. Never invent a person or an id; when a name matches several people or none, ask. * **Changing a column's type:** `ALTER TABLE table ALTER COLUMN col TYPE type`, the same change as ChangeColumnType, where type is text, number, boolean, date, link, select, select\_number, tag, or entity(USER), entity(DOCUMENT), entity(TASK)…, with `[]` for several values (`select[]`). Pick from the column's `safeTypes` and `checkedTypes`. A value that does not fit, or a multi-valued cell a single-valued type would truncate, refuses the statement with counts and examples: fix those values with UPDATE, or add a new column. * **Other schema changes use tools, not SQL:** CreateDatabase, RenameDatabase, CreateTable, RenameTable, ReorderTables, DeleteTable, AddColumn, AddColumnOptions, RenameColumn, ChangeColumnType, DeleteColumn, ReorderColumns, SaveDatabaseView and DeleteDatabaseView. A board view groups by a single-select or single-person column. * Tables you only hold view access on are read-only. To change records, first SELECT the rows you mean (their ids are in `rowIds`), then UPDATE or DELETE exactly those with `WHERE row_id IN (...)`. After changing rows, SELECT the affected records to verify the actual result. On a connection failure, inspect before retrying an INSERT. To create a row and relate it in one go, INSERT it with the relation column set to the target row ids (`INSERT INTO invites (guest, status) VALUES ([''], 'Sent')`); the new row's id is in `insertedRowIds`. Results come back as columns and rows of typed cells (`{"type": "text", "value": "Sam"}`; `null` is an empty cell), with `rowIds`, the id of the row behind each result row of a row-shaped SELECT. Each column names its `kind`. A select column lists its `options`, and its cells hold option ids: read their labels there. An entity column names its `target`, which is how the app renders its ids as clickable chips — prefer selecting an entity column over stringifying it. A `row_id` column has kind `row`: its cells are `{"type": "row", "value": ""}`, rows of the table it names as `relatedTable`, not Macro entities. Writes report `changesApplied` and, for inserts, the `insertedRowIds` the server minted. To answer a question about the data or draw a chart for the user, check the SELECT here, then save it with SaveDatabaseQuery and paste the block it returns: it stays live, where a pasted result goes stale. ## Parameters | Parameter | Type | Required | Description | | - | - | - | - | | `sql` | string | Yes | The statement to run, as one string. Use the table and column names DescribeDatabase reported, quoted when they have spaces. | | `databaseId` | string | No | Id of the database the statement is about, from ListDatabases or DescribeDatabase. Pass it whenever you know it: this database's tables take precedence when another database has a table of the same name. Tables of other databases stay reachable for joins. | | `baseVersions` | ToolTableVersion\[] | No | Optional versions from a previous QueryDatabase read. Reject the write if a listed table being written changed. Read-only dependencies are not guarded; omit for a read or intentional blind edit. | | `display` | `"table"` \| `"scalar"` \| `"bar"` \| `"line"` \| `"area"` \| `"scatter"` \| `"pie"` | No | Preferred native result presentation. For an explicit chart request, select bar, line, area, scatter or pie and return a label column plus numeric values. The app falls back to a table if the data cannot support that display. | This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.