wabot-feature-sqlite 0.2.0

SQLite foundation for Wabot — the document store, migrations and connection handling, for a single-node deployment with no database server.
Documentation
//! Connections: one writer, a few readers, all reached from async
//! code through `spawn_blocking`.
//!
//! ## Why not a pool
//!
//! Every other backend in this framework hands you a pool and lets the
//! engine sort out concurrency. SQLite cannot: **there is one writer,
//! always**. A pool of N connections all willing to write does not buy
//! parallelism, it buys `SQLITE_BUSY` — each connection takes the
//! write lock, the others spin on their busy timeout, and throughput
//! collapses exactly when load arrives.
//!
//! So the shape here is explicit rather than emergent: one writer
//! connection behind a mutex, and a small free-list of read-only
//! connections. Under WAL a reader never blocks the writer and the
//! writer never blocks a reader, so this is the arrangement that
//! actually runs in parallel.
//!
//! ## Why not `sqlx`
//!
//! `sqlx`'s SQLite driver is async-shaped over a blocking engine: it
//! spawns an OS thread per pooled connection and talks to it over
//! channels. That is precisely the per-connection cost a single-node
//! deployment is trying not to pay. `spawn_blocking` puts the same
//! work on tokio's shared blocking pool, which the process has anyway.

use std::path::PathBuf;
use std::sync::Arc;

use parking_lot::Mutex;
use rusqlite::Connection;

use crate::config::SqliteConfig;
use crate::error::{SqliteError, SqliteResult};

struct Inner {
    config: SqliteConfig,
    /// The single writer. Held across a whole `spawn_blocking` call,
    /// never across an await — the closure runs to completion.
    writer: Mutex<Connection>,
    /// Opened lazily up to `max_readers` and returned after use.
    /// A free-list rather than a semaphore-plus-vector because the
    /// only interesting question is "is one available".
    readers: Mutex<Vec<Connection>>,
}

/// Owns the connections. A newtype rather than exposing `Connection`
/// directly, because the DI container keys on the concrete type.
#[derive(Clone)]
pub struct SqliteDatabase {
    inner: Arc<Inner>,
}

impl SqliteDatabase {
    /// Open the database, applying the pragmas in [`SqliteConfig`].
    ///
    /// The parent directory is created if missing: a control plane
    /// that fails to boot because `/var/lib/app` does not exist yet is
    /// a worse experience than one `mkdir`.
    pub fn open(config: SqliteConfig) -> SqliteResult<Self> {
        if let Some(path) = &config.path {
            if let Some(parent) = path.parent() {
                if !parent.as_os_str().is_empty() {
                    std::fs::create_dir_all(parent).map_err(|error| {
                        SqliteError::Config(format!(
                            "could not create {}: {error}",
                            parent.display()
                        ))
                    })?;
                }
            }
        }

        let writer = open_connection(&config, false)?;
        Ok(Self {
            inner: Arc::new(Inner {
                config,
                writer: Mutex::new(writer),
                readers: Mutex::new(Vec::new()),
            }),
        })
    }

    pub fn from_env() -> SqliteResult<Self> {
        Self::open(SqliteConfig::from_env()?)
    }

    /// A throwaway in-memory database. For tests, and for a `doctor`
    /// command that wants to exercise the schema without touching the
    /// real file.
    pub fn in_memory() -> SqliteResult<Self> {
        Self::open(SqliteConfig::in_memory())
    }

    pub fn arc(self) -> Arc<Self> {
        Arc::new(self)
    }

    pub fn path(&self) -> Option<&PathBuf> {
        self.inner.config.path.as_ref()
    }

    /// Run `work` against the writer connection.
    ///
    /// Serialized against every other writer, which is not a
    /// limitation being worked around — it is what SQLite does, made
    /// visible.
    pub async fn write<F, T>(&self, work: F) -> SqliteResult<T>
    where
        F: FnOnce(&Connection) -> rusqlite::Result<T> + Send + 'static,
        T: Send + 'static,
    {
        let inner = self.inner.clone();
        blocking(move || {
            let connection = inner.writer.lock();
            work(&connection).map_err(SqliteError::from)
        })
        .await
    }

