Migrate database at startup

10 files changed, 51 insertions, 36 deletions

diff --git a/Cargo.lock b/Cargo.lock
index 69ab396..bcb6771 100644
--- a/Cargo.lock
+++ b/Cargo.lock
@@ -198,6 +198,7 @@ dependencies = [
  "rand",
  "rand_core",
  "rusqlite",
+ "rusqlite_migration",
  "serde",
  "serde_json",
  "serde_urlencoded",
@@ -1073,6 +1074,16 @@ dependencies = [
 ]
 
 [[package]]
+name = "rusqlite_migration"
+version = "1.3.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "923b42e802f7dc20a0a6b5e097ba7c83fe4289da07e49156fecf6af08aa9cd1c"
+dependencies = [
+ "log",
+ "rusqlite",
+]
+
+[[package]]
 name = "rustc-demangle"
 version = "0.1.24"
 source = "registry+https://github.com/rust-lang/crates.io-index"
diff --git a/Cargo.toml b/Cargo.toml
index e301c69..88eac72 100644
--- a/Cargo.toml
+++ b/Cargo.toml
@@ -19,6 +19,7 @@ minijinja = { version = "2.7", features = ["loader"] }
 rand = { version = "0.9", features = ["os_rng"] }
 rand_core = "0.9"
 rusqlite = { version = "0.32", features = ["chrono"] }
+rusqlite_migration = "1.3"
 serde = { version = "1.0", features = ["derive"] }
 serde_json = "1.0"
 serde_urlencoded = "0.7"
diff --git a/README.md b/README.md
index 3905ef2..8e02d05 100644
--- a/README.md
+++ b/README.md
@@ -9,11 +9,15 @@
 
 1. Use `nix develop` to download dependencies.
 
-2. Initialize the database with `bin/db init` if required.
+2. Start the application:
 
-3. Start the application with `bin/dev-server`.
+        bin/dev-server
 
-4. Connect with either:
+3. Add fixtures:
 
-- `john@mail.com` / `password`
-- or `lisa@mail.com` / `password`.
+        sqlite3 database.db < sql/fixtures.sql
+
+4. Credentials will be:
+
+    - john@mail.com / password
+    - lisa@mail.com / password
diff --git a/bin/db b/bin/db
deleted file mode 100755
index 186f83d..0000000
--- a/bin/db
+++ /dev/null
@@ -1,26 +0,0 @@
-#!/usr/bin/env bash
-set -euo pipefail
-cd $(dirname "$0")/..
-
-DB_PATH="database.db"
-
-if [ "$1" == "init" ]; then
-
-  if [ -f "$DB_PATH" ]; then
-    rm "$DB_PATH"
-  fi
-
-  for MIGRATION in $(ls sql/migrations/*.sql); do
-    printf "\n- Applying $MIGRATION\n\n"
-    sqlite3 database.db < "$MIGRATION"
-  done
-
-  printf "\n- Applying sql/fixtures.sql\n\n"
-  sqlite3 database.db < sql/fixtures.sql
-
-else
-
-  echo "Usage: $0 init"
-  exit 1
-
-fi
diff --git a/sql/migrations/1.sql b/src/db/migration/01-init.sql
index d7c300e..d7c300e 100644
--- a/sql/migrations/1.sql
+++ b/src/db/migration/01-init.sql
diff --git a/sql/migrations/2.sql b/src/db/migration/02-payment-category.sql
index c1d502f..c1d502f 100644
--- a/sql/migrations/2.sql
+++ b/src/db/migration/02-payment-category.sql
diff --git a/sql/migrations/3.sql b/src/db/migration/03-sign-in-token.sql
index a3d8a13..a3d8a13 100644
--- a/sql/migrations/3.sql
+++ b/src/db/migration/03-sign-in-token.sql
diff --git a/sql/migrations/4.sql b/src/db/migration/04-plural-naming.sql
index ec386cb..ec386cb 100644
--- a/sql/migrations/4.sql
+++ b/src/db/migration/04-plural-naming.sql
diff --git a/sql/migrations/5.sql b/src/db/migration/05-strict-tables.sql
index cf7ef4b..cf7ef4b 100644
--- a/sql/migrations/5.sql
+++ b/src/db/migration/05-strict-tables.sql
diff --git a/src/db/mod.rs b/src/db/mod.rs
index 1282f0c..ef5ccef 100644
--- a/src/db/mod.rs
+++ b/src/db/mod.rs
@@ -1,4 +1,5 @@
 use anyhow::{Error, Result};
+use rusqlite_migration::{Migrations, M};
 use tokio_rusqlite::Connection;
 
 pub mod categories;
@@ -13,13 +14,37 @@ pub async fn init(path: &str) -> Result<Connection> {
         Error::msg(format!("Error opening connection: {err}"))
     })?;
 
-    support_foreign_keys(&connection).await?;
-
+    apply_migrations(&connection).await?;
+    set_pragma(&connection, "foreign_keys", "ON").await?;
+    set_pragma(&connection, "journal_mode", "wal").await?;
     Ok(connection)
 }
 
-async fn support_foreign_keys(conn: &Connection) -> Result<()> {
+async fn apply_migrations(conn: &Connection) -> Result<()> {
+    let migrations = Migrations::new(vec![
+        M::up(include_str!("migration/01-init.sql")),
+        M::up(include_str!("migration/02-payment-category.sql")),
+        M::up(include_str!("migration/03-sign-in-token.sql")),
+        M::up(include_str!("migration/04-plural-naming.sql")),
+        M::up(include_str!("migration/05-strict-tables.sql")),
+    ]);
+
+    Ok(conn
+        .call(move |conn| {
+            migrations.to_latest(conn).map_err(|migration_err| {
+                tokio_rusqlite::Error::Other(Box::new(migration_err))
+            })
+        })
+        .await?)
+}
+
+async fn set_pragma(conn: &Connection, key: impl Into<String>, value: impl Into<String>) -> Result<()> {
+    let key = key.into();
+    let value = value.into();
     Ok(conn
-        .call(move |conn| Ok(conn.pragma_update(None, "foreign_keys", "ON")))
-        .await??)
+        .call(move |conn| {
+            conn.pragma_update(None, &key, &value)
+                .map_err(tokio_rusqlite::Error::Rusqlite)
+        })
+        .await?)
 }