Upserts and custom IDs

Any scalar ID type supported by Rent can be your primary key, and its field does not have to be named id. This create uses a string key named token without dropping down to SQL:

let key = client
    .create_api_key()
    .token("key_live_maya")
    .label("primary")
    .save()
    .await?;

Use named keys in relationships

The schema can map Rust-facing field names to different SQL column names. Relationship declarations refer to the schema field names:

model ApiKey {
  token    String       @id @map("key_token")
  label    String
  sessions ApiSession[]
}

model ApiSession {
  id        BigInt  @id
  key_token String? @map("key_ref")
  key       ApiKey? @relation(fields: [key_token], references: [token])
}

Connect the entity directly, then load the relationship in either direction:

let session = client
    .create_api_session()
    .id(1)
    .connect_key(&key)
    .save()
    .await?;

let loaded = client
    .api_key()
    .token_eq(&key.token)
    .with_sessions(|sessions| sessions.with_key(|key| key))
    .only()
    .await?;

For a single unique field other than the primary key, name that field in references instead. For example, a post's author_email can reference User.email when email is unique. connect_author(&user) then copies the email, not the user's primary key. Choose a stable key or configure the appropriate foreign-key update action if the referenced value can change.

After adding these models to your application's schema, generate the client and create the database tables:

rent generate
rent migrate dev --name add_api_sessions
rent migrate drift --check

The migration uses key_token as the referenced SQL column, while application code continues to use token. For an alternate unique key, Rent creates its unique index before adding the foreign key. Join-table columns also inherit the referenced key's database type, including native overrides such as @rent.type(mysql: "varchar(64)") on a string key.

Use compound identities

Compound identities are declared directly in RSL:

model Membership {
  user_id  BigInt
  group_id BigInt
  role     String

  @@id([user_id, group_id])
}

For an upsert, pass the unique key that identifies a conflicting row. Each model's unique module has one key per declared primary key or unique constraint, so a compound unique such as @@unique([user_id, group_id]) is membership::unique::user_id_group_id:

let membership = client
    .upsert_membership(membership::unique::user_id_group_id)
    .user_id(user.id)
    .group_id(group.id)
    .role("owner")
    .save()
    .await?;

The database performs one atomic insert-or-update statement. Rent returns the inserted or updated entity and runs the operation through mutation policies and hooks as an upsert. Updates and deletes reuse the generated compound identity without making you reconstruct its fields:

let updated = client
    .update_membership_one(&membership)
    .role("maintainer")
    .save_one()
    .await?;

client
    .delete_membership_one(&updated)
    .exec()
    .await?;

Upserting the same compound identity twice leaves one row to update or delete. On databases without row-returning inserts, Rent reloads by the complete identity rather than a partial key.

Compound identities retain their field types in generated models, filters, projections, and streamed results. You do not need to stringify a key or decode a JSON object in application code. Rent selects the row-decoding path for the database. Field codecs and query interceptors remain active on these models as well.

Load relationships across a compound identity

When user IDs are unique only inside a tenant, include both columns in the identity and its foreign keys:

model TenantUser {
  tenant_id BigInt
  id        BigInt
  name      String
  posts     TenantPost[] @relation("TenantAuthor") @rent.order(field: "id", direction: Desc)

  @@id([tenant_id, id])
}

model TenantPost {
  id        BigInt      @id
  tenant_id BigInt?
  author_id BigInt?
  title     String
  author    TenantUser? @relation("TenantAuthor", fields: [tenant_id, author_id], references: [tenant_id, id])
}

Here tenant_id is part of a key, not an isolation boundary, so this schema declares tenancy { scope = "none" }. An application that isolates tenants declares scope = "required" instead, and the same compound keys keep working under the tenant guard.

The generated methods are the same as for a single-column relationship. connect_author(&author) assigns both key fields, and eager loading matches the complete pair:

let users = client
    .tenant_user()
    .with_posts(|posts| posts.limit(2).with_author(|author| author))
    .all()
    .await?;

