Skip to main content

rok_db_core/
db.rs

1use std::fmt;
2use std::ops::{Deref, DerefMut};
3use std::sync::{Arc, Mutex};
4use std::time::Duration;
5
6use futures_core::future::BoxFuture;
7use futures_core::stream::BoxStream;
8use sqlx::postgres::{
9    PgConnectOptions, PgConnection, PgPool, PgPoolOptions, PgQueryResult, PgRow, PgStatement,
10    PgTypeInfo,
11};
12use sqlx::{Describe, Either, Execute, Postgres};
13
14use crate::context::Context;
15use crate::{Error, QueryCache, Result};
16
17/// A pooled PostgreSQL connection handle — the entry point of rok-db.
18///
19/// `Db` is cheap to clone (it wraps an `Arc`'d pool) and can be passed
20/// wherever an [`Executor`](crate::Executor) is expected:
21///
22/// ```ignore
23/// let db = Db::connect("postgres://localhost/app").await?;
24/// let user = User::find(&db, 1).await?;
25/// ```
26#[derive(Clone)]
27pub struct Db {
28    pool: PgPool,
29    ctx: Arc<Context>,
30}
31
32impl Db {
33    /// Connect with default pool settings.
34    pub async fn connect(url: &str) -> Result<Self> {
35        Self::builder().connect(url).await
36    }
37
38    /// Connect using the `DATABASE_URL` environment variable.
39    pub async fn connect_env() -> Result<Self> {
40        Self::builder().connect_env().await
41    }
42
43    /// Configure the connection pool before connecting.
44    pub fn builder() -> DbBuilder {
45        DbBuilder::default()
46    }
47
48    /// Wrap an existing sqlx pool.
49    pub fn from_pool(pool: PgPool) -> Self {
50        Self {
51            pool,
52            ctx: Arc::default(),
53        }
54    }
55
56    /// A handle to the same pool and settings that never uses read replicas,
57    /// for reads that must see the latest writes.
58    pub fn primary(&self) -> Db {
59        let mut ctx = (*self.ctx).clone();
60        ctx.replicas.clear();
61        Db {
62            pool: self.pool.clone(),
63            ctx: Arc::new(ctx),
64        }
65    }
66
67    /// Number of configured read replicas.
68    pub fn replica_count(&self) -> usize {
69        self.ctx.replicas.len()
70    }
71
72    /// A snapshot of the connection pool's state.
73    pub fn stats(&self) -> PoolStats {
74        PoolStats {
75            size: self.pool.size(),
76            idle: self.pool.num_idle(),
77            max_connections: self.pool.options().get_max_connections(),
78        }
79    }
80
81    /// Report [`stats`](Self::stats) as `rok_db_pool_connections` gauges
82    /// (feature `metrics`). Call it periodically, e.g. from a background
83    /// task or before each metrics scrape.
84    #[cfg(feature = "metrics")]
85    pub fn record_pool_metrics(&self) {
86        crate::metrics::pool(self.stats());
87    }
88
89    /// The query cache, if enabled with [`DbBuilder::query_cache`].
90    pub fn cache(&self) -> Option<&QueryCache> {
91        self.ctx.cache.as_ref()
92    }
93
94    /// The underlying sqlx pool, for anything rok-db doesn't cover.
95    pub fn pool(&self) -> &PgPool {
96        &self.pool
97    }
98
99    /// Consume the handle and return the underlying sqlx pool.
100    pub fn into_pool(self) -> PgPool {
101        self.pool
102    }
103
104    /// Start a transaction. It is rolled back on drop unless
105    /// [`Tx::commit`] is called.
106    pub async fn begin(&self) -> Result<Tx> {
107        #[cfg_attr(not(feature = "json"), allow(unused_mut))]
108        let mut tx = Tx {
109            inner: self.pool.begin().await?,
110            ctx: Some(self.ctx.clone()),
111            touched: Mutex::default(),
112        };
113        #[cfg(feature = "json")]
114        if let Some(actor) = crate::audit::current_actor() {
115            tx.set_actor(&actor).await?;
116        }
117        Ok(tx)
118    }
119
120    /// Run `f` inside a transaction, committing if it returns `Ok` and
121    /// rolling back if it returns `Err`.
122    ///
123    /// ```ignore
124    /// let user = db.transaction(|tx| Box::pin(async move {
125    ///     let user = new_user.insert(&mut *tx).await?;
126    ///     Profile::create().set(Profile::USER_ID, user.id).exec(&mut *tx).await?;
127    ///     Ok(user)
128    /// })).await?;
129    /// ```
130    pub async fn transaction<T, E, F>(&self, f: F) -> std::result::Result<T, E>
131    where
132        F: for<'t> FnOnce(&'t mut Tx) -> BoxFuture<'t, std::result::Result<T, E>>,
133        E: From<Error>,
134    {
135        let mut tx = self.begin().await?;
136        match f(&mut tx).await {
137            Ok(value) => {
138                tx.commit().await?;
139                Ok(value)
140            }
141            Err(err) => {
142                tx.rollback().await?;
143                Err(err)
144            }
145        }
146    }
147
148    /// Run `f` in a transaction configured by `options`, retrying the whole
149    /// closure when PostgreSQL aborts it with a serialization failure or a
150    /// deadlock (SQLSTATE `40001` / `40P01`), up to `options.retries` times.
151    ///
152    /// The closure may run more than once, so it must be `Fn` and should not
153    /// have side effects outside the database.
154    ///
155    /// ```ignore
156    /// let opts = TxOptions::new().isolation(Isolation::Serializable).retries(5);
157    /// db.transaction_with(opts, |tx| Box::pin(async move {
158    ///     let mut from = Account::find_or_fail(&mut *tx, 1).await?;
159    ///     let mut to = Account::find_or_fail(&mut *tx, 2).await?;
160    ///     from.balance -= 10;
161    ///     to.balance += 10;
162    ///     from.save(&mut *tx).await?;
163    ///     to.save(&mut *tx).await?;
164    ///     Ok::<_, rok_db::Error>(())
165    /// })).await?;
166    /// ```
167    pub async fn transaction_with<T, E, F>(
168        &self,
169        options: TxOptions,
170        f: F,
171    ) -> std::result::Result<T, E>
172    where
173        F: for<'t> Fn(&'t mut Tx) -> BoxFuture<'t, std::result::Result<T, E>>,
174        E: From<Error> + Retryable,
175    {
176        let mut attempt = 0;
177        loop {
178            let result = self.try_transaction(&options, &f).await;
179            match result {
180                Err(err) if attempt < options.retries && err.is_retryable() => {
181                    attempt += 1;
182                    let delay = options.backoff(attempt);
183                    tracing::debug!(target: "rok_db::query", attempt, delay_ms = delay.as_millis() as u64, "retrying transaction");
184                    tokio::time::sleep(delay).await;
185                }
186                other => return other,
187            }
188        }
189    }
190
191    async fn try_transaction<T, E, F>(
192        &self,
193        options: &TxOptions,
194        f: &F,
195    ) -> std::result::Result<T, E>
196    where
197        F: for<'t> Fn(&'t mut Tx) -> BoxFuture<'t, std::result::Result<T, E>>,
198        E: From<Error>,
199    {
200        let mut tx = self.begin().await?;
201        if let Some(sql) = options.set_transaction_sql() {
202            sqlx::query(&sql)
203                .execute(&mut *tx.inner)
204                .await
205                .map_err(Error::from)?;
206        }
207        match f(&mut tx).await {
208            Ok(value) => {
209                tx.commit().await?;
210                Ok(value)
211            }
212            Err(err) => {
213                // The transaction may already be aborted; a failed rollback
214                // must not hide the original error.
215                let _ = tx.rollback().await;
216                Err(err)
217            }
218        }
219    }
220
221    /// Execute one or more raw SQL statements without parameters (e.g. a
222    /// schema script) and return the total number of affected rows.
223    pub async fn execute(&self, sql: &str) -> Result<u64> {
224        Ok(sqlx::raw_sql(sql)
225            .execute(&self.pool)
226            .await?
227            .rows_affected())
228    }
229
230    /// Check that the database is reachable.
231    pub async fn ping(&self) -> Result<()> {
232        sqlx::query("SELECT 1").execute(&self.pool).await?;
233        Ok(())
234    }
235
236    /// Run every pending migration found in the `dir` directory
237    /// (sqlx migration format).
238    #[cfg(feature = "migrate")]
239    pub async fn migrate(&self, dir: impl AsRef<std::path::Path>) -> Result<()> {
240        let migrator = sqlx::migrate::Migrator::new(dir.as_ref()).await?;
241        migrator.run(&self.pool).await?;
242        Ok(())
243    }
244
245    /// Close every connection in the pool and its replicas.
246    pub async fn close(&self) {
247        self.pool.close().await;
248        for replica in &self.ctx.replicas {
249            replica.close().await;
250        }
251    }
252}
253
254impl From<PgPool> for Db {
255    fn from(pool: PgPool) -> Self {
256        Self::from_pool(pool)
257    }
258}
259
260impl fmt::Debug for Db {
261    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
262        f.debug_struct("Db")
263            .field("size", &self.pool.size())
264            .field("idle", &self.pool.num_idle())
265            .field("cache", &self.ctx.cache)
266            .finish()
267    }
268}
269
270/// Connection pool statistics, from [`Db::stats`].
271#[derive(Debug, Clone, Copy, PartialEq, Eq)]
272pub struct PoolStats {
273    /// Open connections (idle and in use).
274    pub size: u32,
275    /// Idle connections.
276    pub idle: usize,
277    /// Configured maximum.
278    pub max_connections: u32,
279}
280
281/// Transaction isolation level, see [`TxOptions::isolation`].
282#[derive(Debug, Clone, Copy, PartialEq, Eq)]
283pub enum Isolation {
284    /// `READ COMMITTED` (PostgreSQL's default).
285    ReadCommitted,
286    /// `REPEATABLE READ`.
287    RepeatableRead,
288    /// `SERIALIZABLE`: transactions behave as if run one after another;
289    /// conflicting ones fail with a serialization error (and are retried by
290    /// [`Db::transaction_with`]).
291    Serializable,
292}
293
294/// Settings for [`Db::transaction_with`].
295#[derive(Debug, Clone, PartialEq, Eq)]
296pub struct TxOptions {
297    isolation: Option<Isolation>,
298    read_only: bool,
299    retries: u32,
300    base_delay: Duration,
301}
302
303impl Default for TxOptions {
304    fn default() -> Self {
305        Self::new()
306    }
307}
308
309impl TxOptions {
310    /// Default settings: the server's isolation level, read-write, no retries.
311    pub fn new() -> Self {
312        Self {
313            isolation: None,
314            read_only: false,
315            retries: 0,
316            base_delay: Duration::from_millis(10),
317        }
318    }
319
320    /// Run with this isolation level.
321    pub fn isolation(mut self, isolation: Isolation) -> Self {
322        self.isolation = Some(isolation);
323        self
324    }
325
326    /// Run as `READ ONLY`.
327    pub fn read_only(mut self) -> Self {
328        self.read_only = true;
329        self
330    }
331
332    /// Retry up to `retries` times on serialization failures and deadlocks.
333    pub fn retries(mut self, retries: u32) -> Self {
334        self.retries = retries;
335        self
336    }
337
338    /// Base delay of the exponential backoff between retries (default 10ms;
339    /// attempt *n* waits `base * 2^(n-1)` plus up to 50% jitter, capped at 1s).
340    pub fn backoff_base(mut self, delay: Duration) -> Self {
341        self.base_delay = delay;
342        self
343    }
344
345    fn backoff(&self, attempt: u32) -> Duration {
346        let exp = self
347            .base_delay
348            .saturating_mul(1 << attempt.saturating_sub(1).min(16))
349            .min(Duration::from_secs(1));
350        // Cheap jitter without a RNG dependency.
351        let nanos = std::time::SystemTime::now()
352            .duration_since(std::time::UNIX_EPOCH)
353            .map_or(0, |d| d.subsec_nanos());
354        exp + exp.mul_f64(f64::from(nanos % 500) / 1000.0)
355    }
356
357    fn set_transaction_sql(&self) -> Option<String> {
358        let mut parts = Vec::new();
359        if let Some(isolation) = self.isolation {
360            parts.push(match isolation {
361                Isolation::ReadCommitted => "ISOLATION LEVEL READ COMMITTED",
362                Isolation::RepeatableRead => "ISOLATION LEVEL REPEATABLE READ",
363                Isolation::Serializable => "ISOLATION LEVEL SERIALIZABLE",
364            });
365        }
366        if self.read_only {
367            parts.push("READ ONLY");
368        }
369        (!parts.is_empty()).then(|| format!("SET TRANSACTION {}", parts.join(", ")))
370    }
371}
372
373/// Errors that can tell whether retrying the transaction might succeed.
374/// Implemented for [`Error`]; implement it for your own error type (usually
375/// by delegating to a wrapped `rok_db::Error`) to use it with
376/// [`Db::transaction_with`].
377pub trait Retryable {
378    /// `true` for serialization failures and deadlocks.
379    fn is_retryable(&self) -> bool;
380}
381
382impl Retryable for Error {
383    fn is_retryable(&self) -> bool {
384        self.is_serialization_failure()
385    }
386}
387
388/// Builder for a [`Db`] connection pool, created with [`Db::builder`].
389#[derive(Debug, Clone, Default)]
390pub struct DbBuilder {
391    options: PgPoolOptions,
392    ctx: Context,
393    replica_urls: Vec<String>,
394    shared_invalidation: bool,
395}
396
397impl DbBuilder {
398    /// Enable the [query cache](QueryCache) used by
399    /// [`Select::memoize`](crate::Select::memoize), holding up to
400    /// `capacity` results.
401    pub fn query_cache(mut self, capacity: usize) -> Self {
402        self.ctx.cache = Some(QueryCache::new(capacity));
403        self
404    }
405
406    /// Use an existing [`QueryCache`], e.g. one shared by several pools.
407    pub fn with_query_cache(mut self, cache: QueryCache) -> Self {
408        self.ctx.cache = Some(cache);
409        self
410    }
411
412    /// Add a read replica. Reads made through `&Db` by the query builder
413    /// (`all`, `first`, `count`, `paginate`, relations, …) are spread across
414    /// replicas round-robin; writes, row locks (`for_update`), transactions
415    /// and raw SQL stay on the primary. A read that fails because a replica
416    /// is unreachable is retried on the primary.
417    ///
418    /// Replicas lag behind the primary: use
419    /// [`Select::on_primary`](crate::Select::on_primary) or
420    /// [`Db::primary`] where a read must see the latest write.
421    pub fn read_replica(mut self, url: impl Into<String>) -> Self {
422        self.replica_urls.push(url.into());
423        self
424    }
425
426    /// Add an existing pool as a read replica (see [`read_replica`](Self::read_replica)).
427    pub fn replica_pool(mut self, pool: PgPool) -> Self {
428        self.ctx.replicas.push(pool);
429        self
430    }
431
432    /// Keep the query caches of every process using this database coherent:
433    /// invalidations are broadcast with PostgreSQL `NOTIFY` and applied by
434    /// all instances that enabled this (needs [`query_cache`](Self::query_cache)
435    /// and a Tokio runtime). A background task holds one extra connection.
436    ///
437    /// Invalidation is asynchronous: other instances drop stale entries a few
438    /// milliseconds after the writing transaction's statement. After a lost
439    /// connection the local cache is cleared, since messages may have been
440    /// missed.
441    pub fn shared_cache_invalidation(mut self) -> Self {
442        self.shared_invalidation = true;
443        self
444    }
445
446    /// Log queries slower than `threshold` at `WARN` level on the
447    /// `rok_db::slow_query` tracing target (default 1s, `Duration::ZERO`
448    /// disables it).
449    pub fn slow_query_threshold(mut self, threshold: Duration) -> Self {
450        self.ctx.slow_query = threshold;
451        self
452    }
453
454    fn build(self, pool: PgPool) -> Db {
455        if self.shared_invalidation {
456            match &self.ctx.cache {
457                Some(cache) => crate::cache_sync::start(pool.clone(), cache),
458                None => {
459                    tracing::warn!(target: "rok_db::cache", "shared_cache_invalidation() without query_cache(); ignored")
460                }
461            }
462        }
463        Db {
464            pool,
465            ctx: Arc::new(self.ctx),
466        }
467    }
468
469    /// Maximum number of pooled connections (default 10).
470    pub fn max_connections(mut self, n: u32) -> Self {
471        self.options = self.options.max_connections(n);
472        self
473    }
474
475    /// Minimum number of idle connections kept open (default 0).
476    pub fn min_connections(mut self, n: u32) -> Self {
477        self.options = self.options.min_connections(n);
478        self
479    }
480
481    /// How long to wait for a free connection (default 30s).
482    pub fn acquire_timeout(mut self, timeout: Duration) -> Self {
483        self.options = self.options.acquire_timeout(timeout);
484        self
485    }
486
487    /// Close connections idle for longer than `timeout` (default 10min).
488    pub fn idle_timeout(mut self, timeout: Duration) -> Self {
489        self.options = self.options.idle_timeout(timeout);
490        self
491    }
492
493    /// Recycle connections older than `lifetime` (default 30min).
494    pub fn max_lifetime(mut self, lifetime: Duration) -> Self {
495        self.options = self.options.max_lifetime(lifetime);
496        self
497    }
498
499    /// Connect to `url` and return the pool.
500    pub async fn connect(self, url: &str) -> Result<Db> {
501        let pool = self.options.clone().connect(url).await?;
502        self.finish(pool).await
503    }
504
505    /// Connect the configured replica URLs, then build the handle.
506    async fn finish(mut self, pool: PgPool) -> Result<Db> {
507        for url in std::mem::take(&mut self.replica_urls) {
508            let replica = self.options.clone().connect(&url).await?;
509            self.ctx.replicas.push(replica);
510        }
511        Ok(self.build(pool))
512    }
513
514    /// Connect using the `DATABASE_URL` environment variable.
515    pub async fn connect_env(self) -> Result<Db> {
516        let url = std::env::var("DATABASE_URL").map_err(|_| {
517            Error::Config("the `DATABASE_URL` environment variable is not set".into())
518        })?;
519        self.connect(&url).await
520    }
521
522    /// Wrap an existing sqlx pool, applying this builder's rok-db settings
523    /// (pool options and replica URLs are ignored; use
524    /// [`replica_pool`](Self::replica_pool) for replicas).
525    pub fn build_with_pool(self, pool: PgPool) -> Db {
526        self.build(pool)
527    }
528
529    /// Connect with fully custom connect options.
530    pub async fn connect_with(self, options: PgConnectOptions) -> Result<Db> {
531        let pool = self.options.clone().connect_with(options).await?;
532        self.finish(pool).await
533    }
534
535    /// Create the pool without opening any connection until first use.
536    pub fn connect_lazy(self, url: &str) -> Result<Db> {
537        let pool = self.options.clone().connect_lazy(url)?;
538        let mut builder = self;
539        for url in std::mem::take(&mut builder.replica_urls) {
540            let replica = builder.options.clone().connect_lazy(&url)?;
541            builder.ctx.replicas.push(replica);
542        }
543        Ok(builder.build(pool))
544    }
545}
546
547/// An open database transaction, created with [`Db::begin`].
548///
549/// Pass `&mut *tx` wherever an [`Executor`](crate::Executor) is expected.
550/// A transaction that is dropped without [`commit`](Tx::commit) is rolled back.
551pub struct Tx {
552    pub(crate) inner: sqlx::Transaction<'static, Postgres>,
553    ctx: Option<Arc<Context>>,
554    touched: Mutex<Vec<&'static str>>,
555}
556
557impl Tx {
558    /// Commit the transaction.
559    pub async fn commit(self) -> Result<()> {
560        self.inner.commit().await?;
561        // Results cached by other connections while this transaction was
562        // open still reflect the old data.
563        if let Some(cache) = self.ctx.as_ref().and_then(|c| c.cache.as_ref()) {
564            let touched = self.touched.lock().unwrap_or_else(|e| e.into_inner());
565            for table in touched.iter() {
566                cache.invalidate(table);
567            }
568        }
569        Ok(())
570    }
571
572    /// Roll the transaction back.
573    pub async fn rollback(self) -> Result<()> {
574        Ok(self.inner.rollback().await?)
575    }
576
577    /// Record `actor` as the author of this transaction's changes in the
578    /// audit log (`SET LOCAL rok.actor`, see [`audit`](crate::audit)).
579    pub async fn set_actor(&mut self, actor: &str) -> Result<()> {
580        sqlx::query("SELECT set_config('rok.actor', $1, true)")
581            .bind(actor)
582            .execute(&mut *self.inner)
583            .await?;
584        Ok(())
585    }
586
587    /// The underlying sqlx transaction.
588    pub fn inner(&mut self) -> &mut sqlx::Transaction<'static, Postgres> {
589        &mut self.inner
590    }
591
592    /// Record a write to `table` made outside the executor path (e.g. COPY).
593    pub(crate) fn note_write(&self, table: &'static str) {
594        let mut touched = self.touched.lock().unwrap_or_else(|e| e.into_inner());
595        if !touched.contains(&table) {
596            touched.push(table);
597        }
598        if let Some(cache) = self.ctx.as_ref().and_then(|c| c.cache.as_ref()) {
599            cache.invalidate(table);
600        }
601    }
602}
603
604impl From<sqlx::Transaction<'static, Postgres>> for Tx {
605    fn from(inner: sqlx::Transaction<'static, Postgres>) -> Self {
606        Self {
607            inner,
608            ctx: None,
609            touched: Mutex::default(),
610        }
611    }
612}
613
614impl Deref for Tx {
615    type Target = PgConnection;
616
617    fn deref(&self) -> &PgConnection {
618        &self.inner
619    }
620}
621
622impl DerefMut for Tx {
623    fn deref_mut(&mut self) -> &mut PgConnection {
624        &mut self.inner
625    }
626}
627
628impl fmt::Debug for Tx {
629    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
630        f.write_str("Tx")
631    }
632}
633
634impl<'c> crate::Executor<'c> for &'c Db {
635    fn __context(&self) -> Option<Arc<Context>> {
636        Some(self.ctx.clone())
637    }
638
639    fn __replica(&self) -> Option<PgPool> {
640        self.ctx.replica()
641    }
642}
643
644impl<'c> crate::Executor<'c> for &'c mut Tx {
645    fn __context(&self) -> Option<Arc<Context>> {
646        self.ctx.clone()
647    }
648
649    fn __touch(&self, table: &'static str) {
650        let mut touched = self.touched.lock().unwrap_or_else(|e| e.into_inner());
651        if !touched.contains(&table) {
652            touched.push(table);
653        }
654    }
655}
656
657/// Implement sqlx's `Executor` by delegating to an inner executor.
658macro_rules! delegate_executor {
659    ($ty:ty, |$this:ident| $inner:expr) => {
660        impl<'c> sqlx::Executor<'c> for $ty {
661            type Database = Postgres;
662
663            fn fetch_many<'e, 'q: 'e, E>(
664                self,
665                query: E,
666            ) -> BoxStream<'e, std::result::Result<Either<PgQueryResult, PgRow>, sqlx::Error>>
667            where
668                'c: 'e,
669                E: 'q + Execute<'q, Postgres>,
670            {
671                let $this = self;
672                $inner.fetch_many(query)
673            }
674
675            fn fetch_optional<'e, 'q: 'e, E>(
676                self,
677                query: E,
678            ) -> BoxFuture<'e, std::result::Result<Option<PgRow>, sqlx::Error>>
679            where
680                'c: 'e,
681                E: 'q + Execute<'q, Postgres>,
682            {
683                let $this = self;
684                $inner.fetch_optional(query)
685            }
686
687            fn prepare_with<'e, 'q: 'e>(
688                self,
689                sql: &'q str,
690                parameters: &'e [PgTypeInfo],
691            ) -> BoxFuture<'e, std::result::Result<PgStatement<'q>, sqlx::Error>>
692            where
693                'c: 'e,
694            {
695                let $this = self;
696                $inner.prepare_with(sql, parameters)
697            }
698
699            fn describe<'e, 'q: 'e>(
700                self,
701                sql: &'q str,
702            ) -> BoxFuture<'e, std::result::Result<Describe<Postgres>, sqlx::Error>>
703            where
704                'c: 'e,
705            {
706                let $this = self;
707                $inner.describe(sql)
708            }
709        }
710    };
711}
712
713delegate_executor!(&'c Db, |this| &this.pool);
714delegate_executor!(&'c mut Tx, |this| &mut *this.inner);