sqlx-ext 0.1.0

Poll-scoped implicit SQLx transactions
Documentation
use std::{
    any::{Any, TypeId},
    cell::RefCell,
    collections::HashMap,
    fmt::Display,
    future::Future,
    pin::Pin,
    str::FromStr,
    sync::{Arc, Mutex as StdMutex, OnceLock},
    task::{Context, Poll},
};

use sqlx::{
    Connection, Database as SqlxDatabase, Pool, Transaction as SqlxNativeTransaction,
    pool::PoolOptions,
};
use tokio::sync::Mutex;

type GlobalPools = HashMap<TypeId, Box<dyn Any + Send + Sync>>;
static GLOBAL_POOLS: OnceLock<StdMutex<GlobalPools>> = OnceLock::new();
thread_local! { static DB_OVERRIDE: RefCell<Option<Box<dyn Any>>> = const { RefCell::new(None) }; }

#[derive(Debug, thiserror::Error)]
pub enum DbError {
    #[error("database pool is not initialized for this SQLx backend")]
    PoolNotInitialized,
    #[error("global database pool is already initialized for this SQLx backend")]
    GlobalPoolAlreadyInitialized,
    #[error(
        "transaction handle leaked outside its scope; do not retain Transaction or captured scope handles"
    )]
    TransactionLeaked,
    #[error("transaction is already closed")]
    TransactionClosed,
    #[error("invalid database connection options: {0}")]
    ConnectionOptions(String),
    #[error("the current scope belongs to a different SQLx database backend")]
    BackendScopeMismatch,
    #[error(transparent)]
    Sqlx(#[from] sqlx::Error),
}

pub enum DatabaseHandle<DB: SqlxDatabase> {
    Pool(Arc<Pool<DB>>),
    Transaction(Arc<SqlxTransaction<DB>>),
}
pub enum ConnectionRef<DB: SqlxDatabase> {
    Pool(Arc<Pool<DB>>),
    Transaction(Arc<SqlxTransaction<DB>>),
}

pub struct Database<DB: SqlxDatabase> {
    pool: Arc<Pool<DB>>,
}

impl<DB> Database<DB>
where
    DB: SqlxDatabase + 'static,
    Pool<DB>: Send + Sync,
    <DB::Connection as Connection>::Options: FromStr,
    <<DB::Connection as Connection>::Options as FromStr>::Err: Display,
{
    pub async fn connect(url: &str) -> Result<Self, DbError> {
        Ok(Self {
            pool: Arc::new(
                PoolOptions::new()
                    .connect_with(
                        url.parse::<<DB::Connection as Connection>::Options>()
                            .map_err(|e| DbError::ConnectionOptions(e.to_string()))?,
                    )
                    .await?,
            ),
        })
    }
    pub async fn init(url: &str) -> Result<(), DbError> {
        Self::set_global(Self::connect(url).await?)
    }
    pub fn from_pool(pool: Pool<DB>) -> Self {
        Self {
            pool: Arc::new(pool),
        }
    }
    pub fn set_global(database: Self) -> Result<(), DbError> {
        let pools = GLOBAL_POOLS.get_or_init(|| StdMutex::new(HashMap::new()));
        let mut pools = pools.lock().expect("global pool lock poisoned");
        if pools.contains_key(&TypeId::of::<DB>()) {
            return Err(DbError::GlobalPoolAlreadyInitialized);
        }
        pools.insert(TypeId::of::<DB>(), Box::new(database.pool));
        Ok(())
    }
    /// Removes this backend's global pool. Intended for tests and deliberate
    /// reconfiguration; existing cloned `Database` handles remain usable.
    pub fn reset_global() {
        if let Some(pools) = GLOBAL_POOLS.get() {
            if let Ok(mut pools) = pools.lock() {
                pools.remove(&TypeId::of::<DB>());
            }
        }
    }
    pub fn global() -> Result<Self, DbError> {
        Ok(Self {
            pool: global_pool::<DB>()?,
        })
    }
    pub fn pool(&self) -> &Pool<DB> {
        self.pool.as_ref()
    }
    /// Runs a transaction using the pool installed by [`Self::init`] or
    /// [`Self::set_global`]. This is the application-service entry point when
    /// services and repositories must not carry a `Database` field.
    pub async fn transaction_global<T, F>(f: F) -> Result<T, DbError>
    where
        F: FnOnce() -> Pin<Box<dyn Future<Output = Result<T, DbError>> + Send>> + Send,
        T: Send,
    {
        Self::global()?.transaction(|_| f()).await
    }
    pub async fn transaction<T, F>(&self, f: F) -> Result<T, DbError>
    where
        F: for<'a> FnOnce(
                &'a Transaction<DB>,
            )
                -> Pin<Box<dyn Future<Output = Result<T, DbError>> + Send + 'a>>
            + Send,
        T: Send,
    {
        if let Some(tx) = current_transaction_override::<DB>()? {
            return f(&Transaction { inner: tx }).await;
        }
        let shared = Arc::new(SqlxTransaction::new(self.pool.begin().await?));
        let outcome = {
            let public = Transaction {
                inner: Arc::clone(&shared),
            };
            with_connection_override(DatabaseHandle::Transaction(Arc::clone(&shared)), f(&public))
                .await
        };
        let inner = Arc::try_unwrap(shared).map_err(|_| DbError::TransactionLeaked)?;
        let tx = inner.take()?;
        match outcome {
            Ok(value) => {
                tx.commit().await?;
                Ok(value)
            }
            Err(error) => {
                let _ = tx.rollback().await;
                Err(error)
            }
        }
    }
}

