Struct QueryBuilder 

pub struct QueryBuilder<D: Dialect> { /* private fields */ }

Typed, dialect-aware SQL query builder.

Implementations

impl<D: Dialect> QueryBuilder<D>

pub fn table(name: &str) -> Self

Start a query against name.

pub fn db(self, name: &str) -> Self

Set the database/schema qualifier (multi-tenant: one connection, many DBs).

The name prefixes the main table and every join table, escaped per dialect: QueryBuilder::<Postgres>::table("users").db("mydb") → … FROM "mydb"."users". Matches 1.x db().

pub fn select<I, S>(self, cols: I) -> Self

where I: IntoIterator<Item = S>, S: AsRef<str>,

Restrict the selected columns. An empty list selects *.

pub fn select_raw(self, sql: &str, binds: Option<Vec<Value>>) -> Self

Add a raw SELECT expression (verbatim, NOT escaped) with optional binds — the escape hatch for aggregates/functions like COUNT(*).

Appended to the column list after any Self::select columns. Multiple calls accumulate.

Warning: positional placeholder contract

sql is emitted verbatim (it is NOT escaped or renumbered) and any binds are appended to the running bind list in order. For Postgres, the caller MUST write $N numbers matching the actual bind position. For MySQL/SQLite use ?.

pub fn select_subquery(self, alias: &str, sub: QueryBuilder<D>) -> Self

Add a subquery SELECT column: emits (<sub>) AS {alias} after the regular columns and any Self::select_raw expressions.

The subquery is compiled with placeholder continuity (its binds appear in $N order at the point it is emitted — before the WHERE clause, since the SELECT list is rendered first). SELECT-only.

pub fn select_count(self, col: &str) -> Self

Add COUNT(col) to the SELECT list (col == "*" → COUNT(*)).

pub fn select_count_as(self, col: &str, alias: &str) -> Self

Add COUNT(col) AS alias (both identifiers escaped; * passed through).

pub fn select_sum(self, col: &str) -> Self

Add SUM(col) to the SELECT list.

pub fn select_sum_as(self, col: &str, alias: &str) -> Self

Add SUM(col) AS alias.

pub fn select_avg(self, col: &str) -> Self

Add AVG(col) to the SELECT list.

pub fn select_avg_as(self, col: &str, alias: &str) -> Self

Add AVG(col) AS alias.

pub fn select_min(self, col: &str) -> Self

Add MIN(col) to the SELECT list.

pub fn select_min_as(self, col: &str, alias: &str) -> Self

Add MIN(col) AS alias.

pub fn select_max(self, col: &str) -> Self

Add MAX(col) to the SELECT list.

pub fn select_max_as(self, col: &str, alias: &str) -> Self

Add MAX(col) AS alias.

pub fn select_as(self, col: &str, alias: &str) -> Self

Add col AS alias to the SELECT list (both identifiers escaped).

pub fn distinct(self) -> Self

Emit SELECT DISTINCT … (all dialects).

pub fn distinct_on<I, S>(self, cols: I) -> Self

where I: IntoIterator<Item = S>, S: AsRef<str>,

Emit SELECT DISTINCT ON (cols) … — Postgres only.

cols are raw identifiers (escaped at compile time). Compiling against a dialect without DISTINCT ON support panics (DISTINCT ON requires PostgreSQL).

pub fn where_ilike(self, col: &str, val: impl IntoBind) -> Self

col ILIKE val — dialect-aware case-insensitive match.

On Postgres this compiles to the native {col} ILIKE {ph}. On MySQL/SQLite (no native ILIKE) it compiles to LOWER({col}) LIKE LOWER({ph}).

pub fn where_jsonb_contains(self, col: &str, val: impl IntoBind) -> Self

col @> val — JSONB containment.

Postgres-specific: the @> operator is emitted verbatim for all dialects, but is only meaningful on Postgres jsonb columns. val is typically a JSON text string (or Value::Json behind the json feature).

pub fn where_eq(self, col: &str, val: impl IntoBind) -> Self

col = val.

pub fn where_ne(self, col: &str, val: impl IntoBind) -> Self

col != val.

pub fn where_gt(self, col: &str, val: impl IntoBind) -> Self

col > val.

pub fn where_gte(self, col: &str, val: impl IntoBind) -> Self

col >= val.

pub fn where_lt(self, col: &str, val: impl IntoBind) -> Self

col < val.

pub fn where_lte(self, col: &str, val: impl IntoBind) -> Self

col <= val.

pub fn where_like(self, col: &str, val: impl IntoBind) -> Self

