# Query Builder

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

Source: https://rws8.tech/docs/database/query-builder/ · Last updated 2026-11-04

`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

```rust
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.

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

Chain multiple calls to AND conditions together:

```rust
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.

```rust
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).

```rust
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:

```rust
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:

```rust
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()`](/database/relations/#eager-loading-with-with) alike.

## Ordering

```rust
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:

```rust
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](/database/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>` |

```rust
// 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

```rust
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.

```rust
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](/database/conventions/#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

```rust
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](/database/pagination/) for `.paginate_after()` (cursor/keyset pagination) and more on the `Link` header.

:::note[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.
:::
