# chain-builder — Guide
A typed, dialect-aware query builder. Generic over a `Dialect`
(PostgreSQL / MySQL / SQLite), with **typed binds** via `IntoBind`, automatic
identifier escaping, dialect-correct placeholders (`$N` for Postgres, `?` for
MySQL/SQLite), and an `sqlx` handoff. See the [README](../README.md) for install
and a quick start.
```toml
chain-builder = { version = "2", features = ["sqlx_postgres"] }
```
## The builder
`QueryBuilder::<D>::table(name)` starts a query for dialect `D`
(`Postgres` / `MySql` / `Sqlite`). Every method is by-value chaining; `to_sql()`
returns `(String, Vec<Value>)`.
```rust
use chain_builder::{QueryBuilder, Postgres, Order};
let (sql, binds) = QueryBuilder::<Postgres>::table("users")
.db("mydb") // multi-tenant: one connection, many DBs
.select(["id", "name"])
.left_join("orders", |j| j.on("users.id", "=", "orders.user_id"))
.where_eq("status", "active")
.where_in("role", ["admin", "staff"])
.group_by(["users.id"])
.order_by("name", Order::Desc)
.paginate(2, 20) // LIMIT/OFFSET
.to_sql();
```
## Typed binds (`IntoBind` / `Value`)
Bind arguments accept any `IntoBind`: integers, `f32`/`f64`, `bool`, `&str`/
`String`, `Vec<u8>`/`&[u8]`, `Option<T>` (`None` → SQL `NULL`), and (with the
`json` feature) `serde_json::Value`. They are stored in an internal `Value` enum
and bound to `sqlx` with the right type — never inlined into SQL.
## WHERE
`where_eq/ne/gt/gte/lt/lte/like`, `where_in/not_in`, `where_null/not_null`,
`where_between`, `where_ilike` (native `ILIKE` on Postgres, `LOWER() LIKE LOWER()`
elsewhere), `where_jsonb_contains` (Postgres `@>`), `where_raw(sql, binds)`.
Group with `and_where(|w| …)` / `or_where(|w| …)` (parenthesized, AND-joined
inner):
```rust
QueryBuilder::<Postgres>::table("users").select(["*"])
.where_eq("active", true)
.or_where(|w| w.where_eq("role", "admin").where_gt("age", 40));
// ... WHERE "active" = $1 OR ("role" = $2 AND "age" > $3)
```
Empty `IN ()` → `1 = 0`; empty `NOT IN ()` → `1 = 1`.
## SELECT / DISTINCT / aggregates
`select([cols])` (bare identifiers, dotted ok), `select_raw(expr, binds)` for
expressions (`COUNT(*)`, `... AS alias`), `distinct()`, `distinct_on([cols])`
(Postgres only — panics elsewhere).
## JOIN / CTE / UNION
- `join` / `inner_join` / `left_join` / `right_join` / `full_outer_join` /
`cross_join`; conditions via the closure: `.on(col, op, col2)`,
`.on_val(col, op, value)`, `.on_raw(sql, binds)`.
- `with(name, query)` / `with_recursive(name, query)` — `WITH [RECURSIVE] …`.
- `union(query)` / `union_all(query)`.
Bind placeholders stay in first-appearance order across CTE → main → UNION (so
Postgres `$1..$n` is correct across nested sub-queries).
## GROUP BY / HAVING / ORDER BY / LIMIT
`group_by([cols])`, `group_by_raw(sql, binds)`, `having(col, op, val)`,
`having_raw(sql, binds)` (for `COUNT(*) > ?`), `order_by(col, Order::Asc|Desc)`
(+ `order_by_asc/desc`), `order_by_raw`, `limit(n)`, `offset(n)`,
`paginate(page, per_page)` (1-based).
## INSERT / UPDATE / DELETE
```rust
QueryBuilder::<Sqlite>::table("users").insert([("name", "John"), ("age", 30)]);
QueryBuilder::<Sqlite>::table("users").insert_many([
[("name", "A"), ("age", 1)],
[("name", "B"), ("age", 2)],
]); // ragged rows bind NULL for missing keys
QueryBuilder::<Sqlite>::table("users").update([("age", 31)]).where_eq("id", 1);
QueryBuilder::<Sqlite>::table("users").delete().where_eq("id", 1);
```
### Upsert + RETURNING
```rust
QueryBuilder::<Postgres>::table("users")
.insert([("email", "a@b.c"), ("name", "A")])
.on_conflict_merge(["email"]) // DO UPDATE SET <non-target cols> = EXCLUDED.<col>
.returning(["id"]);
// .on_conflict_do_nothing(["email"]) → DO NOTHING (Postgres/SQLite) / INSERT IGNORE (MySQL)
```
Dialect map: Postgres/SQLite `ON CONFLICT (targets) DO UPDATE/NOTHING`; MySQL
`ON DUPLICATE KEY UPDATE c = VALUES(c)` (merge) / `INSERT IGNORE` (do-nothing).
`RETURNING` is emitted for Postgres/SQLite and **omitted on MySQL** (no support).
## Dynamic building
```rust
let qb = QueryBuilder::<Postgres>::table("users").select(["*"])
.when(only_active, |q| q.where_eq("status", "active"))
.when_else(by_name, |q| q.order_by_asc("name"), |q| q.order_by_desc("id"));
```
## Execution (sqlx, requires a `sqlx_*` feature)
```rust
// to_sqlx_query() / to_sqlx_query_as::<T>() → sqlx Query / QueryAs
let rows: Vec<UserRow> = qb.fetch_all(&pool).await?; // T: sqlx::FromRow
let one: UserRow = qb.fetch_one(&pool).await?;
let maybe: Option<UserRow> = qb.fetch_optional(&pool).await?;
let n: i64 = qb.count(&pool).await?; // SELECT COUNT(*) FROM (..)
let id: i64 = qb.fetch_scalar(&pool).await?; // first column
qb.execute(&pool).await?; // INSERT/UPDATE/DELETE
```
All three drivers can be enabled at once (`sqlx_mysql` + `sqlx_sqlite` +
`sqlx_postgres`).
## Raw escape hatches
`select_raw`, `where_raw`, `group_by_raw`, `order_by_raw`, `on_raw`, the `having*`
helpers, and `table` set via a dotted string are emitted **verbatim** (not
escaped). For **Postgres**, raw SQL must use the correct `$N` matching the bind
position — the builder does not renumber raw fragments. Never pass untrusted input
through raw methods.
## Known limitations
- Live-DB integration tests require an `sqlx` runtime (the bundled tests are
compile-/string-level).
- No `uuid` / `chrono` / `rust_decimal` `Value` variants yet (the enum is
`#[non_exhaustive]`); JSON path operators and named-constraint upsert targets
are not yet implemented.