native-sql

# Native SQL **Prefer a structured query.** Native SQL is a Metabase query whose single stage is raw SQL (`mbql.stage/native`) instead of structured MBQL (`mbql.stage/mbql`) — both are the same query envelope (`mbql`). Reach for a native stage only when a structured query genuinely can't express it — engine-specific functions, CTEs, window logic beyond `offset`, hairy hand-tuned SQL — or when the user asks for SQL. If you can write it as a structured stage, do. General flag conventions, body-input precedence and `./.scratch` live in `core` (`mb skills get core`). ## The shape A native `dataset_query` is a query with one **native stage** — the `lib/type: "mbql/query"` envelope, a numeric `database`, and a single `mbql.stage/native` stage carrying the SQL string (`native`) plus a `template-tags` map: ```json { "lib/type": "mbql/query", "database": 1, "stages": [ { "lib/type": "mbql.stage/native", "native": "SELECT count(*) FROM orders WHERE {{status}} AND total > {{min_total}}", "template-tags": { "status": { … }, "min_total": { … } } } ] } ``` This is the form a card stores and returns — author it. The CLI **pre-flight-validates** it — the envelope, the template-tag shapes, the field refs — through the usual `--print-schema → --dry-run → run` loop (`mbql`), and a saved card **round-trips** in exactly this shape: `mb card get <id> --full --json`, edit the `stages[0].native` string, send it straight back. Only the warehouse parses the SQL string, so a **SQL** syntax error surfaces when you run it, not at `--dry-run`. A parameterless query needs no `template-tags` — just the `native` string. You may see an older flat form in cards created long ago — `{database, type:"native", native:{query}}`. The server still accepts it (it normalizes to the above) but it skips pre-flight and doesn't round-trip — **don't author it**. ## Parameters are template tags Every `{{name}}` in the SQL must have a matching entry in the stage's `template-tags`, keyed by that name. **The three must agree exactly:** the `{{name}}` in SQL = the map key = the entry's `"name"` field. Names are case-sensitive (`{{Cat}}` ≠ `{{cat}}`). A `{{name}}` with no entry fails at run time; an unused entry is ignored. `--dry-run` and a run fail on a raw variable with no value or `default` outside an optional `[[ … ]]` clause, and on any `"required": true` tag with no value; a field filter with no value compiles to `1 = 1` and matches every row. Four kinds of tag, by `type`: | Kind | `type` | SQL syntax | What it is | | ------------------ | -------------------------------- | ----------------------------- | -------------------------------------------- | | **Raw variable** | `text` `number` `date` `boolean` | `WHERE total > {{min_total}}` | a literal substituted into the SQL | | **Field filter** | `dimension` | `WHERE {{status}}` (bare!) | a smart filter widget bound to a real column | | **Snippet** | `snippet` | `{{snippet: Active Rows}}` | a reusable SQL fragment (`mb snippet`) | | **Card reference** | `card` | `{{#42}}` or `{{#42-slug}}` | another saved query, as a subquery | Give each tag an `id`, unique among the tags; the tag's name serves. Wrap any clause that should be droppable when its value is empty in **`[[ … ]]`**, keyword and all: `[[AND {{status}}]]`, not `AND [[{{status}}]]`. Only one level of nesting; a query using several optional `[[AND …]]` blocks needs a real `WHERE` first (e.g. `WHERE true [[AND {{a}}]] [[AND {{b}}]]`). ## The decision that matters: field filter vs. raw variable This is the call agents get wrong. Default to a **field filter** whenever the tag filters a real table column. - A **raw variable** (`{{x}}`) is a dumb literal splice. You write the operator yourself: `WHERE status = {{x}}`. It gives a plain text/number/date box, no dropdown, no date picker, and it's what powers computed bits that aren't a column (`LIMIT {{n}}`, a threshold, an interpolated identifier). - A **field filter** (`type: dimension`) is a smart widget bound to a column via `dimension`. You write it **bare** — `WHERE {{status}}` — and Metabase expands it to the right SQL (`status IN (...)`, a `BETWEEN` for dates, etc.), driving a dropdown/date-picker sourced from the column's values. Writing `WHERE status = {{status}}` around a field filter **breaks the expansion** — the single most common native-SQL bug. Field filters only bind to a **real, connected database column** — not an expression, not an aggregate, not a subquery/CTE column. If the thing you're filtering isn't a physical column, it has to be a raw variable. ## Template-tag bodies (the two you author most) **Field filter** — `dimension` binds a column, `widget-type` picks the widget: ```json "status": { "id": "status", "name": "status", "display-name": "Status", "type": "dimension", "dimension": ["field", {}, 141], "widget-type": "string/=" } ``` **`dimension` is a field ref: `["field", {options}, <field-id>]`** — options object **second**, id **third**, exactly the `mbql` rule. Send `{}` for the options. The field id comes from `table get <id> --include fields`. `widget-type` must suit the column's type and is a closed enum (same vocabulary as dashboard filter `type`): string ops (`string/=`, `string/!=`, `string/contains`, `string/starts-with`, …), number ops (`number/=`, `number/between`, `number/>=`, …), dates (`date/all-options`, `date/range`, `date/relative`, `date/month-year`, …), plus `category`, `id`, `boolean/=`, and the `location/*` set. Text column → a `string/*` or `category`; datetime → a `date/*`; numeric → a `number/*`. `date/all-options` is the most flexible date widget. **Raw variable** — no `dimension`, no `widget-type`: ```json "min_total": { "id": "min_total", "name": "min_total", "display-name": "Minimum total", "type": "number", "required": true, "default": "50" } ``` Snippet and card-reference bodies (and the full field list for every kind) are in `references/template-tags.md` — load it when you need them (`mb skills get native-sql --full`). ## Snippets and card references - **Snippet** (`{{snippet: Name}}`): a shared SQL fragment stored via `mb snippet create --body '{"name":"Active Rows","content":"status = '\''active'\''"}'` — `content` is bare SQL, no wrapping. Reuse it across queries; edit it once. The tag body carries `snippet-id` + `snippet-name`. - **Card reference** (`{{#42}}`): inlines another saved query as a subquery — `SELECT * FROM {{#42}}` or `WITH x AS {{#42}} …`. The tag body carries `card-id`. - **Neither takes a parameter value.** A referenced card runs with **its own saved defaults** — you can't override its parameters from the parent query. Snippets are static text. Only raw variables and field filters are user-fillable. ## Wiring, defaults, and running **A tag's default is the tag's own `default`.** Metabase derives the card's `parameters` from the template tags; declare one in the card's `parameters` array (alongside `dataset_query`) only to give it a dropdown source, `values_source_type` (`static-list` / `card`). The declared parameter's `id` is the tag's `id`, and its `target` links back to the tag: `["dimension", ["template-tag", "status"]]` for a field filter, `["variable", ["template-tag", "min_total"]]` for a raw variable. **Run a saved card with values** via `card query`, whose `--parameters` is a JSON array of `{type, target, value}` — same `target` grammar: ```bash mb card query 12 --parameters '[{"type":"string/=","target":["dimension",["template-tag","status"]],"value":"active"}]' --json ``` **Expose it as a dashboard filter** by mapping a dashboard parameter to the tag on the dashcard — the mapping `target` is the same `["dimension",["template-tag","status"]]` (field filter) or `["variable",["template-tag","status"]]` (raw variable). The dashboard-side mechanics (the `parameters` array and `parameter_mappings`) live in `core`. ## Don't - Don't wrap a field filter in an operator (`WHERE col = {{ff}}`) — write it bare (`WHERE {{ff}}`). - Don't author the flat `{type:"native", …}` form — send the native stage above. - Don't use native SQL for DDL or multiple statements — the editor is read-only, single-statement; `CREATE`/`UPDATE`/`;`-chained SQL is unsupported. To materialize a table, use a `transform`; to write rows (`INSERT`/`UPDATE`/`DELETE`), use a data action (`data-action`). - Don't expect `[[ ]]` to save you from a case/type mismatch — `WHERE plan = {{p}}` returns zero rows on a case-sensitive engine if the value's case is off; that's a value problem, not syntax. - Don't reach for native when a structured query fits — you lose the engine-independence and readability of an `mbql.stage/mbql` stage.