rusqlite — SQLite for Rust

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: rusqlite.

Setup

[dependencies]
rusqlite = { version = "0.32", features = ["bundled"] }

Features:

• bundled — compile SQLite from source, no system dep (recommended for portability)
• bundled-sqlcipher — SQLCipher (encrypted SQLite) — see databases/sqlcipher
• bundled-sqlcipher-vendored-openssl — SQLCipher with vendored OpenSSL
• chrono, time, uuid, url, serde_json — type integrations
• blob — blob I/O streaming
• array — query parameter as array
• loadable_extension — load runtime SQLite extensions
• vtab — virtual tables
• backup — online backup API
• functions — register custom SQL functions

Quick Start

use rusqlite::{Connection, Result, params};

fn main() -> Result<()> {
    let conn = Connection::open("wallet.db")?;

    conn.execute_batch("
        CREATE TABLE IF NOT EXISTS wallet (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            balance INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL
        );
        CREATE INDEX IF NOT EXISTS idx_wallet_created ON wallet(created_at);
    ")?;

    conn.execute(
        "INSERT INTO wallet (id, name, balance, created_at) VALUES (?1, ?2, ?3, ?4)",
        params!["abc123", "Main", 100_000, 1735689600i64],
    )?;

    let mut stmt = conn.prepare("SELECT id, name, balance FROM wallet WHERE balance > ?1")?;
    let rows = stmt.query_map([0i64], |row| {
        Ok(Wallet {
            id: row.get(0)?,
            name: row.get(1)?,
            balance: row.get(2)?,
        })
    })?;

    for wallet in rows {
        println!("{:?}", wallet?);
    }

    Ok(())
}

#[derive(Debug)]
struct Wallet {
    id: String,
    name: String,
    balance: i64,
}

Connection Patterns

// In-memory (great for tests)
let conn = Connection::open_in_memory()?;

// File path
let conn = Connection::open("data.db")?;

// With flags
use rusqlite::OpenFlags;
let conn = Connection::open_with_flags(
    "data.db",
    OpenFlags::SQLITE_OPEN_READ_ONLY | OpenFlags::SQLITE_OPEN_NO_MUTEX,
)?;

// Pragmas after open
conn.pragma_update(None, "journal_mode", "WAL")?;
conn.pragma_update(None, "synchronous", "NORMAL")?;
conn.pragma_update(None, "cache_size", -65536)?;        // 64MB cache
conn.pragma_update(None, "foreign_keys", "ON")?;
conn.pragma_update(None, "temp_store", "MEMORY")?;

For wallet apps, WAL mode is essential — concurrent reads + single writer without blocking.

Parameter Binding

// Positional
conn.execute("INSERT INTO t (a, b) VALUES (?1, ?2)", params![1, "hello"])?;

// Named
use rusqlite::named_params;
conn.execute(
    "INSERT INTO t (a, b) VALUES (:a, :b)",
    named_params! { ":a": 1, ":b": "hello" },
)?;

// Single param shortcut
conn.execute("DELETE FROM t WHERE id = ?", [42])?;

Always use parameters, never string-format SQL — SQL injection vulnerable.

Querying

// Single row, single column
let count: i64 = conn.query_row(
    "SELECT count(*) FROM wallet",
    [],
    |row| row.get(0),
)?;

// Single row, multiple columns
let (id, name): (String, String) = conn.query_row(
    "SELECT id, name FROM wallet WHERE id = ?",
    ["abc"],
    |row| Ok((row.get(0)?, row.get(1)?)),
)?;

// Optional row (handles "no rows")
use rusqlite::OptionalExtension;
let maybe_wallet: Option<Wallet> = conn
    .query_row(
        "SELECT id, name, balance FROM wallet WHERE id = ?",
        ["abc"],
        |row| Ok(Wallet {
            id: row.get(0)?,
            name: row.get(1)?,
            balance: row.get(2)?,
        }),
    )
    .optional()?;

// Iterate rows
let mut stmt = conn.prepare("SELECT id FROM wallet ORDER BY created_at")?;
let ids: Vec<String> = stmt
    .query_map([], |row| row.get(0))?
    .collect::<Result<Vec<_>, _>>()?;

Transactions

let tx = conn.transaction()?;
tx.execute("UPDATE wallet SET balance = balance - ? WHERE id = ?",
    params![1000, "from_id"])?;
tx.execute("UPDATE wallet SET balance = balance + ? WHERE id = ?",
    params![1000, "to_id"])?;
tx.commit()?;                                     // OR tx.rollback()? OR drop (auto-rollback)

For deferred transactions:

use rusqlite::TransactionBehavior;
let tx = conn.transaction_with_behavior(TransactionBehavior::Immediate)?;
// IMMEDIATE locks DB for write at tx start (vs DEFERRED at first write)

For wallet apps: use IMMEDIATE for transfer flows to avoid SQLITE_BUSY mid-transaction.

Custom Types — ToSql / FromSql

For domain types stored as native SQLite types (TEXT, INTEGER, BLOB):

use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef};
use rusqlite::Result;