    /// Run `work` against a read-only connection.
    ///
    /// Falls back to the writer when the database is in memory (a
    /// second connection to `:memory:` would be a different, empty
    /// database) or when the reader pool is exhausted.
    pub async fn read<F, T>(&self, work: F) -> SqliteResult<T>
    where
        F: FnOnce(&Connection) -> rusqlite::Result<T> + Send + 'static,
        T: Send + 'static,
    {
        let inner = self.inner.clone();
        blocking(move || {
            let Some(connection) = checkout(&inner)? else {
                let connection = inner.writer.lock();
                return work(&connection).map_err(SqliteError::from);
            };
            let result = work(&connection).map_err(SqliteError::from);
            inner.readers.lock().push(connection);
            result
        })
        .await
    }

    /// Run `work` inside `BEGIN IMMEDIATE` … `COMMIT`.
    ///
    /// **`IMMEDIATE`, not the default deferred**: a deferred
    /// transaction takes the write lock on its first write, so a
    /// read-then-write transaction can fail to upgrade and abort with
    /// `SQLITE_BUSY` after doing its reads. Taking the lock up front
    /// turns that into a wait, which the busy timeout already handles.
    pub async fn transaction<F, T>(&self, work: F) -> SqliteResult<T>
    where
        F: FnOnce(&rusqlite::Transaction<'_>) -> rusqlite::Result<T> + Send + 'static,
        T: Send + 'static,
    {
        let inner = self.inner.clone();
        blocking(move || {
            let mut connection = inner.writer.lock();
            let transaction =
                connection.transaction_with_behavior(rusqlite::TransactionBehavior::Immediate)?;
            let value = work(&transaction)?;
            transaction.commit()?;
            Ok(value)
        })
        .await
    }

    /// Round-trip a trivial query. Call at boot when you would rather
    /// fail immediately than on the first real request.
    pub async fn ping(&self) -> SqliteResult<()> {
        self.read(|connection| connection.query_row("SELECT 1", [], |_| Ok(())))
            .await
    }

    /// Checkpoint the WAL and drop the read connections.
    ///
    /// Worth doing on the way down: an un-checkpointed WAL is replayed
    /// at next open, which turns a restart into a slow one.
    pub async fn close(&self) -> SqliteResult<()> {
        self.inner.readers.lock().clear();
        self.write(|connection| {
            connection.pragma_update(None, "wal_checkpoint", "TRUNCATE")?;
            Ok(())
        })
        .await
    }
}

/// Take a reader from the free list, opening one if the pool has not
/// reached its limit. `None` means "use the writer".
fn checkout(inner: &Inner) -> SqliteResult<Option<Connection>> {
    if inner.config.max_readers == 0 {
        return Ok(None);
    }
    if let Some(connection) = inner.readers.lock().pop() {
        return Ok(Some(connection));
    }
    Ok(Some(open_connection(&inner.config, true)?))
}

fn open_connection(config: &SqliteConfig, read_only: bool) -> SqliteResult<Connection> {
    use rusqlite::OpenFlags;

    let connection = match &config.path {
        None => Connection::open_in_memory()?,
        Some(path) => {
            let mut flags = OpenFlags::SQLITE_OPEN_URI | OpenFlags::SQLITE_OPEN_NO_MUTEX;
            if read_only {
                flags |= OpenFlags::SQLITE_OPEN_READ_ONLY;
            } else {
                flags |= OpenFlags::SQLITE_OPEN_READ_WRITE | OpenFlags::SQLITE_OPEN_CREATE;
            }
            Connection::open_with_flags(path, flags)?
        }
    };

    connection.busy_timeout(config.busy_timeout)?;
    // A negative `cache_size` is kibibytes; a positive one is *pages*,
    // whose size depends on the page size. Being explicit about the
    // unit is the difference between 2 MB and 2000 pages.
    connection.pragma_update(None, "cache_size", -(config.cache_kib as i64))?;
    connection.pragma_update(None, "foreign_keys", "ON")?;

    // `LIKE` is ASCII-case-*insensitive* by default in SQLite and
    // case-sensitive everywhere else this framework runs. Without this
    // the same `Query` quietly means something different depending on
    // the backend, which is the exact failure a storage-agnostic query
    // AST exists to prevent. `wabot_core::repository::query::matches`
    // is case-sensitive, and this is what keeps SQLite agreeing.
    connection.pragma_update(None, "case_sensitive_like", "ON")?;

    if !read_only {
        // WAL is what lets readers and the writer run at the same
        // time. It is a persistent property of the file, so setting it
        // on the writer is enough.
        if !config.is_in_memory() {
            connection.pragma_update(None, "journal_mode", "WAL")?;
        }
        connection.pragma_update(
            None,
            "synchronous",
            if config.synchronous_full {
                "FULL"
            } else {
                "NORMAL"
            },
        )?;
    }

    // Deliberately not set: `mmap_size`. It inflates RSS accounting
    // for very little at the data sizes a control plane holds, and
    // "why does this process report 400 MB" is a question nobody
    // enjoys answering.

    Ok(connection)
}

async fn blocking<F, T>(work: F) -> SqliteResult<T>
where
    F: FnOnce() -> SqliteResult<T> + Send + 'static,
    T: Send + 'static,
{
    tokio::task::spawn_blocking(work)
        .await
        .map_err(|error| SqliteError::Worker(error.to_string()))?
}

#[cfg(test)]
mod tests {
    use super::*;

