This module implements macro-based SQL query generation for Ozark. It can be used to construct complex SQL queries in a type-safe manner, with compile-time checks for model and column existence.
Types
Order = enum Asc, Desc
- Sort direction used by orderBy and friends
OzarkModelDefect = object of CatchableError
- Exception type for errors related to model definitions, such as referencing an unknown model or column in a query
Vars
ozarkTxDepth {.threadvar.}: int
- Per-thread nesting depth used by withTransaction
Consts
selectFamilyResults = ["ozarkSelectResult", "ozarkWhereResult", "ozarkWhereInResult", "ozarkRawSQLResult", "ozarkLimitResult", "ozarkOrderByResult", "ozarkOffsetResult", "ozarkGroupByResult", "ozarkHavingResult"]
Procs
proc `$`(sql: SqlQuery): string {....raises: [], tags: [], forbids: [].}
proc ensureStage(sql: NimNode; allowed: openArray[string]; caller: string) {. ...raises: [], tags: [], forbids: [].}
- Compile-time guard: the query must currently be in one of allowed stages
proc getPlaceholder(modelTuple: NimNode; idx: int = 1): string {....raises: [], tags: [], forbids: [].}
- Get the appropriate parameter placeholder for the specified SQL driver. This is used in the insert and update macros to generate the correct SQL syntax for parameter placeholders based on the configured driver.
proc modelSymOf(sql: NimNode): NimNode {....raises: [], tags: [], forbids: [].}
- Extract the model type symbol from a chained query block
proc modelTupleOf(sql: NimNode): NimNode {....raises: [], tags: [], forbids: [].}
- Extract the (bindSym(Model), sqlDriver) tuple carried inside the block
proc normalizeInLists(sqlText: string; driver: SqlDriver = SqlDriver.generic): string {. ...raises: [], tags: [], forbids: [].}
-
Collapse multi-placeholder groups — (?, ?, ?) or ($1, $2) — into a single placeholder so the compile-time SQL validator (which only accepts one expression per parenthesized group) can validate statements with multi-value IN (...) lists. The returned text is used for validation and column extraction only; the original text is what gets executed.
The collapsed placeholder must match driver: the pgsql dialect only accepts $N placeholders while mysql/sqlite only accept ? (see featPlaceholderPg / featPlaceholderQmark in openparser/sql).
proc ozarkCreateTableResult(sql: static[string]): NimNode {.compileTime.}
proc ozarkGroupByResult(sql: static[string]; vals: seq[string]): NimNode {. compileTime.}
proc ozarkHavingResult(sql: static[string]; vals: seq[string]): NimNode {. compileTime.}
proc ozarkHoldModel[T: typedesc](t: T) {.compileTime.}
proc ozarkHoldModel[T](t: T) {.compileTime.}
proc ozarkInsertResult(sql: static[string]; vals: seq[string]): NimNode {. compileTime.}
proc ozarkLimitResult(sql: static[string]; vals: seq[string]): NimNode {. compileTime.}
proc ozarkOffsetResult(sql: static[string]; vals: seq[string]): NimNode {. compileTime.}
proc ozarkOrderByResult(sql: static[string]; vals: seq[string]): NimNode {. compileTime.}
proc ozarkRawSQLResult(sql: static[string]; vals: seq[string]): NimNode {. compileTime.}
proc ozarkRemoveResult(sql: static[string]): NimNode {.compileTime.}
proc ozarkSelectResult(sql: static[string]): NimNode {.compileTime.}
proc ozarkUpdateResult(sql: static[string]; vals: seq[string]): NimNode {. compileTime.}
proc ozarkUpsertResult(sql: static[string]; vals: seq[string]): NimNode {. compileTime.}
proc ozarkWhereInResult(sql: static[string]; vals: seq[string]): NimNode {. compileTime.}
proc ozarkWhereResult(sql: static[string]; vals: seq[string]): NimNode {. compileTime.}
proc paramsBracketOf(trailing: NimNode): NimNode {....raises: [], tags: [], forbids: [].}
- Return a mutable bracket node holding all bound params accumulated so far, regardless of the internal shape produced by earlier writers (@[...] prefix, compiler-folded varargs, bare single value, or none).
proc rebuildResultCall(kindSym: NimNode; sqlText: string; bracket: NimNode): NimNode {. ...raises: [], tags: [], forbids: [].}
- Standardized result call: (kindSym)(sqlText, @params...)
proc resultKindOf(callNode: NimNode): string {....raises: [], tags: [], forbids: [].}
- The callee name of a trailing result call (e.g. "ozarkWhereResult")
proc swapResultCall(trailing: NimNode; kindSym: NimNode; newText: string; bracket: NimNode) {....raises: [], tags: [], forbids: [].}
- In-place rewrite of an existing trailing call to a new stage while preserving every accumulated parameter
proc trailingCallOf(sql: NimNode): NimNode {....raises: [], tags: [], forbids: [].}
- Normalize a chained query expression (block stmt/expr or bare call) to its trailing ozark*Result call node.
proc unbracket(n: NimNode): NimNode {....raises: [], tags: [], forbids: [].}
- Normalizes an array-ish argument (@["a"], ["a"], compiler-folded varargs, or a single value) to a plain bracket node
Macros
macro avgOf(sql: untyped; col: static string): untyped
- Execute SELECT AVG(col) over the current query.
macro count(sql: untyped): untyped
- Execute SELECT COUNT(*) over the current query and return the row count as int64.
macro countDistinct(sql: untyped; col: static string): untyped
- Execute SELECT COUNT(DISTINCT col) over the current query.
macro decrement(modelTuple: untyped; col: static string; n: static int = 1): untyped
- Emit UPDATE table SET col = col - n. Chainable with .where().exec().
macro dropTable(modelTuple: untyped; cascade: static bool = false): untyped
-
Compile-time macro to drop a model's table from the database.
Cascade option is included for compatibility with databases that support it, but will be ignored for SQLite since it does not support CASCADE with DROP TABLE
macro exists(x: untyped): untyped
-
Two forms:
- Chained: ...select(...).where(...).exists() is true when the query matches at least one row.
- Table-level: Models.table(Users).exists() is true when the table has at least one row.
Both execute immediately and return bool.
macro extractSQL(sql: NimNode): untyped
- Extracts the SQL NimNode to string for use in code generation
macro findById(modelTuple: untyped; id: untyped): untyped
- Shorthand for fetching a single row by its id column. Finish the chain with get(). (Named findById because system.find shadows a bare find.)
macro first(sql: untyped): untyped
- Alias for get(): fetch a single row from the chained query.
macro fromSQL(sql: untyped): untyped
- Macro to parse a resumed SQL string back into a NimNode for further manipulation This is used in the where macros to allow chaining multiple clauses based on runtime computations.
macro groupBy(sql: untyped; cols: static openArray[string]): untyped
- Define GROUP BY clause
macro having(sql: untyped; col: static string; val: untyped): untyped
- Define HAVING col = val (must follow groupBy)
macro havingGt(sql: untyped; col: static string; val: untyped): untyped
- Define HAVING col > val
macro havingGte(sql: untyped; col: static string; val: untyped): untyped
- Define HAVING col >= val
macro havingLt(sql: untyped; col: static string; val: untyped): untyped
- Define HAVING col < val
macro havingLte(sql: untyped; col: static string; val: untyped): untyped
- Define HAVING col <= val
macro increment(modelTuple: untyped; col: static string; n: static int = 1): untyped
- Emit UPDATE table SET col = col + n. Chainable with .where().exec().
macro insert(modelTuple, data: untyped): untyped
- Placeholder for an INSERT statement. This macro generates the SQL string for the INSERT statement. This macro performs compile-time checks for the existence of the specified model and the validity of the column names.
macro insertMany(modelTuple: untyped; rows: untyped): untyped
- Insert multiple rows with a single INSERT statement. All rows must declare the exact same set of columns, validated at compile time.
macro last(sql: untyped): untyped
- Fetch a single row ordered by highest id. Executes immediately and returns a Collection[T]. Cannot be combined with an explicit orderBy on the same chain.
macro limit(sql: untyped; count: static int): untyped
- Define LIMIT clause with a constant row count. Must be called before offset (canonical SQL clause order).
macro maxOf(sql: untyped; col: static string): untyped
- Execute SELECT MAX(col) over the current query.
macro minOf(sql: untyped; col: static string): untyped
- Execute SELECT MIN(col) over the current query.
macro offset(sql: untyped; count: static int): untyped
- Define OFFSET clause with a constant row count.
macro orderAscBy(sql: untyped; cols: static openArray[string]): untyped
- Define ORDER BY ... ASC
macro orderDescBy(sql: untyped; cols: static openArray[string]): untyped
- Define ORDER BY ... DESC
macro orWhere(sql: untyped; col: static string; val: untyped): untyped
- Define OR in WHERE clause
macro orWhereBetween(sql: untyped; col: static string; lo: untyped; hi: untyped): untyped
- Define an OR BETWEEN condition in WHERE clause
macro orWhereEndsLike(sql: untyped; col: static string; val: untyped): untyped
- Define an OR condition matching a suffix via LIKE
macro orWhereGt(sql: untyped; col: static string; val: untyped): untyped
- Define a WHERE clause using an ordered comparison operator
macro orWhereGte(sql: untyped; col: static string; val: untyped): untyped
- Define a WHERE clause using an ordered comparison operator
macro orWhereIn(sql: untyped; col: static string; vals: untyped): untyped
- Define an OR IN condition in WHERE clause
macro orWhereLike(sql: untyped; col: static string; val: untyped): untyped
- Define an OR LIKE condition in WHERE clause
macro orWhereLt(sql: untyped; col: static string; val: untyped): untyped
- Define a WHERE clause using an ordered comparison operator
macro orWhereLte(sql: untyped; col: static string; val: untyped): untyped
- Define a WHERE clause using an ordered comparison operator
macro orWhereNot(sql: untyped; col: static string; val: untyped): untyped
- Define a OR condition with NOT in WHERE clause
macro orWhereNotIn(sql: untyped; col: static string; vals: untyped): untyped
- Define an OR NOT IN condition in WHERE clause
macro orWhereNotLike(sql: untyped; col: static string; val: untyped): untyped
- Define an OR NOT LIKE condition in WHERE clause
macro orWhereStartsLike(sql: untyped; col: static string; val: untyped): untyped
- Define an OR condition matching a prefix via LIKE
macro paginate(sql: untyped; page: static int; perPage: static int): untyped
- Apply LIMIT/OFFSET pagination with constant values. page is 1-based.
macro pluck(sql: untyped; col: static string): untyped
- Execute the current query but collect only col into a seq[string], one entry per matched row.
macro prepareTable(modelTuple): untyped
-
Compile-time macro to prepare a model's table in the database.
This macro generates the SQL string for creating the table based on the model definition and executes it at compile time to ensure the table exists before any queries are made against it.
macro removeRow(modelTuple: untyped): untyped
- Placeholder for a DELETE statement. This macro generates the SQL string for the DELETE statement. This macro performs compile-time checks for the existence of the specified model.
macro select(modelTuple: untyped; col: static string): untyped
- Define SELECT clause
macro select(modelTuple: untyped; cols: static openArray[string]): untyped
- Define SELECT clause with specific columns.
macro selectAll(modelTuple: untyped): untyped
- Define SELECT * clause
macro sumOf(sql: untyped; col: static string): untyped
- Execute SELECT SUM(col) over the current query. Returns the raw string value reported by the database driver.
macro truncate(modelTuple: untyped): untyped
- Remove all rows from the model's table. Emits DELETE FROM so the behavior stays portable across drivers (no TRUNCATE support yet).
macro unique(sql: untyped): untyped
- Apply SELECT DISTINCT to the current query. Can only be applied directly to a select/selectAll stage.
macro update(modelTuple, data: untyped): untyped
- Placeholder for an UPDATE statement. This macro generates the SQL string for the UPDATE statement. This macro performs compile-time checks for the existence of the specified model and the validity of the column names.
macro upsert(modelTuple: untyped; data: untyped; conflictCols: static openArray[string]; updateCols: static openArray[string]): untyped
- Insert a row, resolving conflicts on conflictCols. When a conflict occurs, updateCols are overwritten with the proposed values (DO UPDATE SET c = EXCLUDED.c). Pass an empty updateCols to emit DO NOTHING. Uses the portable ON CONFLICT syntax (PostgreSQL and SQLite 3.24+).
macro where(sql: untyped; col: static string; vals: untyped): untyped
- Define WHERE clause
macro whereBetween(sql: untyped; col: static string; lo: untyped; hi: untyped): untyped
- Define WHERE col BETWEEN lo AND hi
macro whereEndsLike(sql: untyped; col: static string; val: untyped): untyped
- Define WHERE clause with LIKE for suffix matching
macro whereGt(sql: untyped; col: static string; val: untyped): untyped
- Define a WHERE clause using an ordered comparison operator
macro whereGte(sql: untyped; col: static string; val: untyped): untyped
- Define a WHERE clause using an ordered comparison operator
macro whereIn(sql: untyped; col: static string; vals: untyped): untyped
- Define WHERE clause with IN operator
macro whereLike(sql: untyped; col: static string; val: untyped): untyped
- Define WHERE clause with LIKE for any position
macro whereLt(sql: untyped; col: static string; val: untyped): untyped
- Define a WHERE clause using an ordered comparison operator
macro whereLte(sql: untyped; col: static string; val: untyped): untyped
- Define a WHERE clause using an ordered comparison operator
macro whereNot(sql: untyped; col: static string; val: untyped): untyped
- Define WHERE clause with NOT
macro whereNotBetween(sql: untyped; col: static string; lo: untyped; hi: untyped): untyped
- Define WHERE col NOT BETWEEN lo AND hi
macro whereNotEndsLike(sql: untyped; col: static string; val: untyped): untyped
- Define WHERE clause with NOT LIKE for suffix matching
macro whereNotIn(sql: untyped; col: static string; vals: untyped): untyped
- Define WHERE clause with NOT IN operator
macro whereNotLike(sql: untyped; col: static string; val: untyped): untyped
- Define WHERE clause with NOT LIKE for any position
macro whereNotNull(sql: untyped; col: static string): untyped
- Define WHERE col IS NOT NULL
macro whereNotStartsLike(sql: untyped; col: static string; val: untyped): untyped
- Define WHERE clause with NOT LIKE for prefix matching
macro whereNull(sql: untyped; col: static string): untyped
- Define WHERE col IS NULL
macro whereStartsLike(sql: untyped; col: static string; val: untyped): untyped
- Define WHERE clause with LIKE for prefix matching
macro withTransaction(body: untyped): untyped
-
Run body inside a database transaction on a dedicated pooled connection. Commits on normal completion; rolls back when the body raises (secondary rollback errors are swallowed, the original exception is re-raised). The connection is always returned to the pool.
Query chains inside the block use the injected dbcon automatically:
withTransaction do: Models.table(Orders).insert({...}).exec() Models.table(Audit).insert({...}).exec()
Nested calls raise OzarkConnectionError.
Templates
template table(models: ptr ModelsTable; name): untyped
- Define SQL statement for a table
template withColumn(x: NimNode; col: string; body)
template withColumnCheck(model: NimNode; col: string; body)
-
Check if the specified column exists in the model definition
This template is used by the query macros to perform compile-time checks for the existence of columns in the specified model. It ensures that any column referenced in a query actually exists in the model definition.
template withColumnsCheck(model: NimNode; cols: openArray[string]; body)
- Check if the specified columns exist in the model definition
template withTableCheck(modelTuple: NimNode; body)
- Check if a model with the given name exists in the Models table.