Skip to content
rentdocsContactGet early access
Tutorials

Querying collections

Maya wants to browse the app's author directory. The sample includes Maya, Ken, Eve, and another author also named Ken: names can repeat even though user IDs cannot. Generated field methods filter that collection, while standard query methods shape the result:

let page = client
    .user()
    .id_ge(2)
    .order_by(user::User::id)
    .offset(1)
    .limit(2)
    .all()
    .await?;

let names = client
    .user()
    .order_by(user::User::id)
    .select_name()
    .all()
    .await?;

let count = client
    .user()
    .count()
    .await?;

For alternatives, compose model-safe filters. Ordinary chained filters use AND. This example selects user 1 or 3:

use generated::user::User;

let selected = client
    .user()
    .filter(User::id.eq(1) | User::id.eq(3))
    .order_by(user::User::id)
    .all()
    .await?;

Use & or .and(...) to combine conditions and !filter to negate one. These filters also work for updates and deletes. Mixing a post filter into a user operation is a compile error. Advanced untyped SQL goes through filter_raw(expression) and still honors policies.

Inspect the exact policy-aware SQL without executing it. Bind values stay redacted, so the result is safe to attach to logs and bug reports:

let inspection = client
    .user()
    .name_eq("Ken")
    .to_sql()?;

assert!(!inspection.sql.contains("Ken"));
assert_eq!(inspection.bindings, ["string"]);

explain() renders the backend-native query-plan statement. explain_analyze() adds runtime analysis where the database supports it. On SQLite it uses EXPLAIN QUERY PLAN.

let plan = client
    .user()
    .name_eq("Ken")
    .explain()?;

println!("{}", plan.sql);

Inspection describes the root statement. Eager-loaded relationships are separate planned statements when Rent selects the batched loading strategy.

Use a unique field as a keyset cursor instead of making the database skip every earlier row:

let next_page = client
    .user()
    .after_id(2)
    .limit(2)
    .all()
    .await?;

A feed reads best through an opaque cursor. Rent performs a one-row lookahead, returns has_more, and gives you the next typed cursor without exposing its encoding:

let first = client
    .user()
    .page_by_id(20, None)
    .await?;

let second = client
    .user()
    .page_by_id(20, first.next.as_ref())
    .await?;

Unique compound indexes generate compound cursor methods with lexicographic predicates. Non-unique indexes append the entity ID as a stable tie-breaker.

When the caller picks the order at run time, such as an API with a sort parameter, build the keys as data and page with page_after. Each key is a generated field and a direction. End with a unique field so every row has one position. The next of a page holds the last row's key values as JSON, ready to wrap in whatever cursor your API hands out:

let newest_name_first = [User::name.keyset(true), User::id.keyset(false)];
let first = client.user().page_after(&newest_name_first, 2, None).await?;
let second = client
    .user()
    .page_after(&newest_name_first, 2, first.next.as_deref())
    .await?;

Keys may mix directions, so Rent expands the comparison into name < ? OR (name = ? AND id > ?) rather than a row-value comparison. Use non-null columns as keys: keyset refuses optional fields at compile time.

A page-numbered contract needs a different shape, because a cursor fixes the ordering to its own key and rejects an offset. Ask for an offset page instead, and get the exact total with it:

let page = client
    .user()
    .order_by(user::User::id)
    .page(1, 3)
    .await?;

assert_eq!((page.total, page.total_pages), (4, 2));
assert!(page.has_more);

Pages are one-indexed. Use slice(n, size) for the same window when the caller needs only has_more and the count is not worth paying for.

Select several fields as a typed tuple. Rent fetches only those columns:

let identities: Vec<(i64, String)> = client
    .user()
    .order_by(user::User::id)
    .select((user::User::id, user::User::name))
    .all()
    .await?;

For a result you will pass around or reuse, give the selected fields names. Define an ordinary Rust struct and tell Rent which model it selects from:

use generated::user::User;

#[derive(serde::Deserialize, rent::Projection)]
#[rent(entity = User)]
struct UserIdentity {
    id: i64,
    name: String,
}

let identities: Vec<UserIdentity> = client
    .user()
    .order_by(User::id)
    .select(UserIdentity::projection())
    .all()
    .await?;

for person in identities {
    println!("{}: {}", person.id, person.name);
}

Both forms fetch the same columns. A tuple is handy when you immediately unpack a few values. A named struct is easier to follow when the selection grows or becomes a response used elsewhere in your application. The compiler checks the selected field names and types.

Large reads can be consumed without collecting the complete result set. Portable generated models stream rows directly from the database. Queries with interceptors, codecs, eager loading, or dynamic decoding transparently use bounded pages, so every query still honors its configured behavior:

use rent::futures_util::TryStreamExt as _;

let users = client
    .user()
    .order_by(user::User::id)
    .stream(500)
    .try_collect::<Vec<_>>()
    .await?;

Aggregates remain typed:

let maximum = client
    .user()
    .maximum(user::User::id)
    .await?;

group_by groups the matching rows. Each aggregate is added under a name you choose, and having, ordering and paging refer to those names. The rows decode into your own type:

let shared: Vec<NameCount> = client
    .user()
    .group_by(User::name)
    .count("users")
    .having_ge("users", 2_i64)
    .all()
    .await?;

Grouped queries also support to_sql(). Inspect a grouping before running it just as you would inspect a row query.

When a query outgrows the generated methods, write it by hand. rent::sql! binds every {value} as a parameter and the rows decode into any Deserialize type by column name. Raw SQL passes the tenant guard and policies by, so it runs only on a system handle:

let at_least = 3_i64;
let long_names: Vec<NameLength> = client
    .system()
    .query_raw(rent::sql!(
        "SELECT DISTINCT name, LENGTH(name) AS letters
         FROM users WHERE LENGTH(name) >= {at_least}
         ORDER BY letters DESC, name"
    ))
    .await?;

See production access patterns for narrow cards, batched counts, scoped pagination and transaction-bound helpers together.

Complete example

Download this project, or copy the files below into an empty directory.

Replace RENT_REPOSITORY_URL in Cargo.toml with the URL supplied with your preview access. Use the matching Rent CLI release.

rent generate
cargo run

The project includes its database setup. Each lesson uses a small, self-contained schema. You can run it without applying the earlier lessons.

Cargo.toml 
[package]
name = "rent-lesson-04-querying"
version = "0.1.0"
edition = "2024"

[workspace]

[[bin]]
name = "rent-lesson-04-querying"
path = "main.rs"

[dependencies]
rent = { git = "RENT_REPOSITORY_URL", tag = "v0.8.6", default-features = false, features = ["sqlite", "migrate", "extensions"] }
anyhow = "1.0.104"
tokio = { version = "1.53.1", features = ["full"] }
rent.toml 
schema = "schema.rsl"
generated = "generated"
migrations = "migrations"

[database]
dialect = "sqlite"
backends = ["sqlite"]
schema.rsl 
datasource db {
  provider = "sqlite"
}

model User {
  id   BigInt  @id
  name String

  @@map("users")
}
main.rs 
use anyhow::{Result, ensure};
use rent::driver::DatabasePool;
use rent::futures_util::TryStreamExt as _;
use rent::serde::Deserialize;
use rent::sql::Dialect;

#[allow(dead_code, unused_imports)]
#[rustfmt::skip]
#[path = "generated/mod.rs"]
mod generated;

use generated::user::User;

#[derive(Deserialize)]
#[serde(crate = "rent::serde")]
struct NameCount {
    name: String,
    users: u64,
}

#[derive(Deserialize)]
#[serde(crate = "rent::serde")]
struct NameLength {
    name: String,
    letters: i64,
}

#[derive(Deserialize, rent::Projection)]
#[serde(crate = "rent::serde")]
#[rent(entity = User)]
struct UserIdentity {
    id: i64,
    name: String,
}