    #[tokio::test]
    async fn it_opens_and_answers() {
        let database = SqliteDatabase::in_memory().expect("open");
        database.ping().await.expect("ping");
    }

    #[tokio::test]
    async fn a_write_is_visible_to_a_read() {
        let database = SqliteDatabase::in_memory().expect("open");
        database
            .write(|c| c.execute("CREATE TABLE t (v TEXT)", []).map(|_| ()))
            .await
            .expect("create");
        database
            .write(|c| c.execute("INSERT INTO t VALUES ('x')", []).map(|_| ()))
            .await
            .expect("insert");

        let value: String = database
            .read(|c| c.query_row("SELECT v FROM t", [], |row| row.get(0)))
            .await
            .expect("read");
        assert_eq!(value, "x");
    }

    /// A file-backed database is the case the reader pool exists for,
    /// and the one where a second connection is a *different* handle
    /// on the same data rather than a different database.
    #[tokio::test]
    async fn a_file_database_serves_reads_from_the_pool() {
        let directory = std::env::temp_dir().join(format!("wabot-sqlite-{}", std::process::id()));
        let path = directory.join("nested").join("test.db");
        let _ = std::fs::remove_dir_all(&directory);

        let database = SqliteDatabase::open(SqliteConfig::at(&path)).expect("open");
        assert!(path.exists(), "the parent directories were created");

        database
            .write(|c| {
                c.execute("CREATE TABLE t (v INTEGER)", [])?;
                c.execute("INSERT INTO t VALUES (7)", [])
            })
            .await
            .expect("write");

        // Concurrent reads exercise the free-list rather than one
        // connection being checked out and returned serially.
        let reads = (0..8).map(|_| {
            let database = database.clone();
            tokio::spawn(async move {
                database
                    .read(|c| c.query_row("SELECT v FROM t", [], |row| row.get::<_, i64>(0)))
                    .await
            })
        });
        for read in reads {
            assert_eq!(read.await.expect("task").expect("read"), 7);
        }

        database.close().await.expect("close");
        let _ = std::fs::remove_dir_all(&directory);
    }

    /// `LIKE` must be case-sensitive, or the same `Query` means
    /// different things on SQLite and Postgres.
    #[tokio::test]
    async fn like_is_case_sensitive() {
        let database = SqliteDatabase::in_memory().expect("open");
        database
            .write(|c| {
                c.execute("CREATE TABLE t (v TEXT)", [])?;
                c.execute("INSERT INTO t VALUES ('Ada')", [])
            })
            .await
            .expect("setup");

        let hits: i64 = database
            .read(|c| {
                c.query_row("SELECT COUNT(*) FROM t WHERE v LIKE 'ada%'", [], |r| {
                    r.get(0)
                })
            })
            .await
            .expect("query");
        assert_eq!(hits, 0, "'ada%' must not match 'Ada'");

        let hits: i64 = database
            .read(|c| {
                c.query_row("SELECT COUNT(*) FROM t WHERE v LIKE 'Ada%'", [], |r| {
                    r.get(0)
                })
            })
            .await
            .expect("query");
        assert_eq!(hits, 1);
    }

    #[tokio::test]
    async fn a_failed_transaction_rolls_back() {
        let database = SqliteDatabase::in_memory().expect("open");
        database
            .write(|c| c.execute("CREATE TABLE t (v INTEGER)", []).map(|_| ()))
            .await
            .expect("create");

        let result = database
            .transaction(|t| {
                t.execute("INSERT INTO t VALUES (1)", [])?;
                Err::<(), _>(rusqlite::Error::InvalidQuery)
            })
            .await;
        assert!(result.is_err());

        let count: i64 = database
            .read(|c| c.query_row("SELECT COUNT(*) FROM t", [], |row| row.get(0)))
            .await
            .expect("count");
        assert_eq!(count, 0, "the insert was rolled back with the transaction");
    }
}