SQL Database

For this component, you need the DistributionCenter trait. If you don't have it yet, do the Storage Backend guide first and then come back here.

SQL databases are ubiquitous and the ability to use them is a marketable skill. You don't need to know SQL to follow this guide, but the provided code snippets won't be explained either. At the start of a new project, we have to ask ourselves which database to pick, like PostgreSQL, MySQL etc. We also have to decide if and what library we are going to use to make queries from our general-purpose programming language.

If we wanted to pull out the big guns, we could go with PostgreSQL and a full-blown ORM like diesel. But for our purposes, we'll travel a little lighter with SQLite as our database and sqlx as our query library.

Connecting to the database

In paekli-core, create a new storage backend like you've probably done before:

#![allow(unused)]
fn main() {
struct SqlDatabase;

impl DistributionCenter for SqlDatabase {
    // ...
}
}

sqlx is database agnostic and provides compile-time checked queries without abstracting the raw power of SQL away from you. It is also fundamentally async, a language feature we did not discuss. Luckily, async is not necessary to understand the rest of what's going on, so it won't be explained here either.

Let's add the dependency with the feature flags sqlite and runtime-tokio, which is necessary to run async code. We'll need to use the runtime tokio directly as well, let's add it with the full feature set.

cargo add sqlx --features sqlite,runtime-tokio
cargo add tokio --features full

In contrast to the file system storage or the HTTP client, the SQL database needs some initialization code.

#![allow(unused)]
fn main() {
impl SqlDatabase {
    fn new() -> Self {
        // ...
    }
}
}

To determine the location to store our database, we'll use the directories crate. You've probably already done this for the CLI.

#![allow(unused)]
fn main() {
let project_dir = directories::ProjectDirs::from("dev", "buenzli", "paekli")
    .expect("the user's home directory seems to be corrupt");

let storage_dir = project_dir.data_dir();

std::fs::create_dir_all(storage_dir).expect("failed to create storage directory");

let db_path = storage_dir.join("db.sqlite");
if !db_path.exists() {
    std::fs::File::create(&db_path).expect("failed to create database");
}
let db_url = format!("sqlite:{}", db_path.display());
}

Next, we need to create a database connection pool. The way to do that with sqlx is an async task, so we need a tokio runtime to execute it. We'll also need the runtime to execute queries later, so we'll store it in a variable rt.

#![allow(unused)]
fn main() {
let rt = tokio::runtime::Runtime::new().unwrap();
let pool_task = SqlitePool::connect(&db_url);
let pool = rt.block_on(pool_task).unwrap();
}

Initial migrations

Now that we have an open database connection, we need to create the schema.

sqlx has a built-in feature for migrations. It allows you to store them as scripts in some directory and automatically execute all of them. However, since we just need a single table, we'll keep it simple and use a regular query.

#![allow(unused)]
fn main() {
let create_table_task = sqlx::query(
    "
    CREATE TABLE IF NOT EXISTS paekli (
        content TEXT
    )
    ",
)
.execute(&pool);
rt.block_on(create_table_task).unwrap();
}

Storing paekli

We can finally start implementing the functionality of DistributionCenter. Here's the query to insert a paekli into the database. This async task needs to be executed on the tokio runtime with .block_on().

#![allow(unused)]
fn main() {
sqlx::query("INSERT INTO paekli VALUES (?)")
    .bind(content)
    .execute(&pool)
}

Retrieving paekli

A reasonable approach for retrieving paekli is a query like the following. (rowid is automatically added to every table by SQLite.)

#![allow(unused)]
fn main() {
let select_task = sqlx::query(
    "
    SELECT rowid, content FROM paekli
    LIMIT 1
    ",
)
.fetch_one(&pool)
}

This would work, but the returned values would be an SQL row, not the most convenient format. Ideally, we want the result to be filled directly into a nice Rust type. We can do that with query_as and a derived FromRow implementation on our own type:

#![allow(unused)]
fn main() {
#[derive(sqlx::FromRow)]
struct PaekliRow {
    rowid: i64,
    content: String,
}

let select_task = sqlx::query_as().fetch_one(&pool);

let PaekliRow { rowid, content } = rt.block_on(select_task).unwrap();
}

Instead of calling .unwrap(), we should handle the case where no paekli exist.

Lastly, execute another query to delete the retrieved paekli from the database. The SQL query to delete a row with a specific ID is: DELETE FROM paekli WHERE rowid = ?.