SQL function builders

SQL function builders A typed library of SQL expression builders — aggregates, window functions, date/time, JSON, string, math, CASE and full-text — exported from the package root. Each takes the dialect as its first argument and returns a literal expression carrying the SQL that engine actually uses, so one call site works everywhere instead of a switch over dialect names. Drop the result into attributes, where, having or order — it renders as a literal expression: Strings are values; col() is a column This trips people up, so it is worth stating plainly. In the string, math, date and JSON builders a plain string argument is rendered as a quoted SQL string literal; to name a column, wrap it in col(): The aggregate builders are the exception: they treat a bare string as an identifier. Use col() everywhere and the distinction stops mattering. Aggregates Function | PostgreSQL | MySQL | SQLite | SQL Server | Oracle | --- | --- | --- | --- | --- | --- | count(d) | COUNT() | same | same | same | same | countDistinct(d, x) | COUNT(DISTINCT "id") | same | same | same | same | sqlSum(d, x) / avg / min / max | SUM("total") | same | same | same | same | stringAgg(d, x, ', ') | STRINGAGG("name", ', ') | GROUPCONCAT("name" SEPARATOR ', ') | GROUPCONCAT("name", ', ') | STRINGAGG(…) | LISTAGG("name", ', ') | arrayAgg(d, x) | ARRAYAGG("tag") | JSONARRAYAGG("tag") | jsongrouparray("tag") | throws | JSONARRAYAGG("tag") | arrayAgg on SQL Server throws with the reason — FOR JSON PATH shapes a whole query, not a single aggregate expression, so there is nothing correct to emit. That is the pattern across this library: where an engine genuinely cannot do it, you get an explanatory error instead of SQL that fails at the server. sum is exported as sqlSum at the package root, because sum is already taken by a utility helper. The same applies to sqlRandom, sqlTruncate, sqlSubstring, sqlTrim, sqlLtrim, sqlRtrim, sqlJsonContains, sqlJsonHasKey, sqlFormatDate, sqlNow, sqlRepeat and sqlReverse. Window functions WindowFunctionBuilder is chainable and renders with toSQL(dialect): Available: rowNumber, rank, denseRank, percentRank, cumeDist, ntile, lag, lead, firstValue, lastValue, nthValue, and the aggregate-over forms sumOver, avgOver, countOver, minOver, maxOver. windowFn(name, …args) builds one the list does not cover. Frames Boundaries are 'unboundedPreceding', 'unboundedFollowing', 'currentRow', { preceding: n } or { following: n }. range(frame) is the RANGE equivalent of rows(frame). toLiteral(dialect) gives the literal-expression object instead of a raw string, and as(alias) names the output column. Date and time Call | PostgreSQL | MySQL | SQLite | SQL Server | Oracle | --- | --- | --- | --- | --- | --- | dateTrunc(d, 'month', col('createdat')) | DATETRUNC('month', "createdat") | DATEFORMAT("createdat", '%Y-%m-01 00:00:00') | strftime('%Y-%m-01 00:00:00', "createdat") | DATETRUNC(month, "createdat") | TRUNC("createdat", 'MM') | dateDiff(d, 'day', a, b) | (CAST("b" AS DATE) - CAST("a" AS DATE)) | TIMESTAMPDIFF(DAY, "a", "b") | CAST(julianday("b") - julianday("a") AS INTEGER) | DATEDIFF(day, "a", "b") | (TRUNC("b") - TRUNC("a")) | currentTimestamp() | CURRENTTIMESTAMP | same | same | same | same | Also dateAdd, dateSub, currentDate, sqlNow, sqlFormatDate. Five genuinely different expressions from one call is the whole point: DATETRUNC does not exist on MySQL, and the DATEFORMAT string that approximates it is not something to retype per query. JSON Call | Output | --- | --- | jsonExtract(d, col('data'), '$.a') on PostgreSQL | "data" #>> '{a}' | … on MySQL | JSONUNQUOTE(JSONEXTRACT(\data\, '$.a')) | … on SQLite | CAST(jsonextract("data", '$.a') AS TEXT) | … on SQL Server | JSONVALUE([data], '$.a') | … on Oracle | JSONVALUE("data", '$.a') | Also jsonKeys, jsonTypeOf, sqlJsonContains, sqlJsonHasKey. Note each dialect's extraction returns text, not JSON — the builders add the unquote/cast so a comparison against a string works without you knowing that JSONEXTRACT alone would return "value" with the quotes. Full-text search Dialect | Output | --- | --- | PostgreSQL | totsvector('english', "title") @@ plaintotsquery('english', 'database index') | MySQL | MATCH(title) AGAINST('database index' IN NATURAL LANGUAGE MODE) | SQLite | "title" MATCH 'database index' | SQL Server | FREETEXT([title], 'database index') | Oracle | CONTAINS("title", 'database index', 1) > 0 | fullTextRank gives the matching relevance expression for ORDER BY. All of these need the corresponding index to exist — see Indexes & constraints. Strings and maths Strings — concat, upper, lower, length, replace, lpad, rpad, split, sqlSubstring, sqlTrim, sqlLtrim, sqlRtrim, sqlRepeat, sqlReverse. Maths — round, ceil, floor, power, sqrt, abs, sign, mod, greatest, least, sqlRandom, sqlTruncate. greatest / least are worth singling out — the same call produces three different shapes: SQL Server has no GREATEST before 2022, so the builder expands it into the equivalent CASE. CASE expressions caseWhen() takes a full where-style condition per branch; caseOf(column) is the simple form that compares one expression against each value. Both support as(alias) and toLiteral(dialect). A common use is conditional aggregation, counting subsets in one pass: Related reading query-builders — how these fit the compiler Querying — attributes, group, having Query operators — the where vocabulary Raw queries — when a builder is not enough