Async SQlite in Rust

SQlite is the most used database engine in the world. Every Android and iOS mobile uses SQLite, alongside most desktop computers. It’s a powerful way to easily store, search, and organise your data without the need for a heavier database engine like MySQL or Postgres.

In this blog, we’re going to be utilising the sqlx Rust package to store and retrieve data in async Rust. To start, you’ll need to add the following dependencies to your Cargo.toml

1[dependencies]
2sqlx = { version = "0.8.2", features = ["runtime-tokio", "sqlite", "chrono"] }
3tokio = { version = "1.15.0", features = ["full"] }
4chrono = "0.4.34"

The tokio crate will provide us with an async runtime and the chrono crate will be used for handling datetime data types.

sqlx is a database driver that can support multiple different kinds of SQL databases - for example, MySQL and Postgres too.

Creating async runtime

Blah blah, using Tokio to make main async… maybe talk about the features of the crate needed. We’ve used full for ease, but think you need the macros feature for this more specifically.

Creating an SQLite database

The first thing to do is to make an SQL database. In sqlx, everything is managed by pools. Pools are collections of connections that are managed for us by sqlx - we don’t need to worry about send requests via specific connections.

We have two ways to make an SQLite database - either in memory or as a file on disk. Making both is very simple.

1let pool = SqlitePoolOptions::new()
2    .max_connections(5)
3    .connect(":memory:")
4    .await?;

Will make an in memory database. Alternatively we might use

1let db_url = "sqlite:mydb.db";
2if !Sqlite::database_exists(db_url).await.unwrap_or(false) {
3    Sqlite::create_database(db_url).await.unwrap();
4}
5
6let pool = SqlitePoolOptions::new()
7    .max_connections(5)
8    .connect(db_url)
9    .await?;

Notice how when connecting to a file database we need to create it first. It’s best to check if the database exists before creating it, as you can’t make a database that already exists! Equally, you can’t connect to a database that doesn’t exist.

For the rest of this blog we’ll be using the in memory database. It’s a bit more convenient for these purposes as it is automatically deleted when the program ends.

Creating Tables with Async SQLite

Before we can really do anything with SQLite, we need to make some tables.

In this example, we’ll make a table to store some data about employees accessing a system that let’s them store their favourite words (it’s contrived but let’s us show off lots of datatypes!). Let’s suppose we want to track the following things

• Name
• Number of times accessed the system
• Last time of system access
• Favourite words
• An authorisation level

Name will obviously be some text, number of times accessing the system will be a number, last time accessed will be a date time. Let’s say they can store arbitrarily many favourite words - so a vec of text. Finally, authorisation level best matches to enums in Rust. Let’s start by just making the table.

Here’s our employee table

1pub const CREATE_TABLE: &'static str = "CREATE TABLE employee(
2    id          INTEGER  PRIMARY KEY NOT NULL,
3    name        TEXT                 NOT NULL UNIQUE,
4    num_times   INTEGER              NOT NULL,
5    last_access TEXT                 NOT NULL,
6    fav_words   TEXT                 NOT NULL,
7    auth        TEXT                 NOT NULL  
8)";
9

Sometimes, rather than storing the table set up commands in the Rust code, it can be nicer to have an sql file, and read them in from the file. We won’t cover that in this more minimal example.

We’ll also make name UNIQUE so that we can use it later in updates to uniquely identify an employee. Obviously, in a real life scenario that would be a bad idea!

Modelling Datatypes from SQLite in Rust

SQLite is quite limited in the types of data it supports compared with something like Postgres. Actually, all you get is

• NULL
• INTEGER
• REAL
• TEXT
• BLOB

That’s why we’ve gone for TEXT for the type of quite a few of the fields. In Postgres, there’s a built in TIMESTAMP type, ARRAY type and the ability to make enums. This does mean we need to be a little bit creative. Let’s tackle them one by one.

Because we added chrono as a feature for sqlite, that will be handled automatically for us.

The enum can be modelled really easily, thanks to Rust’s macro system

1#[derive(Debug, sqlx::Type)]
2enum Auth {
3    Owner,
4    Admin,
5    User,
6}

We’ll say there are three levels of user - owner, admin and bog-standard user.

Finally, for the array, we can actually use serde and serde_json to serialise and deserialise a Vec<String> in Rust into just text to insert it.

We add the following as dependencies

serde = "1.0.214"
serde_json = "1.0.132"

To make things more convenient for us, we can also model the employee as a struct.

1struct Employee {
2    name: String,
3    num_times: i32,
4    last_accessed: DateTime<Utc>,
5    fav_words: Vec<String>,
6    auth: Auth,
7}

Let’s say Mary has just signed up to the system for the first time, and her favourite words are pets.

