Write efficient queries

Maya opens a list of posts. Each card needs a title and comment count. It doesn't need the entire post body. Good database access starts with that response shape. These patterns work with ordinary models. A scoped client keeps the same patterns when you later add tenancy or permissions.

Fetch the fields on the screen

Select just the fields the list needs:

let rows = client.post()
    .order_by(Post::id)
    .limit(25)
    .select((Post::id, Post::title))
    .all().await?;

Fetch the body when someone opens a post. Filtering, sorting, and limiting in the database avoids loading an unbounded collection and trimming it in Rust.

Count for the whole page

One count query for every card turns a page into many database calls. Collect the page's IDs and ask for their counts together:

if rows.is_empty() {
    return Ok(());
}

let counts: Vec<CommentCount> = client.comment()
    .post_id_in(rows.iter().map(|(id, _)| *id))
    .group_by(Comment::post_id)
    .count("comments")
    .all().await?;

A missing group means zero comments. CommentCount has a post_id: i64 and a comments: i64 field and derives serde::Deserialize.

If your response needs related models, use with_comments or with_comments_count instead of loading each relationship in a loop. See relationships.

Choose a page contract

Use page when the UI needs numbered pages and an exact total.
Use slice when numbered pages need only a “more results” flag.
Use a keyset cursor for a feed that continues after the last item.
Use a bounded stream for an export.

The query reference shows these APIs and their ordering rules. Add indexes for the filters and ordering your application actually uses. Inspect the query plan before guessing at an index.

Keep a transaction focused

When two writes must succeed together, use the transaction's client for both:

client.transaction(async |tx| {
    let post = tx.create_post()
        .title("Launch notes")
        .body("A draft")
        .save().await?;

    tx.create_comment()
        .post_id(post.id)
        .body("Discussion opened")
        .save().await?;

    Ok(())
}).await?;

Pass tx into database helpers. Keep email delivery and other network calls outside the transaction so they don't hold a connection while waiting.

For request-scoped access, see production access patterns.

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.

Use a separate local database for this example:

export DATABASE_URL='sqlite://lesson.db?mode=rwc'
rent generate
rent migrate dev --name initial
cargo run

Run the included test with cargo nextest run. It creates an isolated temporary database.

Cargo.toml

[package]
name = "rent-lesson-42-production-access"
version = "0.1.0"
edition = "2024"

[workspace]

[[bin]]
name = "rent-lesson-42-production-access"
path = "main.rs"

[dependencies]
rent = { git = "RENT_REPOSITORY_URL", tag = "v0.7.2", default-features = false, features = ["sqlite", "migrate", "extensions"] }
anyhow = "1.0.104"
tokio = { version = "1.53.1", features = ["full"] }
serde = { version = "1.0.229", features = ["derive", "rc"] }
tempfile = "3.27.0"

# Used by the generated client after `rent generate`.
[package.metadata.cargo-machete]
ignored = ["rent"]

rent.toml

schema = "schema.rsl"
generated = "generated"
migrations = "migrations"

[database]
dialect = "sqlite"
backends = ["sqlite"]

schema.rsl

datasource db {
  provider = "sqlite"
}

model Post {
  id       BigInt     @id  @default(autoincrement())
  title    String
  body     String
  comments Comment[]
}

model Comment {
  id      BigInt  @id  @default(autoincrement())
  body    String
  post_id BigInt
  post    Post    @relation(fields: [post_id], references: [id], onDelete: Cascade)
}

main.rs

use anyhow::Result;
#[allow(dead_code, unused_imports)]
#[rustfmt::skip]
#[path = "generated/mod.rs"]
mod generated;
use generated::client::Client;
use generated::{comment::Comment, post::Post};
#[derive(serde::Deserialize)]
struct CommentCount {
    post_id: i64,
    comments: i64,
}
async fn example(url: &str) -> Result<()> {
    let client = Client::connect(url).await?;
    client
        .transaction(async |tx| {
            let post = tx
                .create_post()
                .title("Launch notes")
                .body("A long body that a card does not need")
                .save()
                .await?;
            tx.create_comment().post_id(post.id).body("Reviewed").save().await?;
            Ok(())
        })
        .await?;
    let rows = client
        .post()
        .order_by(Post::id)
        .limit(25)
        .select((Post::id, Post::title))
        .all()
        .await?;
    if rows.is_empty() {
        return Ok(());
    }
    let counts: Vec<CommentCount> = client
        .comment()
        .post_id_in(rows.iter().map(|(id, _)| *id))
        .group_by(Comment::post_id)
        .count("comments")
        .all()
        .await?;
    for (id, title) in rows {
        let count = counts.iter().find(|row| row.post_id == id).map_or(0, |row| row.comments);
        println!("{title}: {count} comments");
    }
    Ok(())
}

#[tokio::main]
async fn main() -> Result<()> {
    example(&std::env::var("DATABASE_URL")?).await
}
#[cfg(test)]
mod test_support;
#[cfg(test)]
mod tests {
    #[tokio::test]
    async fn example_runs() -> anyhow::Result<()> {
        let (_directory, url) = super::test_support::database()?;
        super::example(&url).await
    }
}

test_support.rs

use anyhow::{Context, Result, ensure};
use std::process::Command;

pub fn database() -> Result<(tempfile::TempDir, String)> {
    let directory = tempfile::tempdir()?;
    std::fs::write(directory.path().join("schema.rsl"), include_str!("schema.rsl"))?;
    std::fs::write(directory.path().join("rent.toml"), r#"schema = "schema.rsl""#)?;
    let url = format!("sqlite://{}?mode=rwc", directory.path().join("app.db").display());
    let executable = std::env::var("RENT_CLI").unwrap_or_else(|_| "rent".into());
    let output = Command::new(executable)
        .arg("migrate")
        .arg("dev")
        .arg("--root")
        .arg(directory.path())
        .arg("--to")
        .arg("schema.rsl")
        .arg("--database-url")
        .arg(&url)
        .output()
        .context("run the Rent CLI")?;
    ensure!(
        output.status.success(),
        "migration failed: {}",
        String::from_utf8_lossy(&output.stderr)
    );
    Ok((directory, url))
}