How to Connect to SQLite from Rust

SQLite is a library, not a server. There is no host, port, or password: you open a file (or an in-memory database) and talk to it in-process. In Rust the standard way to do that is rusqlite, a safe wrapper around the SQLite C library. Around it sit an async adapter (tokio-rusqlite), a pooling pair (r2d2_sqlite), and the usual toolkit alternatives (sqlx, Diesel). This guide shows working code for each and covers what actually matters for an embedded database: the bundled-vs-system build decision, WAL mode, the database is locked problem, and a file-handling trap that silently hides bugs.

The options

One architectural note before you pick: SQLite calls are fast, local, and blocking by nature. There is no network wait to overlap, so a synchronous API is not a compromise here -- it is the honest interface. The async wrappers exist to avoid blocking a tokio runtime's worker threads, not to make SQLite faster.

rusqlite: the default

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

The bundled feature compiles a known-good SQLite (3.53.2 as of rusqlite 0.40.1) into your binary via libsqlite3-sys, so you need a C compiler at build time but no SQLite on the target system. Without bundled, rusqlite links against the system SQLite -- smaller build, but you inherit whatever version the OS ships, and old system SQLites silently lack features like RETURNING (3.35+). For applications, use bundled; skip it only when binary size or distro policy forces the system library.

use rusqlite::{Connection, params};
 
fn main() -> rusqlite::Result<()> {
    let conn = Connection::open("app.db")?;
 
    conn.execute(
        "CREATE TABLE IF NOT EXISTS users (
            id    INTEGER PRIMARY KEY,
            name  TEXT NOT NULL,
            email TEXT UNIQUE
        )",
        (),
    )?;
 
    conn.execute(
        "INSERT INTO users (name, email) VALUES (?1, ?2)",
        params!["Ada", "ada@example.com"],
    )?;
 
    let mut stmt = conn.prepare("SELECT id, name FROM users WHERE email = ?1")?;
    let mut rows = stmt.query_map(params!["ada@example.com"], |row| {
        Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?))
    })?;
 
    while let Some(row) = rows.next() {
        let (id, name) = row?;
        println!("{id}: {name}");
    }
    Ok(())
}

Parameters are ?1, ?2 positional or :name named -- never format values into the SQL string. For a single expected row, query_row is more direct than prepare + query_map.

The accidental-file-creation trap

Connection::open creates the database file if it does not exist. Typo the path and you get a brand-new empty database instead of an error -- your queries return no rows and nothing fails. When the file is supposed to already exist, open it with flags that exclude CREATE:

use rusqlite::{Connection, OpenFlags};
 
let conn = Connection::open_with_flags(
    "app.db",
    OpenFlags::SQLITE_OPEN_READ_WRITE, // no SQLITE_OPEN_CREATE
)?;
// -> Err(...) if app.db is missing, instead of a silent empty database

Connection-scoped pragmas: WAL and busy_timeout

Three pragmas belong right after every open. They are per-connection (except the WAL switch, which persists in the file) and fix the two most common SQLite problems in one stroke:

conn.pragma_update(None, "journal_mode", "WAL")?;   // readers no longer block the writer
conn.pragma_update(None, "synchronous", "NORMAL")?; // safe with WAL, much faster
conn.busy_timeout(std::time::Duration::from_millis(5000))?;

Without busy_timeout, a second connection that hits a locked database fails immediately with SQLITE_BUSY ("database is locked"). With it, SQLite retries for up to the timeout before giving up. WAL mode allows any number of readers concurrent with one writer -- but still exactly one writer at a time. That is a property of SQLite, not of any Rust crate.

Transactions as a performance win

Each standalone INSERT is its own transaction with its own fsync. Batch inserts inside one transaction are commonly 50-100x faster:

let tx = conn.transaction()?;
{
    let mut stmt = tx.prepare("INSERT INTO users (name, email) VALUES (?1, ?2)")?;
    for (name, email) in &rows {
        stmt.execute(params![name, email])?;
    }
}
tx.commit()?;

tokio-rusqlite: SQLite in an async service

Connection is Send but a live query borrows it, and blocking calls stall tokio worker threads. tokio-rusqlite moves the connection to a dedicated thread and hands you an async handle; you send closures to it:

[dependencies]
tokio-rusqlite = "0.7"

use tokio_rusqlite::Connection;
 
let conn = Connection::open("app.db").await?;
 
let count: i64 = conn
    .call(|conn| {
        Ok(conn.query_row("SELECT COUNT(*) FROM users", [], |r| r.get(0))?)
    })
    .await?;

Inside the closure you have a plain rusqlite::Connection, so everything above (pragmas, transactions, prepared statements) applies unchanged. The alternative is calling rusqlite inside tokio::task::spawn_blocking yourself -- that is essentially what this crate packages.

Pooling: mostly unnecessary

SQLite is in-process; there is no connection handshake to amortize. One writer connection (plus, in WAL mode, a handful of reader connections) is the correct shape, not a 20-connection pool -- more writers just queue on the write lock. If your framework expects a pool type, r2d2 0.8.10 + r2d2_sqlite 0.35.0 (or deadpool-sqlite 0.13.0 for async) work fine; set the pool small and keep WAL + busy_timeout on every connection via the pool's init hook.

sqlx: compile-time checked queries

sqlx ships its own SQLite support (enable sqlite for the bundled C library, or sqlite-unbundled to link the system one):

[dependencies]
sqlx = { version = "0.9", features = ["runtime-tokio", "sqlite"] }
tokio = { version = "1", features = ["full"] }

use sqlx::sqlite::SqlitePoolOptions;
 
let pool = SqlitePoolOptions::new()
    .max_connections(4)
    .connect("sqlite://app.db?mode=rwc")
    .await?;
 
let row: (i64, String) =
    sqlx::query_as("SELECT id, name FROM users WHERE email = ?1")
        .bind("ada@example.com")
        .fetch_one(&pool)
        .await?;

Note the inversion of rusqlite's trap: sqlx fails if the file is missing unless you opt into creation with ?mode=rwc in the URL (or SqliteConnectOptions::create_if_missing(true)). Failing loud is the better default; just know which behavior your driver has. The sqlx::query! macro variant checks your SQL against the actual database schema at compile time -- the feature that justifies sqlx's extra weight. One version note: sqlx 0.9 renamed several feature flags and introduced sqlx.toml config, so pre-0.9 tutorials often show feature names that no longer exist.

Diesel

Diesel's SQLite backend goes through libsqlite3-sys like rusqlite (add the returning_clauses_for_sqlite_3_35 feature to use RETURNING). You get a type-safe query DSL and migrations at the cost of schema macros and a learning curve. Worth it for schema-first applications; overkill for a config store or a CLI tool.

Common mistakes

SQLite databases are just files, which makes them easy to create and easy to lose track of. Mako connects to SQLite alongside PostgreSQL, MySQL, and the rest, with AI-powered autocomplete for exploring schemas you didn't write. Try it free at mako.ai.