API reference
KMQL markup reference
Every KMQL token and block for portable SQL: identifiers, booleans, paging, strings, dates, maths and provider branches.
Tip
Included in the free, source-available edition. See Editions and features.
KMQL (Kiwi Markup Query Language) is markup embedded in SQL text as {{TAG: args}}. Before a statement runs, the active database specification resolves each tag into the correct syntax for the connected engine. Text without {{ is passed through unchanged at no cost, and resolved statements are cached per SQL text and provider.
Tags are case-insensitive. Unknown tags with no registration are left as written.
Identifiers#
| Tag | Meaning |
|---|---|
{{TABLE: name}} / {{T: name}} |
Quote a table identifier for the engine. Dot-separated parts are each quoted ({{T: dbo.Employees}} becomes [dbo].[Employees] on SQL Server and "dbo"."Employees" on PostgreSQL). Never put an alias inside the tag; write it after: FROM {{T: Employees}} e |
{{COL: name}} / {{C: name}} |
Quote a column identifier ([Name], "Name", `Name` depending on the engine). Use it for names with spaces, reserved words, or when portability matters |
{{C: a, b, c}} |
A column list: each name resolved and joined with , |
{{C: e.Id, e.Name}} |
Alias-qualified columns: only the column part is quoted, the alias is kept as written |
{{C: e.First + ' ' + e.Last}} |
A compound expression: every column reference inside is resolved, operators and literals are untouched. Function calls whose name matches a token (such as Upper(e.Name)) are resolved through that token. Unknown functions keep their name and only their arguments are resolved |
Inside the tokens below, a bare identifier argument is quoted automatically, so you can write {{DATEPART: year, HireDate}} instead of nesting {{C:}}. Parameters, literals, already-quoted names and multi-token expressions are left alone.
Blocks#
Blocks open with {{TAG: args}} and close with {{/TAG}}.
| Block | Meaning |
|---|---|
{{TOP: N}} ... {{/TOP}} |
Row-limiting clause; N is a literal or @param |
{{PAGINATE: skip=X, take=Y}} ... {{/PAGINATE}} (alias {{PAGINATION}}) |
Offset and limit paging in the engine's native style; either value can be a literal or @param |
{{IF: provider}} ... {{ELSEIF: provider}} ... {{ELSE}} ... {{/IF}} |
Choose a branch by provider. Only the matching branch is resolved. Providers: sqlserver, postgresql, mysql, mariadb, oracle, sqlite, firebird, db2, saphana, informix, msaccess |
SELECT * FROM {{T: Employee}} ORDER BY {{C: Id}} {{PAGINATE: skip=@Skip, take=@Take}}{{/PAGINATE}}
{{IF: sqlserver}} TOP 10 {{ELSEIF: oracle}} FETCH FIRST 10 ROWS ONLY {{ELSE}} LIMIT 10 {{/IF}}
Tokens#
| Tag | Meaning |
|---|---|
{{TRUE}}, {{FALSE}} |
Boolean literals |
{{LEN: expr}} (alias {{LENGTH}}) |
String length |
{{IFNULL: a, b}} (aliases {{COALESCE}}, {{NVL}}) |
Null coalescing |
{{CONCAT: a, b, ...}} |
String concatenation of two or more parts |
{{SUBSTR: expr, start, length}} (alias {{SUBSTRING}}) |
Substring |
{{CHARINDEX: search, expr}} (aliases {{POSITION}}, {{LOCATE}}, {{INSTR}}) |
Position of text within an expression |
{{UPPER: expr}}, {{LOWER: expr}}, {{TRIM: expr}} |
Case conversion and trimming |
{{NOW}}, {{UTCNOW}} |
Current local or UTC timestamp |
{{NEWID}} (aliases {{NEWGUID}}, {{UUID}}) |
A new unique identifier |
{{RANDOM}} (alias {{RAND}}) |
A random value |
{{DATEADD: part, n, date}} |
Date arithmetic. Parts: year, month, day, hour, minute, second |
{{DATEDIFF: part, d1, d2}} |
Returns d2 - d1 in units of part, matching SQL Server's convention |
{{DATEPART: part, expr}} |
One part: Year, Month, Day, Hour, Minute, Second, DayOfWeek, DayOfYear |
{{YEAR: e}}, {{MONTH: e}}, {{DAY: e}}, {{HOUR: e}}, {{MINUTE: e}} |
Shortcuts for single date parts |
{{CAST: expr, type}} |
Portable cast. Types: string/text, int/integer, bigint, decimal, float, date, datetime, bool/boolean, guid/uniqueid |
{{ABS: e}}, {{ROUND: e, decimals}}, {{CEILING: e}} (alias {{CEIL}}), {{FLOOR: e}} |
Maths |
The date part and the cast type are keywords and are never treated as column names.
Extending KMQL#
Register your own markup once at start-up. It resolves the same way as the built-in tags:
using Kiwify.Kiwi.ORM.Core.Helper.Markup;
SqlMarkupResolver.RegisterToken("MYTOKEN", (args, dbSpec) => /* return SQL text */);
SqlMarkupResolver.RegisterBlock("MYBLOCK", (args, content, dbSpec) => /* return SQL text */);