for user in users {
    for post in user.posts()? {
        let author = post.author()?;
        // Each post belongs to this user's complete tenant/user identity.
    }
}

This returns the two highest-ID posts per user, using the relationship's declared descending order. Rent applies the child window in SQL and budgets each complete key tuple when batching. It does not combine independent tenant and user ID lists. If either optional foreign-key component is null, post.author()? is None. An eager-loaded empty collection is an empty slice. Use a transaction with the isolation your application requires when several reads must share a consistent snapshot.

Even when both tenants use the same local user ID, the full key keeps each post connected to its own author in bounded previews and nested eager loads.

Page across compound identities

Use the generated cursor for the complete identity. A tenant-local user ID alone does not uniquely order users across tenants:

let first = client
    .tenant_user()
    .page_by_tenant_id_and_id(1, None)
    .await?;

let second = client
    .tenant_user()
    .page_by_tenant_id_and_id(1, first.next.as_ref())
    .await?;

The cursor contains both fields, so users with the same local ID are returned on separate pages. For non-unique indexes, generated cursors append any missing primary-key fields as tie-breakers. Partial unique indexes also need that complete tie-breaker outside their predicate. Nullable unique fields do not get standalone keyset cursors.

Choose what a conflict does

By default a conflicting upsert writes its create values over the existing row. Two other outcomes come up constantly. A counter creates its row at one and increments it after that, so the conflict needs a different change than the insert:

client
    .upsert_page_view(page_view::unique::path)
    .path("/pricing")
    .views(1)
    .on_conflict_update(|update| update.increment_views(1))
    .save()
    .await?;

Three calls leave views at three. An insert-if-absent keeps whatever row already holds the key and returns it, so a second caller gets the first caller's tag, ID and all:

let tag = client
    .upsert_tag(tag::unique::name)
    .id(2)
    .name("release")
    .keep_existing()
    .save()
    .await?;

Soft delete with a partial unique

A unique over live rows only lets a soft-deleted row give its name back. Declare the condition on the unique:

model Handle {
  id         BigInt     @id
  name       String
  owner      String
  deleted_at DateTime?

  @@unique([name], where: "deleted_at IS NULL")
}

The upsert key repeats the condition, so it matches the live maya and ignores deleted ones. Once the first maya is deleted, the same upsert creates a new row, and both stay in the table:

let reclaimed = client
    .upsert_handle(handle::unique::name)
    .id(2)
    .name("maya")
    .owner("eve")
    .on_conflict_update(|update| update.owner("eve"))
    .save()
    .await?;

PostgreSQL and SQLite have partial indexes. MySQL and MariaDB do not, so a where: unique is a schema error there.

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-16-upserts-custom-ids"
version = "0.1.0"
edition = "2024"

[workspace]

[[bin]]
name = "rent-lesson-16-upserts-custom-ids"
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"] }
chrono = { version = "0.4.45", features = ["clock", "serde"] }

rent.toml

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

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

schema.rsl

datasource db {
  provider = "sqlite"
}

tenancy {
  scope = "none"
}

model ApiKey {
  token    String        @id  @map("key_token")
  label    String
  sessions ApiSession[]

  @@map("api_keys")
}

model ApiSession {
  id        BigInt   @id
  key_token String?  @map("key_ref")
  key       ApiKey?  @relation(fields: [key_token], references: [token])

  @@map("api_sessions")
}

model User {
  id          BigInt        @id
  name        String
  memberships Membership[]  @relation("MembershipUser")

  @@map("users")
}

model Group {
  id          BigInt        @id
  name        String
  memberships Membership[]  @relation("MembershipGroup")

  @@map("teams")
}

model Membership {
  user_id  BigInt
  group_id BigInt
  role     String
  user     User    @relation("MembershipUser", fields: [user_id], references: [id])
  group    Group   @relation("MembershipGroup", fields: [group_id], references: [id])

  @@id([user_id, group_id])
  @@map("memberships")
}

