Build a leaderboard

Acme hosts a Spring Cup game night for Maya, Ken, and Eve. Its leaderboard brings together several features from earlier chapters. Boards choose how they rank players, scores stream in from game servers that know nothing about board setup, and the standings come from aggregates rather than stored rows.

enum Ranking {
  TOTAL
  WEIGHTED {
    kills   Float
    assists Float
  }
}

model Board {
  id      String  @id
  name    String  @unique(mode: insensitive)
  ranking Ranking
  scores  Score[]

  @@map("boards")
}

model Score {
  board_id String
  player   String
  metric   String
  points   Float
  board    Board?  @relation(fields: [board_id], references: [id], foreignKey: false)

  @@id([board_id, metric, player])
  @@map("scores")
}

Ranking is a data-carrying enum: WEIGHTED stores its multipliers next to the tag, and the generated Rust type is Ranking::Weighted { kills, assists }. Board names are unique regardless of case, so Spring Cup and spring cup cannot both exist. The relation declares foreignKey: false, so scores may arrive before their board and an orphaned score loads its board as None instead of failing the write.

Scores before boards

Game servers write scores keyed by board id. Because the relation is not enforced in the database, the writes succeed even when no board row exists yet:

client
    .create_score()
    .board_id("spring")
    .player("maya")
    .metric("kills")
    .points(12.0)
    .save()
    .await?;

let scores = client
    .score()
    .board_id_eq("spring")
    .with_board(|board| board)
    .all()
    .await?;

let orphaned = scores
    .iter()
    .all(|score| score.board().is_ok_and(|board| board.is_none()));

assert!(orphaned);

Creating a second board whose name differs only in case is rejected with EntityError::UniqueViolation.

To create a board only when that name is free, use the generated unique key. The same upsert API handles case-insensitive names. Keeping an existing board preserves its ID, ranking, and original spelling:

let existing = client
    .upsert_board(board::unique::name)
    .id("another")
    .name("SPRING CUP")
    .ranking(Ranking::Total)
    .keep_existing()
    .save()
    .await?;

assert_eq!(existing.id, "spring");
assert_eq!(existing.name, "Spring Cup");

This chapter uses SQLite, whose built-in case folding covers ASCII. See unique comparison rules for provider differences.

Totals with group_by

A TOTAL board ranks by summed points. group_by returns one row per player, and order_by_desc sorts by the named aggregate output:

#[derive(Deserialize)]
#[serde(crate = "rent::serde")]
struct Standing {
    player: String,
    total: f64,
}

let totals: Vec<Standing> = client
    .score()
    .board_id_eq("spring")
    .group_by(Score::player)
    .sum("total", Score::points)
    .order_by_desc("total")
    .all()
    .await?;

Weighted standings with raw SQL

A WEIGHTED board multiplies each metric before summing. That CASE expression is outside the typed query builder, so the example builds it with sql!. Every {...} interpolation becomes a bound parameter, RawSql::join composes the fragments, and raw statements run only on the system client:

let Ranking::Weighted { kills, assists } = board.ranking else {
    anyhow::bail!("the spring board ranks by weight");
};
let mut weighted = rent::sql!("SELECT player, SUM(CASE metric");
weighted
    .push(RawSql::join(
        [
            rent::sql!(" WHEN 'kills' THEN points * {kills}"),
            rent::sql!(" WHEN 'assists' THEN points * {assists}"),
        ],
        "",
    ))
    .push(rent::sql!(
        " ELSE 0 END) AS total FROM scores WHERE board_id = {board.id}
         GROUP BY player ORDER BY total DESC"
    ));
let ranked: Vec<Standing> = client.system().query_raw(weighted).await?;

Leaderboard reads can scan many rows. Bound them with a database statement timeout as described in Production runtime.

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-23-leaderboard"
version = "0.1.0"
edition = "2024"

[workspace]

[[bin]]
name = "rent-lesson-23-leaderboard"
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"] }

rent.toml

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

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

schema.rsl

datasource db {
  provider = "sqlite"
}

tenancy {
  scope = "none"
}

/// How a board orders its players.
enum Ranking {
  /// Every point counts the same.
  TOTAL
  /// Kills and assists count by their weights.
  WEIGHTED {
    kills   Float
    assists Float
  }
}

/// A named leaderboard; its scores may arrive before it is created and outlive it.
model Board {
  id      String   @id
  name    String   @unique(mode: insensitive)
  ranking Ranking
  scores  Score[]

  @@map("boards")
}

