Interacting with databases in Rust using Diesel vs. SQLx

In this tutorial, we’ll explore two libraries used when interacting with relational databases in Rust: Diesel and SQLx.

This article will use a simple classroom database with students to demonstrate each approach. We’ll perform CRUD operations using Diesel ORM and SQLx.

What we’ll cover:

• What is Diesel?
• What is SQLx?
• Getting started with Diesel ORM
  • Initializing a new project with Diesel ORM
  • Installing Diesel CLI
  • Setting up our Diesel environment
  • Setting up Diesel migrations
  • Creating rows with Diesel ORM
  • Rust database querying with Diesel ORM
• Getting started with SQLx
  • Initializing a new project with SQLx
  • Using SQLx and Rust to update or delete database records

To follow along with this tutorial, you will need a working knowledge of Rust along with the ability to access and use Rust, Rust’s build system and package manager Cargo, and a MySQL server instance.

What is Diesel?

Diesel is an ORM that supports PostgreSQL, MySQL, SQLite. ORM stands for object-relational mapping. ORMs help object-oriented programmers abstract the details of relational databases.

ORMs are shipped with query builders, so you don’t have to worry about writing raw SQL queries. Using ORMs, you can communicate with relational databases as if they are object-oriented.

For less experienced developers, using ORMs might be better because ORMs craft optimized SQL queries. ORMs also make you less prone to SQL injection attacks.

What is SQLx?

Unlike Diesel, SQLx is not an ORM. SQLx is an asynchronous Rust SQL crate that features compile-time SQL query checks. It is both database- and runtime-agnostic.

SQLx supports connection pooling, cross-platform development, nested pooling, asynchronous notifications, transport layer security, and other exciting features. When using SQLx, you must craft the SQL queries and migrations yourself.

Having scratched the surface, let’s explore how to interact with relational databases with Diesel and SQLx.

Getting Started with Diesel ORM

The following steps demonstrate how to set up a Rust project with Cargo that uses Diesel ORM.

Initializing a new project with Diesel ORM

Your first step is to initialize the project by running the following command:

cargo new -- lib classroom_diesel
cd classroom_diesel

In the code above, we set up the project and named it classroom_diesel. The new project directory should look like this:

./
│
├── src/
│   └── lib.rs
│
├── .gitignore
└── Cargo.toml

We also need to update the Cargo.toml file with the dependencies we need in the project, like so:

[dependencies]
diesel = { version = "1.4.4", features = ["mysql"] }
dotenv = "0.15.0"

The dotenv dependency helps us manage environment variables in the project.

Installing Diesel CLI

Diesel uses a separate CLI tool. It is a standalone binary; we do not need to add it as a dependency in the cargo.toml file. Simply install it with the command below:

cargo install diesel_cli

Setting up our Diesel environment

We need to set a DATABASE_URL variable in our environment. This is how Diesel knows which MySQL database to connect to:

echo DATABASE_URL=mysql://<username>:<password>@localhost/<database>  > .env

Edit the connection string to match your local database credentials.

Your project directory will now look like this:

./
│
├── src/
│   └── lib.rs
│
├── .env
├── .gitignore
└── Cargo.toml

Now run the following command:

diesel setup

This command will help us set up the database and create an empty migrations directory for managing the database schema.

Setting up Diesel migrations

Migrations help the ORM keep track of database operations, such as adding a field or deleting a table. You can think of them as a version control system for your database.

First, let’s create some migrations for the classroom application using Diesel CLI. Ideally, we should have a table containing data about classroom students.
We need to create empty migration files, then populate them with SQL to create a table.

diesel migration generate create_students

Your file tree will look similar to this:

./
│
├── migrations/
│   │
│   ├── 2022-07-04-062521_create_students/
│   │   ├── down.sql
│   │   └── up.sql
│   │
│   └── .gitkeep
│
├── src/
│   └── lib.rs
│
├── .env
├── .gitignore
├── Cargo.toml
└── diesel.toml

The up.sql file is for creating a migration, while the down.sql file is for reversing it.

Update the up.sql file with the SQL for the migration:

sql
CREATE TABLE students (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(255) NOT NULL,
  lastname TEXT NOT NULL,
  age INTEGER NOT NULL
);

Modify the down.sql file with SQL that can reverse the migration:

sql
DROP TABLE students;

After creating the up and down migrations, we need to execute the SQL on the database:

diesel migration run

We can start writing Rust to perform queries on the table.

Creating rows with Diesel ORM

Let’s write code to establish a connection to the MySQL server using the connection string set in the .env file.

#[macro_use]
extern crate diesel;
extern crate dotenv;

pub mod models;
pub mod schema;

use diesel::prelude::*;
use dotenv::dotenv;
use std::env;

pub fn create_connection() -> MysqlConnection {
    dotenv().ok();

    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    MysqlConnection::establish(&database_url)
        .unwrap_or_else(|_| panic!("Error connecting to {}", database_url))
}

Next, we must write a model for the Students table. Models are where the object-relational mapping takes place. The model will generate the code needed to convert a row or rows on the Students table to a Student struct in Rust.

cd ./src
touch model.rs

In the new model.rs file we just created, add the following:

use super::schema::students;