#[derive(Debug, Clone, PartialEq, Eq, Hash)]
struct Address(String);

impl ToSql for Address {
    fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
        Ok(ToSqlOutput::from(self.0.as_str()))
    }
}

impl FromSql for Address {
    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
        let s = value.as_str()?;
        if !s.starts_with("bc1") {
            return Err(FromSqlError::InvalidType);
        }
        Ok(Address(s.to_string()))
    }
}

// Use directly
let addr: Address = conn.query_row(
    "SELECT address FROM utxo WHERE txid = ?",
    ["abc"],
    |row| row.get(0),
)?;

For enums:

#[derive(Debug, Clone, Copy)]
enum NetworkKind { Bitcoin, Testnet, Signet, Regtest }

impl ToSql for NetworkKind {
    fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
        let s = match self {
            NetworkKind::Bitcoin => "bitcoin",
            NetworkKind::Testnet => "testnet",
            NetworkKind::Signet => "signet",
            NetworkKind::Regtest => "regtest",
        };
        Ok(ToSqlOutput::from(s))
    }
}

impl FromSql for NetworkKind {
    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
        match value.as_str()? {
            "bitcoin" => Ok(NetworkKind::Bitcoin),
            "testnet" => Ok(NetworkKind::Testnet),
            "signet" => Ok(NetworkKind::Signet),
            "regtest" => Ok(NetworkKind::Regtest),
            _ => Err(FromSqlError::InvalidType),
        }
    }
}

JSON1 Support

// Stores JSON as TEXT, queryable via SQLite JSON1
conn.execute("CREATE TABLE labels (txid TEXT PRIMARY KEY, data TEXT)", [])?;

let label = serde_json::json!({
    "type": "tx",
    "label": "Coffee",
    "category": "food",
});

conn.execute(
    "INSERT INTO labels VALUES (?1, ?2)",
    params!["abc...", label.to_string()],
)?;

// Query via json_extract
let category: String = conn.query_row(
    "SELECT json_extract(data, '$.category') FROM labels WHERE txid = ?",
    ["abc..."],
    |row| row.get(0),
)?;

For BIP329 wallet labels: this is the perfect storage pattern.

Blob I/O (Streaming Large Binary)

use std::io::{Read, Write};

// Insert blob with size, then stream
conn.execute(
    "INSERT INTO files (name, content) VALUES (?, ZEROBLOB(?))",
    params!["doc.pdf", 1024 * 1024],          // 1 MB blob
)?;

let rowid = conn.last_insert_rowid();

// Open for write
let mut blob = conn.blob_open(rusqlite::DatabaseName::Main, "files", "content", rowid, false)?;
blob.write_all(&data)?;
drop(blob);                                    // close

// Open for read
let mut blob = conn.blob_open(rusqlite::DatabaseName::Main, "files", "content", rowid, true)?;
let mut buf = vec![0u8; 4096];
loop {
    let n = blob.read(&mut buf)?;
    if n == 0 { break; }
    process(&buf[..n]);
}

