Simple CRUD only using SQLx

Most use cases would need some simple crud operations to do. And that was true even for a new API we built that is now used by one of our user-facing products.

The goal of this article is not to debate whether using an ORM is a good or bad idea. But because there are very few resources about doing some CRUD without an ORM in Rust, I wanted to share my experience.

A few guidelines we had

Centralize insert and update logic to make it obvious
Having full control of the SQL queries since some of the background tasks have to be very optimized
Most of us have a data background (data engineering or data science) which gives us high confidence in writing good optimized SQL queries
Prototype fast but also build for the long term

So we ended up organizing the project by not using an ORM but instead implementing some structure and helpers to make CRUD operations as easy as possible.

Let’s jump into it

Let’s take the following example

struct Users {
  id: i32,
  created_at: chrono::DateTime,
  updated_at: chrono::DateTime,
  email: String,
  password: String,
  preferred_color: Option<Enum>,
  last_login_at: Option<chrono::DataTime>,
}

First, we can see that only the last 2 fields are Optional. This means that every time you will pull a row from your database you have the certainty of which field is potentially null or not. This would be the equivalent of a Model in something like SeaOrm.

What about creating and updating a row?

I did not like the idea of a generic struct where anything could be modified (eg ActiveModel) instead I want us to be very explicit about what can be created and updated. Moreover, I want to make sure that we centralize the business logic for each operation.

First, let’s just have a look at the following

struct Users {
  id: i32,
  created_at: chrono::DataTime,
  updated_at: chrono::DataTime,
  email: String,
  password: String,
  preferred_color: Option<Enum>,
  last_login_at: Option<chrono::DataTime>,
}

struct UpdateUsers {
  id: Option<i32>,
  email: Option<String>,
  password: Option<String>,
  preferred_color: Option<Option<Enum>>,
  last_login_at: Option<Option<chrono::DataTime>>,
}

We can see that we wrap all of the fields that could be updated by another Option<> . The goal here is to check whether a field is set or not. To make things more explicit than having another Option<> . I went with the following approach

#[derive(Builder, Debug, Clone)]
struct UpdateUsers {
  #[default(NotSet)]
  id: SetOption<i32>,
  #[default(NotSet)]
  email: SetOption<String>,
  #[default(NotSet)]
  password: SetOption<String>,
  #[default(NotSet)]
  preferred_color: SetOption<Option<Enum>>,
  #[default(NotSet)]
  last_login_at: SetOption<Option<chrono::DataTime>>,
}

#[derive(Debug, Clone, Default, PartialEq, Eq, PartialOrd, Ord, Hash)]
enum SetOption<T> {
    Set(T),
    #[default]
    NotSet,
}

This simple SetOption enum, working similarly to the Option enum, clearly identifies which field has been set by the user intentionally and which one is left. Thus something that is set to None just means to create or update the field with a NULL value.

You might also have noticed the macro Builder set at the UpdateUsers struct. This is using the [builder-pattern macro](https://lib.rs/crates/builder-pattern) which is very simple and has few dependencies. The goal is that we can build the UpdateUsers struct as we go in our code. It gives us much more flexibility than having to pass all the fields at a given moment.

This makes the logic in the code so much easier to read

// Each of those fields could be constructed in
// different places of the code based on some logic
let update_user: UpdateUsers = UpdateUsers::new()
    .id(user.id)
    .preferred_color(new_color)
    .build()

Next centralizing the business logic

#[derive(Builder, Debug, Clone)]
struct UpdateUsers {
  #[default(NotSet)]
  id: SetOption<i32>,
  #[default(NotSet)]
  email: SetOption<String>,
  #[default(NotSet)]
  password: SetOption<String>,
  #[default(NotSet)]
  preferred_color: SetOption<Option<Enum>>,
  #[default(NotSet)]
  last_login_at: SetOption<Option<chrono::DataTime>>,
}

impl UpdateUsers {
    pub async fn update(sqlx_conn) -> Result<Users, anyhow::Error> {
        if self.id.is_not_set() { // is_not_set() is implemented at the SetOption enum
            // return Err
        }
        if self.email.is_not_set() &&
            self.password.is_not_set() &&
            self.preferred_color.is_not_set() &&
            self.last_login_at.is_not_set() {
            // return Err
        }
    let mut query = sqlx::QueryBuilder::new("UPDATE users SET updated_at = now()");
    
    if let Set(email) = self.email {
        query.push(", email = ");
        query.push_bind(email);
    }
    if let Set(password) = self.password {
        query.push(", password = ");
        query.push_bind(password);
    }
    if let Set(preferred_color) = self.preferred_color {
        query.push(", preferred_color = ");
        query.push_bind(preferred_color);
    }
    if let Set(last_login_at) = self.last_login_at {
        query.push(", last_login_at = ");
        query.push_bind(last_login_at);
    }
    query.push(" WHERE id = ");
    query.push_bind(self.id.value()?); // method `value() to retrieve T implemented at SetOptions
    query.push(" RETURNING *"); // Return the updated row

    query
        .build_query_as()
        .fetch_one(sqlx_conn)
        .await
        .map_err(|e| e.into())
    }

    }
}