#[derive(Queryable)]
pub struct Student {
    pub id: i32,
    pub firstname: String,
    pub lastname: String,
    pub age: i32,
}

#[derive(Insertable)]
#[table_name = "students"]
pub struct NewStudent<'a> {
    pub firstname: &'a str,
    pub lastname: &'a str,
    pub age: &'a i32,
}

With this model, information from the Students table will map to the corresponding Student struct in Rust. The src folder should now look like this:

src/
├── lib.rs
├── models.rs
└── schema.rs

Now, we can write a script to add a student:

cd src
mkdir bin
cd bin
touch create_students.rs

In the create_students.rs file, we can invoke the models and functions written earlier to create a new student:

extern crate classroom_diesel;
extern crate diesel;

use self::classroom_diesel::*;
fn main() {
    let connection = create_connection();
    let firstname = "John";
    let lastname = "Doe";
    let age: i32 = 64;

    let student = create_post(&connection, firstname, lastname, &age);
    println!(
        "Saved student {} with id {}",
        student.firstname, student.id
    );
}

The project’s structure will now look similar to this:

./
│
├── migrations/
│   │
│   ├── 2022-07-04-062521_create_students/
│   │   ├── down.sql
│   │   └── up.sql
│   │
│   └── .gitkeep
│
├── src/
│   │
│   ├── bin/
│   │   └── create_students.rs
│   │
│   ├── lib.rs
│   ├── models.rs
│   └── schema.rs
│
├── .env
├── .gitignore
├── Cargo.lock
├── Cargo.toml
└── diesel.toml

Execute the new script using the following command:

cargo run --bin create_students

As you can see in the image below, the new student file for John has been saved with an id of 1. We can use this id to query Rust databases, which we will take a look at in the next section.

Rust database querying with Diesel ORM

In the previous section, we reviewed how to write into the database in Rust using Diesel ORM. It is also essential to understand how querying, or reading, works.

Let’s write a script to query a student whose id is 1. Start by creating a query_students.rs file:

cd bin
touch query_students.rs

Then, in the query_students.rs file we just created, add the following:

extern crate classroom_diesel;
extern crate diesel;

use self::models::*;
use classroom_diesel::*;
use diesel::prelude::*;

fn main() {
    use self::schema::students::dsl::*;

    let connection = create_connection();
    let result = students
        .filter(id.eq(1))
        .load::<Student>(&connection)
        .expect("Error loading students");

    println!(
        "Student: {} {} {} years",
        result[0].firstname, result[0].lastname, result[0].age
    );
}

Execute the script:

cargo run --bin query_students

As you can see in the image below, the result is a printed line containing the first name, last name, and age of the student file we queried from the database:

Getting started with SQLx

Now that we know how to create a project that uses Diesel ORM to interact with databases in Rust, let’s take a look at how to create a project that uses SQLx instead.

Initializing a new project with SQLx

Start by running the command below:

cargo new classroom_sqlx --bin

Then, add the required dependencies to the cargo.toml file:

[dependencies]
sqlx = { version = "0.5", features = [  "runtime-async-std-native-tls", "mysql" ] }
async-std = { version = "1", features = [ "attributes" ] }

That’s all you need with regards to setting up. Simple, right?

To use SQLx to interact with databases in Rust, all we have to do is write some SQL queries and Rust code. In the Diesel ORM section, we created and read a student record; in this section, we will write queries to update and delete a record.

Using SQLx and Rust to update or delete database records

First, we need to write some Rust code to connect SQLx to the MySQL server:

//main.rs

use sqlx::mysql::MySqlPoolOptions;

#[async_std::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = MySqlPoolOptions::new()
        .max_connections(7)
        .connect("mysql://root:@localhost/classroom_diesel")
        .await?;

    Ok(())
}

SQLx supports both prepared and unprepared SQL queries. Prepared SQL queries are averse to SQL injection.

Let’s see how to update the first and last name of a record with a primary key of 1:

use sqlx::mysql::MySqlPoolOptions;

#[async_std::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = MySqlPoolOptions::new()
        .max_connections(5)
        .connect("mysql://root:@localhost/classroom_diesel")
        .await?;

    sqlx::query("UPDATE students SET firstname=?, lastname=? WHERE id=?")
        .bind("Richard")
        .bind("Roe")
        .bind(1)
        .execute(&pool)
        .await?;
    Ok(())
}

Execute the script with the command below:

cargo run

Deleting the record also takes a similar pattern; the only difference is the SQL query:

use sqlx::mysql::MySqlPoolOptions;

#[async_std::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = MySqlPoolOptions::new()
        .max_connections(5)
        .connect("mysql://root:@localhost/classroom_diesel")
        .await?;

    sqlx::query("DELETE FROM students WHERE id=?")
        .bind(1)
        .execute(&pool)
        .await?;
    Ok(())
}

Execute the script with the command below:

cargo run

Now you can interact with databases in Rust using either Diesel or SQLx.

Conclusion

ORMs like Diesel are adequate; they help you generate some of the SQL you need. Most of the time, adequate is all you need in your applications.

However, it may take more “magic” — in other words, your time and effort — in more extensive applications to get ORMs to work correctly and generate performant SQL queries.

If the need arises to create more complicated queries with high volume and low latency requirements, it may be better to use libraries like SQLx to execute raw SQL queries.