Raw SQL with KMQL

Raw SQL mode gives you full control: write the query you need, and Kiwi ORM handles parameter binding, result mapping and connection management. It is Dapper-style, async-first and runs through the same typed pipeline as the other modes.

KMQL, the Kiwi Markup Query Language, is a small markup layer inside the SQL text. The markup is resolved into the right syntax for the database you are connected to, so one query works across all supported engines.

Read rows#

using Kiwify.Kiwi.ORM.Core.Helper;

public class CustomerRow { public int CustomerId { get; set; } public string Name { get; set; } = ""; public string City { get; set; } = ""; }

var rows = await db.ExecuteReadAsync<CustomerRow>(
    "SELECT {{C: CustomerId, Name, City}} FROM {{T: customers}} WHERE {{C: City}} = @city AND {{C: IsActive}} = {{TRUE}}",
    new { city = "London" });

var one    = await db.ExecuteReadOneAsync<CustomerRow>("SELECT {{C: CustomerId, Name, City}} FROM {{T: customers}} WHERE {{C: CustomerId}} = @id", new { id = 42 });
var count  = await db.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM {{T: customers}}", new { });

• Parameters are named (@name) and passed as an object. They are always bound, never concatenated.
• Result columns map to the properties of the class you specify.
• ExecuteAsync(sql, args) runs a statement that changes data and returns the affected row count.

Make it portable#

Differences between databases (identifier quoting, boolean literals, paging, string and date functions) are expressed with markup:

SELECT {{C: Id, Name}}, {{DATEPART: year, HireDate}} AS HireYear
FROM {{T: employees}}
WHERE {{LOWER: Name}} LIKE @pattern
ORDER BY {{C: Id}} {{PAGINATE: skip=@Skip, take=@Take}}{{/PAGINATE}}

On SQL Server this produces OFFSET ... FETCH, on PostgreSQL and MySQL a LIMIT ... OFFSET, on Oracle its own syntax, and tables and columns are quoted for the engine in use.

When a feature really is vendor-specific, pick a branch per provider:

SELECT * FROM {{T: orders}}
{{IF: sqlserver}} ... {{ELSEIF: oracle}} ... {{ELSE}} ... {{/IF}}

The full list of tags is in the KMQL markup reference.

Mixing KMQL with entities#

Raw statements can use entity tables and reuse the same context, connection and transaction. A common pattern is Entity mode for CRUD and KMQL for the few aggregation or reporting queries that need hand-tuned SQL.

Performance#

Raw SQL mode produced Kiwi ORM's fastest measured results in the published benchmarks. See Performance.

Tips#