Runners Initiative manager v2

After struggling for some time with my previous Tauri app, trying to get it to stability connect to Surrealdb I have finally had to give up. I constantly ran into the app restarting because a setting in the DB had been changed, without any apparent reason. Since this is a hobby project, I decided to try using SQLite instead to move on, and then return to the other app when the API is more stable.

So I started a new Tauri app and added the SQLite package.

cargo create-tauri-app
cargo add rusqlite

I used the rusqlite package because it seemed to be a nice Rust wrapper for SQLite. Rusqlite provides an ergonomic interface for interacting with SQLite databases from Rust. After initializing my Tauri project, I ran the pnpm tauri dev command to get everything compiled and the development server running. With the setup out of the way, I was ready to start coding the app logic.

Taking lessons from my previous attempt, I started by setting up the SQLite database connection and defining a simple query. In the src-tauri/src/main.rs file, I instantiated a new rusqlite Connection in the tauri_app setup function. This Connection would allow me to store the database connection in the app state making it available everywhere in the app.

fn main() {
    tauri::Builder::default()
        .manage(AppState { db: Default::default() })
        .invoke_handler(tauri::generate_handler![get_all_combattens])
        .setup(|app| {
            let handle = app.handle();

            let app_state: State<AppState> = handle.state();
            let db = database::initialize_database(&handle).expect("Database initialize should succeed");
            *app_state.db.lock().unwrap() = Some(db);

            Ok(())
        })
        .run(tauri::generate_context!())
        .expect("error while running tauri application");
}

To encapsulate the database logic, I created a database.rs module. This initializes and opens the SQLite database connection. The initialize() function first checks if the data folder exists, creating it if needed. It then constructs the full database path using the folder path and sqlite filename. This is passed to rusqlite's Connection::open() to get the database handle.

I also implemented schema migration by tracking a user_version PRAGMA. When initializing, the code checks this version against the app's latest schema version. If they differ, it calls an upgrade_database_if_needed() function to perform any needed schema changes like adding tables or columns. For now, its a simple way of managing upgrades but this lays the groundwork to gracefully handle schema changes over time. Overall, the database.rs module provides a clean interface for managing database initialization and migration. (edited)

use rusqlite::{Connection, named_params};
use tauri::AppHandle;
use std::fs;

const CURRENT_DB_VERSION: u32 = 1;

/// Initializes the database connection, creating the .sqlite file if needed, and upgrading the database
/// if it's out of date.
pub fn initialize_database(app_handle: &AppHandle) -> Result<Connection, rusqlite::Error> {
    let app_dir = app_handle.path_resolver().app_data_dir().expect("The app data directory should exist.");
    fs::create_dir_all(&app_dir).expect("The app data directory should be created.");
    let sqlite_path = app_dir.join("initiative.sqlite");

    let mut db = Connection::open(sqlite_path)?;

    let mut user_pragma = db.prepare("PRAGMA user_version")?;
    let existing_user_version: u32 = user_pragma.query_row([], |row| { Ok(row.get(0)?) })?;
    drop(user_pragma);

    upgrade_database_if_needed(&mut db, existing_user_version)?;

    Ok(db)
}

/// Upgrades the database to the current version.
pub fn upgrade_database_if_needed(db: &mut Connection, existing_version: u32) -> Result<(), rusqlite::Error> {
  if existing_version < CURRENT_DB_VERSION {
    db.pragma_update(None, "journal_mode", "WAL")?;

    let tx = db.transaction()?;

    tx.pragma_update(None, "user_version", CURRENT_DB_VERSION)?;

    tx.execute_batch(
      "
      CREATE TABLE campaign (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
      );
      CREATE TABLE encounter (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
      );
      CREATE TABLE combattens (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
      );
      "
    )?;

    tx.commit()?;
  }

  Ok(())
}

With the database connection set up, I needed to manage the state. To manage the state across the app, I created an AppState service which uses a Mutex to store the database connection in memory after the app loads.

I also defined a ServiceAccess trait that defines read and write access to the database. The AppHandle from Tauri implements this trait, returning a database operation that can be used by our actions.

Specifically, ServiceAccess defines two methods:

• db() - Returns a database connection for read operations

• db_mut() - Returns a mutable database connection for write operations

By accessing the database through ServiceAccess, components can perform CRUD operations without directly managing the connection. AppState handles initializing and storing the connection.

This separation of concerns makes the app more maintainable. The database implementation details are encapsulated in AppState and ServiceAccess. Components simply request read or write access as needed to retrieve or modify data.

use rusqlite::Connection;
use tauri::{AppHandle, State, Manager};