col LIKE val.

pub fn where_in( self, col: &str, vals: impl IntoIterator<Item = impl IntoBind>, ) -> Self

col IN (...).

pub fn where_not_in( self, col: &str, vals: impl IntoIterator<Item = impl IntoBind>, ) -> Self

col NOT IN (...).

pub fn where_null(self, col: &str) -> Self

col IS NULL.

pub fn where_not_null(self, col: &str) -> Self

col IS NOT NULL.

pub fn where_between( self, col: &str, lo: impl IntoBind, hi: impl IntoBind, ) -> Self

col BETWEEN lo AND hi.

pub fn where_raw(self, sql: &str, binds: Vec<Value>) -> Self

Raw SQL predicate with its own binds — the verbatim escape hatch.

Warning: positional placeholder contract

sql is emitted verbatim (it is NOT escaped or renumbered) and binds are appended to the running bind list in order. For Postgres, the caller MUST write $N numbers matching the actual bind position — that is, number of binds already accumulated + 1, +2, … For MySQL/SQLite use ?. No renumbering is performed, so a wrong $N produces a malformed query.

pub fn where_column(self, lhs: &str, op: &'static str, rhs: &str) -> Self

lhs op rhs — compare two column identifiers (both escaped at compile time), no bind. e.g. where_column("orders.user_id", "=", "users.id").

pub fn where_exists(self, sub: QueryBuilder<D>) -> Self

EXISTS (subquery) — takes an already-built sub-builder by value (mirrors Self::union / Self::with). The sub-query is compiled with placeholder continuity.

pub fn where_not_exists(self, sub: QueryBuilder<D>) -> Self

NOT EXISTS (subquery). See Self::where_exists.

pub fn where_in_subquery(self, col: &str, sub: QueryBuilder<D>) -> Self

col IN (subquery) — takes an already-built sub-builder by value. The sub-query is compiled with placeholder continuity.

pub fn where_not_in_subquery(self, col: &str, sub: QueryBuilder<D>) -> Self

col NOT IN (subquery). See Self::where_in_subquery.

pub fn and_where( self, f: impl FnOnce(WhereBuilder<D>) -> WhereBuilder<D>, ) -> Self

Add a parenthesized AND (...) group built by the closure.

pub fn or_where( self, f: impl FnOnce(WhereBuilder<D>) -> WhereBuilder<D>, ) -> Self

Add a parenthesized OR (...) group built by the closure.

pub fn insert<K, V, I>(self, row: I) -> Self

where K: AsRef<str>, V: IntoBind, I: IntoIterator<Item = (K, V)>,

Build an INSERT from a single row of (column, value) pairs.

pub fn insert_many<K, V, R, Rows>(self, rows: Rows) -> Self

where K: AsRef<str>, V: IntoBind, R: IntoIterator<Item = (K, V)>, Rows: IntoIterator<Item = R>,

Build a multi-row INSERT from an iterator of rows, each a sequence of (column, value) pairs.

The inserted column set is taken from the first row’s keys (sorted, as with Self::insert). For each subsequent row, a value is bound for every column in that set; a key missing in a later row binds Value::Null rather than panicking (DoS-safe, matching the 1.x hardening). Composes with on_conflict_* and returning.

pub fn update<K, V, I>(self, set: I) -> Self

where K: AsRef<str>, V: IntoBind, I: IntoIterator<Item = (K, V)>,

Build an UPDATE from (column, value) pairs. WHERE still applies.

pub fn delete(self) -> Self

Build a DELETE. WHERE still applies.

pub fn on_conflict_do_nothing<I, S>(self, targets: I) -> Self

where I: IntoIterator<Item = S>, S: AsRef<str>,

On conflict, skip the row (INSERT-only; ignored on UPDATE/DELETE).

targets are the conflict-target columns (may be empty).

• Postgres / SQLite: emits ON CONFLICT ({targets}) DO NOTHING, or bare ON CONFLICT DO NOTHING when targets is empty.
• MySQL: emits INSERT IGNORE INTO … (no trailing clause). Note that IGNORE suppresses more than duplicate-key errors (also truncation and bad-value coercion) — broader than pg/sqlite DO NOTHING.

pub fn on_conflict_merge<I, S>(self, targets: I) -> Self

where I: IntoIterator<Item = S>, S: AsRef<str>,

On conflict, update the non-target inserted columns from the proposed row (INSERT-only; ignored on UPDATE/DELETE).

• Postgres / SQLite: emits ON CONFLICT ({targets}) DO UPDATE SET {c} = EXCLUDED.{c}, … for every inserted column except the conflict targets. If targets is empty or covers all inserted columns (empty SET list), falls back to the DO NOTHING rendering (pg/sqlite require a target for DO UPDATE).
• MySQL: the explicit targets are ignored (MySQL uses its own unique/primary keys); emits ON DUPLICATE KEY UPDATE {c} = VALUES({c}), … for all inserted columns. VALUES() is used for MySQL 5.7/8.x compatibility. Including a PK column in the insert set yields a redundant-but-harmless pk = VALUES(pk).

pub fn returning<I, S>(self, cols: I) -> Self

where I: IntoIterator<Item = S>, S: AsRef<str>,

Add a RETURNING column list. Works on INSERT / UPDATE / DELETE for Postgres and SQLite; a "*" column is emitted unescaped (RETURNING *).

On MySQL this is a silent no-op (MySQL has no RETURNING). On SQLite RETURNING requires SQLite ≥ 3.35.0 (2021); supports_returning() is a compile-time dialect flag, not a runtime version check.

pub fn group_by<I, S>(self, cols: I) -> Self

where I: IntoIterator<Item = S>, S: AsRef<str>,

Add GROUP BY columns (raw owned identifiers, escaped at compile time).

SELECT-only: ignored for INSERT/UPDATE/DELETE.

pub fn group_by_raw(self, sql: &str, binds: Vec<Value>) -> Self

Add a raw GROUP BY fragment with its own binds — the verbatim escape hatch. SELECT-only.

The fragment is appended after any structured Self::group_by columns within the same GROUP BY clause (e.g. GROUP BY "a", <raw>); if no structured columns are present it becomes the whole GROUP BY <raw>.

Warning: positional placeholder contract

sql is emitted verbatim (it is NOT escaped or renumbered) and binds are appended to the running bind list in order. For Postgres, the caller MUST write $N numbers matching the actual bind position — that is, number of binds already accumulated + 1, +2, … For MySQL/SQLite use ?. No renumbering is performed, so a wrong $N produces a malformed query.

pub fn order_by_raw(self, sql: &str, binds: Vec<Value>) -> Self

Add a raw ORDER BY fragment with its own binds — the verbatim escape hatch. SELECT-only.

The fragment is appended after any structured Self::order_by terms within the same ORDER BY clause (e.g. ORDER BY "a" ASC, <raw>); if no structured terms are present it becomes the whole ORDER BY <raw>.

Warning: positional placeholder contract

sql is emitted verbatim (it is NOT escaped or renumbered) and binds are appended to the running bind list in order. For Postgres, the caller MUST write $N numbers matching the actual bind position — that is, number of binds already accumulated + 1, +2, … For MySQL/SQLite use ?. No renumbering is performed, so a wrong $N produces a malformed query.

pub fn order_by(self, col: &str, ord: Order) -> Self

Add an ORDER BY col <ord> term. SELECT-only.

pub fn order_by_asc(self, col: &str) -> Self

Add an ORDER BY col ASC term. SELECT-only.

pub fn order_by_desc(self, col: &str) -> Self

Add an ORDER BY col DESC term. SELECT-only.

pub fn limit(self, n: i64) -> Self

Set LIMIT n (bound as a placeholder). SELECT-only.

pub fn offset(self, n: i64) -> Self

Set OFFSET n (bound as a placeholder). SELECT-only.

offset requires limit: compiling an offset without a limit panics (offset(...) requires limit(...)), uniform across dialects since MySQL rejects a bare OFFSET.

pub fn for_update(self) -> Self

Lock selected rows with FOR UPDATE.

Honored by Postgres / MySQL; a silent no-op on SQLite. Preserves any SKIP LOCKED / NOWAIT modifier already set. SELECT-only: compiling panics if attached to INSERT/UPDATE/DELETE or combined with UNION.

pub fn for_share(self) -> Self

Lock selected rows with FOR SHARE.

Honored by Postgres / MySQL; a silent no-op on SQLite. Preserves any SKIP LOCKED / NOWAIT modifier already set. SELECT-only: compiling panics if attached to INSERT/UPDATE/DELETE or combined with UNION.

pub fn skip_locked(self) -> Self

Add SKIP LOCKED to the row-locking clause (skip already-locked rows).

If no lock strength was set yet, defaults to FOR UPDATE. SELECT-only; no-op on SQLite.

pub fn no_wait(self) -> Self

Add NOWAIT to the row-locking clause (error if a row is already locked).

If no lock strength was set yet, defaults to FOR UPDATE. SELECT-only; no-op on SQLite.

pub fn join( self, table: &str, f: impl FnOnce(JoinClause<D>) -> JoinClause<D>, ) -> Self

INNER JOIN table ON … — conditions built by the closure.

SELECT-only: ignored for INSERT/UPDATE/DELETE.

pub fn left_join( self, table: &str, f: impl FnOnce(JoinClause<D>) -> JoinClause<D>, ) -> Self

LEFT JOIN table ON …. SELECT-only.

pub fn right_join( self, table: &str, f: impl FnOnce(JoinClause<D>) -> JoinClause<D>, ) -> Self

RIGHT JOIN table ON …. SELECT-only.

pub fn full_outer_join( self, table: &str, f: impl FnOnce(JoinClause<D>) -> JoinClause<D>, ) -> Self

FULL OUTER JOIN table ON …. SELECT-only.

pub fn cross_join(self, table: &str) -> Self

CROSS JOIN table — takes no ON closure (a cross join has no condition). SELECT-only.

pub fn having(self, col: &str, op: &str, val: impl IntoBind) -> Self

HAVING col op ? — col is a real column/alias (escaped); value bound.

For aggregate expressions like COUNT(*) > ?, use Self::having_raw. SELECT-only: ignored for INSERT/UPDATE/DELETE. Multiple HAVING terms are joined by AND.

pub fn having_raw(self, sql: &str, binds: Vec<Value>) -> Self

Raw HAVING expression with its own binds — the verbatim escape hatch for aggregates (e.g. having_raw("COUNT(*) > ?", …)).

Warning: positional placeholder contract

sql is emitted verbatim (it is NOT escaped or renumbered) and binds are appended to the running bind list in order. For Postgres, the caller MUST write $N numbers matching the actual bind position — that is, number of binds already accumulated + 1, +2, … For MySQL/SQLite use ?. No renumbering is performed, so a wrong $N produces a malformed query.

pub fn with(self, name: &str, query: QueryBuilder<D>) -> Self

Add a WITH name AS (query) common table expression. SELECT-only.

CTE bodies are compiled before the main query, so their binds (and pg $N numbers) appear first.

pub fn with_recursive(self, name: &str, query: QueryBuilder<D>) -> Self

Add a recursive CTE. If any CTE is recursive, the single WITH carries RECURSIVE. SELECT-only.

pub fn union(self, query: QueryBuilder<D>) -> Self

Append a UNION query arm. SELECT-only.

pub fn union_all(self, query: QueryBuilder<D>) -> Self

Append a UNION ALL query arm. SELECT-only.

pub fn when(self, cond: bool, f: impl FnOnce(Self) -> Self) -> Self

Conditionally apply f to the builder, keeping the chain intact.

Returns f(self) when cond is true, otherwise self unchanged. This lets you add clauses based on a runtime flag without breaking the by-value chain.

use chain_builder::v2::{Postgres, QueryBuilder};
let only_active = true;
let (sql, _) = QueryBuilder::<Postgres>::table("users")
    .select(["id"])
    .when(only_active, |q| q.where_eq("status", "active"))
    .to_sql();
assert_eq!(sql, r#"SELECT "id" FROM "users" WHERE "status" = $1"#);

pub fn when_else( self, cond: bool, if_true: impl FnOnce(Self) -> Self, if_false: impl FnOnce(Self) -> Self, ) -> Self

Apply if_true when cond holds, otherwise if_false, keeping the chain intact.

use chain_builder::v2::{Postgres, QueryBuilder};
let active = false;
let (sql, _) = QueryBuilder::<Postgres>::table("users")
    .select(["id"])
    .when_else(
        active,
        |q| q.where_eq("status", "active"),
        |q| q.where_eq("status", "inactive"),
    )
    .to_sql();
assert_eq!(sql, r#"SELECT "id" FROM "users" WHERE "status" = $1"#);

pub fn paginate(self, page: i64, per_page: i64) -> Self

Apply LIMIT/OFFSET for a 1-based page: row window [(page-1) * per_page, page * per_page).

Equivalent to self.limit(per_page).offset((page - 1).max(0) * per_page). A page < 1 is treated as page 1 (offset 0), so callers never get a negative offset. SELECT-only, like Self::limit / Self::offset.

use chain_builder::v2::{Postgres, QueryBuilder, Value};
let (sql, binds) = QueryBuilder::<Postgres>::table("users")
    .select(["id"])
    .paginate(2, 10)
    .to_sql();
assert_eq!(sql, r#"SELECT "id" FROM "users" LIMIT $1 OFFSET $2"#);
assert_eq!(binds, vec![Value::I64(10), Value::I64(10)]);

pub fn to_sql(&self) -> (String, Vec<Value>)

Compile to (sql, binds).

impl<D: SqlxDialect> QueryBuilder<D>

pub async fn fetch_all<'e, T, E>(&self, executor: E) -> Result<Vec<T>, Error>

where T: for<'r> FromRow<'r, <D::Database as Database>::Row> + Send + Unpin, E: Executor<'e, Database = D::Database>,

Fetch all rows, decoding each into T.

Delegates to self.to_sqlx_query_as::<T>().fetch_all(executor).

pub async fn fetch_one<'e, T, E>(&self, executor: E) -> Result<T, Error>

where T: for<'r> FromRow<'r, <D::Database as Database>::Row> + Send + Unpin, E: Executor<'e, Database = D::Database>,

Fetch exactly one row, decoding it into T.

Errors with sqlx::Error::RowNotFound if no row is returned.

pub async fn fetch_optional<'e, T, E>( &self, executor: E, ) -> Result<Option<T>, Error>

where T: for<'r> FromRow<'r, <D::Database as Database>::Row> + Send + Unpin, E: Executor<'e, Database = D::Database>,

Fetch at most one row, decoding it into T.

pub async fn execute<'e, E>( &self, executor: E, ) -> Result<<D::Database as Database>::QueryResult, Error>

where E: Executor<'e, Database = D::Database>,

Execute the query (INSERT / UPDATE / DELETE / DDL), returning the database’s QueryResult (affected rows, last insert id, …).

Delegates to self.to_sqlx_query().execute(executor).

pub async fn count<'e, E>(&self, executor: E) -> Result<i64, Error>

where E: Executor<'e, Database = D::Database>, i64: for<'r> Decode<'r, D::Database> + Type<D::Database>, usize: ColumnIndex<<D::Database as Database>::Row>,

Count the rows the query would return.

Wraps the built SQL as SELECT COUNT(*) FROM (<sql>) AS __cb_count, binds the same arguments, and fetches a single i64. Mirrors 1.x ChainBuilder::count.

pub async fn fetch_scalar<'e, T, E>(&self, executor: E) -> Result<T, Error>

where T: for<'r> Decode<'r, D::Database> + Type<D::Database> + Send + Unpin, E: Executor<'e, Database = D::Database>, usize: ColumnIndex<<D::Database as Database>::Row>,

Fetch the first column of the first row, decoded into T.

Errors with sqlx::Error::RowNotFound if no row is returned. Useful for pluck/aggregate-style queries.

pub async fn fetch_optional_scalar<'e, T, E>( &self, executor: E, ) -> Result<Option<T>, Error>

where T: for<'r> Decode<'r, D::Database> + Type<D::Database> + Send + Unpin, E: Executor<'e, Database = D::Database>, usize: ColumnIndex<<D::Database as Database>::Row>,

Fetch the first column of the first row (if any), decoded into T.

impl<D: SqlxDialect> QueryBuilder<D>

pub fn to_sqlx_query( &self, ) -> Query<'_, D::Database, <D::Database as Database>::Arguments>

Build an executable sqlx::query::Query from this builder.

The SQL is builder-generated with bound placeholders, so it is asserted safe via sqlx::AssertSqlSafe (which also satisfies sqlx 0.9’s 'static SQL bound). Mirrors 1.x ChainBuilder::to_sqlx_query.

pub fn to_sqlx_query_as<T>( &self, ) -> QueryAs<'_, D::Database, T, <D::Database as Database>::Arguments>

where T: for<'r> FromRow<'r, <D::Database as Database>::Row>,

Build an executable sqlx::query::QueryAs decoding rows into T.

Trait Implementations

impl<D: Clone + Dialect> Clone for QueryBuilder<D>

fn clone(&self) -> QueryBuilder<D>

Returns a duplicate of the value.

fn clone_from(&mut self, source: &Self)

Performs copy-assignment from source.

impl<D: Debug + Dialect> Debug for QueryBuilder<D>

fn fmt(&self, f: &mut Formatter<'_>) -> Result

Formats the value using the given formatter.

impl<D: PartialEq + Dialect> PartialEq for QueryBuilder<D>

fn eq(&self, other: &QueryBuilder<D>) -> bool

Tests for self and other values to be equal, and is used by ==.

fn ne(&self, other: &Rhs) -> bool

Tests for !=. The default implementation is almost always sufficient, and should not be overridden without very good reason.

impl<D: Dialect> StructuralPartialEq for QueryBuilder<D>