/// One player's points in one metric on one board.
model Score {
  board_id String
  player   String
  metric   String
  points   Float
  board    Board?  @relation(fields: [board_id], references: [id], foreignKey: false)

  @@id([board_id, metric, player])
  @@map("scores")
}

main.rs

use anyhow::{Result, ensure};
use rent::driver::DatabasePool;
use rent::runtime::EntityError;
use rent::serde::Deserialize;
use rent::sql::Dialect;
use rent::sql::raw::RawSql;

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

use generated::composite_types::Ranking;
use generated::score::Score;
use generated::{board, client::Client};

#[derive(Deserialize)]
#[serde(crate = "rent::serde")]
struct Standing {
    player: String,
    total: f64,
}

async fn exercise() -> Result<()> {
    let pool = DatabasePool::connect(Dialect::Sqlite, "sqlite::memory:").await?;
    for statement in [
        "CREATE TABLE boards (id TEXT PRIMARY KEY, name TEXT NOT NULL, ranking TEXT NOT NULL)",
        "CREATE UNIQUE INDEX boards_name_key ON boards (lower(\"name\"))",
        "CREATE TABLE scores (board_id TEXT NOT NULL, player TEXT NOT NULL, metric TEXT NOT NULL, points REAL NOT NULL, PRIMARY KEY (board_id, metric, player))",
    ] {
        pool.execute(statement).await?;
    }
    let client = Client::new(pool);

    // Scores can arrive before their board exists: the relation has no foreign key.
    for (player, metric, points) in [
        ("maya", "kills", 12.0),
        ("maya", "assists", 2.0),
        ("ken", "kills", 6.0),
        ("ken", "assists", 9.0),
        ("eve", "kills", 9.0),
    ] {
        client
            .create_score()
            .board_id("spring")
            .player(player)
            .metric(metric)
            .points(points)
            .save()
            .await?;
    }
    let orphan = client.score().board_id_eq("spring").with_board(|board| board).all().await?;
    ensure!(orphan.iter().all(|score| score.board().is_ok_and(|board| board.is_none())));

    client
        .create_board()
        .id("spring")
        .name("Spring Cup")
        .ranking(Ranking::Weighted {
            kills: 1.0,
            assists: 0.5,
        })
        .save()
        .await?;
    let taken = client
        .create_board()
        .id("copy")
        .name("spring cup")
        .ranking(Ranking::Total)
        .save()
        .await;
    ensure!(matches!(taken, Err(EntityError::UniqueViolation { .. })));

    let existing = client
        .upsert_board(board::unique::name)
        .id("another")
        .name("SPRING CUP")
        .ranking(Ranking::Total)
        .keep_existing()
        .save()
        .await?;
    ensure!(existing.id == "spring" && existing.name == "Spring Cup");

    let totals: Vec<Standing> = client
        .score()
        .board_id_eq("spring")
        .group_by(Score::player)
        .sum("total", Score::points)
        .order_by_desc("total")
        .all()
        .await?;
    ensure!(totals.iter().map(|row| (row.player.as_str(), row.total)).eq([
        ("ken", 15.0),
        ("maya", 14.0),
        ("eve", 9.0)
    ]));

    let board = client.board().id_eq("spring").only().await?;
    let Ranking::Weighted {
        kills,
        assists,
    } = board.ranking
    else {
        anyhow::bail!("the spring board ranks by weight");
    };
    let mut weighted = rent::sql!("SELECT player, SUM(CASE metric");
    weighted
        .push(RawSql::join(
            [
                rent::sql!(" WHEN 'kills' THEN points * {kills}"),
                rent::sql!(" WHEN 'assists' THEN points * {assists}"),
            ],
            "",
        ))
        .push(rent::sql!(
            " ELSE 0 END) AS total FROM scores WHERE board_id = {board.id}
             GROUP BY player ORDER BY total DESC"
        ));
    let ranked: Vec<Standing> = client.system().query_raw(weighted).await?;
    ensure!(ranked.iter().map(|row| (row.player.as_str(), row.total)).eq([
        ("maya", 13.0),
        ("ken", 10.5),
        ("eve", 9.0)
    ]));
    Ok(())
}

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

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

Build a leaderboard

Scores before boards

Totals with group_by

Weighted standings with raw SQL

Complete example

On this page