SQLite in Rust, and how to not misuse it.

12.1Picking a crate

There's no built-in data layer; you choose one, the way you'd pick between raw ADO.NET, Dapper, and EF Core.

This chapter uses rusqlite for the examples because it's the most direct and the concepts transfer; the best practices at the end apply whichever you pick.

12.2The basics — connect, execute, query

Open a connection, run statements, read rows. Note the row-mapping closure: it's the manual equivalent of an EF projection or a Dapper map.

use rusqlite::{Connection, params};

let conn = Connection::open("mail.db")?;

conn.execute(
    "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)",
    [],
)?;

conn.execute("INSERT INTO users (name) VALUES (?1)", params![name])?;

let mut stmt = conn.prepare("SELECT id, name FROM users WHERE id = ?1")?;
let user = stmt.query_row(params![id], |row| {
    Ok(User { id: row.get(0)?, name: row.get(1)? })
})?;

12.3Always parameterize — never format SQL

The single most important rule, identical to .NET: pass values as parameters (?1, or named :name), never by building a SQL string. String interpolation is a SQL-injection hole and defeats statement caching.

// string-built SQL: injectable
var sql = $"SELECT * FROM users " +
          $"WHERE name = '{name}'";

// value is bound, never concatenated
conn.query_row(
    "SELECT id FROM users WHERE name = ?1",
    params![name], |r| r.get(0))?;

12.4Transactions — for atomicity and speed

Wrap related writes in a transaction. Beyond all-or-nothing semantics (like a SqlTransaction / TransactionScope), it's a massive throughput win: without one, SQLite commits — and fsyncs — after every statement. A thousand inserts inside one transaction can be orders of magnitude faster than a thousand bare inserts.

let tx = conn.transaction()?;
for u in &users {
    tx.execute("INSERT INTO users (name) VALUES (?1)", params![u.name])?;
}
tx.commit()?;     // dropping it without commit rolls back — RAII again

12.5The pragmas that matter

SQLite's defaults are conservative. A few PRAGMA settings, applied once per connection at open time, are what most "best practices" guides are really about.

conn.execute_batch("
    PRAGMA journal_mode = WAL;      -- readers don't block the writer
    PRAGMA synchronous = NORMAL;    -- safe with WAL, much faster than FULL
    PRAGMA foreign_keys = ON;       -- enforce FKs (off by default!)
    PRAGMA busy_timeout = 5000;     -- wait 5s on a locked db, don't fail instantly
")?;

12.6One writer, and a connection pool

A rusqlite::Connection isn't Sync — you can't share one across threads (the compiler enforces this; ch. 2). The standard answer is a pool: r2d2 with r2d2_sqlite, the role a DbContext-per-request or an ADO.NET connection pool plays.

use r2d2_sqlite::SqliteConnectionManager;

let manager = SqliteConnectionManager::file("mail.db");
let pool = r2d2::Pool::new(manager)?;

// per request / per task: check one out, use it, return it on drop
let conn = pool.get()?;
conn.execute("INSERT INTO users (name) VALUES (?1)", params![name])?;

12.7Schema migrations

For evolving the schema over releases — the EF Core Migrations equivalent — use rusqlite_migration or refinery with rusqlite, or sqlx migrate if you're on sqlx. Each tracks which versioned migrations have run and applies the rest at startup.