Persisting data with SQLite

Step 5 of 13. Step 4 left rustnote with three commands that compute: add, divide, and stats take numbers in and return numbers out, and nothing they touch survives closing the window. This step gives the app a memory. SQLite arrives through rusqlite, the connection opens once at startup in the app’s data directory, a version-checked migration creates the notes table, and a new command writes the first row. The step starts from tauri-ch03, the tag step 4 left behind.

Add rusqlite, bundled

One new dependency, added from src-tauri/:

cargo add rusqlite@0.37 --features bundled

[dependencies]
tauri = { version = "2", features = [] }
tauri-plugin-opener = "2"
serde = { version = "1", features = ["derive"] }
serde_json = "1"
rusqlite = { version = "0.37", features = ["bundled"] }

Version 0.37, not the 0.40 that plain cargo add rusqlite would pick today: rusqlite 0.40 bundles SQLite through libsqlite3-sys 0.38, which cannot coexist with sqlx 0.9’s SQLite bindings in one build — and a project with both is common enough that the older pin is the compatible choice. Every API this article touches is identical in both.

The bundled feature is the one that matters. Without it, rusqlite links against whatever libsqlite3 the operating system provides — macOS ships one version, every Linux distribution another, and the app’s behaviour shifts with them. With bundled, the build compiles SQLite from its own C source into the binary through libsqlite3-sys, so every machine runs the same engine. First build takes longer; the SQLite C source is part of your dependency tree now.

Where the database file lives

The connection opens in the setup hook, the piece of the Builder that runs after the app exists but before anything else uses it:

.setup(|app| {
    let dir = app.path().app_data_dir().expect("no app data directory");
    std::fs::create_dir_all(&dir).expect("cannot create the app data directory");
    let conn = Connection::open(dir.join("rustnote.db")).expect("cannot open rustnote.db");
    run_migrations(&conn).expect("migrations failed");
    app.manage(Mutex::new(conn));
    Ok(())
})

app.path().app_data_dir() derives its path from the identifier in tauri.conf.json — com.rustnote.app, chosen at scaffold time in step 2. On macOS that resolves to ~/Library/Application Support/com.rustnote.app, on Linux to ~/.local/share/com.rustnote.app, on Windows to a folder of the same name under %APPDATA%. The directory does not exist on first launch, hence create_dir_all. The full file is src-tauri/src/lib.rs, and it is shown in pieces throughout this page — the tag at the end collects it into one place.

A migration the database remembers

The table has to be created before the first INSERT — and never created twice. The cheapest honest way is SQLite’s own user_version pragma, an integer stored in the database file itself:

use rusqlite::Connection;

const MIGRATION_1: &str = "
CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL DEFAULT '',
    created_at INTEGER NOT NULL
);";

fn run_migrations(conn: &Connection) -> rusqlite::Result<()> {
    let version: i64 = conn.query_row("PRAGMA user_version", [], |row| row.get(0))?;
    if version < 1 {
        conn.execute_batch(MIGRATION_1)?;
        conn.pragma_update(None, "user_version", 1)?;
    }
    Ok(())
}

user_version starts at 0 on a fresh file. The first run applies the migration and writes 1; every run after that reads 1 and does nothing. Because the number lives inside the database file, it survives restarts — the second launch of the app skips straight past the migration. You can see the whole cycle in one process on an in-memory database:

use rusqlite::params;
use rusqlite::Connection;

const MIGRATION_1: &str = "
CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL DEFAULT '',
    created_at INTEGER NOT NULL
);";

fn run_migrations(conn: &Connection) -> rusqlite::Result<()> {
    let version: i64 = conn.query_row("PRAGMA user_version", [], |row| row.get(0))?;
    if version < 1 {
        conn.execute_batch(MIGRATION_1)?;
        conn.pragma_update(None, "user_version", 1)?;
    }
    Ok(())
}

fn main() -> Result<(), rusqlite::Error> {
    let conn = Connection::open_in_memory()?;
    run_migrations(&conn)?;
    run_migrations(&conn)?; // a second start reads version 1 and does nothing

    conn.execute(
        "INSERT INTO notes (title, body, created_at) VALUES (?1, '', 1792395356)",
        params!["Shopping list"],
    )?;

    let count: i64 = conn.query_row("SELECT count(*) FROM notes", [], |row| row.get(0))?;
    let version: i64 = conn.query_row("PRAGMA user_version", [], |row| row.get(0))?;
    println!("{count} note on disk, schema version {version}");
    Ok(())
}

1 note on disk, schema version 1

A grown-up migration runner keeps an ordered list of SQL files with checksums and applies them one by one — that is what sqlx does, and what step 6 of the axum track used. For one table, the pragma does the same job in twelve lines. When the schema needs a second change, the pattern extends: check for version < 2, apply MIGRATION_2, write 2.

The command that writes

create_note is the first command that touches shared state. It takes the connection through State, and it runs async. The use lines here are what the block needs to compile on its own; src-tauri/src/lib.rs itself gains use std::sync::Mutex;, use rusqlite::Connection;, and use tauri::{Manager, State}; at the top — Manager because the setup hook calls app.path and app.manage:

use std::sync::Mutex;

use rusqlite::Connection;
use tauri::State;

#[tauri::command]
async fn create_note(state: State<'_, Mutex<Connection>>, title: String) -> Result<i64, String> {
    let conn = state.lock().map_err(|e| e.to_string())?;
    let now = std::time::SystemTime::now()
        .duration_since(std::time::UNIX_EPOCH)
        .map_err(|e| e.to_string())?
        .as_secs() as i64;
    conn.execute(
        "INSERT INTO notes (title, body, created_at) VALUES (?1, '', ?2)",
        rusqlite::params![title, now],
    )
    .map_err(|e| e.to_string())?;
    Ok(conn.last_insert_rowid())
}

Three things are going on, and two of them are the compiler’s doing.

The Mutex is not a style choice. manage only accepts state that is Send + Sync + 'static, and rusqlite’s Connection is Send but not Sync — it wraps a RefCell, which cannot be shared between threads. Hand the bare connection to manage and the build fails on exactly that bound. The Mutex<Connection> satisfies it, and the lock also keeps two commands from interleaving statements on one connection.

The Result return type is equally mandatory for an async command that takes a borrowed parameter like State<'_, T>. Drop it and the command refuses to compile:

use std::sync::Mutex;

use rusqlite::Connection;
use tauri::State;

#[tauri::command]
async fn count_notes(state: State<'_, Mutex<Connection>>) -> i64 {
    let _conn = state.lock();
    0
}

fn main() {
    let _builder = tauri::Builder::default()
        .invoke_handler(tauri::generate_handler![count_notes]);
}

Run this in the Rust Playground

error[E0277]: async commands that contain references as inputs must return a `Result`
 --> src/main.rs:7:62
  |
7 | async fn count_notes(state: State<'_, Mutex<Connection>>) -> i64 {
  |                                                              ^^^ the trait `AsyncCommandMustReturnResult` is not implemented for `i64`
  |
help: the trait `AsyncCommandMustReturnResult` is implemented for `Result<A, B>`
 --> src/main.rs:7:62
  |
7 | async fn count_notes(state: State<'_, Mutex<Connection>>) -> i64 {
  |                                                              ^^^
  = note: required for the cast from `&i64` to `&dyn AsyncCommandMustReturnResult`

The trait name says it outright: an async command whose inputs contain references must return a Result. A second error about a lifetime follows in the same build; returning Result clears both. create_note does — every failure path maps into a String the way step 4’s divide did, so a database error reaches the front end as the promise rejection you already know how to catch.

The third thing is ordinary Rust: ?1 is a positional parameter, rusqlite::params! binds the values, and last_insert_rowid() returns the row’s id — the same integer SQLite assigned, handed back to the front end as the command’s Ok value. created_at stores seconds since the Unix epoch as an INTEGER, which sorts and compares naturally in SQL.

Registration is the same three-word change as before: create_note joins the list.

.invoke_handler(tauri::generate_handler![add, divide, stats, create_note])

The front end

src/App.tsx gains two pieces of state, one handler, and one row of UI:

const [title, setTitle] = useState("");
const [saved, setSaved] = useState("");

async function saveNote() {
  try {
    const id = await invoke<number>("create_note", { title });
    setSaved(`saved note #${id}`);
  } catch (e) {
    setSaved(`Error: ${e}`);
  }
}

<div className="row">
  <input
    value={title}
    onChange={(e) => setTitle(e.currentTarget.value)}
    aria-label="note title"
  />
  <button onClick={saveNote}>Save note</button>
</div>
<p>{saved}</p>

The invoke key is title, which is the camelCase form of the Rust parameter title — the same string, so nothing to trip over. The three demo commands from step 4 stay where they are; the test bench keeps running while the app grows real features around it.

See it work

npm run tauri dev, type a title into the new input, press Save note, and the line under the button reads saved note #1. On macOS, sqlite3 (shipped with the system) can look at what the command wrote:

$ sqlite3 "$HOME/Library/Application Support/com.rustnote.app/rustnote.db" "PRAGMA user_version;"
1
$ sqlite3 "$HOME/Library/Application Support/com.rustnote.app/rustnote.db" "SELECT id, title, body FROM notes;"
1|Shopping list|

Version 1 in the file header, one row with an empty body — the column the migration defaults and no UI fills yet. Close the window and run npm run tauri dev again: the migration log is silent because user_version is already 1, and the row is still there. The file in Application Support is the app’s memory now; nothing in the startup path touches it except the version check.

Where the code stands

The step is tagged tauri-ch04 in the examples repository:

git clone https://github.com/hackingwithrust/examples
cd examples/tauri-desktop-app
git checkout tauri-ch04
npm install
npm run tauri dev

If you have been following along, your tree is current: Cargo.toml, src-tauri/src/lib.rs, and src/App.tsx are the only files this step touched.

Step 6 reads the rows back: a list_notes command that turns the table into a Vec<Note> and carries it across the IPC boundary — where every field of Note needs the Serialize derive that step 4 introduced. The track overview keeps the full list current as articles land.