How to use Diesel ORM in Rust

Diesel is a framework for Object Relational Mapping and Query Building. It’s goal is to get rid of alot of boilerplate code usually needed for interacting with a database. It should feel like Rust, so that the developer does not need to write SQL and instead can focus on coding.

Great examples of SQL statements being executed through Rust code can be seen on the home page of Diesel, but the best example from Diesels page for seeing why Diesel is needed is this:

#[derive(Queryable)]
pub struct Download {
    id: i32,
    version_id: i32,
    downloads: i32,
    counted: i32,
    date: SystemTime,
}

Thanks to #[derive(Queryable)] Diesel generates all the code needed for selecting this entity from the database. Let’s take a look at the same code without Diesel:

pub struct Download {
    id: i32,
    version_id: i32,
    downloads: i32,
    counted: i32,
    date: SystemTime,
}

impl Download {
    fn from_row(row: &Row) -> Download {
        Download {
            id: row.get("id"),
            version_id: row.get("version_id"),
            downloads: row.get("downloads"),
            counted: row.get("counted"),
            date: row.get("date"),
        }
    }
}

In order to make entities queryable, you would have to implement Download trait for every entity. Diesel generates this code automatically, but this is not everything that Diesel can do.

In this post we are going to focus on getting up to speed with Diesel and working with MySQL. We are going to see how to read, write, delete and update the data.

Disclaimer Diesel has its own guides. This is my personal note. The kind of a note that you write for yourself, when you learn something new. I hope that this will help somebody get up to speed πŸ™‚

Scenario

Imagine that you work at a company, that offers video streaming services. Think of a company like Youtube, Vimeo or Dailymotion.

Each video is described by tags like entertainment, funny, cars, games etc.

Multiple tags can be assigned to one video. Tags can repeat, so we don’t want to store each and every one in a single row, because then we would end up with a huge table and redundancy.

Setup

Let’s create a project and call it diesel_tutorial.

cargo new diesel_tutorial --bin

In Cargo.toml we need to specify our dependencies.

[dependencies]
diesel = { version = "1.0.0-beta1", features = ["mysql"] }
diesel_infer_schema = { version = "1.0.0-beta1", features = ["mysql"] }
dotenv = "0.9.0"

We’re going to use diesel for mapping with mysql features. We could specify multiple features, if we want to work with multiple databases, but then we would have to separate them by a comma:

diesel = { version = "1.0.0-beta1", features = ["postgres", "mysql"] }

Version “1.0.0-beta1” supports following databases:

  • MySQL (mysql)
  • PostgresSQl (postgressql)
  • SQLite (sqlite)

Each Database has its own connector for creating the connection. Everything else works universally.

Available connectors are:

  • MysqlConnection for MySQL (mysql)
  • PgConnection for PostgresSQl (postgressql)
  • SqliteConnection for SQLite (sqlite)

diesel_infer_schemas is a crate for automatic generation of code needed to represent tables in your database.

We will use dotenv for storing our variables.

Export string with your database connection url to .env file with console:

> echo DB_URL=mysql://username:[email protected]:port/db_name > .env

First function that we want to implement, is a function that returns MysqlConnection object, so that we can reuse it.

pub fn establish_connection() -> MysqlConnection {
    dotenv().ok();
    let db_url : String = String::from(env::var("DB_URL")
                                .expect("DB_URL must be set"));
    let db_connection = MysqlConnection::establish(&db_url)
                                .expect(&format!("Error connecting to {}",&db_url));

    return db_connection;
}

Data types

Mapping between data types in Rust and types in the database is following:

Database Rust
BigInt i64
Binary Vec< u8> or &[u8]
Boolean bool
Double f64
Float f32
Integer i32
Serial i32
SmallInt i16
Text String or &str
Timestamp SystemTime
VarChar String

In case of binary and text, Vec< u8> and String are used for inserting the data into the database.

Reading from the Tag table

Let’s begin with the actual implementation of our application.

#![recursion_limit="128"]

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

extern crate dotenv;

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

Now we have to call infer_schema in order to generate code for connecting to the database.

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

use schema::*;

Imagine that a table called Tag stores our tags for videos. Each tag consists of a tag_id and tag_name.

  • tag_id is a SmallInt in our table
  • tag_name is a VarChar in our table

If for some reason infer_schema! encounters problems, you can chose to specify the table layout by yourself using the table! macro, like this:

table! {
    tag {
        tag_id -> SmallInt,
        tag_name -> Varchar,
    }
}

For easier manipulation of the data, we have to create a struct representing a row in our table:

#[derive(Queryable)]
pub struct Tag {
    pub tag_id: i16,
    pub tag_name: String
}

#[derive(Queryable)] generates the code needed for reading Tag from the table.

Now we want to:
1. Create a function for reading from the database
2. Reuse the connection
3. Fetch the data and keep it in our struct
4. Output the number of results
5. Iterate over results and manipulate them one by one

fn read_and_output(db_connection : &MysqlConnection) {

We reuse the connection to load the results from table tag and store each record in Tag struct.

    let results = tag::table.load::<Tag>(&db_connection)
                        .expect("problem");

Let’s check how many results we’ve gotten:

   println!("Returned results: {}" , results.len());

Now we can iterate over our results and manipulate them one by one.

    for r in results {
        println!("{} {}", r.tag_id, r.tag_name);
    } 
}