FTS5 Full-Text Search

conn.execute_batch("
    CREATE VIRTUAL TABLE tx_search USING fts5(
        txid, label, notes, tokenize='porter ascii'
    );
")?;

conn.execute(
    "INSERT INTO tx_search (txid, label, notes) VALUES (?, ?, ?)",
    params!["abc", "Coffee at Starbucks", "Morning latte"],
)?;

// Search
let mut stmt = conn.prepare("SELECT txid, label FROM tx_search WHERE tx_search MATCH ?")?;
let results = stmt
    .query_map(["coffee"], |row| {
        Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?))
    })?
    .collect::<Result<Vec<_>, _>>()?;

Migrations

Use rusqlite_migration (or refinery):

rusqlite_migration = "1.3"

use rusqlite_migration::{Migrations, M};

let migrations = Migrations::new(vec![
    M::up("CREATE TABLE wallet (id TEXT PRIMARY KEY, name TEXT)"),
    M::up("ALTER TABLE wallet ADD COLUMN balance INTEGER DEFAULT 0"),
    M::up("CREATE INDEX idx_balance ON wallet(balance)"),
]);

migrations.to_latest(&mut conn)?;

For production: keep migrations as separate .sql files, version-controlled.

Connection Pooling

For multi-threaded access (each thread needs own Connection — Connection is Send not Sync):

r2d2 = "0.8"
r2d2_sqlite = "0.25"

use r2d2::Pool;
use r2d2_sqlite::SqliteConnectionManager;

let manager = SqliteConnectionManager::file("wallet.db")
    .with_init(|c| {
        c.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")
    });

let pool = Pool::builder()
    .max_size(15)
    .build(manager)?;

// Per-task
let conn = pool.get()?;
let count: i64 = conn.query_row("SELECT count(*) FROM wallet", [], |row| row.get(0))?;

Async (tokio-rusqlite)

rusqlite is sync — wrapping in tokio-rusqlite keeps connection on a dedicated thread:

tokio-rusqlite = "0.6"

use tokio_rusqlite::Connection;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let conn = Connection::open("wallet.db").await?;

    let count = conn.call(|conn| {
        let n: i64 = conn.query_row("SELECT count(*) FROM wallet", [], |row| row.get(0))?;
        Ok(n)
    }).await?;

    println!("Count: {}", count);
    Ok(())
}

Each query runs on the dedicated thread — no async/await contention with the SQLite C API.

For full async ORM: use sqlx with sqlite driver (separate skill).

SQLCipher Integration

[dependencies]
rusqlite = { version = "0.32", features = ["bundled-sqlcipher"] }

let conn = Connection::open("wallet.db")?;
let key_hex: String = key_bytes.iter().map(|b| format!("{:02x}", b)).collect();
conn.pragma_update(None, "key", format!("x'{}'", key_hex))?;

// Verify key
conn.query_row("SELECT count(*) FROM sqlite_master", [], |_| Ok(()))?;

// Tune
conn.pragma_update(None, "journal_mode", "WAL")?;

See databases/sqlcipher for full SQLCipher details.

Custom SQL Functions (Rust → SQL)

features = ["functions"]

use rusqlite::functions::FunctionFlags;

conn.create_scalar_function(
    "btc_to_sats",
    1,
    FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
    |ctx| {
        let btc: f64 = ctx.get(0)?;
        Ok((btc * 100_000_000.0) as i64)
    },
)?;

let sats: i64 = conn.query_row(
    "SELECT btc_to_sats(0.001)",
    [],
    |row| row.get(0),
)?;
assert_eq!(sats, 100_000);

Backup API

features = ["backup"]

use rusqlite::backup::Backup;
use std::time::Duration;

let src = Connection::open("wallet.db")?;
let mut dst = Connection::open("wallet_backup.db")?;
let backup = Backup::new(&src, &mut dst)?;
backup.run_to_completion(5, Duration::from_millis(250), None)?;

For SQLCipher: backup target needs its own PRAGMA key after attach.

Testing

