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
//! Ordered, checksummed migrations for the tables an application owns.
//!
//! The framework's own tables are created on first use by
//! [`SqliteJsonbStore`](crate::SqliteJsonbStore); this is for
//! everything else — the relational schema an application designs,
//! where the shape has to change over time without losing data.
//!
//! ## Embedded, not a directory
//!
//! The Postgres runner reads `.sql` files off disk at run time, which
//! suits a service deployed next to its migrations. This one takes
//! them as `&'static str`, because the deployment it exists for is a
//! **single binary copied to a machine** — a runner that needs a
//! sibling directory would make that binary not self-contained, which
//! was the whole point.
//!
//! ```ignore
//! MigrationRunner::new(database)
//!     .with_migration(Migration::new("0001_initial", include_str!("../migrations/0001_initial.sql")))
//!     .with_migration(Migration::new("0002_routes",  include_str!("../migrations/0002_routes.sql")))
//!     .up()
//!     .await?;
//! ```
//!
//! ## Drift is reported, never repaired
//!
//! Each applied migration's checksum is stored. If the text later
//! changes, [`status`](MigrationRunner::status) reports it as drifted
//! and [`up`](MigrationRunner::up) refuses to continue. Re-running an
//! edited migration would apply the *diff nobody wrote*; silently
//! ignoring the edit would leave two deployments with the same version
//! and different schemas. Neither is a decision this code should take
//! on its own.

use std::collections::HashMap;

use crate::database::SqliteDatabase;
use crate::error::{SqliteError, SqliteResult};

/// One migration: an id that orders it, and the SQL.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct Migration {
    /// Sorted lexicographically, so zero-pad: `0002` before `0010`.
    pub id: String,
    pub sql: &'static str,
}

impl Migration {
    pub fn new(id: impl Into<String>, sql: &'static str) -> Self {
        Self { id: id.into(), sql }
    }

    fn checksum(&self) -> String {
        // Line endings are normalised first: a file that round-tripped
        // through a Windows checkout is the same migration, and
        // flagging it as drift would be a false alarm nobody can fix.
        let normalised = self.sql.replace("\r\n", "\n");
        let digest = <sha2::Sha256 as sha2::Digest>::digest(normalised.as_bytes());
        hex_encode(&digest)
    }
}

fn hex_encode(bytes: &[u8]) -> String {
    bytes.iter().map(|byte| format!("{byte:02x}")).collect()
}

#[derive(Debug, Clone, PartialEq, Eq)]
pub struct MigrationStatus {
    pub id: String,
    pub applied: bool,
    /// Applied, but the SQL has changed since.
    pub drifted: bool,
    /// Epoch milliseconds.
    pub applied_at: Option<i64>,
}

/// Applies migrations in order, once each.
pub struct MigrationRunner {
    database: SqliteDatabase,
    migrations: Vec<Migration>,
    table: String,
}

const DEFAULT_TABLE: &str = "wabot_migrations";

impl MigrationRunner {
    pub fn new(database: SqliteDatabase) -> Self {
        Self {
            database,
            migrations: Vec::new(),
            table: DEFAULT_TABLE.to_string(),
        }
    }

    pub fn with_migration(mut self, migration: Migration) -> Self {
        self.migrations.push(migration);
        self
    }

    pub fn with_migrations(mut self, migrations: impl IntoIterator<Item = Migration>) -> Self {
        self.migrations.extend(migrations);
        self
    }

    /// Where the ledger lives. For an application that already owns a
    /// table by that name.
    pub fn with_table(mut self, table: impl Into<String>) -> Self {
        self.table = table.into();
        self
    }

    /// Sorted by id, which is the order they apply in.
    fn ordered(&self) -> Vec<Migration> {
        let mut migrations = self.migrations.clone();
        migrations.sort_by(|a, b| a.id.cmp(&b.id));
        migrations
    }

