ch-database 0.2.1

Database and object storage primitives for SQLx, SpacetimeDB, and S3
Documentation
use std::future::Future;
use std::pin::Pin;
use std::time::Duration;

use anyhow::{Context, Result};
use sqlx::mysql::MySqlPoolOptions;
use sqlx::{ConnectOptions, MySql, MySqlPool, Transaction};
use thiserror::Error;
use tokio::time::sleep;
use tracing::{debug, warn};

use super::url::{MySqlUrlError, parse_mysql_url};

/// Errors returned while opening or probing a MySQL connection pool.
#[derive(Debug, Error)]
pub enum MySqlConnectionError {
    /// The connection URL is invalid.
    #[error(transparent)]
    Url(#[from] MySqlUrlError),
    /// At least one connection must be allowed.
    #[error("max_connections must be greater than zero")]
    InvalidMaxConnections,
    /// At least one connection attempt must be allowed.
    #[error("max_attempts must be greater than zero")]
    InvalidMaxAttempts,
    /// All configured connection attempts failed.
    #[error("failed to connect to the database after {attempts} attempt(s)")]
    Connect {
        /// Number of attempted connections.
        attempts: u32,
        /// Last error reported by SQLx.
        #[source]
        source: sqlx::Error,
    },
}

/// Result returned by MySQL pool lifecycle helpers.
pub type MySqlConnectionResult<T> = std::result::Result<T, MySqlConnectionError>;

/// Connection settings used when building SQLx MySQL pools.
#[derive(Debug, Clone)]
pub struct MySqlPoolSettings {
    /// Maximum number of connections maintained by the pool.
    pub max_connections: u32,
    /// Number of connection attempts before failing.
    pub max_attempts: u32,
    /// Delay between connection attempts.
    pub retry_delay: Duration,
}

impl Default for MySqlPoolSettings {
    fn default() -> Self {
        Self {
            max_connections: 5,
            max_attempts: 5,
            retry_delay: Duration::from_secs(4),
        }
    }
}

/// Creates a new SQLx pool from a validated MySQL URL with retries.
///
/// SQLx decodes URL credentials directly. For raw, separate credentials use
/// [`connect_mysql_pool_with_options`] instead of assembling a URL.
pub async fn connect_mysql_pool(
    url: &str,
    settings: &MySqlPoolSettings,
) -> MySqlConnectionResult<MySqlPool> {
    if settings.max_connections == 0 {
        return Err(MySqlConnectionError::InvalidMaxConnections);
    }
    if settings.max_attempts == 0 {
        return Err(MySqlConnectionError::InvalidMaxAttempts);
    }
    let parsed = parse_mysql_url(url)?;
    let options = sqlx::mysql::MySqlConnectOptions::from_url(&parsed).map_err(|source| {
        MySqlConnectionError::Connect {
            attempts: 0,
            source,
        }
    })?;
    connect_mysql_pool_with_options(options, settings).await
}

/// Opens a pool from validated SQLx options with the shared retry policy.
pub async fn connect_mysql_pool_with_options(
    options: sqlx::mysql::MySqlConnectOptions,
    settings: &MySqlPoolSettings,
) -> MySqlConnectionResult<MySqlPool> {
    if settings.max_connections == 0 {
        return Err(MySqlConnectionError::InvalidMaxConnections);
    }
    if settings.max_attempts == 0 {
        return Err(MySqlConnectionError::InvalidMaxAttempts);
    }
    let mut attempt = 0;
    loop {
        attempt += 1;

        match MySqlPoolOptions::new()
            .max_connections(settings.max_connections)
            .connect_with(options.clone())
            .await
        {
            Ok(pool) => return Ok(pool),
            Err(error) if attempt < settings.max_attempts => {
                warn!(
                    attempt,
                    max_attempts = settings.max_attempts,
                    retry_delay_secs = settings.retry_delay.as_secs(),
                    error = %error,
                    "failed to connect to database, retrying"
                );
                sleep(settings.retry_delay).await;
            }
            Err(error) => {
                return Err(MySqlConnectionError::Connect {
                    attempts: attempt,
                    source: error,
                });
            }
        }
    }
}

/// Runs the given async function inside a SQL transaction.
pub type TransactionFuture<'c, T> = Pin<Box<dyn Future<Output = Result<T>> + Send + 'c>>;

/// Runs the given async function inside a SQL transaction.
///
/// If the operation fails, rollback errors are logged and the original operation
/// error is returned.
pub async fn transaction<'p, T, F>(pool: &'p MySqlPool, f: F) -> Result<T>
where
    T: Send + 'static,
    F: for<'c> FnOnce(&'c mut Transaction<'p, MySql>) -> TransactionFuture<'c, T>,
{
    debug!("starting database transaction");
    let mut tx = pool.begin().await.context("failed to begin transaction")?;
    let result = f(&mut tx).await;

    match result {
        Ok(value) => {
            tx.commit().await.context("failed to commit transaction")?;
            Ok(value)
        }
        Err(error) => {
            if let Err(rollback_error) = tx.rollback().await {
                warn!(error = %rollback_error, "failed to rollback transaction");
            }
            Err(error)
        }
    }
}

#[cfg(test)]
mod tests {
    use super::{MySqlConnectionError, MySqlPoolSettings, connect_mysql_pool};

    #[test]
    fn sqlx_parses_credentials_without_a_normalization_pass() {
        use crate::mariadb::url::{normalize_mysql_url, parse_mysql_url};
        use sqlx::{ConnectOptions, mysql::MySqlConnectOptions};

        for raw in [
            "mysql://user:p@ss word@localhost/db",
            "mysql://user:päss%20%zz@localhost/db",
            "mysql://user:p%2Fa%3Fb%23c%25@localhost/db?ssl-mode=REQUIRED&charset=utf8mb4",
            "mysql://user@name:pass@localhost:3307/db",
        ] {
            let direct = MySqlConnectOptions::from_url(&parse_mysql_url(raw).unwrap()).unwrap();
            let normalized: MySqlConnectOptions =
                normalize_mysql_url(raw).unwrap().parse().unwrap();
            assert_eq!(direct.to_url_lossy(), normalized.to_url_lossy());
        }
    }

    #[tokio::test]
    async fn rejects_zero_pool_limits_before_connecting() {
        let settings = MySqlPoolSettings {
            max_connections: 0,
            ..MySqlPoolSettings::default()
        };
        assert!(matches!(
            connect_mysql_pool("mysql://localhost/example", &settings).await,
            Err(MySqlConnectionError::InvalidMaxConnections)
        ));

        let settings = MySqlPoolSettings {
            max_connections: 1,
            max_attempts: 0,
            ..MySqlPoolSettings::default()
        };
        assert!(matches!(
            connect_mysql_pool("mysql://localhost/example", &settings).await,
            Err(MySqlConnectionError::InvalidMaxAttempts)
        ));
    }
}