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:
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"
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:
More great articles from LogRocket:
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:
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
up.sql file is for creating a migration, while the
down.sql file is for reversing it.
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 );
down.sql file with SQL that can reverse the migration:
sql DROP TABLE students;
After creating the
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
#[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 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
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
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
1. Start by creating a
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.firstname, result.lastname, result.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
[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:
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:
Now you can interact with databases in Rust using either Diesel or SQLx.
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.
LogRocket: Full visibility into production Rust apps
Debugging Rust applications can be difficult, especially when users experience issues that are difficult to reproduce. If you’re interested in monitoring and tracking performance of your Rust apps, automatically surfacing errors, and tracking slow network requests and load time, try LogRocket.
LogRocket is like a DVR for web and mobile apps, recording literally everything that happens on your Rust app. Instead of guessing why problems happen, you can aggregate and report on what state your application was in when an issue occurred. LogRocket also monitors your app’s performance, reporting metrics like client CPU load, client memory usage, and more.
Modernize how you debug your Rust apps — start monitoring for free.