type Colony {
name: Str
apiary: Str
frames: Int
}
query findColonies(apiary: Str, minFrames: Int, small: Bool, huge: Bool): Colony[dyn] {
SELECT name, apiary, frames FROM colonies
WHERE {
if apiary != "" { apiary = {apiary} }
if minFrames > 0 { frames >= {minFrames} }
or {
if small { frames < 4 }
if huge { frames > 10 }
}
}
ORDER BY name
}
proc main(): void {
opened := hive.sql.pool(hive.sql.DatabaseDriver.SQLite(), "file::memory:?cache=shared", 1, 1)
if opened is Result.Ok(db) {
hive.sql.raw(db, "CREATE TABLE colonies (name TEXT, apiary TEXT, frames INTEGER)")
hive.sql.raw(db, "INSERT INTO colonies VALUES ('north', 'orchard', 12)")
hive.sql.raw(db, "INSERT INTO colonies VALUES ('south', 'orchard', 2)")
hive.sql.raw(db, "INSERT INTO colonies VALUES ('east', 'meadow', 7)")
// Everything off: no predicate holds, so there is no WHERE at all.
if hive.sql.run(db, findColonies("", 0, false, false)) is Result.Ok(all) {
echo len(all)
}
// apiary AND frames >= 5 AND (frames > 10).
if hive.sql.run(db, findColonies("orchard", 5, false, true)) is Result.Ok(some) {
echo some
}
hive.sql.close(db)
}
}A search screen with four boxes on it is four optional predicates, and assembling that as text is where SQL injection and WHERE 1 = 1 both come from. A WHERE block is the declaration that replaces it: it ANDs together the predicates whose conditions hold, and a nested or { } or and { } flips the connective for the group inside it.
A group that contributes nothing disappears rather than leaving a dangling connective, a group contributing more than one predicate is parenthesised, and when no predicate holds there is no WHERE at all — no 1 = 1 to write, and none to read back in a log. Every branch's text is fixed when the program compiles; the only thing decided while it runs is which branches are taken, and the values still travel as bound parameters.
Two things a WHERE block deliberately cannot do. A column name or a sort direction can never be a parameter — ORDER BY {col} would order every row by one constant string — so make that choice a variant type and pick one query per ordering, each answering with the same hive.sql.Fragment<Row[dyn]>. And SQL you genuinely assemble yourself goes through hive.sql.raw, untyped by construction and easy to find.