My desktop SSH client needs to remember saved sessions, pinned host keys, and window settings between launches, with no server and no network, so I reach for rusqlite local storage: a single SQLite file on disk, queried directly from Rust. rusqlite is the maintained SQLite binding for Rust, and with the bundled feature it compiles SQLite straight into your binary, so there is no system libsqlite3 to install on a user's machine and no version skew across platforms. The fix for durable, offline app state is one .db file in the per-user app data directory, opened through a Connection, with every write going through a prepared statement and bound parameters. Get the file path wrong or string-format your SQL and you will pay for it; both are easy to get right.
Why rusqlite instead of a JSON file or sled?
A flat JSON or TOML file is fine until you have concurrent writes, partial saves, or a schema that grows. The moment the app crashes mid-write you have a truncated config and an angry user. SQLite gives you atomic transactions, a real query language, and a WAL that survives a hard kill, all in a file you can copy and back up. sled and other pure-Rust key-value stores work well, but SQLite is one of the most widely deployed and well-tested storage engines available, and it ships everywhere. rusqlite wraps the C library with a safe API, and the bundled feature means I am not asking users to have the right libsqlite3 installed. This is the same local-first store I hinted at in building an offline SSH client with Tauri, Rust, and xterm.js: the host-key pinning and saved-sessions list both land in this database.
How do I add rusqlite so SQLite compiles in?
Add rusqlite with the bundled feature. Without it, rusqlite links against whatever SQLite the host happens to provide, which is a portability landmine on Windows and old Linux. With bundled, libsqlite3-sys vendors and compiles a known-good SQLite into your binary, so the only build requirement is a C compiler, which you already have if you are building Tauri.
[dependencies]
# bundled compiles SQLite into the binary -- no system libsqlite3
rusqlite = { version = "0.32", features = ["bundled"] }That single feature is the difference between a binary that runs on a clean machine and one that fails to start because the host's SQLite is too old or missing. Pin the version; rusqlite tracks the underlying SQLite release and a major bump can change the libsqlite3-sys requirement.
Where should the database file actually live?
Not next to the binary. Program Files on Windows and /usr/local on Linux are read-only for a normal user, and writing your database beside the executable is the classic mistake that works on the developer's machine and fails for everyone else. The database belongs in the per-user app data directory. In Tauri v2 the path resolver gives you exactly that: app.path().app_data_dir() returns a Result<PathBuf> that resolves to the OS-correct, writable, per-user location (FOLDERID_RoamingAppData on Windows, ~/Library/Application Support on macOS, $XDG_DATA_HOME on Linux), namespaced by your bundle identifier. The .path() method comes from the Manager trait, so that import has to be in scope. Create the directory if it does not exist, then open the file inside it.
use std::fs;
use rusqlite::Connection;
use tauri::{AppHandle, Manager}; // Manager brings .path() into scope
/// Open (or create) the app database in the per-user data directory.
pub fn open(app: &AppHandle) -> rusqlite::Result<Connection> {
// OS-correct, writable, per-user path -- never beside the binary.
let dir = app
.path()
.app_data_dir()
.expect("no app data dir on this platform");
fs::create_dir_all(&dir).expect("failed to create app data dir");
let conn = Connection::open(dir.join("sessions.db"))?;
// WAL survives a hard kill far better than the default rollback journal,
// and enforce foreign keys -- SQLite leaves them off by default.
conn.pragma_update(None, "journal_mode", "WAL")?;
conn.pragma_update(None, "foreign_keys", "ON")?;
Ok(conn)
}If you are not on Tauri, the directories crate gives you the same per-user resolution, though its API is honest about platforms where there is no home directory: ProjectDirs::from("com", "ryn", "sshclient") returns an Option<ProjectDirs>, so you unwrap or handle the None case before calling .data_dir() on it. Either way the rule holds: resolve a writable per-user path, never hard-code one beside the binary.
Migrations and safe CRUD with bound parameters
A migration here is just a CREATE TABLE IF NOT EXISTS run at startup; for a small app you do not need a migration framework. The non-negotiable rule for every read and write is bound parameters. Never format a value into a SQL string. The day a saved session has a host like O'Brien-box or a name with a quote in it, naive string concatenation either breaks the query or, if any of that input ever comes from outside, opens you to injection. rusqlite's params! macro binds values positionally and the database treats them as data, never as SQL.
use rusqlite::{params, Connection};
use serde::Serialize;
#[derive(Serialize)] // serialized straight back to the frontend by a command
pub struct Session {
pub id: i64,
pub label: String,
pub host: String,
pub port: u16,
pub username: String,
}
/// Run once at startup. Idempotent.
pub fn migrate(conn: &Connection) -> rusqlite::Result<()> {
conn.execute(
"CREATE TABLE IF NOT EXISTS sessions (
id INTEGER PRIMARY KEY,
label TEXT NOT NULL,
host TEXT NOT NULL,
port INTEGER NOT NULL DEFAULT 22,
username TEXT NOT NULL
)",
[],
)?;
Ok(())
}
/// Insert -- values are BOUND, never formatted into the SQL string.
pub fn add_session(conn: &Connection, s: &Session) -> rusqlite::Result<i64> {
conn.execute(
"INSERT INTO sessions (label, host, port, username)
VALUES (?1, ?2, ?3, ?4)",
params![s.label, s.host, s.port, s.username],
)?;
Ok(conn.last_insert_rowid())
}
/// Read -- query_map turns each row into a Session struct.
pub fn list_sessions(conn: &Connection) -> rusqlite::Result<Vec<Session>> {
let mut stmt = conn.prepare(
"SELECT id, label, host, port, username FROM sessions ORDER BY label",
)?;
let rows = stmt.query_map([], |row| {
Ok(Session {
id: row.get(0)?,
label: row.get(1)?,
host: row.get(2)?,
port: row.get(3)?,
username: row.get(4)?,
})
})?;
rows.collect() // Result<Vec<Session>>
}
pub fn delete_session(conn: &Connection, id: i64) -> rusqlite::Result<usize> {
conn.execute("DELETE FROM sessions WHERE id = ?1", params![id])
}Two details that trip people up. query_map returns an iterator of Result<T>, so collecting into a Result<Vec<T>> is the idiomatic way to surface the first error and otherwise hand back a clean Vec. And row.get(i) is generic over the target type, so the compiler infers u16 for the port column from the Session field; rusqlite implements FromSql for u16, and if a stored value does not fit you get a typed error rather than silent truncation.
How do I keep one connection alive across Tauri commands?
Opening a fresh Connection on every command is wasteful and breaks WAL's benefits. Open it once at setup and put it in Tauri's managed state. The catch is that rusqlite's Connection is Send but not Sync, so you cannot share a bare &Connection across threads; wrap it in a Mutex. I use the std Mutex here because SQLite calls are fast and synchronous, so there is nothing to gain from an async lock, and holding a std lock across a non-await section is exactly what it is for.
use std::sync::Mutex;
use rusqlite::Connection;
use tauri::Manager;
mod db;
// Connection is Send but not Sync, so wrap it in a Mutex for managed state.
pub struct Db(pub Mutex<Connection>);
#[tauri::command]
fn sessions(state: tauri::State<'_, Db>) -> Result<Vec<db::Session>, String> {
let conn = state.0.lock().map_err(|e| e.to_string())?;
db::list_sessions(&conn).map_err(|e| e.to_string())
}
fn main() {
tauri::Builder::default()
.setup(|app| {
let conn = db::open(app.handle())?;
db::migrate(&conn)?;
app.manage(Db(Mutex::new(conn))); // one connection, shared
Ok(())
})
.invoke_handler(tauri::generate_handler![sessions])
.run(tauri::generate_context!())
.expect("error while running tauri application");
}Now every command locks the same connection, reuses the same WAL, and the database is opened exactly once. The command returns Vec<db::Session> directly because Session derives Serialize, so Tauri hands the rows back to the frontend without a separate DTO. If you later need genuine concurrent readers, SQLite in WAL mode handles multiple readers with a single writer, but for a desktop tool one connection behind a Mutex is the simplest thing that is correct.
Keep the data on the user's disk, key it through bound parameters, and open the connection once. Everything else about local storage is detail.
What about secrets and encryption at rest?
Saved sessions are not very sensitive, but private-key passphrases and stored credentials are, and a plain SQLite file is readable by anything that can open the file. If you store secrets, encrypt the database at rest with SQLCipher, which rusqlite exposes through the sqlcipher feature. Swap features in Cargo.toml, then set the key with a PRAGMA as the very first statement after opening, before any query. A few rules I hold to:
- Enable the sqlcipher feature instead of bundled when you need encryption: rusqlite = { version = "0.32", features = ["sqlcipher"] }. The whole file becomes opaque on disk.
- Run PRAGMA key as the first statement after Connection::open, before any read or write, or the connection stays unencrypted.
- Never hardcode the key or store it in the same database. Derive it from an OS keychain entry (keyring crate) or a user passphrase, and zero it from memory after use; the database is only as safe as where the key lives.
- Treat the -wal and -shm sidecar files as part of the database -- they are encrypted too under SQLCipher, but they must travel with the main file if you copy it.
- Back up by copying the file while no write transaction is open, or use SQLite's online backup API; do not copy a -wal mid-checkpoint.
// With the `sqlcipher` feature enabled, key the database immediately.
let conn = Connection::open(dir.join("sessions.db"))?;
conn.pragma_update(None, "key", passphrase.as_str())?; // FIRST, before any query
conn.pragma_update(None, "journal_mode", "WAL")?;The Tauri side stays identical: the encrypted Connection still goes into managed state behind a Mutex, and your commands never know the difference. The encryption boundary is entirely between Connection::open and the PRAGMA key call. The same single-connection pattern keeps the system tray and settings windows reading from one source of truth, which pairs naturally with Tauri system tray integration when you want quick-connect entries in the tray menu.
That is the whole local-storage story for a desktop app: add rusqlite with bundled so SQLite ships in the binary, resolve a writable per-user path with app_data_dir, run an idempotent CREATE TABLE IF NOT EXISTS at startup, and route every read and write through prepared statements with bound parameters. Hold the connection in managed state behind a Mutex so it opens once, and reach for the sqlcipher feature the moment you store anything you would not want read off a stolen disk. It is a handful of functions, it works offline, and it has not lost a saved session on me yet. The rusqlite reference at https://docs.rs/rusqlite covers the rest of the API when your schema grows past one table.