async fn exercise() -> Result<()> {
    let pool = DatabasePool::connect(Dialect::Sqlite, "sqlite::memory:").await?;
    pool.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
        .await?;
    let client = generated::client::Client::new(pool);
    client
        .create_user_many([
            client.create_user().id(1).name("Maya"),
            client.create_user().id(2).name("Ken"),
            client.create_user().id(3).name("Eve"),
            client.create_user().id(4).name("Ken"),
        ])
        .save()
        .await?;

    let page = client
        .user()
        .id_ge(2)
        .order_by(generated::user::User::id)
        .offset(1)
        .limit(2)
        .all()
        .await?;
    ensure!(page.iter().map(|user| user.name.as_str()).collect::<Vec<_>>() == ["Eve", "Ken"]);

    let names = client.user().order_by(generated::user::User::id).select_name().all().await?;
    ensure!(names == ["Maya", "Ken", "Eve", "Ken"]);

    let selected = client
        .user()
        .filter(User::id.eq(1).or(User::id.eq(3)))
        .order_by(generated::user::User::id)
        .all()
        .await?;
    ensure!(selected.iter().map(|user| user.id).collect::<Vec<_>>() == [1, 3]);
    ensure!(client.user().filter(!User::id.eq(1)).count().await? == 3);

    let inspection = client.user().name_eq("Ken").to_sql()?;
    ensure!(!inspection.sql.contains("Ken"));
    ensure!(inspection.bindings == ["string"]);

    let plan = client.user().name_eq("Ken").explain()?;
    ensure!(plan.sql.starts_with("EXPLAIN QUERY PLAN "));

    let identities = client
        .user()
        .order_by(generated::user::User::id)
        .select((generated::user::User::id, generated::user::User::name))
        .all()
        .await?;
    ensure!(identities.first() == Some(&(1, "Maya".to_owned())));

    let identities = client.user()
        .order_by(User::id)
        .select(UserIdentity::projection())
        .all().await?;
    let maya = identities.first().ok_or_else(|| anyhow::anyhow!("missing identity"))?;
    ensure!(maya.id == 1 && maya.name == "Maya");

    let next_page = client.user().after_id(2).limit(2).all().await?;
    ensure!(next_page.iter().map(|user| user.id).collect::<Vec<_>>() == [3, 4]);

    let first_cursor_page = client.user().page_by_id(2, None).await?;
    ensure!(first_cursor_page.items.iter().map(|user| user.id).collect::<Vec<_>>() == [1, 2]);
    ensure!(first_cursor_page.has_more);

    let second_cursor_page = client.user().page_by_id(2, first_cursor_page.next.as_ref()).await?;
    ensure!(second_cursor_page.items.iter().map(|user| user.id).collect::<Vec<_>>() == [3, 4]);
    ensure!(!second_cursor_page.has_more && second_cursor_page.next.is_none());

    let newest_name_first = [User::name.keyset(true), User::id.keyset(false)];
    let first_sorted_page = client.user().page_after(&newest_name_first, 2, None).await?;
    ensure!(first_sorted_page.items.iter().map(|user| user.id).collect::<Vec<_>>() == [1, 2]);
    let after = first_sorted_page.next.as_deref();
    let second_sorted_page = client.user().page_after(&newest_name_first, 2, after).await?;
    ensure!(second_sorted_page.items.iter().map(|user| user.id).collect::<Vec<_>>() == [4, 3]);
    ensure!(!second_sorted_page.has_more);

    let offset_page = client.user().order_by(generated::user::User::id).page(1, 3).await?;
    ensure!(offset_page.items.iter().map(|user| user.id).collect::<Vec<_>>() == [1, 2, 3]);
    ensure!(offset_page.total == 4 && offset_page.total_pages == 2);
    ensure!(offset_page.has_more);

    let last_offset_page = client.user().order_by(generated::user::User::id).page(2, 3).await?;
    ensure!(last_offset_page.items.iter().map(|user| user.id).collect::<Vec<_>>() == [4]);
    ensure!(!last_offset_page.has_more);

    let offset_slice = client.user().order_by(generated::user::User::id).slice(1, 3).await?;
    ensure!(offset_slice.items.len() == 3 && offset_slice.has_more);

    let streamed = client
        .user()
        .order_by(generated::user::User::id)
        .stream(2)
        .try_collect::<Vec<_>>()
        .await?;
    ensure!(streamed.len() == 4);
    ensure!(client.user().count().await? == 4);
    ensure!(client.user().maximum(generated::user::User::id).await? == Some(4));

    group_and_drop_to_sql(&client).await
}

/// Counts users per name, then computes something the generated methods do not offer in raw SQL.
async fn group_and_drop_to_sql(client: &generated::client::Client) -> Result<()> {
    let grouped = client.user().group_by(User::name).count("users").having_ge("users", 2_i64);
    let inspection = grouped.to_sql()?;
    ensure!(inspection.sql.contains("GROUP BY") && inspection.sql.contains("HAVING"));
    let shared: Vec<NameCount> = grouped.all().await?;
    ensure!(shared.len() == 1);
    ensure!(shared.iter().all(|group| group.name == "Ken" && group.users == 2));

    let at_least = 3_i64;
    let long_names: Vec<NameLength> = client
        .system()
        .query_raw(rent::sql!(
            "SELECT DISTINCT name, LENGTH(name) AS letters FROM users
             WHERE LENGTH(name) >= {at_least} ORDER BY letters DESC, name"
        ))
        .await?;
    ensure!(
        long_names
            .iter()
            .map(|row| (row.name.as_str(), row.letters))
            .eq([("Maya", 4), ("Eve", 3), ("Ken", 3)])
    );
    Ok(())
}

#[tokio::main]
async fn main() -> Result<()> {
    exercise().await
}

#[cfg(test)]
mod tests {
    #[tokio::test]
    async fn example_runs() -> anyhow::Result<()> {
        super::exercise().await
    }
}

Many-to-many tags

Connect posts and tags, model membership records, and traverse a group hierarchy.

Rich values

Round-trip portable scalar, document, binary, time, UUID, enum, and nullable values.

On this page

Complete example