#[derive(Debug, Clone, Default, PartialEq, Eq, PartialOrd, Ord, Hash)]
enum SetOption<T> {
    Set(T),
    #[default]
    NotSet,
}

Let’s look at what is happening. We first need to construct the UpdateUsers using the builder pattern which gives us the flexibility to set any of the fields at any moment.

Then we run some sanity checks like “field ID has to be set” or “At least one of the fields has to be set otherwise there is nothing to update”

Then we construct a query using sqlx::QueryBuilder so that we only update the fields that have been set. Also, notice that we set the updated_at field automatically so users cannot forget about it.

Even if this looks verbose, a tool like Copilot will fill in 80% of the boilerplate at the end :)

The main benefit is that all the business logic can be centralized in one place. Therefore anytime someone wants to “update a user” we can then validate as many things as we want (Eg: the password is good and stored as a hash, etc.)

So overall this could look like the following

let user = Users.select_by_email(sqlx_conn, "an_email");
let update_user = Updater_users::new().id(user.id);
...
update_user.password(new_password);
...
update_user.preferred_color(new_color);
...
let user = update_user.build().update(sqlx_conn).await?;

At the end, the code looks like the following

#[derive(sqlx::FromRow, Debug, Default, Clone, PartialEq, Eq)]
struct Users {
  id: i32,
  created_at: chrono::DataTime,
  updated_at: chrono::DataTime,
  email: String,
  password: String,
  preferred_color: Option<Enum>,
  last_login_at: Option<chrono::DataTime>,
}
#[derive(Builder, Debug, Clone)]
struct CreateUsers {
  #[default(NotSet)]
  email: SetOption<String>,
  #[default(NotSet)]
  password: SetOption<String>m
}

#[derive(Builder, Debug, Clone)]
struct UpdateUsers {
  #[default(NotSet)]
  id: SetOption<i32>,
  #[default(NotSet)]
  email: SetOption<String>,
  #[default(NotSet)]
  password: SetOption<String>,
  #[default(NotSet)]
  preferred_color: SetOption<Option<Enum>>,
  #[default(NotSet)]
  last_login_at: SetOption<Option<chrono::DataTime>>,
}

impl NewUsers {
    pub async fn create(sqlx_conn) -> Result<Users, anyhow::Error> {
        if self.email.is_not_set() &&
            self.password.is_not_set() {
            // return Err
        }
    sqlx::query_as(
        r#"
        INSERT INTO users (email, password, created_at, updated_at)
        VALUES ($1, $2, now(), now())
        RETURNING *
        "#,
    )
    .bind(self.email.value()?)
    .bind(self.password.value()?)
    .fetch_one(pool)
    .await
    .map_err(|e| e.into())
    }

    }
}

