Chain builder

Appearance

WHERE ​

Predicates are the heart of the builder: every where_* method records a typed predicate, and every value you pass becomes a bound parameter — never text spliced into the SQL. Consecutive top-level predicates are joined with AND; OR and explicit parentheses come from the group closures below. This page lists every predicate method, the empty-IN semantics, the dialect-aware ones (where_ilike, where_jsonb_contains), subquery predicates, and the where_raw escape hatch.

Comparison predicates ​

where_eq, where_ne, where_gt, where_gte, where_lt, where_lte emit col = ?, col != ?, col > ?, col >= ?, col < ?, col <= ? respectively. where_like emits col LIKE ?:

rust
let (sql, binds) = QueryBuilder::<Postgres>::table("t")
    .where_ne("a", 1i64)
    .where_gte("b", 2i64)
    .where_lt("c", 3i64)
    .where_lte("d", 4i64)
    .where_like("e", "%x%")
    .to_sql();
// SELECT * FROM "t" WHERE "a" != $1 AND "b" >= $2 AND "c" < $3 AND "d" <= $4 AND "e" LIKE $5

⚠️ where_like caveat — the pattern is bound as a value (no SQL injection possible), but SQL LIKE wildcards % and _ inside user input are NOT escaped. A user who types % matches everything. If you build patterns from untrusted input, escape the wildcards yourself — see Case-insensitive Search.

where_in / where_not_in ​

Take any iterable of bindable values and emit one placeholder per element:

rust
let (sql, binds) = QueryBuilder::<Postgres>::table("users")
    .select(["id", "name"])
    .where_eq("status", "active")
    .where_in("role", ["admin", "staff"])
    .where_gt("age", 18i64)
    .to_sql();
// SELECT "id", "name" FROM "users" WHERE "status" = $1 AND "role" IN ($2, $3) AND "age" > $4

Empty-IN semantics. SQL has no valid IN (), so the builder substitutes a constant predicate with the logically correct truth value:

  • empty where_in → 1 = 0 (nothing matches — no value is in an empty set)
  • empty where_not_in → 1 = 1 (everything matches)
rust
let (sql, binds) = QueryBuilder::<Postgres>::table("users")
    .where_in("x", Vec::<i64>::new())
    .to_sql();
// SELECT * FROM "users" WHERE 1 = 0

let (sql, binds) = QueryBuilder::<Postgres>::table("users")
    .where_not_in("x", Vec::<i64>::new())
    .to_sql();
// SELECT * FROM "users" WHERE 1 = 1

This makes list-driven filters (e.g. from an HTTP query string) safe to chain without special-casing the empty list.

where_null / where_not_null ​

Emit col IS NULL / col IS NOT NULL — no binds:

rust
let (sql, binds) = QueryBuilder::<Postgres>::table("t")
    .where_null("a")
    .where_not_null("b")
    .to_sql();
// SELECT * FROM "t" WHERE "a" IS NULL AND "b" IS NOT NULL
// binds is empty

where_between ​

where_between(col, lo, hi) emits col BETWEEN ? AND ? with two binds:

rust
let (sql, binds) = QueryBuilder::<Postgres>::table("t")
    .where_between("age", 18i64, 65i64)
    .to_sql();
// SELECT * FROM "t" WHERE "age" BETWEEN $1 AND $2

where_ilike — dialect-aware case-insensitive match ​

On Postgres this compiles to the native ILIKE operator. MySQL and SQLite have no ILIKE, so the builder lowers it to LOWER(col) LIKE LOWER(?) — same semantics, portable chain:

rust
let (sql, binds) = QueryBuilder::<Postgres>::table("t")
    .select(["a"])
    .where_ilike("name", "%jo%")
    .to_sql();
// SELECT "a" FROM "t" WHERE "name" ILIKE $1
rust
let (sql, binds) = QueryBuilder::<MySql>::table("t")
    .select(["a"])
    .where_ilike("name", "%jo%")
    .to_sql();
// SELECT `a` FROM `t` WHERE LOWER(`name`) LIKE LOWER(?)

The where_like wildcard caveat applies here too — see Case-insensitive Search for the full recipe.

where_jsonb_contains — JSONB containment ​

where_jsonb_contains(col, val) emits col @> ?. The value is typically a JSON text string (or Value::Json behind the json feature — see Binds & Values):

rust
let (sql, binds) = QueryBuilder::<Postgres>::table("t")
    .select(["a"])
    .where_jsonb_contains("meta", "{\"a\":1}")
    .to_sql();
// SELECT "a" FROM "t" WHERE "meta" @> $1

Dialect note — the @> operator is emitted verbatim on ALL dialects; the builder does not lower it for MySQL/SQLite, where the resulting SQL is invalid or means something else. This predicate is only meaningful on Postgres jsonb columns.

where_column — column-to-column comparison ​

where_column(lhs, op, rhs) compares two columns (both escaped, no bind). op is a &'static str, so the operator cannot be built from runtime input:

rust
let (sql, binds) = QueryBuilder::<Postgres>::table("t")
    .select(["x"])
    .where_column("a.x", "=", "b.y")
    .to_sql();
// SELECT "x" FROM "t" WHERE "a"."x" = "b"."y"

Its main job is correlating subqueries with the outer query (next section).

Subquery predicates ​

