SQL

July 22, 2026 ยท View on GitHub

MongrelDB ships a DataFusion-backed SQL engine at POST /sql. From Mojo, run SQL with db.sql(...):

var rows = db.sql("SELECT 1")

How sql behaves

db.sql(sql) sends {"sql": "...", "format": "json"} to /sql. It returns a Python list of row dicts when the daemon replies with a JSON result set, and an empty list otherwise.

  • DDL and DML reply with a non-JSON status body. sql returns an empty list
    • success is the signal.
  • SELECT in most daemon builds streams Arrow IPC bytes rather than JSON, so sql returns an empty list. Use db.sql_arrow(sql) to request raw Arrow IPC (format: "arrow") when the server supports it, or use the native QueryBuilder for typed row retrieval.

Errors are mapped to the typed error categories: HTTP 400/5xx raises QueryError; 409 raises ConflictError. Match the category prefix in the error message:

try:
    _ = db.sql("INSERT INTO orders (id, amount) VALUES (99, 999.0)")
except e:
    if String(e).contains("ConflictError"):
        print("duplicate row")

CREATE TABLE

_ = db.sql(
    "CREATE TABLE products (id INT64 PRIMARY KEY, name VARCHAR, price FLOAT64)")

INSERT / UPDATE / DELETE

_ = db.sql("INSERT INTO products (id, name, price) VALUES (1, 'Widget', 9.99)")
_ = db.sql("UPDATE products SET price = 14.99 WHERE id = 1")
_ = db.sql("DELETE FROM products WHERE id = 2")

For bulk inserts, the native batch transaction (db.begin()) is usually faster.

CREATE TABLE AS SELECT

_ = db.sql("CREATE TABLE archive AS SELECT * FROM orders WHERE amount > 500")

Recursive CTEs

_ = db.sql("WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r WHERE n<10) SELECT n FROM r")

Window functions

_ = db.sql(
    "SELECT id, customer, amount, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY amount DESC) AS rn FROM orders")

When to use SQL vs. the query builder

Reach forWhen
QueryBuilderPoint lookups, range scans, bitmap filters, full-text, vector similarity that map to a native index. Sub-millisecond, rows decode into dicts directly.
SQLDDL, multi-statement setup, joins, recursive CTEs, window functions, arbitrary aggregates.

Mix freely: create tables with SQL, write rows with put, read them back with QueryBuilder, and run analytics with SQL.

Next steps