Query Builder

The std/db/query module provides a fluent, chainable SQL query builder that generates parameterized SQL. It works on top of the Database core and is portable across SQLite, PostgreSQL, and MySQL.

Import

import { db } from "std/db"
import { QueryBuilder } from "std/db/query"

Getting Started

Create a QueryBuilder bound to a database connection:

var conn: any = db.open(DbDriver.Sqlite, ":memory:")
var qb: any = new QueryBuilder(conn)

// Fluent query
var users: any = qb.table("users")
    .where("age", ">", 18)
    .orderBy("name")
    .limit(10)
    .all()

Each call to .table() resets the builder, so a single QueryBuilder instance can be reused for multiple queries.

SELECT Queries

.all(): []any

Return all matching rows as an array of maps.

var users: any = qb.table("users").all()

for (var i: int = 0; i < length(users); i++) {
    println(users[i]["name"])
}

.first(): any

Return the first matching row, or null if none match.

var user: any = qb.table("users").where("id", "=", 1).first()
if (user != null) {
    println(user["name"])
}

.count(): int

Return the count of matching rows.

var total: any = qb.table("users").count()
var activeCount: any = qb.table("users").where("active", "=", 1).count()

.select(columns: []any)

Specify which columns to retrieve. Omit for SELECT *.

var names: any = qb.table("users").select(["name", "email"]).all()

WHERE Clauses

.where(column, op, value)

Add a condition joined by AND. Supported operators: =, !=, >, <, >=, <=, LIKE.

var adults: any = qb.table("users")
    .where("age", ">=", 18)
    .where("active", "=", 1)
    .all()

.orWhere(column, op, value)

Add a condition joined by OR.

var result: any = qb.table("users")
    .where("role", "=", "admin")
    .orWhere("role", "=", "moderator")
    .all()

.whereNull(column) / .whereNotNull(column)

Filter for NULL or non-NULL values.

var noEmail: any = qb.table("users").whereNull("email").all()
var hasEmail: any = qb.table("users").whereNotNull("email").all()

.whereIn(column, values)

Filter where column value is in a list.

var admins: any = qb.table("users")
    .whereIn("role", ["admin", "moderator"])
    .all()

.whereNotIn(column, values)

Filter where column value is not in a list.

var active: any = qb.table("users")
    .whereNotIn("status", ["banned", "suspended"])
    .all()

.whereBetween(column, min, max)

Filter where column is between two values (inclusive).

var midRange: any = qb.table("products")
    .whereBetween("price", 10, 50)
    .all()

.whereNotBetween(column, min, max)

Filter where column is not between two values.

var outliers: any = qb.table("orders")
    .whereNotBetween("total", 0, 10)
    .all()

.whereRaw(sql, params?)

Add a raw SQL WHERE clause. Use ? for parameter placeholders.

var results: any = qb.table("users")
    .whereRaw("age > ? AND name LIKE ?", [18, "%alice%"])
    .all()

Ordering & Pagination

.orderBy(column, direction?)

Sort results. Direction defaults to "ASC".

var newest: any = qb.table("users").orderBy("created_at", "DESC").all()
var alphabetical: any = qb.table("users").orderBy("name").all()

.limit(n) / .offset(n)

Paginate results.

// Page 2, 10 items per page
var page2: any = qb.table("users")
    .orderBy("id")
    .limit(10)
    .offset(10)
    .all()

.distinct()

Eliminate duplicate rows from the result set.

var cities: any = qb.table("users")
    .distinct()
    .select(["city"])
    .all()

Grouping

.groupBy(columns: []any)

Group results for aggregate queries.

var stats: any = qb.table("orders")
    .select(["status", "COUNT(*) as total"])
    .groupBy(["status"])
    .all()

.having(column, op, value)

Add a HAVING condition for filtering grouped results. Used with .groupBy().

var bigSpenders: any = qb.table("orders")
    .select(["customerId", "SUM(total) as totalSpent"])
    .groupBy(["customerId"])
    .having("SUM(total)", ">", 500)
    .all()

Joins

.join(table, on, type?)

Add a JOIN clause. Type defaults to "INNER".

var result: any = qb.table("users")
    .select(["users.name", "orders.amount"])
    .join("orders", "users.id = orders.user_id")
    .all()

.leftJoin(table, on) / .rightJoin(table, on)

Shorthand for LEFT and RIGHT joins.

var result: any = qb.table("users")
    .leftJoin("profiles", "users.id = profiles.user_id")
    .all()

INSERT / UPDATE / DELETE

.insert(data: map)

Insert a row. Pass a map of column names to values. Returns { rowsAffected, lastInsertId }.

var result: any = qb.table("users").insert({
    "name": "Alice",
    "email": "alice@example.com",
    "age": 30
})
println(result["lastInsertId"]) // 1

.insertMany(rows: []any)

Insert multiple rows in a single statement. All maps must have the same keys. Returns { rowsAffected, lastInsertId }.

var result: any = qb.table("users").insertMany([
    {"name": "Alice", "email": "alice@example.com", "age": 30},
    {"name": "Bob", "email": "bob@example.com", "age": 25},
    {"name": "Charlie", "email": "charlie@example.com", "age": 35}
])
println(result["rowsAffected"]) // 3

.upsert(data: map, conflictColumns: []any)

Insert a row, or update it if a row with the same conflict columns already exists. The conflict columns specify the unique key that determines a conflict. Returns { rowsAffected, lastInsertId }.

// Insert or update based on "email"
qb.table("users").upsert(
    {"name": "Alice", "email": "alice@example.com", "age": 31},
    ["email"]
)

.update(data: map)

Update matching rows. Combine with .where(). Returns { rowsAffected, lastInsertId }.

