Photo by Markus Winkler

In this article, we'll explore how to use SQLite (in conjunction with Diesel) with Rust in both file and memory modes.

Prerequisite

In addition to having Rust & Diesel CLI installed, install SQLite for your platform:

# Linux
$ sudo apt install sqlite3 libsqlite3-0 libsqlite3-dev

# OSX
$ brew install sqlite3

Create project (commit)

$ cargo new diesel-sqlite
$ cd diesel-sqlite

Add the following dependencies:

  • Diesel to provide ORM capabilities.
  • Actix web for our web layer.
  • Dotenv for working with environment variables.
  • Uuid to generate IDs.
[dependencies]
actix-rt = "1.0"
actix-web = "2.0"
chrono = { version = "0.4.11", features = ["serde"] }
diesel = { version = "1.4.4", features = ["sqlite", "uuidv07", "chrono"] }
dotenv = "0.15.0"
serde = { version = "1.0", features = ["derive"] }
serde_json = "1.0"
uuid = { version = "0.8", features = ["serde", "v4"] }

Next, create a .env file with a DATABASE_URL:

DATABASE_URL=users.db

Initialize Diesel and create migration script (commit)

$ diesel setup
$ diesel migration generate create_users

Add the following SQL statements to the generated up and down migration files.

-- migrations/xxxx_create_users/up.sql

