rusqlite

What this skill does

# rusqlite — SQLite for Rust

> **Deep Knowledge**: Use `mcp__documentation__fetch_docs` with technology: `rusqlite`.

## Setup

```toml
[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

```rust
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

```rust
// 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

```rust
// 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

```rust
// 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

```rust
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:

```rust
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):

```rust
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:

```rust
#[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

```rust
// 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)

```rust
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

```rust
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_