Database Structure #4

We’ve redesigned the database layer a couple of times by now. Currently, any service can access any database, which is a situation we would like to resolve. Therefore, it’s time to refactor the database layer again.

Goals

• We want to separate our database queries from the rest of our code
• We want a solid overview of which services can write to which tables. This way, any guarantees a single service sets up about its database can be upheld.
• We want every service to read from every table
• We don’t want any SQL injections

A Short History of Our Database Layer

First Iteration - Database Everywhere

When we started needing a database, we just threw in sqlx::query! calls wherever we needed, directly in the handlers. The only rule was that the macro had to be used, to ensure that the queries were compiled offline, such that introducing SQL injection vulnerabilities would be very difficult.

Second Iteration - Database Modules

We wanted a clearer separation between the database code, and the rest of the code, and we therefore created a db module in each service crate, which would contain all the queries. This also had the goal of making queries reusable.

However, queries were not reusable across services, something that is a goal, as spelled out in our architecture documentation.

Third Iteration - Database Crates

In order to make the code easier to navigate, we split the database modules into their own crate in the crates/libs/db/ directory. This also introduced the DefaultDb abstraction, where each database crate would expose a trait with all its available queries, and implement it for the DefaultDb struct.

This also ended up letting us compile and test database crates individually, something that turned out to be quite beneficial. For instance, with complicated queries such as message ordering or sync, this has been a big boon.

The goal would be that every table, had roughly one crate for all queries relating to it. But this did not make much sense, considering that JOINs exist. The rule quickly got many exceptions, to the point where it didn’t make much sense any more.

The Next Iteration

Each service in Arminius should have a database user. For each table there exists one user, which has the privileges to update the table. All users are allowed to read from all tables. When the monolith is spawned, it gets a special monolith user, which is allowed to read from and write to all tables.

We split our database crates up into two crates per service. A write crate, which is only usable by the service which is supposed to write to its tables, and a read crate, which is allowed to read from the tables. We use sqlx.toml files to ensure that each db crate and each service uses the correct postgres account to connect to the database.

Example

The arminius-server-client owns the global_account_data table. Therefore, all queries which write to the global_account_data table are found in arminius-lib-db-client-write, and all queries which read from the table are found in arminius-lib-db-client-read. Only arminius-lib-db-client-read may be depended on by other crates than arminius-server-client.