fn global_pool<DB: SqlxDatabase + 'static>() -> Result<Arc<Pool<DB>>, DbError> {
    GLOBAL_POOLS
        .get()
        .and_then(|pools| {
            pools
                .lock()
                .ok()?
                .get(&TypeId::of::<DB>())
                .and_then(|pool| pool.downcast_ref::<Arc<Pool<DB>>>().cloned())
        })
        .ok_or(DbError::PoolNotInitialized)
}
pub fn current_connection<DB: SqlxDatabase + 'static>() -> Result<ConnectionRef<DB>, DbError> {
    if let Some(handle) = current_override_handle::<DB>()? {
        return Ok(match handle {
            DatabaseHandle::Pool(pool) => ConnectionRef::Pool(pool),
            DatabaseHandle::Transaction(tx) => ConnectionRef::Transaction(tx),
        });
    }
    Ok(ConnectionRef::Pool(global_pool::<DB>()?))
}
fn current_override_handle<DB: SqlxDatabase + 'static>()
-> Result<Option<DatabaseHandle<DB>>, DbError> {
    DB_OVERRIDE.with(|slot| {
        let slot = slot.borrow();
        match slot.as_ref() {
            None => Ok(None),
            Some(handle) => handle
                .downcast_ref::<DatabaseHandle<DB>>()
                .cloned()
                .map(Some)
                .ok_or(DbError::BackendScopeMismatch),
        }
    })
}
fn current_transaction_override<DB: SqlxDatabase + 'static>()
-> Result<Option<Arc<SqlxTransaction<DB>>>, DbError> {
    Ok(match current_override_handle::<DB>()? {
        Some(DatabaseHandle::Transaction(transaction)) => Some(transaction),
        Some(DatabaseHandle::Pool(_)) | None => None,
    })
}
struct ResetDbOverride(Option<Box<dyn Any>>);
impl Drop for ResetDbOverride {
    fn drop(&mut self) {
        DB_OVERRIDE.with(|slot| {
            slot.replace(self.0.take());
        });
    }
}
fn install_db_override<DB: SqlxDatabase + 'static>(handle: &DatabaseHandle<DB>) -> ResetDbOverride {
    ResetDbOverride(DB_OVERRIDE.with(|slot| slot.replace(Some(Box::new(clone_handle(handle))))))
}
pub struct ScopedOverrideFuture<DB: SqlxDatabase, F> {
    handle: Option<DatabaseHandle<DB>>,
    future: Pin<Box<F>>,
}
impl<DB: SqlxDatabase + 'static, F: Future> Future for ScopedOverrideFuture<DB, F> {
    type Output = F::Output;
    fn poll(self: Pin<&mut Self>, cx: &mut Context<'_>) -> Poll<Self::Output> {
        let this = self.get_mut();
        let result = if let Some(handle) = this.handle.as_ref() {
            let _reset = install_db_override(handle);
            this.future.as_mut().poll(cx)
        } else {
            this.future.as_mut().poll(cx)
        };
        if result.is_ready() {
            this.handle = None;
        }
        result
    }
}
pub fn with_connection_override<DB: SqlxDatabase + 'static, F: Future>(
    handle: DatabaseHandle<DB>,
    future: F,
) -> ScopedOverrideFuture<DB, F> {
    ScopedOverrideFuture {
        handle: Some(handle),
        future: Box::pin(future),
    }
}