impl UpdateUsers {
    pub async fn update(sqlx_conn) -> Result<Users, anyhow::Error> {
        if self.id.is_not_set() { // is_not_set() is implemented at the SetOption enum
            // return Err
        }
        if self.email.is_not_set() &&
            self.password.is_not_set() &&
            self.preferred_color.is_not_set() &&
            self.last_login_at.is_not_set() {
            // return Err
        }
    let mut query = sqlx::QueryBuilder::new("UPDATE users SET updated_at = ");
    query.push_bind(Utc::now());
    
    if let Set(email) = self.email {
        query.push(", email = ");
        query.push_bind(email);
    }
    if let Set(password) = self.password {
        query.push(", password = ");
        query.push_bind(password);
    }
    if let Set(preferred_color) = self.preferred_color {
        query.push(", preferred_color = ");
        query.push_bind(preferred_color);
    }
    if let Set(last_login_at) = self.last_login_at {
        query.push(", last_login_at = ");
        query.push_bind(last_login_at);
    }
    query.push(" WHERE id = ");
    query.push_bind(self.id.value()?); // method `value() to retrieve T implemented at SetOptions
    query.push(" RETURNING *"); // Return the updated row

    query
        .build_query_as()
        .fetch_one(sqlx_conn)
        .await
        .map_err(|e| e.into())
    }

    }
}

impl Users {
    pub async fn select_by_id(...) -> Self {
        ...
    }
    // And any other method to retrieve the users with your business logic.
}

// -------------------
// SetOption util
#[derive(Debug, Clone, Default, PartialEq, Eq, PartialOrd, Ord, Hash)]
enum SetOption<T> {
    Set(T),
    #[default]
    NotSet,
}

/// Implement `From` for `SetOption` to allow for easy conversion from a value to a `SetOption`.
/// Mostly used by the `[into]` attribute of the builder-pattern macros
/// ```
/// let set_option: SetOption<i32> = 1.into();
/// assert_eq!(set_option, SetOption::Set(1));
/// ```
impl<T> From<T> for SetOption<T> {
    fn from(value: T) -> Self {
        SetOption::Set(value)
    }
}

impl<T> SetOption<T> {
    pub fn value(&self) -> Result<&T, anyhow::Error> {
        match self {
            SetOption::Set(value) => Ok(value),
            SetOption::NotSet => Err(anyhow!("Value is not set")),
        }
    }
    pub fn is_set(&self) -> bool {
        match self {
            SetOption::Set(_) => true,
            SetOption::NotSet => false,
        }
    }

    pub fn is_not_set(&self) -> bool {
        match self {
            SetOption::Set(_) => false,
            SetOption::NotSet => true,
        }
    }
}

A few notes here

Note 1: We only use the attribute sqlx::FromRow at the Users object. This is because we never retrieve a partially created or updated user.

Note 2: For “complex” (or custom) queries, we simply have a queries/... folder in which we create a struct that corresponds to the result of the query. This gives us a lot of flexibility and clarity.

Note 3: I wish I could iterate over the fields of my Struct easily to have a logic like

for name, value in my_struct.fields() {
    if name == "id" {
        // This has to be set otherwise return an error
    } else {
        query.push(", {name} = "); // just to illustrate the idea
        query.push_bind(value);
    }
}

In order to conclude this article, after doing it, what are the pros and cons of this approach compare to using an orm?

Pros

Fewer dependencies. We added smaller dependencies like builder-pattern but the reality is that those macros are so simple that it has not been updated since 2021 and worked out of the box in my new project in 2024.
We only use some of the concepts of an ORM that we like (eg Model and ActiveModel for which I took some inspiration from (see seaORM here)
All the business logic is centralized and we can lock down how each object is inserted and updated
All of our custom/complex queries are plain based SQL which are easy to debug and prototype first (vs converting SQL <> ORM logic back n forth)
We have total control of the queries and how we want to handle them (eg: manipulating a query to force using an index, etc.)
We just use Sqlx for everything we need (eg Migrations, queries, transactions, etc.)

Cons

We have to reimplement some of the boilerplate that an ORM covers like the ability to insert or update certain fields to our database
All this custom code (or boilerplate) has to be tested
This misses the ability to generate struct from a CLI that inspect the database (eg with SeaORM here)

In the end, we like where we landed with our approach. We could have used something bigger like SeaORM but we felt that we would embark too much concepts for our simple use case.

While implementing all that logic was not trivial at first (first time the team built in Rust) but mostly because of looking for how we wanted to structure the project (also first time building a web API for a user-facing product). Now that we have this pattern and built our experience, the second project would be much faster if we wanted to use the same approach.