ozark/query

Search:
Group by:

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 orderBy(sql: untyped; cols: static openArray[string];
              dir: static Order = Desc): untyped
Define ORDER BY with an explicit sort direction
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.