rusqlite + SQLite Development Guide

Setup

[dependencies]
rusqlite = { version = "0.32", features = ["bundled"] }
dirs = "5.0"  # OS data directory

Connection

use rusqlite::{Connection, Result};

let conn = Connection::open("app.db")?;           // File-based
let conn = Connection::open_in_memory()?;         // In-memory (tests)

// OS-specific data dir
fn get_db_path() -> PathBuf {
    dirs::data_local_dir().unwrap_or_else(|| PathBuf::from("."))
        .join("myapp").join("app.db")
}
// Linux: ~/.local/share/myapp/app.db
// Windows: C:\Users\<user>\AppData\Local\myapp\app.db

// Initialize
let conn = Connection::open(&db_path)?;
conn.execute_batch("PRAGMA foreign_keys = ON;")?;
run_migrations(&conn)?;

CRUD

use rusqlite::params;

// INSERT
conn.execute("INSERT INTO entries (title) VALUES (?1)", params![title])?;
let id = conn.last_insert_rowid();

// SELECT (single)
conn.query_row("SELECT * FROM entries WHERE id = ?1", params![id], |row| {
    Ok(Entry { id: row.get(0)?, title: row.get(1)? })
})?

// SELECT (multiple)
let mut stmt = conn.prepare("SELECT * FROM entries LIMIT ?1")?;
let entries = stmt.query_map(params![limit], |row| {
    Ok(Entry { id: row.get(0)?, title: row.get(1)? })
})?.collect::<Result<Vec<_>>>()?;

// UPDATE
conn.execute("UPDATE entries SET title = ?1 WHERE id = ?2", params![title, id])?;

// DELETE
conn.execute("DELETE FROM entries WHERE id = ?1", params![id])?;

Transactions

let tx = conn.transaction()?;

tx.execute("INSERT INTO entries (title) VALUES (?1)", params![title])?;
let entry_id = tx.last_insert_rowid();

tx.execute("INSERT INTO tags (name) VALUES (?1)", params![tag])?;

tx.commit()?;
// Error before commit → auto rollback on drop

Named Parameters

use rusqlite::named_params;

conn.execute(
    "INSERT INTO entries (title, versioned) VALUES (:title, :versioned)",
    named_params! { ":title": title, ":versioned": versioned as i32 },
)?;

Migrations (PRAGMA user_version)

fn run_migrations(conn: &Connection) -> Result<()> {
    let version: i32 = conn.query_row("PRAGMA user_version", [], |r| r.get(0))?;

    if version < 1 {
        conn.execute_batch(include_str!("../../migrations/001_initial.sql"))?;
        conn.execute("PRAGMA user_version = 1", [])?;
    }
    if version < 2 {
        conn.execute_batch(include_str!("../../migrations/002_add_field.sql"))?;
        conn.execute("PRAGMA user_version = 2", [])?;
    }
    Ok(())
}

FTS5 Full-Text Search

-- Virtual table
CREATE VIRTUAL TABLE entries_fts USING fts5(title, code, memo, content='entry_versions', content_rowid='id');

-- Sync triggers
CREATE TRIGGER entry_versions_ai AFTER INSERT ON entry_versions BEGIN
    INSERT INTO entries_fts(rowid, title, code, memo) SELECT ev.id, e.title, ev.code, ev.memo
    FROM entry_versions ev JOIN entries e ON e.id = ev.entry_id WHERE ev.id = NEW.id;
END;

fn search(conn: &Connection, query: &str, limit: i64) -> Result<Vec<SearchResult>> {
    let fts_query = query.split_whitespace()
        .map(|w| format!("{}*", w)).collect::<Vec<_>>().join(" ");

    let mut stmt = conn.prepare(
        "SELECT ev.entry_id, e.title,
                snippet(entries_fts, 1, '<mark>', '</mark>', '...', 32) as code_snippet
         FROM entries_fts
         JOIN entry_versions ev ON entries_fts.rowid = ev.id
         JOIN entries e ON ev.entry_id = e.id
         WHERE entries_fts MATCH ?1 ORDER BY rank LIMIT ?2"
    )?;

    stmt.query_map(params![fts_query, limit], |row| {
        Ok(SearchResult { entry_id: row.get(0)?, title: row.get(1)?, snippet: row.get(2)? })
    })?.collect()
}

FTS5 Query Syntax

Syntax	Example	Meaning
Word	axum	Contains "axum"
Multiple	axum handler	Contains both (AND)
Prefix	hand*	Starts with "hand"
Phrase	"error handling"	Exact phrase
OR	axum OR leptos	Either one
NOT	axum NOT leptos	Has axum, not leptos

Error Handling

use rusqlite::Error as SqliteError;

pub enum DbError {
    Sqlite(SqliteError),
    NotFound(i64),
}

impl From<SqliteError> for DbError {
    fn from(e: SqliteError) -> Self { DbError::Sqlite(e) }
}

// Convert QueryReturnedNoRows to NotFound
conn.query_row(...).map_err(|e| match e {
    SqliteError::QueryReturnedNoRows => DbError::NotFound(id),
    _ => DbError::Sqlite(e),
})

SQLx (PostgreSQL) vs rusqlite

Item	rusqlite	SQLx
Compile-time check	No	query! macro
Connection	Sync (file)	Async (network)
Migrations	Manual (PRAGMA)	sqlx-cli
ENUM	No (use INTEGER)	Native
Full-text search	FTS5	tsvector
Use case	Single-user app	Multi-user SaaS