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()

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()

Grouping

.groupBy(columns: []any)

Group results for aggregate queries.

var stats: any = qb.table("orders")
    .select(["status", "COUNT(*) as total"])
    .groupBy(["status"])
    .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

.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()

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
.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
.insert(data)	any	Insert a row
.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)