Building queries with rqb

1. Install

[dependencies]
rqb = "0.1.7"
sqlx = { version = "0.9.0", default-features = false, features = [
    "postgres",
    "derive",
    "uuid",
    "chrono",
    "json",
    "runtime-tokio",
    "tls-rustls-ring-webpki",
] }
uuid = "1"
serde = { version = "1", features = ["derive"] }
serde_json = "1"

rqb does not select a sqlx runtime or TLS provider. Choose the runtime-* and tls-* features on your application's direct sqlx dependency.

The CLI is a separate crate with a binary named rqb:

cargo install rqb-cli

2. Schema Metadata

rqb queries start from metadata. Small examples can use rqb::schema! directly. Applications usually generate the module with rqb generate and check it into source control.

use rqb::prelude::*;
use uuid::Uuid;

rqb::schema! {
    table public.users {
        id: uuid = Uuid,
        email: text = String,
        status: text = String,
        display_name: text = String,
        active: bool = bool,
    }
}

The generated module exposes users::table(), typed field constants such as users::EMAIL, and alias handles such as users::alias("u"). Use alias handles as soon as a query has more than one source.

Later snippets assume generated users, orders, and events modules plus ordinary application values such as user_id, order_id, patch, new_user, incoming_users, and search_request. Runnable versions of these patterns live in samples/.

3. Select

let query = select(users::table())
    .columns((users::ID, users::EMAIL))
    .filter(users::STATUS.eq("active"))
    .filter(users::ACTIVE.eq(true))
    .order_asc(users::EMAIL)
    .limit(20)
    .build()?;

select(table()) uses metadata fields as the default root projection. Joins and computed columns are explicit:

use rqb::dsl::{count_all, sum};

let u = users::alias("u");
let o = orders::alias("o");

let report = select(&u)
    .join(&o, u.id().eq_field(o.user_id()))
    .expr_as(u.email(), "email")
    .expr_as(sum(o.total_cents()), "gross_cents")
    .expr_as(count_all(), "row_count")
    .group_by(u.email())
    .build()?;

Window expressions stay in the same projection layer:

use rqb::dsl::{sum, window};

let ranked = select(orders::table())
    .column(orders::USER_ID)
    .expr_as(
        sum(orders::TOTAL_CENTS).over(window().partition_by(orders::USER_ID)),
        "user_total_cents",
    )
    .build()?;

JSON aggregates can still decode into typed Rust structs. rqb builds the JSON shape; sqlx and serde decode the returned jsonb column.

use rqb::dsl::{coalesce, literal};

#[derive(serde::Deserialize, serde::Serialize)]
struct OrderSummaryItem {
    id: Uuid,
    status: String,
    total_cents: i64,
}

#[derive(serde::Serialize, sqlx::FromRow)]
struct UserSummaryRow {
    email: String,
    #[sqlx(json)]
    orders: Vec<OrderSummaryItem>,
}

let u = users::alias("u");
let o = orders::alias("o");

let rows = select(&u)
    .left_join(&o, u.id().eq_field(o.user_id()))
    .expr_as(u.email(), "email")
    .expr_as(
        coalesce([
            jsonb_agg_object![o.id(), o.status(), o.total_cents()]
                .aggregate_order_desc(o.created_at())
                .aggregate_filter(o.id().is_not_null()),
            literal("[]").cast("jsonb"),
        ]),
        "orders",
    )
    .group_by(u.id())
    .group_by(u.email())
    .fetch_all_as::<UserSummaryRow>(&pool)
    .await?;

jsonb_agg_object! uses field metadata keys such as total_cents. The nested struct field names, or serde renames, must match those JSON keys. The coalesce keeps users with no orders as [] instead of SQL NULL.

4. Execute

Builders execute through sqlx Postgres executors. Use typed row structs with sqlx::FromRow.

#[derive(sqlx::FromRow)]
struct UserRow {
    id: Uuid,
    email: String,
}

let rows = select(users::table())
    .columns((users::ID, users::EMAIL))
    .filter(users::STATUS.eq("active"))
    .fetch_all_as::<UserRow>(&pool)
    .await?;

Counts are an execution helper on Select. The count query strips ordering, limits, offsets, fetch clauses, and row locks before wrapping the select.

let total = select(users::table())
    .filter(users::ACTIVE.eq(true))
    .count(&pool)
    .await?;

5. Composition

Reusable query shape is usually a plain sync function. That keeps executor lifetimes out of normal service signatures and still works inside transactions.

fn find_user_query(id: Uuid) -> Select {
    select(users::table())
        .columns((users::ID, users::EMAIL))
        .filter(users::ID.eq(id))
}

async fn find_user(pool: &PgPool, id: Uuid) -> rqb::Result<UserRow> {
    find_user_query(id).fetch_one_as::<UserRow>(pool).await
}

CTEs and subqueries remain server-owned query shapes. When a select projects plain fields, infer_cte can infer the exposed field metadata.

let active_users = select(users::table())
    .columns((users::ID, users::EMAIL))
    .filter(users::ACTIVE.eq(true))
    .infer_cte("active_users")?
    .not_materialized();

let au = active_users.source().alias("au");

let active_report = select(au)
    .with(active_users)
    .column(users::EMAIL.at("au"))
    .build()?;

Subqueries work in predicates and value expressions:

use rqb::dsl::exists;

let paid_orders = select(orders::table())
    .column(orders::USER_ID)
    .filter(orders::STATUS.eq("paid"));

let u = users::alias("u");
let o = orders::alias("o");

let paid_users = select(&u)
    .filter(u.id().in_subquery(paid_orders))
    .filter(exists(
        select(&o)
            .column(o.id())
            .filter(o.user_id().eq_field(u.id())),
    ))
    .build()?;

