CRUD

To perform database operations, one of the four CRUD builders is used.

They are all started by a function with two arguments. The first argument is an executor (i.e. &Database or &mut Transaction) and the second argument is the model whose table to interact with.

The following examples are written using these models:

use rorm::prelude::*;

#[derive(Model)]
struct User {
    #[rorm(id)]
    id: i64,

    #[rorm(max_length = 255, unique)]
    username: String,

    #[rorm(max_length = 255)]
    password: String,

    posts: BackRef<field!(Post.user)>,
}

#[derive(Model)]
struct Post {
    #[rorm(id)]
    id: i64,

    #[rorm(max_length = 255)]
    message: String,

    user: ForeignKey<User>,
}

Query

To retrieve data from the database the rorm::query builder is used:

async fn query_example(db: &Database) -> Result<(), rorm::Error> {
    let all_users: Vec<User> = rorm::query(db, User)
        .all()
        .await?;

    let bob: Option<User> = rorm::query(db, User)
        .condition(User.username.equals("bob"))
        .optional()
        .await;
    if bob.is_none() {
        println!("No user named bob was found");
    }

    Ok(())
}

The last method called on the builder specifies how the query is resolved. The following four are available:

all()

Executes the query in a future which resolves to all rows matching the query.

let all_users: Vec<User> = rorm::query(&db, User).all().await.unwrap();

one()

Executes the query in a future which resolves to exactly one row. (If no matching row is found, this will be treated as an error)

let user: User = rorm::query(&db, User).one().await.unwrap();

optional()

Executes the query in a future which resolves to at most one row.

let user: Option<User> = rorm::query(&db, User).optional().await.unwrap();

stream()

Behaves like all() but instead of returning a future which collects all resulting rows in a Vec before resolving, it produces a stream which has to be polled per row.

If you're not comfortable with rust's async streams, you can always start using all() until you notice performance issues.

Add conditions

The optional .condition(...) method can be invoked to add a condition the returned rows must satisfy.

Note

This directly corresponds to adding a WHERE clause in sql

To construct conditions use a comparison method on the field syntax

User.username.equals("bob")
^^^^^^^^^^^^^        ^^^^^ - Value to compare against
|             ^^^^^^
|             |
|             Comparison operator
|
Field to compare

The concrete comparisons available depend on the field's type:

Comparisons (most types):

• equals, not_equals
• less_than, less_equals, greater_than, greater_equals

Sets — check the field against a collection of values (SQL IN):

// `in` is a rust keyword, so it has to be a raw identifier: `r#in`
User.id.r#in([1, 2, 3])
User.id.not_in(some_ids)

Options — for nullable fields (i.e. fields with an Option<...> type):

Post.user.is_none()
Post.user.is_some()

Strings:

• like, not_like: SQL LIKE with its wildcards % (any substring) and _ (any character)
• contains, starts_with, ends_with: convenience wrappers around LIKE which escape any wildcards contained in their argument
• regexp, not_regexp: match against a regular expression

User.username.like("bob%")          // wildcards are interpreted
User.username.starts_with("bob")    // wildcards are escaped

Postgres only — with the postgres-only feature there are also:

• equals_ignore_case
• like_ignore_case, not_like_ignore_case (SQL ILIKE)
• contains_ignore_case, starts_with_ignore_case, ends_with_ignore_case
• net_contained_in and net_contained_in_or_equals on IpNetwork fields, checking whether the field's network is contained in a given network (postgres' << and <<= operators)

Conditions can then be combined using the or! and and! macros:

rorm::query(db, User)
    .condition(and!(
        User.username.equals("alice"),
        User.password.equals(leaked_pw)
    ))
    .optional()

Customize what to select

In its most basic usage (rorm::query(db, User)) the query will select every column and return them in the model's struct.

This can be customized by changing the function's second argument.

Note

The rorm::query builder is somewhat unique in regard to its second argument.

The other builders won't behave comparibly.

As the simplest alternative a Patch can be specified instead of the whole Model to only select some columns:

#[derive(Patch)]
#[rorm(model = "User")]
struct UserWithoutPassword {
    id: i64,
    username: String,
    posts: BackRef<field!(Post.user)>,
}

// Query every field from the struct UserWithoutPassword
let users: Vec<UserWithoutPassword> = rorm::query(db, UserWithoutPassword).all().await?;

This can get quite annoying when you have to specify a struct for every combination of fields you might want to query together.

Therefore, you can use the field syntax to query tuples of fields:

// Only query the user's id and username
let users: Vec<(i64, String)> =
    rorm::query(db, (User.id, User.username)).all().await?;

This syntax also work with relations:

let posts: Vec<(String, String)> =
    rorm::query(db, (Post.message, Post.user.username)).all().await?;

