diff options
| -rw-r--r-- | Cargo.lock | 11 | ||||
| -rw-r--r-- | Cargo.toml | 1 | ||||
| -rw-r--r-- | README.md | 14 | ||||
| -rwxr-xr-x | bin/db | 26 | ||||
| -rw-r--r-- | src/db/migration/01-init.sql (renamed from sql/migrations/1.sql) | 0 | ||||
| -rw-r--r-- | src/db/migration/02-payment-category.sql (renamed from sql/migrations/2.sql) | 0 | ||||
| -rw-r--r-- | src/db/migration/03-sign-in-token.sql (renamed from sql/migrations/3.sql) | 0 | ||||
| -rw-r--r-- | src/db/migration/04-plural-naming.sql (renamed from sql/migrations/4.sql) | 0 | ||||
| -rw-r--r-- | src/db/migration/05-strict-tables.sql (renamed from sql/migrations/5.sql) | 0 | ||||
| -rw-r--r-- | src/db/mod.rs | 35 |
10 files changed, 51 insertions, 36 deletions
@@ -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" @@ -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" @@ -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 @@ -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?) } |