Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Database

The crate arminius-lib-db contains our basic interface to execute queries and manage transactions

Queries are meant to be defined in their own crates. The name of such a crate should adhere to the pattern arminius-lib-db-<table-or-partition-name>. As a rule of thumb, one such crate should be created per table. However, there may be situations where it makes sense to have one crate being responsible for multiple tables, if those are so closely related to each other that it makes no sense to use them in isolation.

Application code may only depend on arminius-lib-db / arminius-lib-db-* to interface with the database, but must not use sqlx directly.

See the module-level documentation in arminius-lib-db/src/lib.rs for more details and examples.

Query Functions

  • Functions should use the statically checked query functions from sqlx - sqlx::query!, sqlx::query_as!, sqlx::query_file!, and family

  • Function names must be declarative and indicate the type of operation. For example:

    • Select statements must start with fetch_
    • Insert statements with insert_
    • Delete statements with delete_
    • Update statements with update_
    • Conditional insert or update statements with upsert_

  • Be explicit about which columns you select. SELECT * might be tempting, but will possibly select more columns than expected and will silently change the columns returned when the schema changes.

  • Query functions must return SQL-related errors, not API errors. This enforces a clear separation between database logic and API layers.

  • Functions must not accept or return any complex Ruma-api-specific structs (e.g. Ruma Responses). Only primitive types or simple domain types that don’t implement complex functionality should be used for input.

  • Functions should only implement database operations, not entire endpoints.

  • Functions should use strong types for their parameters where possible. Wrappers around primitive types that just validate the format of the input are explicitly encouraged.

    • Example: ruma::OwnedUserId is a wrapper around String that verifies the string is a correct MXID

  • Function must not return tuples. Tuples force the caller to remember (or look up) the meaning of each position, which hurts readability and maintainability. Structs also provide better forward compatibility. If the function needs to return an additional field, this is easier to maintain with structs than with tuples.

  • The sqlx method fetch_optional is generally preferred to fetch_one.

    • Whereas fetch_one returns a Result<_, sqlx::Error> where the error may be sqlx::Error::RowNotFound, fetch_optional returns a Result<Option<_>, sqlx::Error>, where sqlx::Error::RowNotFound is ruled out. This allows us to explicitly handle the case of a missing row, and consider all other database errors unrecoverable. With fetch_one it is far too easy to write code that returns a 500 when it should return a 404

  • sqlx is not perfect at telling when a column may or may not be null. If you know a query can’t be null, prefer forcing non-null by writing SELECT column as "column!" FROM function() as your query, instead of handling it in Rust.

Tip

The sqlx::query! macro returns anonymous structs, so their type cannot be described. If you find yourself in this situation:

async fn fetch_all_users(conn: &mut PgConnection) -> Result</* WTF DO I PUT HERE */, sqlx::Error> {
 sqlx::query!("SELECT column FROM  users").fetch_all(conn).await
}

you should use the query_as! macro in cases where it is easily possible to map records into their respective representation without doing any sophisticated kind of transformation. Alternatively your function might use query! internally as long as it immediately converts the return type to a named type, but query_as! is preferred where possible.

  • query functions should be traced using the tracing::instrument macro to give us insights into the database usage. Long term we will refine the rules around that or move to automated instrumentation for those queries to limit what data is collected in those traces, but until then all query functions should generally be instrumented.

Disclaimer: This project is still under active development. If better conventions are identified in the future, this documentation will be updated.