Database abstraction layer - accessing underlying connection

help
1

As a learning exercise, I am creating a simple authentication web service with Rust. I thought it would be nice to create a DB abstraction layer, so that it could work with different DB engines (Postgres, MySQL, MSSQL, Oracle etc.) - currently based on the connection string in an env variable (i.e. at runtime).

I tried a couple of different approaches, but have ended up with something like this (I am using sqlx for Postgres, MySQL and sqlite):

pub enum DatabaseType {
    Postgres,
    MySQL,
    SQLite,
    MSSQL,
}

//
// shared traits
//
#[async_trait]
pub trait Pool<'pool> {
    fn db_type(&self) -> DatabaseType;
    async fn get_connection<'conn>(&self) -> Result<Box<dyn Connection + 'conn>, DatabaseError>
    where
        'pool: 'conn;
}

#[async_trait]
pub trait Connection<'conn> {
    fn db_type(&self) -> DatabaseType;
    async fn begin<'tx_instance>(
        &'tx_instance mut self,
    ) -> Result<Box<dyn Transaction + 'tx_instance>, DatabaseError>
    where
        'conn: 'tx_instance;
}

#[async_trait]
pub trait Transaction {
    fn db_type(&self) -> DatabaseType;
    async fn commit(self) -> Result<(), DatabaseError>;
    async fn rollback(self) -> Result<(), DatabaseError>;
    async fn create_savepoint(&mut self, name: &str) -> Result<(), DatabaseError>;
    async fn commit_savepoint(&mut self, name: &str) -> Result<(), DatabaseError>;
}

//
// postgres implementation
//
pub struct PostgresPool {
    pool: SqlxPool<SqlxPostgres>,
}

#[async_trait]
impl<'pool> Pool<'pool> for PostgresPool {
   // implementation here
}

pub struct PostgresConnection {
    conn: sqlx::pool::PoolConnection<SqlxPostgres>,
}

#[async_trait]
impl<'conn> Connection<'conn> for PostgresConnection {
   // implementation here
}

pub struct PostgresTransaction<'a> {
    transaction: SqlxTransaction<'a, SqlxPostgres>,
}

#[async_trait]
impl Transaction for PostgresTransaction<'_> {
   // implementation here
}

// the querying is done through macros - mainly to avoid re-implementing the object mapping SQLX's macros already provide (and take advantage of its compile time query checking)

#[macro_export]
macro_rules! fetch_one {
    ($tx:expr, $return_type:ty, $query:literal, $($args:tt)*) => {{
        async {
            use $crate::lib::db::db_wrapper::{DatabaseType, DatabaseError};
            let db_type = $tx.db_type();

            match db_type {
                DatabaseType::Postgres => {
                    // Forward the arguments directly to sqlx's query_as!
                    let query_result = sqlx::query_as!($return_type, $query, $($args)*)
                        .fetch_optional(&mut *$tx.transaction) // <-- compilation error here
                        .await?;

                    Ok(query_result)
                },
                // Other database types...
            }
        }
    }};
}

My intention was to create a transaction per request in middleware, and pass the resulting dyn Transaction to an AppState which axum would then pass into the request handlers:

pub struct AppState {
    pub tx: dyn Transaction,
}

// this method is called by the request handler:
pub async fn logon(
    username: String,
    password: String,
    state: &AppState,
) -> Result<User, AppError> {
    let user = fetch_one!(
        state.tx, // <-- transaction created in middleware - causes the compilation error
        LogonUserDetails,
        "
        SELECT id, uuid, email, password 
        FROM auth.app_user
        WHERE tenant_id = $1
          AND email = $2",
        1,
        username
    )
    .await?;
}

Compilation currently fails with no field 'transaction' on type '(dyn lib::db::db_wrapper::Transaction + 'static)' within the fetch_one! macro

My question is:

  • is there a solution to the problem above (i.e. getting the implementation-specific transaction from the Transaction / PostgresTransaction argument passed to the macro, in order to pass it into sqlx's query_as! macro - and similarly for other databases)
  • more generally, have I taken the best approach to this problem? (a wrapper around different database drivers in which none of the implementation-specific concepts or types leak through to the calling code - ideally reusable in other projects)
2

Instead of a field .transaction, you could try using a method .transaction() that is declared in Transaction.

3

Thanks for the suggestion. The issue is that I need to return the underlying DB driver transaction object (e.g. sqlx's Transaction struct), for it then to be used in the macro (so I can't define the return type in the main Transaction trait, as it varies per implementation).

I thought of using a generic method (e.g. get_transaction<T>(&self) -> T on my Transaction trait), but then my Transaction trait is no longer 'object-safe', and can no longer be used as the return type from my Connection::begin(&self) method (the method signature of that is async fn begin<'tx_instance>(&'tx_instance mut self) -> Result<Box<dyn Transaction + 'tx_instance>, DatabaseError>).

I was thinking that either:

  • there is a way to return the 'underlying' PostgresTransaction struct from within my fetch_one macro:
    ($tx:expr, $return_type:ty, $query:literal) => {{
        async {
            use $crate::lib::db::db_wrapper::{DatabaseType, DatabaseError};
            let db_type = $tx.db_type();
            match db_type {
                DatabaseType::Postgres => {
                    let sqlx_tx = ...; // TODO do something here to get a PostgresTransaction struct from $tx - which is typed as `Box<Transaction>`
                    let query_result = sqlx::query_as!($return_type, $query)
                        .fetch_optional(&mut *sqlx_tx.transaction)
                        .await?;

                    Ok(query_result)
                },
                // Other database types...
            }
        }
    }};

(this would be a cast in something like C#, Java or Kotlin)

  • Or, there is a better approach which would allow me to wrap my own structs / methods / macros around DB drivers' (such as sqlx, Tiberius etc.) without leaking the details to the calling code than the one I took

Suggestions welcome!

4

I think this code could be refactored to an enum with values:

pub enum DatabaseType {
    Postgres(PostgresConnection),
    MySQL(MySqlConnection),
    SQLite(SQLiteConnection),
    MSSQL(MsSqlConnection)
}

You might want something similar for Transactions.

Then, instead of querying the type and then running into the problem of needing to "cast" something, you can just match the enum:

    ($tx:expr, $return_type:ty, $query:literal) => {{
        async {
            use $crate::lib::db::db_wrapper::{DatabaseType, 
            match $tx {
                DatabaseType::PostgresTransaction(tx) => {
                    let query_result = sqlx::query_as!($return_type, $query)
                        .fetch_optional(tx)
                        .await?;

                    Ok(query_result)
                },
                // Other database types...
            }
        }
    }};
5

I suggest to take a look a diesels #[derive(MutliConnection)] for such an abstraction. It already provides a simple way to write code that works with different database backends out of the box, including oracle (via diesel-oci). It also allows you to mostly write one query for all backends instead of repeating similar queries for the different backends, while still providing the possibility to write backend specific variants by just matching on the connection enum. All of these queries are still checked at compile time and the macro also restricts the set of supported SQL to what's supported by all backends you declare in your connection type.

6

Thanks -I updated my code to use this pattern; it compiles and the tests pass.

Next step is to get it so that there is a 'transaction per request' set up in Axum middleware...

7

That's great - that is indeed exactly what I am trying to achieve. Will take a more detailed look into how they implemented this. Thanks!