CREATE TABLE IF NOT EXISTS users (
  id CHARACTER(36) NOT NULL PRIMARY KEY,
  email VARCHAR(60),
  phone VARCHAR(20),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER IF NOT EXISTS UpdateTimestamps AFTER UPDATE ON users
  FOR EACH ROW WHEN NEW.updated_at <= OLD.updated_at 
BEGIN 
  update users set updated_at=CURRENT_TIMESTAMP where id=OLD.id;  
END;

-- migrations/xxxx_create_users/down.sql

DROP TRIGGER IF EXISTS UpdateTimestamps;

DROP TABLE IF EXISTS users;

Create the users table by running:

$ diesel migration run

After running that, Diesel should create a schema in src/schema.rs for you.

Let's re-organize our database files:

  • Create a db directory under the src directory.
  • Move src/schema.rs into the db directory.
  • Update the file variable in diesel.toml from src/schema.rs to src/db/schema.rs.
  • Create a file models.rs in the db directory.

After that, create a file src/db.rs with the following:

// src/db.rs
pub mod models;
pub mod schema;

Create model (commit)

First, we'll embed our migrations since we'll want to use SQLite's memory mode for our tests. An added benefit is that our migrations is compiled into our app creating a single executable and removing that dependency on a file system.

Add diesel_migrations to our dependencies:

# Cargo.toml

[dependencies]
# ...
diesel_migrations = "1.4.0"

Add these to the top of main.rs:

// src/main.rs
#[macro_use]
extern crate diesel;
#[macro_use]
extern crate diesel_migrations;


mod db;
// ...

In src/db.rs, paste:

// src/db.rs

// ...
embed_migrations!();

pub fn establish_connection() -> SqliteConnection {
    if cfg!(test) {
        let conn = SqliteConnection::establish(":memory:")
          .unwrap_or_else(|_| panic!("Error creating test database"));
        
        let _result = diesel_migrations::run_pending_migrations(&conn);

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

We make use of Rust's conditional compilation facilities in establish_connection() to return an in-memory connection for tests and a file connection for normal runs.

Paste this into src/db/models.rs:

use uuid::Uuid;
use serde::{Deserialize, Serialize};
use diesel::prelude::*;

use super::schema::users;
use super::schema::users::dsl::users as user_dsl;


#[derive(Debug, Deserialize, Serialize, Queryable, Insertable)]
#[table_name = "users"]
pub struct User {
    pub id: String,
    pub email: Option<String>,
    pub phone: Option<String>,
    pub created_at: chrono::NaiveDateTime,
    pub updated_at: chrono::NaiveDateTime,
}

impl User {
    pub fn list(conn: &SqliteConnection) -> Vec<Self> {
        user_dsl.load::<User>(conn).expect("Error loading users")
    }

    pub fn by_id(id: &str, conn: &SqliteConnection) -> Option<Self> {
        if let Ok(record) = user_dsl.find(id).get_result::<User>(conn) {
            Some(record)
        } else {
            None
        }
    }

    pub fn by_email(email_str: &str, conn: &SqliteConnection) -> Option<Self> {
        use super::schema::users::dsl::email;

        if let Ok(record) = user_dsl.filter(email.eq(email_str)).first::<User>(conn) {
            Some(record)
        } else {
            None
        }
    }

    pub fn by_phone(phone_str: &str, conn: &SqliteConnection) -> Option<Self> {
        use super::schema::users::dsl::phone;

        if let Ok(record) = user_dsl.filter(phone.eq(phone_str)).first::<User>(conn) {
            Some(record)
        } else {
            None
        }
    }

    pub fn create(email: Option<&str>, phone: Option<&str>, conn: &SqliteConnection) -> Option<Self> {
        let new_id = Uuid::new_v4().to_hyphenated().to_string();
        
        if email.is_none() && phone.is_none() {
            return None
        } 
                
        if phone.is_some() {
            if let Some(user) = Self::by_phone(&phone.unwrap(), conn) {
                return Some(user)
            } 
        }
        
        if email.is_some() {
            if let Some(user) = Self::by_email(&email.unwrap(), conn) {
                return Some(user)
            } 
        }

        let new_user = Self::new_user_struct(&new_id, phone, email);

        diesel::insert_into(user_dsl)
            .values(&new_user)
            .execute(conn)
            .expect("Error saving new user");

        Self::by_id(&new_id, conn)
    }

    fn new_user_struct(id: &str, phone: Option<&str>, email: Option<&str>) -> Self {
        User {
            id: id.into(),
            email: email.map(Into::into),
            phone: phone.map(Into::into),
            created_at: chrono::Local::now().naive_local(),
            updated_at: chrono::Local::now().naive_local(),
        }
    }
}


#[cfg(test)]
mod user_test;

First, we declare import and declare our User struct.
Each of our User methods takes a SqliteConnection argument so we can pass in any connection we want (test or otherwise) without having to alter the method.
For the query methods, Diesel returns a Result, which we convert to an Option with .ok() to conform to our return type.

In create(), we first ensure that the supplied email and/or phone do not exist in the database before creating a new User record.

At the bottom of the file, we declare that the tests are declared in a file located in src/db/models/user_test.rs by convention.

Paste these tests in the file:


use crate::db::{establish_connection, models::User};

#[test]
fn create_user_with_phone_and_email() {
    let conn = establish_connection();
    let email = Some("test@email.com");
    let phone = Some("123456789");

    let user = User::create(email, phone, &conn).unwrap();

    assert_eq!(user.email.unwrap().as_str(), email.unwrap());
    assert_eq!(user.phone.unwrap().as_str(), phone.unwrap());
}

#[test]
fn create_user_with_phone_only() {
    let conn = establish_connection();
    let email = None;
    let phone = Some("123456789");

    let user = User::create(email, phone, &conn).unwrap();

    assert!(user.email.is_none());
    assert_eq!(user.phone.unwrap().as_str(), phone.unwrap());
}

#[test]
fn create_user_with_email_only() {
    let conn = establish_connection();
    let email = Some("test@email.com");
    let phone = None;

    let user = User::create(email, phone, &conn).unwrap();

    assert_eq!(user.email.unwrap().as_str(), email.unwrap());
    assert!(user.phone.is_none());
}

#[test]
fn create_user_with_existing_email() {
    let conn = establish_connection();
    let email = Some("test@email.com");
    let phone = None;

    let user = User::create(email, phone, &conn).unwrap();
    let existing_user = User::create(email, phone, &conn).unwrap();

    assert_eq!(user.id, existing_user.id);
}

#[test]
fn create_user_with_existing_phone() {
    let conn = establish_connection();
    let email = None;
    let phone = Some("123456789");

    let user = User::create(email, phone, &conn).unwrap();
    let existing_user = User::create(email, phone, &conn).unwrap();

    assert_eq!(user.id, existing_user.id);
}

#[test]
fn list_users() {
    let conn = establish_connection();
    let email = None;
    let phone = Some("123456789");

    let user = User::create(email, phone, &conn).unwrap();
    let existing_users = User::list(&conn);

    assert_eq!(1, existing_users.len());
    assert_eq!(user.id, existing_users[0].id);
}

#[test]
fn get_user_by_phone() {
    let conn = establish_connection();
    let email = None;
    let phone = Some("123456789");

    let user = User::create(email, phone, &conn).unwrap();
    let existing_user = User::by_phone(&phone.unwrap(), &conn).unwrap();

    assert_eq!(user.id, existing_user.id);
}

#[test]
fn get_user_by_email() {
    let conn = establish_connection();
    let email = Some("test@email.com");
    let phone = None;

    let user = User::create(email, phone, &conn).unwrap();
    let existing_user = User::by_email(&email.unwrap(), &conn).unwrap();

    assert_eq!(user.id, existing_user.id);
}

#[test]
fn get_user_by_id() {
    let conn = establish_connection();
    let email = Some("test@email.com");
    let phone = Some("123456789");

    let user = User::create(email, phone, &conn).unwrap();
    let existing_user = User::by_id(&user.id, &conn).unwrap();

    assert_eq!(user.id, existing_user.id);
}

Add a web service (commit)

Update our actix-web dependency to provide some test facilities that aren't available in 2.0 and add r2d2 to help with connection pooling:

[dependencies]
# ...
actix-web = "3.0.0-alpha.1"
diesel = { version = "1.4.4", features = ["sqlite", "uuidv07", "r2d2", "chrono"] }
r2d2 = "0.8.8"
r2d2-diesel = "1.0.0"

Let's refactor src/db.rs to use connection pooling:

// src/db.rs
// ...
use diesel::sqlite::SqliteConnection;
use r2d2_diesel::ConnectionManager;
use r2d2::Pool;


embed_migrations!();


pub type DbPool = Pool<ConnectionManager<SqliteConnection>>;


pub fn run_migrations(conn: &SqliteConnection) {
  let _ = diesel_migrations::run_pending_migrations(&*conn);
}

pub fn establish_connection() -> DbPool {
    if cfg!(test) {
        let manager = ConnectionManager::<SqliteConnection>::new(":memory:");
        let pool = r2d2::Pool::builder().build(manager).expect("Failed to create DB pool.");
        
        run_migrations(&pool.get().unwrap());

        pool
    } else {
        dotenv().ok();
    
        let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
        let manager = ConnectionManager::<SqliteConnection>::new(&database_url);
        
        r2d2::Pool::builder().build(manager).expect("Failed to create DB pool.")
    }
}

We also separate creating a test connection pool from running migrations.

In each of our models test, we change the way we get connections from

let conn = establish_connection();

to

let conn = establish_connection().get().unwrap();

Create a service module src/services.rs and paste:

// src/services.rs
pub mod user;

#[cfg(test)]
mod user_test;

Then we create the user service file src/services/user.rs and paste:

// src/services/user.rs
use actix_web::{HttpResponse, web};
use serde::{Serialize, Deserialize};

use crate::db::{DbPool, models::User};

#[derive(Serialize, Deserialize)]
pub struct UserForm {
    email: Option<String>,
    phone: Option<String>,
}

pub fn create(user_form: web::Json<UserForm>, pool: web::Data<DbPool>) -> HttpResponse {
    let conn = pool.get().unwrap();

    match User::create(user_form.email.as_deref(), user_form.phone.as_deref(), &conn) {
        Some(user) => HttpResponse::Ok().json(user),
        _ => HttpResponse::InternalServerError().json("Could not create user")
    }
}

pub fn index(pool: web::Data<DbPool>) -> HttpResponse {
    let conn = pool.get().unwrap();

    HttpResponse::Ok().json(User::list(&conn))
}

pub fn get(id: web::Path<String>, pool: web::Data<DbPool>) -> HttpResponse {
    let conn = pool.get().unwrap();

    match User::by_id(&id, &conn) {
        Some(user) => HttpResponse::Ok().json(user),
        _ => HttpResponse::NotFound().json("Not Found")
    }
}

pub fn init_routes(cfg: &mut web::ServiceConfig) {
    /* 
     * index: curl -i -X GET -H "Content-Type: application/json" http://localhost:5000/users
     * get: curl -i -X GET -H "Content-Type: application/json" http://localhost:5000/users/<id>
     * post: curl -i -X POST -H "Content-Type: application/json" -d '{"email":"xxx", "phone": "yyy"}' http://localhost:5000/users
     */
    
    cfg.service(
        web::resource("/users")
            .route(web::post().to(create))
            .route(web::get().to(index))
    )
    .service(
        web::scope("/users")
            .route("/{id}", web::get().to(get)),
    );
}

create(), index() and get() handle creating, listing and getting a user by id.
init_routes() adds our routes to the web server.

We then update src/main.rs to start our web server:

// src/main.rs

// ...
#[macro_use]
extern crate serde_json;
extern crate r2d2_diesel;


// ...
mod services;


#[actix_rt::main]
async fn main() -> std::io::Result<()> {
    use actix_web::{App, HttpServer, web::JsonConfig};

    let conn_pool = db::establish_connection();

    HttpServer::new(move || {
        App::new()
            .data(conn_pool.clone())
            .data(JsonConfig::default().limit(4096))
            .configure(services::user::init_routes)
    })
    .bind("0.0.0.0:5000")?
    .run()
    .await
}

With that update, our service is ready to receive and serve requests.

Let's add tests so we can have some assurance that our service does what it is billed to do.
In src/services/user_test.rs, paste:

// src/services/user_test.rs
use actix_web::{
    App,
    test::{read_body_json, read_body, init_service, TestRequest}
};

use crate::{db::{models::User, establish_connection}, services::user::init_routes};

#[actix_rt::test]
async fn create_user_from_api() {
    let test_email   = "test@email.com";
    let test_phone   = "123456789";
    let request_body = json!({ "email": test_email, "phone": test_phone });
    let conn_pool    = establish_connection();
    let mut app      = init_service(App::new().data(conn_pool.clone()).configure(init_routes)).await;

    let resp = TestRequest::post()
      .uri("/users")
      .set_json(&request_body)
      .send_request(&mut app)
      .await;

    assert!(resp.status().is_success(), "Failed to create user");

    let user: User = read_body_json(resp).await;

    assert_eq!(user.email.unwrap(), test_email);
    assert_eq!(user.phone.unwrap(), test_phone);
}

#[actix_rt::test]
async fn get_user_from_api_by_id() {
    let test_email   = "test@email.com";
    let test_phone   = "123456789";
    let request_body = json!({ "email": test_email, "phone": test_phone });
    let conn_pool    = establish_connection();
    let mut app      = init_service(App::new().data(conn_pool.clone()).configure(init_routes)).await;

    let create_resp = TestRequest::post()
      .uri("/users")
      .set_json(&request_body)
      .send_request(&mut app)
      .await;

    assert!(create_resp.status().is_success(), "Failed to create user");

    let created_user: User = read_body_json(create_resp).await;
    println!("/users/{}", created_user.id);
    
    let resp = TestRequest::get()
      .uri(format!("/users/{}", created_user.id).as_str())
      .send_request(&mut app)
      .await;

    assert!(resp.status().is_success(), "Failed to get user");

    let retrieved_user: User = read_body_json(resp).await;

    assert_eq!(created_user.id, retrieved_user.id);
}

#[actix_rt::test]
async fn list_users_from_api() {
    let test_email   = "test@email.com";
    let test_phone   = "123456789";
    let request_body = json!({ "email": test_email, "phone": test_phone });
    let conn_pool    = establish_connection();
    let mut app      = init_service(App::new().data(conn_pool.clone()).configure(init_routes)).await;

    let mut list_resp = TestRequest::get().uri("/users").send_request(&mut app).await;
    
    assert!(list_resp.status().is_success(), "Failed to list users");

    let mut body = read_body(list_resp).await;  
    let mut retrieved_users: Vec<User> = serde_json::from_slice::<Vec<User>>(&body).unwrap();

    assert_eq!(retrieved_users.len(), 0);

    let create_resp = TestRequest::post()
      .uri("/users")
      .set_json(&request_body)
      .send_request(&mut app)
      .await;

    assert!(create_resp.status().is_success(), "Failed to create user");
    
    list_resp = TestRequest::get().uri("/users").send_request(&mut app).await;

    assert!(list_resp.status().is_success(), "Failed to list users");

    body = read_body(list_resp).await;    
    retrieved_users = serde_json::from_slice::<Vec<User>>(&body).unwrap();

    assert_eq!(retrieved_users.len(), 1);
}

The complete code can be found here.

Credits

Photo by Markus Winkler on Unsplash.