    async fn ensure_ledger(&self) -> SqliteResult<()> {
        let sql = format!(
            "CREATE TABLE IF NOT EXISTS \"{}\" (
                 \"id\"         TEXT PRIMARY KEY,
                 \"checksum\"   TEXT NOT NULL,
                 \"applied_at\" INTEGER NOT NULL
             )",
            self.table
        );
        self.database
            .write(move |connection| connection.execute(&sql, []).map(|_| ()))
            .await
    }

    async fn applied(&self) -> SqliteResult<HashMap<String, (String, i64)>> {
        let sql = format!(
            "SELECT \"id\", \"checksum\", \"applied_at\" FROM \"{}\"",
            self.table
        );
        let rows: Vec<(String, String, i64)> = self
            .database
            .read(move |connection| {
                connection
                    .prepare(&sql)?
                    .query_map([], |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?)))?
                    .collect()
            })
            .await?;
        Ok(rows
            .into_iter()
            .map(|(id, checksum, at)| (id, (checksum, at)))
            .collect())
    }

    /// What is applied, what is pending, and what has drifted.
    pub async fn status(&self) -> SqliteResult<Vec<MigrationStatus>> {
        self.ensure_ledger().await?;
        let applied = self.applied().await?;

        Ok(self
            .ordered()
            .into_iter()
            .map(|migration| match applied.get(&migration.id) {
                Some((checksum, at)) => MigrationStatus {
                    drifted: *checksum != migration.checksum(),
                    id: migration.id,
                    applied: true,
                    applied_at: Some(*at),
                },
                None => MigrationStatus {
                    id: migration.id,
                    applied: false,
                    drifted: false,
                    applied_at: None,
                },
            })
            .collect())
    }

    /// Apply everything pending, in order. Returns the ids applied.
    ///
    /// Each migration and its ledger row commit **together**: a
    /// migration that ran but was not recorded would be applied again
    /// on the next boot, which for anything not idempotent is worse
    /// than not having run at all.
    pub async fn up(&self) -> SqliteResult<Vec<String>> {
        self.ensure_ledger().await?;
        let applied = self.applied().await?;

        let mut drifted: Vec<String> = Vec::new();
        let mut pending: Vec<Migration> = Vec::new();
        for migration in self.ordered() {
            match applied.get(&migration.id) {
                Some((checksum, _)) if *checksum != migration.checksum() => {
                    drifted.push(migration.id)
                }
                Some(_) => {}
                None => pending.push(migration),
            }
        }

        if !drifted.is_empty() {
            return Err(SqliteError::Other(format!(
                "migrations changed after they were applied: {}. \
                 Add a new migration instead of editing one that has run.",
                drifted.join(", ")
            )));
        }

        let mut done = Vec::new();
        for migration in pending {
            let table = self.table.clone();
            let id = migration.id.clone();
            let checksum = migration.checksum();
            let sql = migration.sql;

            self.database
                .transaction(move |transaction| {
                    // `execute_batch` so one migration can hold several
                    // statements, which is what a schema change usually
                    // is.
                    transaction.execute_batch(sql)?;
                    transaction.execute(
                        &format!(
                            "INSERT INTO \"{table}\" (\"id\", \"checksum\", \"applied_at\") \
                             VALUES (?1, ?2, ?3)"
                        ),
                        rusqlite::params![id, checksum, chrono::Utc::now().timestamp_millis()],
                    )?;
                    Ok(())
                })
                .await
                .map_err(|error| {
                    SqliteError::Other(format!("migration {} failed: {error}", migration.id))
                })?;

            tracing::info!(migration = %migration.id, "applied");
            done.push(migration.id);
        }
        Ok(done)
    }
}

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

    const FIRST: &str = "CREATE TABLE a (v TEXT);";
    const SECOND: &str = "CREATE TABLE b (v TEXT); CREATE INDEX b_v ON b (v);";

    fn runner(database: SqliteDatabase) -> MigrationRunner {
        MigrationRunner::new(database)
            .with_migration(Migration::new("0001_a", FIRST))
            .with_migration(Migration::new("0002_b", SECOND))
    }

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

        let applied = runner(database.clone()).up().await.expect("up");
        assert_eq!(applied, vec!["0001_a", "0002_b"]);

        let again = runner(database.clone()).up().await.expect("up again");
        assert!(again.is_empty(), "nothing was pending the second time");

        // Both statements of the multi-statement migration ran.
        let count: i64 = database
            .read(|c| {
                c.query_row(
                    "SELECT COUNT(*) FROM sqlite_master WHERE name IN ('a', 'b', 'b_v')",
                    [],
                    |row| row.get(0),
                )
            })
            .await
            .expect("count");
        assert_eq!(count, 3);
    }

    /// Ids order the run, not the order they were registered — so a
    /// migration added to the middle of the list still applies where
    /// it belongs.
    #[tokio::test]
    async fn ids_decide_the_order() {
        let database = SqliteDatabase::in_memory().expect("open");
        let applied = MigrationRunner::new(database)
            .with_migration(Migration::new("0002_b", SECOND))
            .with_migration(Migration::new("0001_a", FIRST))
            .up()
            .await
            .expect("up");
        assert_eq!(applied, vec!["0001_a", "0002_b"]);
    }

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

        let status = runner(database.clone()).status().await.expect("status");
        assert!(status.iter().all(|s| !s.applied));

        runner(database.clone()).up().await.expect("up");
        let status = runner(database).status().await.expect("status");
        assert!(status.iter().all(|s| s.applied && !s.drifted));
        assert!(status[0].applied_at.is_some());
    }

    /// Editing an applied migration is refused rather than re-run or
    /// ignored: one would apply a diff nobody wrote, the other would
    /// leave two deployments claiming the same version.
    #[tokio::test]
    async fn an_edited_migration_is_refused() {
        let database = SqliteDatabase::in_memory().expect("open");
        runner(database.clone()).up().await.expect("up");

        let edited = MigrationRunner::new(database.clone())
            .with_migration(Migration::new("0001_a", "CREATE TABLE a (v INTEGER);"))
            .with_migration(Migration::new("0002_b", SECOND));

        let status = edited.status().await.expect("status");
        assert!(status[0].drifted, "the change was noticed");
        assert!(!status[1].drifted);

        let error = edited.up().await.expect_err("up refuses");
        assert!(error.to_string().contains("0001_a"), "{error}");
    }

    /// A migration and its ledger row commit together, so a failure
    /// leaves neither behind.
    #[tokio::test]
    async fn a_failing_migration_records_nothing() {
        let database = SqliteDatabase::in_memory().expect("open");
        let error = MigrationRunner::new(database.clone())
            .with_migration(Migration::new("0001_ok", FIRST))
            .with_migration(Migration::new("0002_bad", "CREATE TABLE ((("))
            .up()
            .await
            .expect_err("the bad migration failed");
        assert!(error.to_string().contains("0002_bad"), "{error}");

        let status = MigrationRunner::new(database)
            .with_migration(Migration::new("0001_ok", FIRST))
            .with_migration(Migration::new("0002_bad", "CREATE TABLE ((("))
            .status()
            .await
            .expect("status");
        assert!(status[0].applied, "the one before it stands");
        assert!(!status[1].applied, "the failure was not recorded");
    }

    #[tokio::test]
    async fn line_endings_are_not_drift() {
        let unix = Migration::new("0001", "CREATE TABLE a (v TEXT);\nSELECT 1;");
        let windows = Migration::new("0001", "CREATE TABLE a (v TEXT);\r\nSELECT 1;");
        assert_eq!(unix.checksum(), windows.checksum());
    }
}