1let mary = Employee {
2    name: "Mary".to_string(),
3    num_times: 1,
4    last_accessed: Utc::now(),
5    auth: Auth::User,
6    fav_words: vec!["Cat".to_string(), "dog".to_string()],
7};

By the way, make sure to use chrono::offset::Utc;

Inserting into an SQlite Database

The first thing we need is an insert our user. I think a convenient way is as an implementation on the Employee struct

1impl Employee {
2    async fn insert(&self, db_connection: &Pool<Sqlite>) -> Result<(), sqlx::Error> {
3        let fav_words = serde_json::to_string(&self.fav_words).unwrap();
4
5        let _insert = sqlx::query(&INSERT_NEW_ROW)
6            .bind(&self.name)
7            .bind(self.num_times)
8            .bind(self.last_accessed)
9            .bind(fav_words)
10            .bind(&self.auth)
11            .execute(db_connection)
12            .await?;
13
14        return Ok(());
15    }
16}

Notice how we call .bind()  in order to attach variables to the query - these need to correspond to the order of the variables in the command. So the first .bind() corresponds to $1, the second .bind() corresponds to $2 and so on and so forth.

And the INSERT_NEW_ROW command is given by

1pub const INSERT_NEW_ROW: &'static str = "
2INSERT INTO employee( 
3        name, 
4        num_times,
5        last_access,
6        fav_words,
7        auth 
8    )
9    VALUES ( 
10        $1, 
11        $2,
12        $3,
13        $4,
14        $5
15    )
16";

We can then insert just by calling

mary.insert(&pool).await.unwrap();

Updating a Row

Now let’s say that later on, Mary decides that she’s had enough of pets - now she likes food.

let new_favourites = vec!["pizza".to_string(), "ice cream".to_string()];

There’s a few different ways to model this - in this example we are going to update the local state and the database state at the same time. Obviously, in a real life example you would need robust tests to ensure the local state and the database state do not diverge from one another!

We’ll make another function as an implementation for this

1impl Employee {
2
3    async fn update_fav_words(
4        &mut self,
5        new_words: Vec<String>,
6        db_connection: &Pool<Sqlite>,
7    ) -> Result<(), sqlx::Error> {
8        let new_words_str = serde_json::to_string(&new_words).unwrap();
9
10        let now = Utc::now();
11
12        let _update = sqlx::query(&UPDATE_FAV_WORDS)
13            .bind(&now)
14            .bind(new_words_str)
15            .bind(&self.name)
16            .execute(db_connection)
17            .await?;
18
19        self.fav_words = new_words;
20        self.last_accessed = now;
21        self.num_times += 1;
22
23        return Ok(());
24    }
25
26}

Notice how we wait till we know that the database has updated successfully before we then update the local state. We can call this by simply running (make sure to mark mark at mut)

mary.update_fav_words(new_favourites, &pool).await.unwrap();

UPDATE_FAV_WORDS is given by

1pub const UPDATE_FAV_WORDS: &'static str = "
2    UPDATE employee
3    SET 
4        last_access = $1,
5        fav_words = $2,
6        num_times = num_times + 1
7    WHERE 
8        name = $3;
9";

Querying the Database

Querying is also nice and simple. Since we have that little custom conversion from a String to Vec<String> we can handle it inside another implementation

1impl Employee {
2    async fn find_by_name(name: &str, db_connection: &Pool<Sqlite>) -> Result<Self, sqlx::Error> {
3        let row = sqlx::query(&FIND_EMPLOYEE)
4            .bind(name)
5            .fetch_one(db_connection)
6            .await?;
7
8        let name: String = row.try_get("name")?;
9        let num_times: i32 = row.try_get("num_times")?;
10        let last_accessed: DateTime<Utc> = row.try_get("last_access")?;
11        let fav_words_str: String = row.try_get("fav_words")?;
12        let auth: Auth = row.try_get("auth")?;
13
14        let fav_words: Vec<String> = serde_json::from_str(&fav_words_str).unwrap();
15
16        let e = Employee {
17            auth,
18            fav_words,
19            last_accessed,
20            name,
21            num_times,
22        };
23
24        return Ok(e);
25    }
26}

You will need to use sqlx::Row; to get access to the try_get function. Also notice how this time we use .fetch_one to actually get a row back.

Now we can get what might be Mary from the database (make sure to derive PartialEq on Employee and Auth so you can compare them!)

1let maybe_mary = Employee::find_by_name("Mary", &pool).await.unwrap();
2
3if mary != maybe_mary {
4    panic!("Not the same person!");
5}

Of course, it doesn’t panic because they are the same!

That covers the majority of simple things you might need to do with SQLite - everything past here is just building blocks of what was covered in this article.

You can find the full code of this example INSERT LINK HERE LATER