pub struct AppState {
  pub db: std::sync::Mutex<Option<Connection>>,
}

pub trait ServiceAccess {
  fn db<F, TResult>(&self, operation: F) -> TResult where F: FnOnce(&Connection) -> TResult;

  fn db_mut<F, TResult>(&self, operation: F) -> TResult where F: FnOnce(&mut Connection) -> TResult;
}

impl ServiceAccess for AppHandle {
  fn db<F, TResult>(&self, operation: F) -> TResult where F: FnOnce(&Connection) -> TResult {
    let app_state: State<AppState> = self.state();
    let db_connection_guard = app_state.db.lock().unwrap();
    let db = db_connection_guard.as_ref().unwrap();

    operation(db)
  }

  fn db_mut<F, TResult>(&self, operation: F) -> TResult where F: FnOnce(&mut Connection) -> TResult {
    let app_state: State<AppState> = self.state();
    let mut db_connection_guard = app_state.db.lock().unwrap();
    let db = db_connection_guard.as_mut().unwrap();

    operation(db)
  }
}

Now I could begin adding interactions to fetch or mutate data. As a first step, I wrote a simple RPC command to retrieve all "combattens" from a table and return them to the frontend. By leveraging rusqlite's typed query API, this was straightforward - I could execute a query and map the rows into my Combatten struct using rusqlite's query_map method. Overall, I found rusqlite combined nicely with Tauri for building a persistence layer. The strong typing made for clean data access code on the Rust side, which I could call via RPC from my frontend.

I added an actions folder that will contain all the modules for interacting with the models in the app. Here I then created a combatten.rs file for the combattens and added the get_app_combattens function.

use rusqlite::{Connection, named_params};
use serde::{Serialize, Deserialize};

#[derive(Debug, Serialize, Deserialize)]
pub struct Combatten {
    id: i32,
    name: String,
}

pub fn get_all_combattens(db: &Connection) -> Result<Vec<Combatten>, rusqlite::Error> {
  let mut statement = db.prepare("SELECT * FROM combattens")?;
  let combattens_iter = statement.query_map([], |row| {
    Ok(Combatten {
      id: row.get(0)?,
      name: row.get(1)?,
    })
  }).unwrap();
  let combattens = combattens_iter.collect::<Result<Vec<_>, _>>().unwrap();
  Ok(combattens)
}

I have used the query_map to parse the results to the Combatten struct which can then be parsed to Svelte as objects.

In the main.rs I added the tauri command.

use actions::combatten;

#[tauri::command]
fn get_all_combattens(app_handle: AppHandle) -> Vec<combatten::Combatten> {
    let items = app_handle.db(|db|  combatten::get_all_combattens(db)).unwrap();
    items
}

And as the last thing, I also add a function to add a new combatten.

# actions/combatten.rs

pub fn add_combatten(name: &str, db: &Connection) -> Result<(), rusqlite::Error> {
  let mut statement = db.prepare("INSERT INTO combattens (name) VALUES (@name)")?;
  statement.execute(named_params! { "@name": name })?;

  Ok(())
}

# main.rs

#[tauri::command]
fn add_combatten(app_handle: AppHandle, name: &str) -> String {
    app_handle.db(|db|  combatten::add_combatten(name, db)).unwrap();

    format!("{} added", name)
}

Next step is to update the UI of the Svelte app so the combattens are loaded on startup.

<script lang="ts">
  import { invoke } from "@tauri-apps/api/tauri";

  let combattens = [];

  async function getCombattens() {
    // Learn more about Tauri commands at https://tauri.app/v1/guides/features/command
    combattens = await invoke("get_all_combattens");
  }

  getCombattens();
</script>

<main class="container">
  <h1>Welcome to Tauri!</h1>
  <p>Current combattens:</p>
  <div class="row">
    {#each combattens as combatten}
      <div>{combatten.name}</div>
    {/each}
  </div>
</main>

<style>
  .logo.vite:hover {
    filter: drop-shadow(0 0 2em #747bff);
  }

  .logo.svelte:hover {
    filter: drop-shadow(0 0 2em #ff3e00);
  }
</style>

To display data from the SQLite database, I added a function that calls the Rust RPC command to fetch all combattens. It maps the results to a list of div elements, showing any retrieved combattens. This confirms we can successfully get data from the database and display it in the UI.

The next steps will be enabling adding new combattens via the frontend, and starting work on a campaign framework to manage encounters using the stored combattens.

For reference, I've restructured the repositories. The code for this article is now available at https://github.com/jespermb/runners-initiative-manager. This repo will contain the full app as it evolves across future tutorial parts.