A query language is only as good as the functions you can reach without thinking, because nobody in the middle of an incident wants to stop and write a date parser in application code just to answer “what happened last Tuesday.” I learned the MySQL function list in the early 2000s the way everyone did, by typing DATE_FORMAT and NOW() and MD5() into a client until they were muscle memory, and when SQLite later grew the json1 extension that same kind of catalog became the part of the database people actually memorized, far more than any wire protocol or storage format. So when we built the SQL frontend for MongrelDB on DataFusion, the function catalog was treated as part of the contract rather than an afterthought, because an engine that parses SELECT but shrugs at json_extract is really telling you to go write your own JSON path evaluator in every application that touches it, and that is the exact moment the “the database speaks SQL” claim stops meaning anything.

The catalog lives in the engine, not in the SDK

The whole library is registered in one pass over the DataFusion SessionContext from crates/mongreldb-query/src/extended_sql_functions.rs, a little over a hundred names, and the shapes are deliberately SQLite-shaped because that is the vocabulary twenty years of developers already carry around. The JSON family covers json_extract, json_type, json_array_length, json_set, json_insert, json_replace, json_remove, json_quote, and json_valid, with json_each and json_tree registered as table-valued functions for exploding arrays and walking whole documents, and every one of them has a jsonb twin that reads the binary JSONB form directly instead of re-parsing text on every row. The idioms transfer unchanged:

SELECT json_extract(payload, '$.city') AS city,
       json_type(payload, '$.tags')   AS tags_type
FROM events
WHERE json_valid(payload) = 1;

SELECT e.id, tag.value
FROM events e, json_each(e.payload, '$.tags') AS tag;

The date and time family, date, time, datetime, julianday, unixepoch, strftime, and timediff, parses 'now', RFC 3339 strings, YYYY-MM-DD HH:MM:SS, bare dates, bare times, and raw unix seconds, and the modifier grammar covers 'start of day', 'unixepoch', and signed offsets like '-7 days' or '+2 hours', which is a smaller modifier list than SQLite’s full set and we are honest about that, but it covers the offsets people actually write in dashboards and cron queries:

SELECT datetime('now', 'start of day', '-7 days') AS window_start,
       timediff('now', created_at)                AS age_seconds
FROM sessions;

Math gets the full trig and log run from acos through tanh with degrees and radians for the conversions, plus ceil, floor, round, sign, pi, and a pow/power alias pair, and the string family brings instr, substr, hex/unhex, printf/format, the trim variants, unicode, and even soundex, which is one of those functions you mock until the day a customer-support search needs it and then you are very glad it exists.

Aggregates that carry state

The aggregate side is the part I would point a skeptic at first, because median, percentile, percentile_cont, and percentile_disc are registered in crates/mongreldb-query/src/percentile.rs as real DataFusion aggregate UDFs with accumulator state, which means they participate in GROUP BY the way count and sum do instead of being a two-pass job where you export rows and compute percentiles in a notebook. The semantics follow Postgres, percentile_cont interpolates between neighbors and percentile_disc returns the first value at or past the cutoff, and group_concat and string_agg handle the string-rollup case that reporting queries always end up needing:

SELECT endpoint,
       count(*)                          AS calls,
       percentile_cont(latency_ms, 0.95) AS p95,
       percentile_disc(latency_ms, 0.5)  AS p50
FROM request_log
GROUP BY endpoint
ORDER BY p95 DESC;

Kit’s helpers are quoting, not magic

On the TypeScript side, Kit does not try to build an expression DSL over these functions; each helper in packages/kit/src/sql.ts returns a { sql } fragment, column arguments pass through identifier quoting, string and scalar arguments pass through sqlLiteral with proper '' escaping, and the fragments compose into ordinary template literals, so the value being added is quoting discipline and a name you can autocomplete rather than a new abstraction to learn:

import {
  groupConcat,
  jsonExtract,
  percentileCont,
  unixEpoch,
} from '@visorcraft/mongreldb-kit';

const rows = await db.sqlRows(`
  SELECT ${jsonExtract(events.payload, '$.city').sql}      AS city,
         ${percentileCont(events.latency_ms, 0.95).sql}    AS p95_ms,
         ${groupConcat(events.tag, '|').sql}               AS tags,
         ${unixEpoch('now', 'start of day').sql}           AS today_utc
  FROM events
  GROUP BY city
`);

The one honest seam is mathFn(name, ...args), which takes the function name as raw SQL because the alternative is maintaining an enum of every math function forever, so the rule is trusted names only, and if you are building a query from user input you pick from a fixed list instead of interpolating the name.

The volatility tradeoff

The cost side of shipping clock and randomness functions is that their results change between identical executions, and a result cache that served a cached datetime('now') would be a wrong answer delivered quickly, so every function in the registry carries a volatility marker and the executor scans the query text for calls into the volatile set, date, time, datetime, julianday, unixepoch, strftime, timediff, random, and randomblob, and simply refuses to serve those queries from the result cache. That is the tradeoff made explicit rather than documented in a footnote: you get the convenience of clock functions anywhere in your SQL, you lose the cache hit on exactly the queries that use them, and the engine makes that determination from the SQL text itself instead of trusting every caller to remember. The immutable majority, json_extract, sqrt, percentile_cont, and friends, stays fully cacheable, so the common analytical query pays nothing for the policy.

The old world had this right in a way we almost forgot: the MySQL and SQLite function lists followed you for a decade because they were stable, complete, and documented once, and the modern equivalent for an engine built on DataFusion is not to invent a fresh catalog with fresh names but to register the vocabulary people already have, mark each function with an honest volatility, and let every SDK inherit the whole library for free because there was never anything to reimplement, rename, or re-document on the client side.