model TenantUser {
  tenant_id BigInt        @map("tenant_key")
  id        BigInt        @map("user_key")
  name      String
  posts     TenantPost[]  @relation("TenantAuthor")  @rent.order(field: "id", direction: Desc)

  @@id([tenant_id, id])
  @@map("eager_tenant_users")
}

model TenantPost {
  id        BigInt       @id
  tenant_id BigInt?      @map("tenant_key")
  author_id BigInt?      @map("author_key")
  title     String
  author    TenantUser?  @relation("TenantAuthor", fields: [tenant_id, author_id], references: [tenant_id, id])

  @@map("eager_tenant_posts")
}

model PageView {
  path  String  @id
  views BigInt

  @@map("page_views")
}

model Tag {
  id   BigInt  @id
  name String  @unique

  @@map("tags")
}

model Handle {
  id         BigInt     @id
  name       String
  owner      String
  deleted_at DateTime?

  @@unique([name], where: "deleted_at IS NULL")
  @@map("handles")
}

main.rs

use anyhow::{Result, ensure};
use rent::driver::DatabasePool;
use rent::sql::Dialect;

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

async fn exercise() -> Result<()> {
    let pool = DatabasePool::connect(Dialect::Sqlite, "sqlite::memory:").await?;
    pool.execute("PRAGMA foreign_keys = ON").await?;
    for statement in [
        "CREATE TABLE api_keys (key_token TEXT PRIMARY KEY, label TEXT NOT NULL)",
        "CREATE TABLE api_sessions (id INTEGER PRIMARY KEY, key_ref TEXT REFERENCES api_keys(key_token))",
        "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)",
        "CREATE TABLE teams (id INTEGER PRIMARY KEY, name TEXT NOT NULL)",
        "CREATE TABLE memberships (user_id INTEGER NOT NULL REFERENCES users(id), group_id INTEGER NOT NULL REFERENCES teams(id), role TEXT NOT NULL, PRIMARY KEY (user_id, group_id))",
        "CREATE TABLE eager_tenant_users (tenant_key INTEGER NOT NULL, user_key INTEGER NOT NULL, name TEXT NOT NULL, PRIMARY KEY (tenant_key, user_key))",
        "CREATE TABLE eager_tenant_posts (id INTEGER PRIMARY KEY, tenant_key INTEGER, author_key INTEGER, title TEXT NOT NULL, FOREIGN KEY (tenant_key, author_key) REFERENCES eager_tenant_users(tenant_key, user_key))",
        "CREATE TABLE page_views (path TEXT PRIMARY KEY, views INTEGER NOT NULL)",
        "CREATE TABLE tags (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE)",
        "CREATE TABLE handles (id INTEGER PRIMARY KEY, name TEXT NOT NULL, owner TEXT NOT NULL, deleted_at TEXT)",
        "CREATE UNIQUE INDEX handles_name_key ON handles (name) WHERE deleted_at IS NULL",
    ] {
        pool.execute(statement).await?;
    }
    let client = generated::client::Client::new(pool);

    let key = client.create_api_key().token("key_live_maya").label("primary").save().await?;

    ensure!(key.token == "key_live_maya");
    let session = client.create_api_session().id(1).connect_key(&key).save().await?;

    let loaded = client
        .api_key()
        .token_eq(&key.token)
        .with_sessions(|sessions| sessions.with_key(|key| key))
        .only()
        .await?;

    ensure!(loaded.sessions()?.len() == 1);
    ensure!(loaded.sessions()?.first().is_some_and(|row| row.id == session.id));
    for session in loaded.sessions()? {
        ensure!(session.key()?.is_some_and(|related| related.token == key.token));
    }

    let user = client.create_user().id(1).name("Maya").save().await?;
    let group = client.create_group().id(1).name("maintainers").save().await?;

    let membership = client
        .upsert_membership(generated::membership::unique::user_id_group_id)
        .user_id(user.id)
        .group_id(group.id)
        .role("member")
        .save()
        .await?;

    ensure!(membership.role == "member");

    let membership = client
        .upsert_membership(generated::membership::unique::user_id_group_id)
        .user_id(user.id)
        .group_id(group.id)
        .role("owner")
        .save()
        .await?;

    ensure!(membership.role == "owner");
    let updated = client.update_membership_one(&membership).role("maintainer").save_one().await?;

    ensure!(updated.role == "maintainer");
    ensure!(client.membership().count().await? == 1);

    client.delete_membership_one(&updated).exec().await?;
    ensure!(client.membership().count().await? == 0);

    let maintainers = client.create_group().id(2).name("reviewers").save().await?;
    let reviewers = client.create_group().id(3).name("release-managers").save().await?;
    let memberships = client
        .create_membership_bulk([
            client
                .create_membership()
                .user_id(user.id)
                .group_id(maintainers.id)
                .role("reviewer"),
            client
                .create_membership()
                .user_id(user.id)
                .group_id(reviewers.id)
                .role("release-manager"),
        ])
        .await?;

    ensure!(memberships.len() == 2);
    ensure!(client.membership().count().await? == 2);
    compound_relationships(&client).await?;
    conflict_policies(&client).await?;
    Ok(())
}

