Metabase Reference

Metabase Reference

REPL Recipes

Inspect tables: (t2/select [:model/Table :id :name :db_id] :db_id 1)

Check settings: (t2/select-one :model/Setting :key "site-name")

Create saved question (native SQL):

(mt/user-http-request :crowberto :post 200 "card"
  {:name "Test Question" :database_id 1
   :dataset_query {:database 1 :type :native :native {:query "SELECT 1"}}
   :display :table :visualization_settings {} :collection_id nil})

Create MBQL question (via API — preferred for card creation):

api_call(method="POST", path="/card", body='{"name":"Test Question","database_id":1,"dataset_query":{"database":1,"type":"query","query":{"source-table":3,"aggregation":[["count"]],"breakout":[["field",63,{"temporal-unit":"month"}],["field",18,null]],"order-by":[["asc",["field",63,{"temporal-unit":"month"}]]]}},"display":"bar","visualization_settings":{},"collection_id":null}')

Key fields: database_id (top-level, required), dataset_query.database (must match), dataset_query.type = "query" for MBQL. Use "native" only for raw SQL.

Create MBQL question (via REPL — use only for non-UI bugs):

(mt/user-http-request :crowberto :post 200 "card"
  {:name "Test Question" :database_id 1
   :dataset_query {:database 1 :type :query
                   :query {:source-table 3
                           :aggregation [[:count]]
                           :breakout [[:field 63 {:temporal-unit :month}]
                                      [:field 18 nil]]}}
   :display :bar :visualization_settings {} :collection_id nil})

Returns the full card map with :id. Do NOT use t2/insert-returning-instance! for cards — it silently fails due to internal validation.

Card creation pitfall: Never use t2/insert-returning-instance! for :model/Card — it silently returns nil when the internal pMBQL validation rejects the query format. Always use api_call or mt/user-http-request which give clear error messages on schema violations.

Look up table and field IDs (needed for MBQL queries):

api_call(method="GET", path="/database/1/metadata", jq_filter="[.tables[] | {id, name, fields: [.fields[] | {id, name}]}]")

Or via REPL:

(t2/select [:model/Field :id :name :table_id] :table_id 3)

Run query:

(require '[metabase.query-processor :as qp])
(qp/process-query {:database 1 :type :native :native {:query "SELECT * FROM ORDERS LIMIT 5"}})

Toggle feature flag: (t2/update! :model/Setting :key "enable-pivoted-filters" {:value "true"}) Note: t2/update! may return 0 even when successful. Verify with t2/select-one.

Check permissions: (t2/select :model/Permissions :group_id 1)

Compile query to SQL:

(require '[metabase.query-processor.compile :as qp.compile])
(qp.compile/compile {:database 1 :type :query :query {:source-table 1 :filter [:= [:field 1 nil] 42]}})

Load test/dev namespaces (available natively — no setup needed):

(require '[metabase.test :as mt])

Test namespaces are on the classpath when Metabase starts via clj. metabase.test, dev, and enterprise test namespaces are all available.

Key test/dev namespaces: metabase.test (mt) — mt/user-http-request, mt/id, mt/with-temp; dev — dev/pprint-sql, dev/explain-query.

API Call Patterns

api_call(method="GET", path="/database")
api_call(method="GET", path="/database", jq_filter=".data[].name")
api_call(method="POST", path="/card", body='{"name":"test","dataset_query":{"database":1,"type":"native","native":{"query":"SELECT 1"}}}')
api_call(method="GET", path="/database/1/metadata")  # All tables+fields in one call

Visualization settings: graph.tooltip_columns values must use column reference format: ["[\"name\",\"COLUMN_NAME\"]"], not bare names.

Avoid large responses: Never call /table without a narrow jq_filter. For field discovery:

api_call(method="GET", path="/database/1/fields", jq_filter="[.[] | select(.table_name == \"ORDERS\") | {id, name, table: .table_name}]")

Domain Knowledge

Pivot tables: "Old pivot" = table.pivot: true on Table visualization (3-column only: 2 dimensions + 1 metric, code: data_grid.js → pivot()). "New pivot" = PivotTable visualization type (code: data_grid.js → multiLevelPivot()). Separate code paths, separate bugs.

Serialization sort-by quirk: sort-by with a single key that has ties (e.g., :created_at) produces non-deterministic order. Recurring source of serialization diff bugs.

Appdb as data warehouse: The Postgres appdb can double as a data warehouse — register via POST /api/database with same connection details. Use REPL + next.jdbc to CREATE SCHEMA and CREATE TABLE for multi-schema testing.

H2 Tips

• Does NOT support report-timezone
• Reserves SQL keywords (value, count, key, type) as column names/aliases — use amount, qty, cnt instead
• May reject NULL insertions even after dropping NOT NULL constraints
• Does NOT support DATETRUNC() — use CAST(col AS DATE) for date-typed columns
• FORMATDATETIME() returns text, not a date type — avoid when testing date formatting
• No pg_sleep() — for timing-dependent issues, use code analysis instead
• In Sample Database, CATEGORY is in PRODUCTS, not ORDERS — join via PRODUCTS P ON O.PRODUCT_ID = P.ID

Edge Case Decision Table

MCP Tools Reference