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

This section of the documentation is all about the Arminius PostgreSQL database.

A list of tables, their columns and attributes is provided. This is auto-generated, to avoid users and developers having to use the psql command to look at how the database is set up. Alternatives considered to auto-generated schema documentation were:

  • Manually written schema descriptions
    • Manually writing a copy of the schema in markdown format is very expensive to maintain, and prone to documentation rot, which would confuse more than inform, and make any changes to the database needlessly annoying
  • Just comments in migration files
    • Depending solely on migration files makes viewing the state of the database difficult in the future, when we may have multiple migrations.

Tradeoffs between Query Complexity and Performance

CRUD operations are simpler to read and debug for most people, but consistency and performance requirements can make fewer, more complicated queries more beneficial than multiple simpler queries.

Multiple queries have additional network latency and splitting up queries makes it harder to reason about ids and relations used over multiple tables.

Rules for Database Tables

All surrogate keys should have the name nid.

Foreign keys should use the format <TABLE>_nid, where <TABLE> is the table we are referencing, unless the relationship between the tables is not an obvious “belongs to” relation. That is, events have a column room_nid, to specify which room the event belongs to, while to-device messages have a sender_nid, which references the users table.

All tables and columns should have a postgres comment, this is also checked in the CI.

For storing time, always use timestamp with time zone. This type is stored internally as UTC, which sqlx and chrono are aware of. Therefore, we can pass chrono::DateTime<chrono::Utc> directly to the database, and get it back later, which is always what we want. The timestamp without time zone in postgres is only really suitable for timestamps the application is not meant to understand. For more information, see “Don’t do this” on the PostgreSQL Wiki.

Communication with the Database

The crates/libs/db/arminius-lib-db crate is primarily responsible for providing a connection to the database. Individual queries are then in database crates, such as crates/libs/db/arminius-lib-db-keys or crates/libs/db/arminius-lib-db-user, which provide extension traits for the database type from crates/libs/db/arminius-lib-db.

The crates in crates/libs/db are the only crates which should have a direct dependency on sqlx.

Do Not Use NIDs Outside of the Database Layer

We decided on this rule so that NIDs are internal to the database layer. Therefore, types that will be returned by SQL queries should not mention nor contain any such NID column. Instead, call these queries using the Matrix ID of things (for example, query a user using its user_id, not its nid).

Crate Documentation

For the crate documentation, see arminius-lib-db/src/lib.rs