Query Builder

Use QueryBuilder to construct typed async SELECT, COUNT, DELETE, and UPDATE queries without writing SQL.

On this page

QueryBuilder<T> is obtained from T::query(&pool) and provides a fluent API for building queries against the table mapped to T. All builder methods consume self and return a new QueryBuilder. Terminal methods (fetch_all, fetch_one, count, delete, update) are async fn.

Obtaining a builder

use rust_web_server::model::DbPool;

let pool = DbPool::from_env().await?;

// QueryBuilder<User> — tied to the `users` table
let qb = User::query(&pool);

Filtering

Equality filter

where_eq(col, val) adds a col = ? condition. The placeholder is ? for SQLite and MySQL, $N for PostgreSQL — the builder handles the substitution automatically.

let admins: Vec<User> = User::query(&pool)
    .where_eq("role", "admin")
    .fetch_all().await?;

Chain multiple calls to AND conditions together:

let result = User::query(&pool)
    .where_eq("role", "admin")
    .where_eq("active", true)
    .fetch_all().await?;

Raw filter

filter(expr, params) accepts a raw SQL fragment and a Vec<Value>. Use ? as the placeholder regardless of backend — the builder converts to $N for PostgreSQL automatically.

use rust_web_server::model::Value;

let adults = User::query(&pool)
    .filter("age >= ?", vec![Value::Int(18)])
    .fetch_all().await?;

IN and NULL filters

where_in(col, values) adds col IN (?, ?, …) (no values match no rows); where_null(col) and where_not_null(col) add IS NULL / IS NOT NULL (18.30.0).

let staff = User::query(&pool)
    .where_in("role", ["admin", "moderator"])
    .where_null("banned_at")
    .fetch_all().await?;

Scopes

A scope is a reusable piece of a query: a function from a QueryBuilder to a narrower one. .scope(f) applies it (18.30.0); scopes take arguments by being closures, and compose:

use rust_web_server::model::QueryBuilder;

fn published(q: QueryBuilder<'_, Post>) -> QueryBuilder<'_, Post> {
    q.where_eq("published", true).where_null("hidden_at")
}

fn by_author(id: i64) -> impl FnOnce(QueryBuilder<'_, Post>) -> QueryBuilder<'_, Post> {
    move |q| q.where_eq("author_id", id)
}

let mine = Post::query(&pool).scope(published).scope(by_author(7)).count().await?;

To call them as methods — Post::query(&pool).published() — put them in an extension trait for that model's builder:

pub trait PostScopes {
    fn published(self) -> Self;
    fn recent(self, n: u64) -> Self;
}

impl PostScopes for QueryBuilder<'_, Post> {
    fn published(self) -> Self {
        self.where_eq("published", true)
    }
    fn recent(self, n: u64) -> Self {
        self.order_by("created_at", Order::Desc).limit(n)
    }
}

let front_page = Post::query(&pool).published().recent(10).with(Post::author()).fetch_all().await?;

Scopes run before the terminal call, so they work with count, paginate, update, delete and with() alike.

Ordering

use rust_web_server::model::Order;

let recent = User::query(&pool)
    .order_by("created_at", Order::Desc)
    .fetch_all().await?;

let alphabetical = User::query(&pool)
    .order_by("name", Order::Asc)
    .fetch_all().await?;

Pagination

.limit(n) and .offset(n) map directly to SQL LIMIT and OFFSET, for full manual control:

let page = 2u64;
let page_size = 20u64;

let users = User::query(&pool)
    .order_by("id", Order::Asc)
    .limit(page_size)
    .offset((page - 1) * page_size)
    .fetch_all().await?;

For most list endpoints, .paginate(page, per_page) (offset-based, with total counts) and .paginate_after(cursor, per_page) (cursor/keyset-based, for large tables) do the COUNT(*) + LIMIT/OFFSET bookkeeping above for you and return a Page<T>/CursorPage<T> — see Pagination for both, plus a built-in Link response header builder.

Fetching results

Method SQL Return type
fetch_all().await SELECT * FROM … WHERE … ORDER BY … LIMIT … OFFSET … Result<Vec<T>, DbError>
fetch_one().await same with LIMIT 1 Result<Option<T>, DbError>
count().await SELECT COUNT(*) FROM … WHERE … Result<i64, DbError>
// all matching rows
let users: Vec<User> = User::query(&pool)
    .where_eq("active", true)
    .fetch_all().await?;

// first match only
let user: Option<User> = User::query(&pool)
    .where_eq("email", "alice@example.com")
    .fetch_one().await?;

// count without loading rows
let total: i64 = User::query(&pool)
    .where_eq("role", "admin")
    .count().await?;

Mutation

Delete matching rows

User::query(&pool)
    .where_eq("active", false)
    .delete().await?;

Update a single column

update(col, val).await issues UPDATE table SET col = ? WHERE …. Combine with filters to scope the update. On a model with #[timestamps] it also sets updated_at, with #[version] it increments the version.

User::query(&pool)
    .where_eq("id", 42i64)
    .update("role", "moderator").await?;

Soft-deleted rows

For a #[soft_delete] model every builder operation skips soft-deleted rows; .with_deleted() includes them and .only_deleted() keeps only them. delete() sets deleted_at, force_delete() removes rows (answering how many), restore() clears deleted_at on the matching deleted rows. See soft delete.

count() leaves the chain's order_by out (PostgreSQL refuses ORDER BY beside COUNT(*)).

Placeholder rules

The builder transparently converts between placeholder styles:

  • SQLite / MySQL — ?
  • PostgreSQL — $1, $2, … (auto-numbered from left to right)

You never need to pick a style; write ? in raw .filter() expressions and the builder takes care of the rest.

Complete example: paginated list endpoint

use rust_web_server::header::Header;
use rust_web_server::model::{DbPool, Order, Page};
use rust_web_server::response::{Response, STATUS_CODE_REASON_PHRASE};
use std::sync::Arc;

async fn list_users(pool: Arc<DbPool>, page: u64, per_page: u64) -> Response {
    let per_page = per_page.min(100);

    let page: Page<User> = User::query(&pool)
        .where_eq("active", true)
        .order_by("created_at", Order::Desc)
        .paginate(page, per_page).await
        .unwrap_or_else(|_| Page::new(vec![], page, per_page, 0));

    let mut r = Response::new();
    r.status_code = *STATUS_CODE_REASON_PHRASE.n200_ok.status_code;
    r.reason_phrase = STATUS_CODE_REASON_PHRASE.n200_ok.reason_phrase.to_string();
    if let Some(link) = page.link_header("https://api.example.com/users") {
        r.headers.push(Header::new("Link", link));
    }
    // serialize `page.items`, `page.total_items`, `page.total_pages` into the response body
    r
}

See Pagination for .paginate_after() (cursor/keyset pagination) and more on the Link header.

Zero rows is not an error

fetch_all().await returns an empty Vec when no rows match — it only returns Err on a real database error. Similarly, fetch_one().await returns Ok(None) for no match.

Last updated · Edit this page on GitHub · Markdown