When you want to select your relations' fields and there are a lot of them, specifying them all like this can get quite verbose. On top of that, due to rust limitations regarding tuples, the maximum number of fields you can query in one go is 32. To mitigate this, there is a syntax combining individual fields with patches:

let posts: Vec<(String, UserWithoutPassword)> =
    rorm::query(db, (Post.message, Post.user.query_as(UserWithoutPassword))).all().await?;

Aggregation

Instead of selecting a field's values, you can select an aggregation over them by calling one of the aggregation methods on the field:

// Number of posts written by a specific user
let count: i64 = rorm::query(db, Post.id.count())
    .condition(Post.user.username.equals("alice"))
    .one()
    .await?;

The following aggregation functions are available:

• count(): number of rows (an i64)
• sum(): sum of the field's values
• avg(): average of the field's values (an Option<f64>)
• max() / min(): largest / smallest value

Which ones a field offers depends on its type — sum and avg require numeric fields while max and min work on anything with an ordering (e.g. dates too).

Note

Except for count, the aggregations return an Option which will be None if no row matched the condition. Also mind that sum might use a bigger type than the field itself (e.g. Option<i64> for an i16 field) to reduce the risk of overflows.

Limit & offset

Just as you would expect them, the .limit(u64) and .offset(u64) functions can be used to add limits and offsets to the SQL query. Note that .limit() can not be combined with .one(), since the latter already adds a limit of 1. Also, the .offset() can not be used without a limit. The following examples illustrate possible uses:

rorm::query(db, Post).limit(10).all().await?;
rorm::query(db, Post).offset(13).one().await?;
rorm::query(db, Post).limit(10).offset(42).all().await?;
rorm::query(db, Post).limit(100).offset(1337).stream();

There is also the .range() function which provides a convenient way to add both the limit and offset. Mixing a range with the previous functions for limit and offset is not allowed. Thus, the following example will return at most 10 elements since it corresponds to limit 10 and offset 30:

rorm::query(db, Post).range(30..40).all().await?;

Ordering

To sort the returned rows, use .order_asc(...) and .order_desc(...) with a field to order by:

// All users, ordered by their username
rorm::query(db, User).order_asc(User.username).all().await?;

Both methods can be called multiple times. The first call takes the highest priority, every further call breaks the ties of the previous one:

// Sort posts by their creator, newest first per creator
rorm::query(db, Post)
    .order_asc(Post.user.username)
    .order_desc(Post.id)
    .all()
    .await?;

There is also the more generic .order_by(field, ordering) which takes the direction as a value of rorm::db::sql::ordering::Ordering, in case it has to be decided at runtime.

Distinct

The .distinct() method eliminates duplicate rows from the result:

// Every username only once, even if several posts link to the same user
let usernames: Vec<String> = rorm::query(db, Post.user.username)
    .distinct()
    .all()
    .await?;

Note

This directly corresponds to adding a DISTINCT to the SELECT statement.

Row locking (postgres only)

When querying inside a transaction, .lock(...) can be used to lock the returned rows, for example to prevent concurrent transactions from modifying them until yours is finished.

use rorm::db::sql::select::{LockAcquire, LockStrength};

let mut tx = db.start_transaction().await?;

// SELECT ... FOR UPDATE
let user: User = rorm::query(&mut tx, User)
    .condition(User.id.equals(user_id))
    .lock(LockStrength::Update, LockAcquire::Wait)
    .one()
    .await?;

LockStrength selects the lock mode (Update, NoKeyUpdate, Share, KeyShare — corresponding to FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE).

LockAcquire controls what happens when a row is already locked: Wait blocks until the lock is released, NoWait errors immediately and SkipLocked silently omits locked rows from the result.

Warning

.lock() is only available with the postgres-only cargo feature, since the other databases don't support this family of locks.

Insert

To create new rows in the database, the rorm::insert builder is used:

#[derive(Patch)]
#[rorm(model = "User")]
struct NewUser {
    username: String,
    password: String,
}

#[derive(Patch)]
#[rorm(model = "Post")]
struct NewPost {
    message: String,
    user: ForeignKey<User>,
}

async fn insert_example(db: &Database) -> Result<(), rorm::Error> {
    // insert a single user
    rorm::insert(db, NewUser).single(&NewUser {
        username: "alice".to_string(),
        password: "Secure-123".to_string(),
    }).await?;

    // insert a collection of user posts
    let posts: Vec<> = vec![...];
    rorm::insert(db, NewPost).bulk(&posts).await?;

    Ok(())
}

Note

Since the id field is annotated with #[rorm(id)] it is set by the database.

Therefore, we mustn't set it ourselves. So we declare a patch (NewUser or NewPost) which doesn't contain it and insert that instead of the model structs themselves.