6. JSON Search

SearchRequest lets client JSON control filters, sort, limit, and offset only over fields exposed by metadata. Server Rust code still owns sources, joins, projection, raw SQL, CTEs, and writes.

{
  "filter": {
    "and": [
      { "field": "status", "operator": "equals", "value": "active" },
      { "field": "email", "operator": "iLike", "value": "%@example.com" }
    ]
  },
  "sort": [{ "field": "email", "dir": "asc" }],
  "limit": 20
}

let query = select(users::table())
    .filter(users::ACTIVE.eq(true))
    .apply_search(search_request)?
    .build()?;

7. Writes

Writes use field assignments or derive-generated mappings. rqb does not accept arbitrary JSON as a write shape.

let created_id = insert(users::table())
    .set_many((
        users::ID.set(user_id),
        users::EMAIL.set("ada@example.com"),
        users::STATUS.set("active"),
        users::ACTIVE.set(true),
    ))
    .returning(users::ID)
    .fetch_one_scalar::<Uuid>(&pool)
    .await?;

returning(...) accepts one field or a tuple/list of fields. Insertable and Changeset map DTO fields to generated schema fields without routing values through JSON.

#[derive(rqb::Changeset)]
#[rqb(table = users)]
struct UserPatch {
    email: Option<String>,
    status: Option<String>,
}

update(users::table())
    .patch(&patch)
    .filter(users::ID.eq(user_id))
    .returning(users::ID)
    .execute(&pool)
    .await?;

Common upserts can update several fields from EXCLUDED without repeating set_excluded().

insert(users::table())
    .values(&new_user)
    .on_conflict(users::EMAIL)
    .do_update_excluded((users::STATUS, users::DISPLAY_NAME))
    .returning(users::ID)
    .execute(&pool)
    .await?;

Batch DTO inserts use an inline VALUES source. Conflict updates read the proposed row through EXCLUDED.

insert(users::table())
    .values_many(&incoming_users)?
    .on_conflict(users::EMAIL)
    .do_update_excluded((users::STATUS, users::DISPLAY_NAME))
    .returning(users::ID)
    .execute(&pool)
    .await?;

8. Transactions

Use tx! for closure-style transactions. Use explicit begin and commit only when you need sqlx transaction ownership or savepoint control outside the closure.

tx!(&pool, |conn| {
    let created_id = insert(users::table())
        .set_many((
            users::ID.set(user_id),
            users::EMAIL.set("ada@example.com"),
        ))
        .returning(users::ID)
        .fetch_one_scalar::<Uuid>(conn)
        .await?;

    Ok(created_id)
})
.await?;

Row locks stay on SELECT. Transaction-scoped advisory locks live in rqb::dsl and are safer than session locks when using connection pools.

use rqb::dsl::try_advisory_xact_lock_named;

let order = tx!(&pool, |conn| {
    let acquired = try_advisory_xact_lock_named(format!("job:{job_id}"))
        .fetch_one_scalar::<bool>(&mut *conn)
        .await?;

    if acquired {
        select(orders::table())
            .filter(orders::ID.eq(order_id))
            .for_update()
            .skip_locked()
            .fetch_optional_as::<OrderRow>(&mut *conn)
            .await
    } else {
        Ok(None)
    }
})
.await?;

9. Debug Output

Build once when you want to inspect generated SQL or execute the same validated query more than once.

let built = select(users::table())
    .filter(users::EMAIL.eq("ada@example.com"))
    .build()?;

println!("{}", built.summary());

SQL:
SELECT
    "id",
    "email",
    "status",
    "display_name",
    "active"
FROM "public"."users"
WHERE "email" = $1

Params (1):
$1: String

Cacheable: true

Use built.pretty_sql() when a log or console only needs the formatted SQL text. Bind values are never interpolated.

10. Raw SQL

The typed DSL is not meant to mirror every PostgreSQL construct. Raw fragments are server-owned escape hatches at exact AST slots, and bind counts are still validated.

let raw_stmt = raw("SELECT '?' AS marker, ?::uuid AS id")
    .bind(user_id)
    .build()?;

assert_eq!(raw_stmt.sql, "SELECT '?' AS marker, $1::uuid AS id");

Raw expressions and raw sources compose with typed queries. raw("...") uses fluent .bind(value); slot-level helpers such as raw_expr(...), raw_predicate(...), and raw_source(...) take explicit Param::typed(value) binds. Any raw fragment marks the built query as non-cacheable because rqb cannot prove the text is a stable prepared-statement shape.

let extension_score = raw_expr(
    "custom_extension_score(payload, ?::text)",
    [Param::typed("strict".to_owned())],
);

let scored = select(events::table())
    .column(events::ID)
    .expr_as(extension_score, "extension_score")
    .build()?;

11. Errors

rqb returns one structured Error enum for validation failures, sqlx execution failures, and mapped Postgres SQLSTATE errors. Application code matches variants instead of parsing message text.

match insert(users::table())
    .set(users::EMAIL.set("ada@example.com"))
    .execute(&pool)
    .await
{
    Ok(_) => Ok(()),
    Err(rqb::Error::UniqueViolation(_err)) => Ok(()),
    Err(error) => Err(error),
}

12. CLI

Generate schema metadata from a real Postgres database:

rqb generate \
  --database-url "$DATABASE_URL" \
  --schema public \
  --out src/schema.rs

Use a checked-in config for project-owned domains and extension types:

[type_map."bitcoin.uint256"]
rust = "crate::types::PgU256"
ops = "ordered"
json = "text"
array = true

rqb generate \
  --database-url "$DATABASE_URL" \
  --schema public \
  --config rqb.toml \
  --out src/schema.rs \
  --check \
  --report