This directory contains functionality relating to Nexus' database implementation. This implementation makes use of Diesel, a rust query builder.
Why use Diesel?
Diesel provides a strongly-typed, extensible framework for creating queries. Instead of parsing SQL as raw strings, this provides additional layers of validation through the type system, ensuring:
Operations only are issued to tables with corresponding columns.
Complex queries can be chained together and validated at compile-time.
Code generation avoids boilerplate, by autogenerating the translation of structs to/from SQL.
Escape hatches exist for writing raw SQL and CTEs.
How do I use Diesel?
Although there are high-quality guides for Diesel, the following will be a brief overview of features as they appear within Omicron.
Tables
Diesel auto-generates types which allow callers to construct SQL expressions. These types, however, must map to the underlying database to be useful.
We make use of the table macro
within schema.rs to define these tables and their
corresponding SQL types. These types are then accessible within the schema
module.
Creating Queries
For performing queries on a database - insertions, queries, updates, and deletions - Diesel can operate on explicit columns or a structure which maps to a group of columns.
Many of these structures - representing the data model - are stored within nexus-db-model, where they also have helper conversions from/into types which are used outside the database (such as in the HTTP API).
Diesel provides some derive macros worth knowing about:
Insertable indicates that a structure may be inserted into a table.
#[derive(Insertable)]
#[diesel(table_name = instance)]
struct Instance {
...
}
use nexus_db_schema::schema::::instance::dsl;
let insert_query = diesel::insert_into(dsl::instance)
.values(Instance::new());Queryable indicates that a structure may be queried from a table.
#[derive(Queryable)]
struct Instance {
...
}
use nexus_db_schema::schema::instance::dsl;
let lookup_query = dsl::instance.filter(dsl::id.eq(id_to_lookup));AsChangeset indicates that a structure can be used to update a table.
#[derive(AsChangeset)]
struct InstanceUpdate {
...
}
use nexus_db_schema::schema::instance::dsl;
let update_query = diesel::update(dsl::instance).set(InstanceUpdate::new());Issuing Queries
To issue these queries to the actual database, we make use of async-bb8-diesel, which provides an async wrapper above a threaded connection pool.
In the prior section, we showed how to construct queries, but never actually issued the database.
Assuming we have access to a database pool object, queries take the following form:
let pool = ...; // See: pool.rs
let query = ...; // See: prior section on constructing queries.
query.execute_async(&pool).awaitAdditional examples of issuing queries can be found within the
examples section
of the async-bb8-diesel repository.
async-bb8-diesel crate provides an adapter trait
called AsyncRunQueryDsl which provides the same functionality, but asynchronously
dispatching work to a thread pool. The goal of this trait it to fairly aligned
with the Diesel API for issuing requests, but (1) async, and (2) operating
on a thread pool.Transactions
Transactions in Diesel are issued as closures. Diesel automatically commits or rolls back the operation, depending on the return value of the contained closure.
Using the async adapter, diesel transactions may be issued as follows:
// Note that the transaction is async, but the operations
// within the transaction are not. This is okay - they'll be
// moved to a thread where blocking is acceptible before
// being executed.
let result = pool.transaction(|conn| {
diesel::insert_into(...)
.values(...)
.execute(conn)?;
diesel::update(...)
.set(...)
.execute(conn)?;
Ok(result)
}).await?;Helper Functions
As with any style of programming, when operations are repeated, it can be useful to refactor them. As one example, we make use of pagination while accessing the database.
As many of the Diesel structures are strongly typed - tables, columns, etc - the magic sauce for making helper functions work correctly is generics. This typically meanings using trait bounds indicating which methods should be accessible, and then performing regular Diesel operations.
CTEs
Diesel support for CTEs is still underway, though the entrypoints for injecting raw SQL are either: - sql_query: An entrypoint for a full SQL query, if you want mostly dodge Diesel’s query builder, or… - Extending the DSL, which may involve a custom implementation of QueryFragment.
An example CTE exists within Omicron, which extends the Diesel DSL to issue a "select" and "update" query simultaneously, performing a conditional update that allows callers to distinguish between "object found and updated", "object found and NOT updated", and "object not found".
This is just one example of a CTE which extends diesel, but it fits in with the typical Diesel pattern of "fully construct a query, then execute it".
let updated = diesel::update(dsl::instance)
.filter(dsl::time_deleted.is_null())
.filter(dsl::id.eq(instance_id))
.set(new_runtime)
// New query method: blanket implemented as an extension for update queries.
.check_if_exists::<db::model::Instance>(instance_id)
// New execution method: only callable after "check_if_exists".
.execute_and_check(pool)
.await?;Areas for Improvement
Selectable for embedded structures: Many of our database model structures - which map to a SQL table - are "flattened" for usage in diesel. We could potentially avoid this with "Selectable".
Native Async Support: The
async-bb8-dieselrespository provides a mechanism for offloading Diesel requests to a Tokio-controlled synchronous thread pool, but ideally we’d use a native Diesel API that never requires blocking threads. This improvement would require contribution to upstream Diesel.