var result: any = qb.table("users")
    .where("id", "=", 1)
    .update({"age": 31})
println(result["rowsAffected"]) // 1

.delete()

Delete matching rows. Combine with .where(). Returns { rowsAffected, lastInsertId }.

var result: any = qb.table("users")
    .where("id", "=", 1)
    .delete()

Aggregate Functions

.sum(column): any

Return the sum of a column’s values.

var totalRevenue: any = await qb.table("orders").sum("amount")
println(totalRevenue)

.avg(column): any

Return the average of a column’s values.

var avgAge: any = await qb.table("users").avg("age")
println(avgAge)

.min(column): any

Return the minimum value of a column.

var cheapest: any = await qb.table("products").min("price")
println(cheapest)

.max(column): any

Return the maximum value of a column.

var mostExpensive: any = await qb.table("products").max("price")
println(mostExpensive)

Aggregate functions can be combined with WHERE clauses:

var avgSalary: any = await qb.table("employees")
    .where("department", "=", "engineering")
    .avg("salary")

Pagination & Chunking

.paginate(page, perPage): any

Return a paginated result set. Returns a map with data, total, page, perPage, and lastPage.

var result: any = await qb.table("products")
    .orderBy("name")
    .paginate(1, 20)

println(result["data"])      // array of rows for this page
println(result["total"])     // total matching rows
println(result["page"])      // current page number
println(result["perPage"])   // rows per page
println(result["lastPage"])  // last page number

.chunk(size, callback): void

Process rows in batches for memory-efficient iteration over large datasets. The callback receives each batch as an array of rows. Return false from the callback to stop early.

await qb.table("logs").orderBy("id").chunk(100, (batch) => {
    for (var i: int = 0; i < length(batch); i++) {
        println(batch[i]["message"])
    }
    return true // continue to next batch
})

Debugging

.toSql(): map

Get the generated SQL and parameters without executing the query.

var debug: any = qb.table("users")
    .where("age", ">", 18)
    .orderBy("name")
    .limit(5)
    .toSql()

println(debug["sql"])    // SELECT * FROM users WHERE age > ? ORDER BY name ASC LIMIT 5
println(debug["params"]) // [18]

Method Reference

Method	Returns	Description
.table(name)	self	Set table, reset builder
.select(columns)	self	Columns to select
.where(col, op, val)	self	AND condition
.orWhere(col, op, val)	self	OR condition
.whereNull(col)	self	IS NULL condition
.whereNotNull(col)	self	IS NOT NULL condition
.whereIn(col, values)	self	IN (values) condition
.whereNotIn(col, values)	self	NOT IN (values) condition
.whereBetween(col, min, max)	self	BETWEEN min AND max
.whereNotBetween(col, min, max)	self	NOT BETWEEN min AND max
.whereRaw(sql, params?)	self	Raw SQL WHERE clause
.having(col, op, val)	self	HAVING condition (with groupBy)
.distinct()	self	Eliminate duplicate rows
.orderBy(col, dir?)	self	Sort (ASC default)
.limit(n)	self	Max rows
.offset(n)	self	Skip rows
.groupBy(cols)	self	Group results
.join(table, on, type?)	self	JOIN clause
.leftJoin(table, on)	self	LEFT JOIN
.rightJoin(table, on)	self	RIGHT JOIN
.all()	[]any	All matching rows
.first()	any	First row or null
.count()	int	Row count
.sum(col)	any	Sum of column
.avg(col)	any	Average of column
.min(col)	any	Minimum of column
.max(col)	any	Maximum of column
.paginate(page, perPage)	any	Paginated results with metadata
.chunk(size, callback)	void	Process rows in batches
.insert(data)	any	Insert a row
.insertMany(rows)	any	Insert multiple rows
.upsert(data, conflictCols)	any	Insert or update on conflict
.update(data)	any	Update matching rows
.delete()	any	Delete matching rows
.toSql()	any	Get SQL + params

Complete Example

import { db, DbDriver } from "std/db"
import { QueryBuilder } from "std/db/query"

var conn: any = db.open(DbDriver.Sqlite, ":memory:")
var qb: any = new QueryBuilder(conn)

// Schema
db.exec(conn, "CREATE TABLE products (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, price REAL, category TEXT, stock INTEGER DEFAULT 0)")

// Seed data
qb.table("products").insert({"name": "Widget", "price": 9.99, "category": "tools", "stock": 100})
qb.table("products").insert({"name": "Gadget", "price": 24.99, "category": "electronics", "stock": 50})
qb.table("products").insert({"name": "Doohickey", "price": 4.99, "category": "tools", "stock": 200})
qb.table("products").insert({"name": "Thingamajig", "price": 49.99, "category": "electronics", "stock": 25})

// Expensive electronics
var expensive: any = qb.table("products")
    .where("category", "=", "electronics")
    .where("price", ">", 20.0)
    .orderBy("price", "DESC")
    .all()

for (var i: int = 0; i < length(expensive); i++) {
    var p: any = expensive[i]
    println(p["name"] + " — $" + string(p["price"]))
}

// Category totals
var totals: any = qb.table("products")
    .select(["category", "COUNT(*) as count", "SUM(stock) as total_stock"])
    .groupBy(["category"])
    .all()

for (var i: int = 0; i < length(totals); i++) {
    var t: any = totals[i]
    println(t["category"] + ": " + string(t["count"]) + " products, " + string(t["total_stock"]) + " in stock")
}

// Restock low items
qb.table("products")
    .where("stock", "<", 50)
    .update({"stock": 100})

// Verify
var thingamajig: any = qb.table("products")
    .where("name", "=", "Thingamajig")
    .first()
println("Thingamajig stock: " + string(thingamajig["stock"])) // 100

db.close(conn)