where_exists / where_not_exists and where_in_subquery / where_not_in_subquery take an already-built sub-builder by value. The subquery is compiled with placeholder continuity — its binds continue the outer query's $N numbering at the point the predicate is emitted:

rust
let (sql, binds) = QueryBuilder::<Postgres>::table("users")
    .select(["id"])
    .where_eq("active", true)
    .where_exists(
        QueryBuilder::<Postgres>::table("orders")
            .select(["1"])
            .where_column("orders.user_id", "=", "users.id")
            .where_gt("total", 100i64),
    )
    .to_sql();
// SELECT "id" FROM "users" WHERE "active" = $1 AND EXISTS (SELECT "1" FROM "orders" WHERE "orders"."user_id" = "users"."id" AND "total" > $2)
rust
let (sql, binds) = QueryBuilder::<Postgres>::table("users")
    .select(["id"])
    .where_in_subquery(
        "id",
        QueryBuilder::<Postgres>::table("ban")
            .select(["user_id"])
            .where_eq("k", 7i64),
    )
    .to_sql();
// SELECT "id" FROM "users" WHERE "id" IN (SELECT "user_id" FROM "ban" WHERE "k" = $1)

where_not_exists renders NOT EXISTS (…); where_not_in_subquery renders col NOT IN (…). For subqueries in the SELECT list, see select_subquery.

Groups: and_where / or_where ​

To get parentheses and OR, pass a closure to and_where or or_where. The closure receives a WhereBuilder exposing the predicate methods, plus and_where/or_where again, so groups nest. The method name decides how the group attaches to what precedes it (AND (…) vs OR (…)); inside the group, predicates are joined with AND unless they are themselves or_where sub-groups:

rust
let (sql, binds) = QueryBuilder::<Postgres>::table("users")
    .where_eq("active", true)
    .or_where(|w| w.where_eq("role", "admin").where_gt("age", 40i64))
    .to_sql();
// SELECT * FROM "users" WHERE "active" = $1 OR ("role" = $2 AND "age" > $3)

Nesting — an or_where inside an and_where group attaches with OR:

rust
let (sql, binds) = QueryBuilder::<Postgres>::table("t")
    .select(["*"])
    .and_where(|g| g.where_eq("a", 1i64).or_where(|h| h.where_eq("b", 2i64)))
    .to_sql();
// SELECT * FROM "t" WHERE ("a" = $1 OR ("b" = $2))

Since 3.1.0 the four subquery predicates are also available inside groups, with the same contracts (placeholder continuity included):

rust
let (sql, _) = QueryBuilder::<Postgres>::table("users")
    .select(["id"])
    .and_where(|g| {
        g.where_in_subquery(
            "id",
            QueryBuilder::<Postgres>::table("ban")
                .select(["user_id"])
                .where_eq("k", 7i64),
        )
        .where_not_exists(
            QueryBuilder::<Postgres>::table("audit")
                .select(["1"])
                .where_eq("level", 3i64),
        )
    })
    .to_sql();
// SELECT "id" FROM "users" WHERE ("id" IN (SELECT "user_id" FROM "ban" WHERE "k" = $1) AND NOT EXISTS (SELECT "1" FROM "audit" WHERE "level" = $2))

Two edge cases are handled for you:

  • A group as the first predicate has nothing to attach to, so no leading AND/OR is emitted: .or_where(|w| w.where_eq("x", 1i64)) alone renders WHERE ("x" = $1).
  • Empty groups are omitted entirely. A closure that adds no predicates (common with conditional chains) produces no (), no dangling AND/OR — and if the empty group was the only predicate, no WHERE at all:
rust
let (sql, binds) = QueryBuilder::<Postgres>::table("t")
    .where_eq("a", 1i64)
    .and_where(|w| w)
    .to_sql();
// SELECT * FROM "t" WHERE "a" = $1

let (sql, binds) = QueryBuilder::<Postgres>::table("t")
    .and_where(|w| w)
    .to_sql();
// SELECT * FROM "t"

This makes groups safe to combine with when-style conditional building.

where_raw — the escape hatch ​

where_raw(sql, binds) records a raw SQL predicate with its own binds, for operators and expressions the structured API does not model:

⚠️ Positional placeholder contract

The sql fragment is emitted verbatim — it is NOT escaped and NOT renumbered. binds are appended to the running bind list in order. On Postgres you must hand-write $N numbers matching the actual bind position (number of binds already accumulated + 1, + 2, …); on MySQL/SQLite use ?. A wrong $N produces a malformed query. Never build the fragment itself from untrusted input — see the Security Model.

rust
// Seven binds precede the raw predicate (a, b, c, d, e, and h's two BETWEEN
// bounds), so its single bind is the 8th → `$8`.
let (sql, binds) = QueryBuilder::<Postgres>::table("t")
    .where_ne("a", 1i64)
    .where_gte("b", 2i64)
    .where_lt("c", 3i64)
    .where_lte("d", 4i64)
    .where_like("e", "%x%")
    .where_null("f")
    .where_not_null("g")
    .where_between("h", 5i64, 6i64)
    .where_raw("j @> $8", vec![Value::Text("raw".into())])
    .to_sql();
// SELECT * FROM "t" WHERE "a" != $1 AND "b" >= $2 AND "c" < $3 AND "d" <= $4 AND "e" LIKE $5 AND "f" IS NULL AND "g" IS NOT NULL AND "h" BETWEEN $6 AND $7 AND j @> $8