Crate mas_storage_pg

An implementation of the storage traits for a PostgreSQL database

This backend uses sqlx to interact with the database. Most queries are type-checked, using introspection data recorded in the sqlx-data.json file. This file is generated by the sqlx CLI tool, and should be updated whenever the database schema changes, or new queries are added.

Implementing a new repository

When a new repository is defined in mas_storage, it should be implemented here, with the PostgreSQL backend.

A typical implementation will look like this:

/// An implementation of [`FakeDataRepository`] for a PostgreSQL connection
pub struct PgFakeDataRepository<'c> {
    conn: &'c mut PgConnection,
}

impl<'c> PgFakeDataRepository<'c> {
    /// Create a new [`FakeDataRepository`] from an active PostgreSQL connection
    pub fn new(conn: &'c mut PgConnection) -> Self {
        Self { conn }
    }
}

#[derive(sqlx::FromRow)]
struct FakeDataLookup {
    fake_data_id: Uuid,
}

impl From<FakeDataLookup> for FakeData {
    fn from(value: FakeDataLookup) -> Self {
        Self {
            id: value.fake_data_id.into(),
        }
    }
}

#[async_trait]
impl<'c> FakeDataRepository for PgFakeDataRepository<'c> {
    type Error = DatabaseError;

    #[tracing::instrument(
        name = "db.fake_data.lookup",
        skip_all,
        fields(
            db.query.text,
            fake_data.id = %id,
        ),
        err,
    )]
    async fn lookup(&mut self, id: Ulid) -> Result<Option<FakeData>, Self::Error> {
        // Note: here we would use the macro version instead, but it's not possible here in
        // this documentation example
        let res: Option<FakeDataLookup> = sqlx::query_as(
            r#"
                SELECT fake_data_id
                FROM fake_data
                WHERE fake_data_id = $1
            "#,
        )
        .bind(Uuid::from(id))
        .traced()
        .fetch_optional(&mut *self.conn)
        .await?;

        let Some(res) = res else { return Ok(None) };

        Ok(Some(res.into()))
    }

    #[tracing::instrument(
        name = "db.fake_data.add",
        skip_all,
        fields(
            db.query.text,
            fake_data.id,
        ),
        err,
    )]
    async fn add(
        &mut self,
        rng: &mut (dyn RngCore + Send),
        clock: &dyn Clock,
    ) -> Result<FakeData, Self::Error> {
        let created_at = clock.now();
        let id = Ulid::from_datetime_with_source(created_at.into(), rng);
        tracing::Span::current().record("fake_data.id", tracing::field::display(id));

        // Note: here we would use the macro version instead, but it's not possible here in
        // this documentation example
        sqlx::query(
            r#"
                INSERT INTO fake_data (id)
                VALUES ($1)
            "#,
        )
        .bind(Uuid::from(id))
        .traced()
        .execute(&mut *self.conn)
        .await?;

        Ok(FakeData {
            id,
        })
    }
}

A few things to note with the implementation:

• All methods are traced, with an explicit, somewhat consistent name.
• The SQL statement is included as attribute, by declaring a db.query.text attribute on the tracing span, and then calling ExecuteExt::traced.
• The IDs are all Ulid, and generated from the clock and the random number generated passed as parameters. The generated IDs are recorded in the span.
• The IDs are stored as Uuid in PostgreSQL, so conversions are required
• “Not found” errors are handled by returning Ok(None) instead of an error.

Modules

app_session

A module containing PostgreSQL implementation of repositories for sessions

compat

A module containing PostgreSQL implementation of repositories for the compatibility layer

oauth2

A module containing the PostgreSQL implementations of the OAuth2-related repositories

queue

A module containing the PostgreSQL implementation of the job queue

upstream_oauth2

A module containing the PostgreSQL implementation of the repositories related to the upstream OAuth 2.0 providers

user

A module containing the PostgreSQL implementation of the user-related repositories

Structs

PgRepository

An implementation of the Repository trait backed by a PostgreSQL transaction.

Enums

DatabaseError

Generic error when interacting with the database

Statics

MIGRATOR

Embedded migrations, allowing them to run on startup

Traits

ExecuteExt

An extension trait for sqlx::Execute that records the SQL statement as db.query.text in a tracing span