If we want to use SQL/relational databases in Rust, there is no other crate to think about than Diesel. If you need access to NoSQL databases such as Redis or MongoDB, you will also find proper crates, but since the most used databases are relational databases, we will check Diesel here.

Diesel makes working with MySQL/MariaDB, PostgreSQL, and SQLite very easy by providing a great ORM and typesafe query builder. It prevents all potential SQL injections at compile time, but is still extremely fast. In fact, it's usually faster than using prepared statements, due to the way it manages connections to databases. Without entering into technical details, we will check how this stable framework works.

The development of Diesel has been impressive and it's already working in stable Rust. It even has a stable 1.x version, so let's check how we can map a simple table. Diesel comes with a command-line interface program, which makes it much easier to use. To install it, run cargo install diesel_cli. Note that, by default, this will try to install it for PostgreSQL, MariaDB/MySQL, and SQLite.

For this short tutorial, you need to have SQLite 3 development files installed, but if you want to avoid installing all MariaDB/MySQL or PostgreSQL files, you should run the following command:

cargo install --no-default-features --features sqlite diesel_cli

Then, since we will be using SQLite for our short test, add a file named .env to the current directory, with the following content:

DATABASE_URL=test.sqlite

We can now run diesel setup and diesel migration generate initial_schema. This will create the test.sqlite SQLite database and a migrations folder, with the first empty initial schema migration. Let's add this to the initial schema up.sql file:

CREATE TABLE 'users' (
  'username' TEXT NOT NULL PRIMARY KEY,
  'password' TEXT NOT NULL,
  'email' TEXT UNIQUE
);

In its counterpart down.sql file, we will need to drop the created table:

DROP TABLE `users`;

Then, we can execute diesel migration run and check that everything went smoothly. We can execute diesel migration redo to check that the rollback and recreation worked properly. We can now start using the ORM. We will need to add diesel, diesel_infer_schema, and dotenv to our Cargo.toml. The dotenv crate will read the .env file to generate the environment variables. If you want to avoid using all the MariaDB/MySQL or PostgreSQL features, you will need to configure diesel for it:

[dependencies]
dotenv = "0.10.1"

[dependencies.diesel]
version = "1.1.1"
default-features = false
features = ["sqlite"]

[dependencies.diesel_infer_schema]
version = "1.1.0"
default-features = false
features = ["sqlite"]

Let's now create a structure that we will be able to use to retrieve data from the database. We will also need some boilerplate code to make everything work:

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

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

#[derive(Debug, Queryable)]
struct User {
    username: String,
    password: String,
    email: Option<String>,
}

fn establish_connection() -> SqliteConnection {
 dotenv().ok();

 let database_url = env::var("DATABASE_URL")
 .expect("DATABASE_URL must be set");
 SqliteConnection::establish(&database_url)
 .expect(&format!("error connecting to {}", database_url))
}

mod schema {
 infer_schema!("dotenv:DATABASE_URL");
}

Here, the establish_connection() function will call dotenv() so that the variables in the .env file get to the environment, and then it uses that DATABASE_URL variable to establish the connection with the SQLite database and returns the handle.

The schema module will contain the schema of the database. The infer_schema!() macro will get the DATABASE_URL variable and connect to the database at compile time to generate the schema. Make sure you run all the migrations before compiling.

We can now develop a small main() function with the basics to list all of the users from the database:

fn main() {
    use schema::users::dsl::*;

    let connection = establish_connection();
    let all_users = users
       .load::<User>(&connection)
       .expect("error loading users");

    println!("{:?}", all_users);
}

As you can see in the guides at diesel.rs, you can also generate Insertable objects, which might not have some of the fields with default values, and you can perform complex queries by filtering the results in the same way you would write a SELECT statement.