But what if you need the values of fields which are set by the database?

Returning

The rorm::insert builder returns the whole model it just inserted:

#[derive(Patch)]
#[rorm(model = "User")]
struct NewUser {
    username: String,
    password: String,
}

// Note that the type `User` is returned instead of just a `NewUser`
let new_user: User = rorm::insert(db, NewUser).single(&NewUser {..}).await?;

To customize the behavior, a family of .return_...() methods are provided:

pub async fn show_various_returns(db: &Database, user: &NewUser) -> Result<(), Error> {
    // Return model instance by default
    let _: User = rorm::insert(db, NewUser)
        .single(user)
        .await?;

    // Return a patch's instance instead of whole model
    // (including the one used to insert and the model itself)
    let _: AnotherUserPatch = rorm::insert(db, NewUser)
        .return_patch::<UserPatch>()
        .single(user)
        .await?;

    // Return a tuple of fields
    let _: (i64, String) = rorm::insert(db, NewUser)
        .return_tuple((User.id, User.name))
        .single(user)
        .await?;

    // Return the model's primary key
    let _: i64 = rorm::insert(db, NewUser)
        .return_primary_key()
        .single(user)
        .await?;

    // Return nothing
    let _: () = rorm::insert(db, NewUser)
        .return_nothing()
        .single(user)
        .await?;

    Ok(())
}

Update

To change models' fields the rorm::update builder is used:

pub async fn set_good_password(db: &Database) -> Result<(), rorm::Error> {
    rorm::update(db, User)
        .set(User.password, "I am way more secure™".to_string())
        .condition(User.password.equals("password"))
        .await?;
    Ok(())
}

Dynamic mode and set_if

Before executing the query, set has to be called at least once to set a value for a column (the first call changes the builder's type). Otherwise, the query wouldn't do anything.

This can be limiting when your calls are made conditionally.

To support this, the builder can be put into a "dynamic" mode by calling begin_dyn_set. Then calls to set won't change the type. When you're done, use finish_dyn_set to go back to "normal" mode. It will check the number of "sets" and return Result which is Ok for at least one and an Err for zero. Both variants contain the builder in "normal" mode to continue.

A common pattern for dynamic mode is to check a bunch of Options and inserting the Somes:

async fn update_user(
    db: &Database,
    user_id: i64,
    optional_new_username: Option<String>,
    optional_new_password: Option<String>
) -> Result<(), rorm::Error> {
    let mut updater = rorm::update(db, User).begin_dyn_set();

    if let Some(new_username) = optional_new_username {
        updater = updater.set(User.username, new_username);
    }
    if let Some(new_password) = optional_new_password {
        updater = updater.set(User.password, new_password);
    }

    match updater.finish_dyn_set() {
        Ok(updater) => updater.condition(User.id.equals(user_id)).await?,
        Err(_) => println!("Nothing to update"),
    }

    Ok(())
}

This can be simplified using the set_if method which takes an Option and calls set internally if the option is Some:

async fn update_user(
    db: &Database,
    user_id: i64,
    optional_new_username: Option<String>,
    optional_new_password: Option<String>
) -> Result<(), rorm::Error> {
    let updater = rorm::update(db, User)
        .begin_dyn_set()
        .set_if(User.username, optional_new_username)
        .set_if(User.password, optional_new_password);

    match updater.finish_dyn_set() {
        Ok(updater) => updater.condition(User.id.equals(user_id)).await?,
        Err(_) => println!("Nothing to update"),
    }

    Ok(())
}

Delete

To delete rows from a table, the rorm::delete builder is used:

pub async fn delete_single_user(db: &Database, user: &UserPatch) -> Result<(), rorm::Error> {
    rorm::delete(db, User)
        .single(user)
        .await?;
    Ok(())
}
pub async fn delete_many_users(db: &Database, users: &[UserPatch]) -> Result<(), rorm::Error> {
    rorm::delete(db, User)
        .bulk(users)
        .await?;
    Ok(())
}
pub async fn delete_underage(db: &Database) -> Result<(), rorm::Error> {
    let num_deleted: u64 = rorm::delete(db, User)
        .condition(User.age.less_equals(18))
        .await?;
    Ok(())
}

A rorm::delete is quite simple. It expects only one of four methods which specify what to delete:

.single(...)

To delete a single row, use the single method and pass it the model to delete.

Note

single doesn't require the actual model. It accepts any patch which contains the primary key.

.bulk(...)

bulk is used like single but takes an iterator of instances and deletes all at once.

.condition(...)

When you need more complex deleting logic than just some concrete instances, you can use the conditon method. Any row which matches the provided condition will be deleted.

See query for how conditions look.

all()

For the case where you'd want to wipe the whole table, you can use the all method.