fn clone_handle<DB: SqlxDatabase>(handle: &DatabaseHandle<DB>) -> DatabaseHandle<DB> {
    match handle {
        DatabaseHandle::Pool(pool) => DatabaseHandle::Pool(Arc::clone(pool)),
        DatabaseHandle::Transaction(tx) => DatabaseHandle::Transaction(Arc::clone(tx)),
    }
}
impl<DB: SqlxDatabase> Clone for DatabaseHandle<DB> {
    fn clone(&self) -> Self {
        clone_handle(self)
    }
}
impl<DB: SqlxDatabase> Clone for ConnectionRef<DB> {
    fn clone(&self) -> Self {
        match self {
            Self::Pool(pool) => Self::Pool(Arc::clone(pool)),
            Self::Transaction(tx) => Self::Transaction(Arc::clone(tx)),
        }
    }
}
impl<DB: SqlxDatabase> Clone for Database<DB> {
    fn clone(&self) -> Self {
        Self {
            pool: Arc::clone(&self.pool),
        }
    }
}

pub struct SqlxTransaction<DB: SqlxDatabase> {
    inner: Mutex<Option<SqlxNativeTransaction<'static, DB>>>,
}
impl<DB: SqlxDatabase> SqlxTransaction<DB> {
    fn new(tx: SqlxNativeTransaction<'static, DB>) -> Self {
        Self {
            inner: Mutex::new(Some(tx)),
        }
    }
    pub(crate) async fn lock(
        &self,
    ) -> Result<tokio::sync::MutexGuard<'_, Option<SqlxNativeTransaction<'static, DB>>>, DbError>
    {
        let guard = self.inner.lock().await;
        if guard.is_none() {
            return Err(DbError::TransactionClosed);
        }
        Ok(guard)
    }
    fn take(self) -> Result<SqlxNativeTransaction<'static, DB>, DbError> {
        self.inner.into_inner().ok_or(DbError::TransactionClosed)
    }
}
pub struct Transaction<DB: SqlxDatabase> {
    inner: Arc<SqlxTransaction<DB>>,
}
impl<DB: SqlxDatabase> Transaction<DB> {
    pub fn is_same(&self, other: &Self) -> bool {
        Arc::ptr_eq(&self.inner, &other.inner)
    }
}
impl<DB: SqlxDatabase> Clone for Transaction<DB> {
    fn clone(&self) -> Self {
        Self {
            inner: Arc::clone(&self.inner),
        }
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use futures::task::noop_waker;
    use sqlx::{Sqlite, sqlite::SqlitePoolOptions};
    use std::sync::{Arc, Mutex};

    struct TwoPoll(Arc<Mutex<usize>>);
    impl Future for TwoPoll {
        type Output = ();
        fn poll(self: Pin<&mut Self>, _: &mut Context<'_>) -> Poll<()> {
            assert!(matches!(
                current_connection::<Sqlite>(),
                Ok(ConnectionRef::Pool(_))
            ));
            let mut polls = self.0.lock().unwrap();
            *polls += 1;
            if *polls == 1 {
                Poll::Pending
            } else {
                Poll::Ready(())
            }
        }
    }

    #[tokio::test]
    async fn override_survives_cross_thread_polls_and_is_restored() {
        let pool = Arc::new(
            SqlitePoolOptions::new()
                .connect_lazy("sqlite::memory:")
                .unwrap(),
        );
        let polls = Arc::new(Mutex::new(0));
        let future = Box::pin(with_connection_override(
            DatabaseHandle::Pool(pool),
            TwoPoll(Arc::clone(&polls)),
        ));
        let future = std::thread::spawn(move || {
            let waker = noop_waker();
            let mut context = Context::from_waker(&waker);
            let mut future = future;
            assert!(future.as_mut().poll(&mut context).is_pending());
            assert!(matches!(
                current_connection::<Sqlite>(),
                Err(DbError::PoolNotInitialized)
            ));
            future
        })
        .join()
        .unwrap();
        std::thread::spawn(move || {
            let waker = noop_waker();
            let mut context = Context::from_waker(&waker);
            let mut future = future;
            assert!(future.as_mut().poll(&mut context).is_ready());
            assert!(matches!(
                current_connection::<Sqlite>(),
                Err(DbError::PoolNotInitialized)
            ));
        })
        .join()
        .unwrap();
        assert_eq!(*polls.lock().unwrap(), 2);
    }

    #[tokio::test]
    async fn global_pool_can_be_reset() {
        Database::<Sqlite>::reset_global();
        let pool = SqlitePoolOptions::new()
            .connect_lazy("sqlite::memory:")
            .unwrap();
        Database::<Sqlite>::set_global(Database::from_pool(pool)).unwrap();
        assert!(Database::<Sqlite>::global().is_ok());
        Database::<Sqlite>::reset_global();
        assert!(matches!(
            Database::<Sqlite>::global(),
            Err(DbError::PoolNotInitialized)
        ));
    }
}