Skip to content

The easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:

2.5 KB
Raw Blame

id: create-sql-query title: Creating SQL queries description: Creating a new SQL query with create_sql_query — load before writing SQL for the read-only contract, model reference syntax ({{#model_id}}), and identifier quoting. tools: [create_sql_query] priority: 50

When to use:

  • User asks for SQL queries but isn't editing an existing one
  • You want to create SQL using models ({{#model_id}} syntax) or tables
  • User requests new SQL analysis or data exploration
  • You need to demonstrate SQL patterns or solutions

Usage: database_id, sql_query, and title are all required — the tool rejects a call that omits any of them.

  • Provide the database_id where the SQL should run (available from model/table representations)
  • Write complete, valid SQL using table names or {{#model_id}} template syntax for models.
  • Provide a title: a short, human-friendly name for the query, shown above its results when the query is delivered as a result card.
  • Quote column names with special characters, spaces, or reserved keywords using double quotes (e.g., "column name", "order", "group", "column_name")
  • The tool will return a newly created SQL query that you can show to the user. If chart tools (create_chart/edit_chart) are available to you, you can also build a chart from it.
  • The tool does NOT execute the SQL, it only creates the query

Model Usage:

  • When querying Metabase models, remember that their fully qualified name is of the form {{#model_id}}, e.g. SELECT * FROM {{#5}} AS mymodel and the references always require an alias (e.g. AS mymodel).
  • Model database_id is available in the model metadata
  • Always prefer existing models over manual table joins when models provide needed relationships

Limitations:

  • Creates new queries only (use edit_sql_query for modifying existing queries)
  • Requires valid database_id for the target database
  • SQL must be compatible with the target database's SQL engine
  • When using tables, always use fully qualified table names (include namespace / schema / catalog).

Metabase is read-only analytics - you only write SELECT queries.

NEVER use:

  • CREATE TABLE / CREATE VIEW
  • INSERT / UPDATE / DELETE
  • ALTER / DROP / TRUNCATE

For transformations, use CTEs:

WITH step1 AS (
  SELECT * FROM {{#model_id}} AS name_alias
),
step2 AS (
  SELECT ... FROM step1
)
SELECT * FROM step2

Example for syntax when creating a query using a Metabase model:

SELECT * FROM {{#model_id}} AS name_alias WHERE condition;