advotracker - database handling

advotracker make use of [Diesel]¹ to handle all SQL access functionality. Diesel requires to be compiled with Rust version 1.24 or later.

The actual version is tested with the sqlite3 backend. All following documentation will reference to this constellation.

Diesel

First of all, add the required dependencies to the projects Cargo.toml

[dependencies]
diesel = { version = "1.4.4", features = ["sqlite" "chrono"] }
dotenv = "0.15.0"

CLI helper

Diesel provides a separate CLI tool to help manage the project. This CLI should be installed on your system. I you don't have that already available on you system, go and install it like this:

cargo install diesel_cli
cargo install diesel_cli --no-default-features --features sqlite3

Database Setup

We do uses sqlite for now. Use your distro package for sqlite3.

Now direct diesel to the correct database instance. We can create an environment variable DATABASE_URL, that will point to the instance. For the test setup, we take advantage of an .env file:

DATABASE_URL=sqlite://localhost/advotracker
#DATABASE_URL=postgres://username:password@localhost/advotracker

• Instantiate a new database

In the beginning, there is .... nothing! We will create an empty database to get started:

$ sqlite3 <absolute_path_to>/advotracker.sqlite3>
sqlite3 advotracker.sqlite3

• Test availability

Sqlite will drop us in the CLI shell. Try to get available databases, which should response a main pointing to the absolute path of the created database.

SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> .databases
main: /<the_absolute_path>/advotracker.sqlite3
.quit

All fine.

Diesel Setup

When managing the database schema, probably it will evolve over the time. Diesel takes that into account and supports a bundles called Migrations. They allow us to define states, that can be applied (up.sql) or reverted (down.sql). Applying and immediately reverting a migration is a good test, to make sure you that the used migration leaves your database schema in the requested state.

• Instantiate a migration

The following command will crate the basic advotracker structure.

diesel setup
diesel migration generate advotracker

You can review the generated structure in the subfolder migrations. It holds a timestamp based subfolder, named <timestamp-advotracker, that provides to empty files. You'll see output that looks something like this:

Creating migrations/20200625133000_advotracker/up.sql
Creating migrations/20200625133000_advotracker/down.sql

• Update the SQL-files

Both files have to be updated to meet our target structure. up.sql will create tables, beside their constraints, indices and views. down.sql work delete the database schema providing a virgin state. If you leave this scripts empty, diesel can't generate the target schema file, that is used to provide the API consumed by you rust code. You are free to edit and apply this in a manual fashion, but this is not recommended.

• Migration run

We can apply the new migration like this:

diesel migration run

Using the the redo command will prove the expected functionality: Run down and up scrips in a sequence. This will drop the given schema and create a new one using the adopted files in the migration path.

diesel migration run

• Test our generated structure

As a basic success test, we the CLI interface should respond with a list of indices, if we call a query. Go ahead an run:

$ sqlite3 <abolute_path_to>/advotracker.sqlite3>
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> .index
claims_ix_id_user
harms_ix_number_harm
sqlite_autoindex___diesel_schema_migrations_1
sqlite_autoindex_roles_1
sqlite_autoindex_user_roles_1
user_roles_ix_id_role
user_roles_ix_id_user
users_ix_email
.quit

You are done. The database is ready to be accessed for the advotracker rust code.

Reference

Any further information can be obtained from [Diesel's project page]¹ .

---

This work is licensed under a Creative Common License 4.0

© 2020 Ralf Zerres, Networkx GmbH

---

Footnotes

---

1. Diesel Guides: https://diesel.rs/guides/getting-started/ ↩︎