Rusqlite

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

Anti-Patterns

Troubleshooting

When NOT to Use This Skill