async fn conflict_policies(client: &generated::client::Client) -> Result<()> {
    for _ in 0..3 {
        client
            .upsert_page_view(generated::page_view::unique::path)
            .path("/pricing")
            .views(1)
            .on_conflict_update(|update| update.increment_views(1))
            .save()
            .await?;
    }
    let counter = client.page_view().path_eq("/pricing").only().await?;

    ensure!(counter.views == 3);

    let first = client
        .upsert_tag(generated::tag::unique::name)
        .id(1)
        .name("release")
        .keep_existing()
        .save()
        .await?;
    let again = client
        .upsert_tag(generated::tag::unique::name)
        .id(2)
        .name("release")
        .keep_existing()
        .save()
        .await?;

    ensure!(again.id == first.id);
    ensure!(client.tag().count().await? == 1);

    let claimed = client
        .upsert_handle(generated::handle::unique::name)
        .id(1)
        .name("maya")
        .owner("ken")
        .on_conflict_update(|update| update.owner("ken"))
        .save()
        .await?;
    client
        .update_handle_one(&claimed)
        .deleted_at(chrono::Utc::now())
        .save_one()
        .await?;
    let reclaimed = client
        .upsert_handle(generated::handle::unique::name)
        .id(2)
        .name("maya")
        .owner("eve")
        .on_conflict_update(|update| update.owner("eve"))
        .save()
        .await?;

    ensure!(reclaimed.id == 2);
    ensure!(client.handle().name_eq("maya").count().await? == 2);
    Ok(())
}

async fn compound_relationships(client: &generated::client::Client) -> Result<()> {
    for tenant in [1, 2] {
        let author = client.create_tenant_user().tenant_id(tenant).id(1).name("Maya").save().await?;

        for number in 1..=4 {
            client
                .create_tenant_post()
                .id(tenant * 100 + number)
                .title("A tenant's post")
                .connect_author(&author)
                .save()
                .await?;
        }
    }
    let first = client.tenant_user().page_by_tenant_id_and_id(1, None).await?;

    let second = client.tenant_user().page_by_tenant_id_and_id(1, first.next.as_ref()).await?;

    ensure!(first.items.first().is_some_and(|user| user.tenant_id == 1));
    ensure!(second.items.first().is_some_and(|user| user.tenant_id == 2));
    ensure!(!second.has_more);

    let users = client
        .tenant_user()
        .with_posts(|posts| posts.limit(2).with_author(|author| author))
        .all()
        .await?;

    ensure!(users.len() == 2);
    for user in users {
        ensure!(
            user.posts()?.iter().map(|post| post.id).collect::<Vec<_>>()
                == [user.tenant_id * 100 + 4, user.tenant_id * 100 + 3]
        );
        for post in user.posts()? {
            ensure!(
                post.author()?
                    .is_some_and(|author| author.tenant_id == user.tenant_id && author.id == user.id)
            );
        }
    }
    Ok(())
}

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

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