use std::hash::{BuildHasher, RandomState};
use std::str::FromStr;
use std::sync::Arc;
use std::sync::atomic::{AtomicBool, AtomicUsize, Ordering};
use std::time::Duration;
use futures_core::future::BoxFuture;
use futures_core::stream::BoxStream;
use ruprizzle_core::ir::Provider;
use sqlx::any::AnyPoolOptions;
use sqlx::mysql::MySqlPoolOptions;
use sqlx::sqlite::SqlitePoolOptions;
#[derive(Clone, Debug)]
pub enum Pool {
Any(sqlx::Pool<sqlx::Any>),
Postgres(sqlx::Pool<sqlx::Postgres>),
Sqlite(sqlx::Pool<sqlx::Sqlite>),
Mysql(sqlx::Pool<sqlx::MySql>),
#[cfg(feature = "sqlite-rusqlite")]
SqliteNative(crate::rusqlite::RusqlitePool),
#[cfg(feature = "postgres-tokio-postgres")]
PostgresNative(crate::tokio_postgres::TokioPostgresPool),
}
impl Pool {
#[must_use]
pub fn provider(&self) -> Provider {
match self {
Pool::Any(any) => {
let opts = any.connect_options();
let scheme = opts.database_url.scheme();
Provider::parse(scheme).unwrap_or(Provider::Postgres)
}
Pool::Postgres(_) => Provider::Postgres,
#[cfg(feature = "postgres-tokio-postgres")]
Pool::PostgresNative(_) => Provider::Postgres,
Pool::Sqlite(_) => Provider::Sqlite,
Pool::Mysql(_) => Provider::Mysql,
#[cfg(feature = "sqlite-rusqlite")]
Pool::SqliteNative(_) => Provider::Sqlite,
}
}
pub async fn begin(&self) -> Result<crate::tx::Tx, crate::Error> {
crate::tx::Tx::begin(self).await
}
#[must_use]
pub fn size(&self) -> u32 {
match self {
Pool::Any(p) => p.size(),
Pool::Postgres(p) => p.size(),
Pool::Sqlite(p) => p.size(),
Pool::Mysql(p) => p.size(),
#[cfg(feature = "sqlite-rusqlite")]
Pool::SqliteNative(p) => p.num_total() as u32,
#[cfg(feature = "postgres-tokio-postgres")]
Pool::PostgresNative(p) => p.size(),
}
}
#[must_use]
pub fn num_idle(&self) -> usize {
match self {
Pool::Any(p) => p.num_idle(),
Pool::Postgres(p) => p.num_idle(),
Pool::Sqlite(p) => p.num_idle(),
Pool::Mysql(p) => p.num_idle(),
#[cfg(feature = "sqlite-rusqlite")]
Pool::SqliteNative(p) => p.num_idle(),
#[cfg(feature = "postgres-tokio-postgres")]
Pool::PostgresNative(p) => p.num_idle(),
}
}
#[must_use]
pub fn num_waiters(&self) -> usize {
match self {
#[cfg(feature = "postgres-tokio-postgres")]
Pool::PostgresNative(p) => p.num_waiters(),
#[cfg(feature = "sqlite-rusqlite")]
Pool::SqliteNative(p) => p.num_waiters(),
_ => 0,
}
}
#[must_use]
pub fn options(&self) -> Option<&sqlx::pool::PoolOptions<sqlx::Any>> {
match self {
Pool::Any(any) => Some(any.options()),
_ => None,
}
}
#[must_use]
pub fn postgres_options(&self) -> Option<&sqlx::pool::PoolOptions<sqlx::Postgres>> {
match self {
Pool::Postgres(p) => Some(p.options()),
_ => None,
}
}
#[must_use]
pub fn sqlite_options(&self) -> Option<&sqlx::pool::PoolOptions<sqlx::Sqlite>> {
match self {
Pool::Sqlite(p) => Some(p.options()),
_ => None,
}
}
pub async fn acquire(&self) -> Result<sqlx::pool::PoolConnection<sqlx::Any>, crate::Error> {
match self {
Pool::Any(any) => any.acquire().await.map_err(crate::Error::Sqlx),
_ => Err(crate::Error::NotImplemented),
}
}
#[must_use]
pub fn as_any(&self) -> Option<&sqlx::Pool<sqlx::Any>> {
match self {
Pool::Any(any) => Some(any),
_ => None,
}
}
#[must_use]
pub fn as_postgres(&self) -> Option<&sqlx::Pool<sqlx::Postgres>> {
match self {
Pool::Postgres(p) => Some(p),
_ => None,
}
}
#[must_use]
pub fn as_sqlite(&self) -> Option<&sqlx::Pool<sqlx::Sqlite>> {
match self {
Pool::Sqlite(p) => Some(p),
_ => None,
}
}
#[must_use]
pub fn as_mysql(&self) -> Option<&sqlx::Pool<sqlx::MySql>> {
match self {
Pool::Mysql(p) => Some(p),
_ => None,
}
}
#[cfg(feature = "sqlite-rusqlite")]
#[must_use]
pub fn as_rusqlite(&self) -> Option<&crate::rusqlite::RusqlitePool> {
match self {
Pool::SqliteNative(p) => Some(p),
_ => None,
}
}
#[cfg(feature = "postgres-tokio-postgres")]
#[must_use]
pub fn as_tokio_postgres(&self) -> Option<&crate::tokio_postgres::TokioPostgresPool> {
match self {
Pool::PostgresNative(p) => Some(p),
_ => None,
}
}
pub async fn close(&self) {
tracing::info!(target: "ruprizzle::connection", event = "disconnect", "pool closing");
match self {
Pool::Any(p) => p.close().await,
Pool::Postgres(p) => p.close().await,
Pool::Sqlite(p) => p.close().await,
Pool::Mysql(p) => p.close().await,
#[cfg(feature = "sqlite-rusqlite")]
Pool::SqliteNative(_) => (),
#[cfg(feature = "postgres-tokio-postgres")]
Pool::PostgresNative(p) => p.close().await,
}
}
}
impl<'c> sqlx::Executor<'c> for &'c Pool {
type Database = sqlx::Any;
fn fetch_many<'e, 'q: 'e, E>(
self,
query: E,
) -> BoxStream<
'e,
Result<
sqlx::Either<
<Self::Database as sqlx::Database>::QueryResult,
<Self::Database as sqlx::Database>::Row,
>,
sqlx::Error,
>,
>
where
'c: 'e,
E: 'q + sqlx::Execute<'q, Self::Database>,
{
match self {
Pool::Any(any) => sqlx::Executor::fetch_many(any, query),
_ => Box::pin(futures_util::stream::iter(std::iter::once(Err(
sqlx::Error::Protocol(not_any_message().into()),
)))),
}
}
fn fetch_optional<'e, 'q: 'e, E>(
self,
query: E,
) -> BoxFuture<'e, Result<Option<<Self::Database as sqlx::Database>::Row>, sqlx::Error>>
where
'c: 'e,
E: 'q + sqlx::Execute<'q, Self::Database>,
{
match self {
Pool::Any(any) => sqlx::Executor::fetch_optional(any, query),
_ => Box::pin(futures_util::future::ready(Err(sqlx::Error::Protocol(
not_any_message().into(),
)))),
}
}
fn prepare_with<'e, 'q: 'e>(
self,
sql: &'q str,
parameters: &'e [<Self::Database as sqlx::Database>::TypeInfo],
) -> BoxFuture<'e, Result<<Self::Database as sqlx::Database>::Statement<'q>, sqlx::Error>>
where
'c: 'e,
{
match self {
Pool::Any(any) => sqlx::Executor::prepare_with(any, sql, parameters),
_ => Box::pin(futures_util::future::ready(Err(sqlx::Error::Protocol(
not_any_message().into(),
)))),
}
}
fn describe<'e, 'q: 'e>(
self,
sql: &'q str,
) -> BoxFuture<'e, Result<sqlx::Describe<Self::Database>, sqlx::Error>>
where
'c: 'e,
{
match self {
Pool::Any(any) => sqlx::Executor::describe(any, sql),
_ => Box::pin(futures_util::future::ready(Err(sqlx::Error::Protocol(
not_any_message().into(),
)))),
}
}
}
fn not_any_message() -> &'static str {
"this Pool variant does not support generic sqlx::Any queries; use the typed as_* accessors"
}
#[derive(Debug, Clone, PartialEq, Eq)]
#[non_exhaustive]
pub struct PoolConfig {
pub max_connections: u32,
pub min_connections: u32,
pub acquire_timeout: Duration,
pub idle_timeout: Option<Duration>,
pub max_lifetime: Option<Duration>,
pub test_before_acquire: bool,
pub reset_on_recycle: bool,
pub row_buffer_size: u32,
pub full_table_include_limit: Option<u64>,
pub slow_query_threshold: Option<Duration>,
}
impl Default for PoolConfig {
fn default() -> Self {
Self {
max_connections: 10,
min_connections: 0,
acquire_timeout: Duration::from_secs(30),
idle_timeout: Some(Duration::from_secs(600)),
max_lifetime: Some(Duration::from_secs(1800)),
test_before_acquire: false,
reset_on_recycle: false,
row_buffer_size: 1024,
full_table_include_limit: Some(100_000),
slow_query_threshold: None,
}
}
}
pub async fn connect(url: &str) -> Result<Pool, crate::Error> {
connect_with(url, &PoolConfig::default()).await
}
pub async fn connect_with(url: &str, config: &PoolConfig) -> Result<Pool, crate::Error> {
crate::executor::set_full_table_include_limit(config.full_table_include_limit);
crate::executor::set_slow_query_threshold(config.slow_query_threshold);
sqlx::any::install_default_drivers();
let scheme = url.split(':').next().unwrap_or("");
tracing::info!(
target: "ruprizzle::connection",
event = "connect",
scheme,
"connecting to database"
);
match scheme {
"postgres" | "postgresql" => {
#[cfg(feature = "postgres-tokio-postgres")]
if url
.split_once('?')
.is_some_and(|(_, q)| q.contains("driver=tokio-postgres"))
{
let pool = crate::tokio_postgres::TokioPostgresPool::connect(url, config).await?;
return Ok(Pool::PostgresNative(pool));
}
let pool = sqlx::postgres::PgPoolOptions::new()
.max_connections(config.max_connections)
.min_connections(config.min_connections)
.acquire_timeout(config.acquire_timeout)
.idle_timeout(config.idle_timeout)
.max_lifetime(config.max_lifetime)
.test_before_acquire(config.test_before_acquire)
.after_connect(|_conn, _meta| {
Box::pin(async move {
tracing::info!(
target: "ruprizzle::connection",
event = "connect",
backend = "postgres",
"sqlx connection opened"
);
Ok(())
})
})
.connect(url)
.await
.map_err(crate::Error::Sqlx)?;
Ok(Pool::Postgres(pool))
}
"mysql" | "mariadb" => {
let pool = MySqlPoolOptions::new()
.max_connections(config.max_connections)
.min_connections(config.min_connections)
.acquire_timeout(config.acquire_timeout)
.idle_timeout(config.idle_timeout)
.max_lifetime(config.max_lifetime)
.test_before_acquire(config.test_before_acquire)
.after_connect(|_conn, _meta| {
Box::pin(async move {
tracing::info!(
target: "ruprizzle::connection",
event = "connect",
backend = "mysql",
"sqlx connection opened"
);
Ok(())
})
})
.connect(url)
.await
.map_err(crate::Error::Sqlx)?;
Ok(Pool::Mysql(pool))
}
"sqlite" => {
#[cfg(feature = "sqlite-rusqlite")]
if url
.split_once('?')
.is_some_and(|(_, q)| q.contains("driver=rusqlite"))
{
let pool = crate::rusqlite::RusqlitePool::connect(url, config).await?;
return Ok(Pool::SqliteNative(pool));
}
let mut connect_opts =
sqlx::sqlite::SqliteConnectOptions::from_str(url).map_err(crate::Error::Sqlx)?;
connect_opts = connect_opts
.row_buffer_size(config.row_buffer_size as usize)
.journal_mode(sqlx::sqlite::SqliteJournalMode::Wal)
.synchronous(sqlx::sqlite::SqliteSynchronous::Normal)
.busy_timeout(Duration::from_secs(30))
.statement_cache_capacity(256)
.pragma("cache_size", "-64000")
.pragma("mmap_size", "268435456")
.pragma("temp_store", "MEMORY");
let pool = SqlitePoolOptions::new()
.max_connections(config.max_connections)
.min_connections(config.min_connections)
.acquire_timeout(config.acquire_timeout)
.idle_timeout(config.idle_timeout)
.max_lifetime(config.max_lifetime)
.test_before_acquire(config.test_before_acquire)
.after_connect(|_conn, _meta| {
Box::pin(async move {
tracing::info!(
target: "ruprizzle::connection",
event = "connect",
backend = "sqlite",
"sqlx connection opened"
);
Ok(())
})
})
.connect_with(connect_opts)
.await
.map_err(crate::Error::Sqlx)?;
Ok(Pool::Sqlite(pool))
}
_ => {
let pool = AnyPoolOptions::new()
.max_connections(config.max_connections)
.min_connections(config.min_connections)
.acquire_timeout(config.acquire_timeout)
.idle_timeout(config.idle_timeout)
.max_lifetime(config.max_lifetime)
.test_before_acquire(config.test_before_acquire)
.after_connect(|_conn, _meta| {
Box::pin(async move {
tracing::info!(
target: "ruprizzle::connection",
event = "connect",
backend = "any",
"sqlx connection opened"
);
Ok(())
})
})
.connect(url)
.await
.map_err(crate::Error::Sqlx)?;
Ok(Pool::Any(pool))
}
}
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
#[non_exhaustive]
pub struct PoolStats {
pub size: u32,
pub idle: usize,
pub in_use: usize,
pub waiters: usize,
}
#[must_use]
pub fn stats(pool: &Pool) -> PoolStats {
let size = pool.size();
let idle = pool.num_idle();
let waiters = pool.num_waiters();
PoolStats {
size,
idle,
in_use: (size as usize).saturating_sub(idle),
waiters,
}
}
#[must_use]
pub fn report_metrics(pool: &Pool) -> PoolStats {
let s = stats(pool);
#[cfg(feature = "metrics")]
{
use crate::metrics::{POOL_IDLE, POOL_IN_USE, POOL_SIZE, POOL_WAITERS, gauge};
gauge(POOL_SIZE, s.size as f64);
gauge(POOL_IDLE, s.idle as f64);
gauge(POOL_IN_USE, s.in_use as f64);
gauge(POOL_WAITERS, s.waiters as f64);
}
s
}
pub async fn ping(pool: &Pool) -> Result<(), crate::Error> {
crate::executor::Executor::execute_raw(pool, std::borrow::Cow::from("SELECT 1"), Vec::new())
.await
.map(|_| ())
}
#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
pub enum LoadBalancing {
#[default]
RoundRobin,
LeastConnections,
Random,
}
#[derive(Debug, Clone)]
pub struct ReplicaPool {
pub pool: Pool,
pub healthy: Arc<AtomicBool>,
pub active_conns: Arc<AtomicUsize>,
}
impl ReplicaPool {
#[must_use]
pub fn new(pool: Pool) -> Self {
Self {
pool,
healthy: Arc::new(AtomicBool::new(true)),
active_conns: Arc::new(AtomicUsize::new(0)),
}
}
#[must_use]
pub fn is_healthy(&self) -> bool {
self.healthy.load(Ordering::Relaxed)
}
pub fn set_healthy(&self, healthy: bool) {
self.healthy.store(healthy, Ordering::Relaxed);
}
#[must_use]
pub fn active_connections(&self) -> usize {
self.active_conns.load(Ordering::Relaxed)
}
}
#[derive(Debug, Clone)]
pub struct RoutedPool {
pub(crate) primary: Pool,
pub(crate) replicas: Vec<ReplicaPool>,
pub(crate) load_balancing: LoadBalancing,
pub(crate) rr_index: Arc<AtomicUsize>,
pub(crate) fallback_to_primary: bool,
}
impl RoutedPool {
#[must_use]
pub fn builder(primary: Pool) -> RoutedPoolBuilder {
RoutedPoolBuilder::new(primary)
}
#[must_use]
pub fn primary(&self) -> &Pool {
&self.primary
}
#[must_use]
pub fn replicas(&self) -> &[ReplicaPool] {
&self.replicas
}
#[must_use]
pub fn load_balancing(&self) -> LoadBalancing {
self.load_balancing
}
pub async fn begin(&self) -> Result<crate::tx::Tx, crate::Error> {
self.primary.begin().await
}
#[must_use]
pub fn select_replica(&self) -> &Pool {
match self.route_read() {
ReadRoute::Replica(r) => &r.pool,
ReadRoute::Primary => &self.primary,
ReadRoute::NoHealthyReplica => self.replicas.first().map_or(&self.primary, |r| &r.pool),
}
}
pub(crate) fn route_read(&self) -> ReadRoute<'_> {
match self.pick_healthy() {
Some(replica) => ReadRoute::Replica(replica),
None if self.fallback_to_primary || self.replicas.is_empty() => ReadRoute::Primary,
None => ReadRoute::NoHealthyReplica,
}
}
fn pick_healthy(&self) -> Option<&ReplicaPool> {
let healthy: Vec<&ReplicaPool> = self.replicas.iter().filter(|r| r.is_healthy()).collect();
let count = healthy.len();
if count == 0 {
return None;
}
match self.load_balancing {
LoadBalancing::RoundRobin => {
let idx = self.rr_index.fetch_add(1, Ordering::Relaxed);
healthy.get(idx.checked_rem(count).unwrap_or(0)).copied()
}
LoadBalancing::LeastConnections => {
healthy.into_iter().min_by_key(|r| r.active_connections())
}
LoadBalancing::Random => {
let draw =
RandomState::new().hash_one(self.rr_index.fetch_add(1, Ordering::Relaxed));
let bound = u64::try_from(count).unwrap_or(u64::MAX);
let target = draw
.checked_rem(bound)
.and_then(|t| usize::try_from(t).ok())
.unwrap_or(0);
healthy.get(target).copied()
}
}
}
pub async fn check_health(&self) {
for replica in &self.replicas {
let ok = ping(&replica.pool).await.is_ok();
replica.set_healthy(ok);
}
}
#[must_use = "dropping the handle stops the health checks"]
pub fn spawn_health_checks(&self, interval: Duration) -> HealthCheckTask {
let router = self.clone();
let period = interval.max(Duration::from_millis(1));
let handle = tokio::spawn(async move {
let mut ticker = tokio::time::interval(period);
ticker.set_missed_tick_behavior(tokio::time::MissedTickBehavior::Delay);
loop {
ticker.tick().await;
router.check_health().await;
}
});
HealthCheckTask { handle }
}
#[must_use]
pub fn falls_back_to_primary(&self) -> bool {
self.fallback_to_primary
}
}
#[derive(Debug)]
pub struct HealthCheckTask {
handle: tokio::task::JoinHandle<()>,
}
impl HealthCheckTask {
pub fn stop(self) {
drop(self);
}
#[must_use]
pub fn is_finished(&self) -> bool {
self.handle.is_finished()
}
}
impl Drop for HealthCheckTask {
fn drop(&mut self) {
self.handle.abort();
}
}
pub(crate) enum ReadRoute<'a> {
Replica(&'a ReplicaPool),
Primary,
NoHealthyReplica,
}
pub(crate) struct InFlight(Arc<AtomicUsize>);
impl InFlight {
pub(crate) fn start(replica: &ReplicaPool) -> Self {
replica.active_conns.fetch_add(1, Ordering::Relaxed);
Self(Arc::clone(&replica.active_conns))
}
}
impl Drop for InFlight {
fn drop(&mut self) {
self.0.fetch_sub(1, Ordering::Relaxed);
}
}
pub(crate) fn is_replica_safe(sql: &str) -> bool {
const READ_START: &[&str] = &["SELECT", "WITH", "VALUES", "TABLE", "SHOW"];
const NOT_ON_REPLICA: &[&str] = &[
"INSERT",
"UPDATE",
"DELETE",
"MERGE",
"INTO",
"RETURNING",
"LOCK",
"SHARE",
"NEXTVAL",
"SETVAL",
];
let mut words = strip_leading_comments(sql)
.split(|c: char| !(c.is_ascii_alphanumeric() || c == '_'))
.filter(|w| !w.is_empty());
let Some(first) = words.next() else {
return false;
};
READ_START.iter().any(|k| first.eq_ignore_ascii_case(k))
&& !words.any(|w| NOT_ON_REPLICA.iter().any(|k| w.eq_ignore_ascii_case(k)))
}
fn strip_leading_comments(mut sql: &str) -> &str {
loop {
sql = sql.trim_start();
if let Some(rest) = sql.strip_prefix("--") {
sql = rest.split_once('\n').map_or("", |(_, after)| after);
} else if let Some(rest) = sql.strip_prefix("/*") {
sql = rest.split_once("*/").map_or("", |(_, after)| after);
} else {
return sql;
}
}
}
#[derive(Debug)]
pub struct RoutedPoolBuilder {
primary: Pool,
replicas: Vec<ReplicaPool>,
load_balancing: LoadBalancing,
fallback_to_primary: bool,
}
impl RoutedPoolBuilder {
#[must_use]
pub fn new(primary: Pool) -> Self {
Self {
primary,
replicas: Vec::new(),
load_balancing: LoadBalancing::RoundRobin,
fallback_to_primary: true,
}
}
#[must_use]
pub fn add_replica(mut self, replica: Pool) -> Self {
self.replicas.push(ReplicaPool::new(replica));
self
}
#[must_use]
pub fn load_balancing(mut self, lb: LoadBalancing) -> Self {
self.load_balancing = lb;
self
}
#[must_use]
pub fn fallback_to_primary(mut self, fallback: bool) -> Self {
self.fallback_to_primary = fallback;
self
}
#[must_use]
pub fn build(self) -> RoutedPool {
RoutedPool {
primary: self.primary,
replicas: self.replicas,
load_balancing: self.load_balancing,
rr_index: Arc::new(AtomicUsize::new(0)),
fallback_to_primary: self.fallback_to_primary,
}
}
}
#[cfg(test)]
mod tests {
use super::is_replica_safe;
#[test]
fn plain_reads_may_use_a_replica() {
for sql in [
"SELECT * FROM users",
" select id from users where updated_at > $1",
"WITH recent AS (SELECT id FROM posts) SELECT * FROM recent",
"VALUES (1), (2)",
"TABLE users",
"SHOW server_version",
"-- leading comment\nSELECT 1",
"/* hint */ SELECT 1",
] {
assert!(is_replica_safe(sql), "{sql}");
}
}
#[test]
fn writes_locks_and_sequences_stay_on_the_primary() {
for sql in [
"INSERT INTO users (name) VALUES ($1) RETURNING id",
"UPDATE users SET name = $1 RETURNING *",
"DELETE FROM users WHERE id = $1 RETURNING id",
"WITH gone AS (DELETE FROM users RETURNING id) SELECT * FROM gone",
"SELECT * FROM users WHERE id = $1 FOR UPDATE",
"SELECT * FROM users FOR SHARE",
"SELECT * FROM users LOCK IN SHARE MODE",
"SELECT * INTO backup FROM users",
"SELECT nextval('users_id_seq')",
"SELECT setval('users_id_seq', 1)",
"MERGE INTO t USING s ON t.id = s.id WHEN MATCHED THEN DELETE",
"EXPLAIN ANALYZE DELETE FROM users",
"CALL refresh()",
"/* SELECT */ DELETE FROM users",
"-- SELECT\nUPDATE users SET name = 'x'",
"",
" ",
] {
assert!(!is_replica_safe(sql), "{sql}");
}
}
}