#[cfg(test)]
mod tests {
    use super::*;
    use rusqlite::Connection;

    fn test_db() -> Connection {
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch(include_str!("schema.sql")).unwrap();
        conn
    }

    #[test]
    fn test_insert_wallet() {
        let conn = test_db();
        conn.execute(
            "INSERT INTO wallet (id, name) VALUES (?, ?)",
            params!["1", "Main"],
        ).unwrap();

        let count: i64 = conn.query_row("SELECT count(*) FROM wallet", [], |r| r.get(0)).unwrap();
        assert_eq!(count, 1);
    }
}

Mobile / Cross-Compile

bundled feature requires C compiler for cross-compile target. cargo-ndk handles this:

# Android
cargo ndk -t arm64-v8a -o jniLibs build --release

# iOS
cargo build --release --target aarch64-apple-ios

For SQLCipher mobile: use bundled-sqlcipher-vendored-openssl to avoid system OpenSSL hassles.

Performance Tips

Tip	Why
PRAGMA journal_mode=WAL	Concurrent reads, faster writes
PRAGMA synchronous=NORMAL	Faster (still durable enough with WAL)
PRAGMA cache_size=-65536	64MB cache (negative = KB)
Use prepared statements (prepare_cached) for repeated queries	Skip SQL parsing each call
Wrap bulk inserts in transaction	100x speedup for many INSERTs
Use WITHOUT ROWID for tables with non-INTEGER PK	Smaller, faster
Index columns used in WHERE/ORDER BY	Avoid full table scan
Use EXPLAIN QUERY PLAN to verify index usage	Debug slow queries
Use INTEGER for sat amounts (not REAL/TEXT)	Exact arithmetic, no float drift
PRAGMA optimize periodically	Updates query planner stats

Anti-Patterns

Anti-pattern	Why it's bad	Correct approach
Concatenating SQL strings	Injection risk	Use params!
f64 for sat amounts	Float precision lost	Use i64 (sats fit easily)
Single Connection across threads	Not Sync	Pool with r2d2_sqlite
Connection per query	Slow, no PRAGMA persistence	Reuse connection
Missing transaction for bulk insert	100x slower	Wrap in transaction()
Forgetting WAL for mobile/multi-reader	SQLITE_BUSY errors	Always WAL for app DB
Storing large blobs in main row	Bloats every read	Use blob I/O streaming
prepare() in tight loop	Re-parses SQL each call	prepare_cached()
Custom enums as TEXT without FromSql	Stringly-typed	Implement ToSql/FromSql
Not handling SQLITE_BUSY in concurrent app	Random failures	Set busy_timeout or use immediate transactions

Troubleshooting

Symptom	Cause	Fix
database is locked	Multi-writer or long read	WAL + busy_timeout; reduce tx duration
Slow inserts	No transaction	Wrap in transaction()
no such function: json_extract	JSON1 not enabled	Built-in since SQLite 3.38; use bundled feature for new-enough version
Cross-compile fails (libsqlite3-sys)	C compiler missing for target	Use cargo-ndk or cross
iOS app size +500KB after add	bundled SQLite size	Acceptable; native SQLite alternative is smaller but version uncertain
OptionalExtension not found	Forgot use rusqlite::OptionalExtension	Import the trait
params![] macro not found	Forgot import	use rusqlite::params
Custom function called too often	Not marked DETERMINISTIC	Add FunctionFlags::SQLITE_DETERMINISTIC
Result<_, rusqlite::Error> everywhere	No ? operator	Use anyhow::Result or convert
Connection drop hangs	Pool config	Tune Pool::builder().connection_timeout(...)

When NOT to Use This Skill

Scenario	Use Instead
Encrypted SQLite (SQLCipher)	databases/sqlcipher
Server PostgreSQL/MySQL	respective skills
ORM (Diesel, SeaORM)	ORM-specific
Async-native ORM	sqlx
KMP shared DB	mobile/kotlin-multiplatform (SQLDelight)
Pure SQL knowledge	databases/sql-fundamentals