Most use cases require some simple CRUD operations. 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. Because there are very few resources on doing 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 complete control of the SQL queries since some of the background tasks have to be 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
The Example: User Model
So we organized the project without an ORM, instead implementing a 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 two fields are Optional. This means that every time you 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 ensure we centralize the business logic for each operation.
First, let’s 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>>,
}
Introducing SetOption
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. 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 was set by the user intentionally and which is left. Thus, setting a field to None means creating or updating the field with a NULL value.
Builder Pattern for Flexibility
You might also have noticed the macro Builder set at the UpdateUsers struct. This uses the [builder-pattern macro](https://lib.rs/crates/builder-pattern), which is very simple and has few dependencies. The goal is to build the UpdateUsers Struct as we go in our code. It gives us much more flexibility than having to fill out all the fields at once.
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, centralize 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 field at any time.
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 automatically added the updated_at field 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. Thus, whenever someone wants to “update a user,” we can validate as many things as we want (e.g., that the password is valid and stored as a hash).
Complete Implementation
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 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. That would require creating a macro and parsing the AST.
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);
}
}
To conclude this article, after doing it, what are the pros and cons of this approach compared 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 they haven’t been updated since 2021, since they are stable. - We only use some of the concepts of an ORM that we like (eg
ModelandActiveModelfor 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 and 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 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 specific fields in our database
- All this custom code (or boilerplate) has to be tested
- This misses the ability to generate Struct from a CLI that inspects 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 be embarking on too many concepts for our simple use case.
While implementing all that logic was not trivial at first (first time the team built in Rust), it was primarily because we were 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 used the same approach.
This is why, I ended up creating a simple library that do just that. It’s very well tested and comes with smart default. More about it in the next post