That’s it! Thanks to Diesel we didn’t had to implement the Download trait for our table.

Inserting into the Tag table

Right now our rust struct Tag is only queryable. If we want Diesel to generate code for inserting data, we have to derive Insertable with rust #[derive(Insertable)]

#[derive(Queryable, Insertable)]
pub struct Tag {
    pub tag_id: i16,
    pub tag_name: String
}

For inserting the data we are going to create a new function and pass a reference to tag_id and tag_name.

pub fn insert_tag(db_connection : &MysqlConnection, tag_id_val: i16, tag_name_val : String) {

    let new_tag = Tag {
        tag_id : tag_id_val,
        tag_name : tag_name_val
    };

   diesel::insert_into(tag::table)
        .values(&new_tag)
        .execute(db_connection)
        .expect("Error inserting");
}

Now we can write and read from the database! Great!

Our main function should look like this:

fn main() {

Let’s call the methods we have created and check if everything works properly:

    let db_connection = establish_connection();

    // 1. query data from the table
    read_and_output(&db_connection);

    // 2. insert new data into the table
    let tag_id: i16 = 777;
    let tag_name: String = String::from("educational");
    insert_tag(&db_connection, tag_id, tag_name);
    read_and_output(&db_connection);
}

Now we can build our app

cargo build

and run it:

target/debug/diesel_tutorial

If everything went fine, we can move on to updating the records.

Updating records in the table

Next thing we want to focus on is updating our data.
Add Identifiable to our Tag struct and specify the primary key of your table using [primary_key(tag_id)]:

#[derive(Queryable, Insertable, Identifiable)]
#[table_name = "tag"]
#[primary_key(tag_id)]
pub struct Tag {
    pub tag_id: i16,
    pub tag_name: String,
} 

Now we will create a function, that takes 3 arguments: MysqlConnection, id of tag to be updated and it’s new value:

fn update_tag(db_connection : &MysqlConnection, id : i16, new_value : String){
    diesel::update(tag::table.find(id))
        .set(tag::tag_name.eq(new_value))
        .execute(db_connection);
}

Remember to always use execute, get_result or get_results at the end of your statement. Otherwise your query will not be executed πŸ˜‰

Deleting the data

Deleting the data follows the same convention as selecting, inserting and updating.

pub fn delete_tag(db_connection : &MysqlConnection, tag_id_val: i16){
    diesel::delete(tag::table.find(tag_id_val))
        .execute(db_connection);
}

Frequently Encountered Problems

Important if during build you encounter this error message:

note: /usr/bin/ld: cannot find -lmysqlclient
          collect2: error: ld returned 1 exit status

then you have to install libmysqlclient

sudo apt-get install libmysqlclient-dev

If you encounter error about Nullable not being implemented for data type X, then it means that the values in the column may be null. To fix that, set each column in your table to NOT NULL.

Code

You can find the whole code on my Github profile

#![recursion_limit = "128"]

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

extern crate dotenv;

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

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

use schema::*;

#[derive(Queryable, Insertable, Identifiable)]
#[table_name = "tag"]
#[primary_key(tag_id)]
pub struct Tag {
    pub tag_id: i16,
    pub tag_name: String,
} 

pub fn establish_connection() -> MysqlConnection {
    dotenv().ok();
    let db_url: String = String::from(env::var("DB_URL").expect("DB_URL must be set"));
    let db_connection =
        MysqlConnection::establish(&db_url).expect(&format!("Error connecting to {}", &db_url));

    return db_connection;
}

fn read_and_output(db_connection: &MysqlConnection) {

    let results = tag::table.load::<Tag>(db_connection).expect("problem");

    println!("Returned results: {}", results.len());

    for r in results {
        println!("{} {}", r.tag_id, r.tag_name);
    }
}

pub fn insert_tag(db_connection: &MysqlConnection, tag_id_val: i16, tag_name_val: String) {

    let new_tag = Tag {
        tag_id: tag_id_val,
        tag_name: tag_name_val,
    };

    diesel::insert_into(tag::table)
        .values(&new_tag)
        .execute(db_connection)
        .expect("Error inserting");
}

fn update_tag(db_connection : &MysqlConnection, id : i16, new_value : String){
    diesel::update(tag::table.find(id))
        .set(tag::tag_name.eq(new_value))
        .execute(db_connection);
}

pub fn delete_tag(db_connection : &MysqlConnection, tag_id_val: i16){
    diesel::delete(tag::table.find(tag_id_val))
        .execute(db_connection);

}

fn main() {
    let db_connection = establish_connection();

    // 1. query data from the table
    read_and_output(&db_connection);

    // 2. insert new data into the table
    let tag_id: i16 = 777;
    let tag_name: String = String::from("educational");
    insert_tag(&db_connection, tag_id, tag_name);
    read_and_output(&db_connection);

    // 3. update existing data
    update_tag(&db_connection, tag_id, String::from("science"));
    read_and_output(&db_connection);

    // 4. delete data from the table
    delete_tag(&db_connection, tag_id);
    read_and_output(&db_connection);

}

2 Comments

Comments are closed, but trackbacks and pingbacks are open.