Skip to content
rusthacks.dev
Databases

Rust and SQLite With Rusqlite and SQLx Compared for New Code

rusqlite is a thin sync wrapper over SQLite and SQLx adds async and compile-time checks. Choose by whether your program is async and where the database file lives.

3 min read
Summarize in AI
Flat illustration for the post “Rust and SQLite With Rusqlite and SQLx Compared for New Code”, composed around database.

SQLite is a library, not a server, and that changes the usual database questions. There is no network, no pool to protect a remote machine, and one writer at a time. So the choice between the two Rust crates is smaller than it looks: it is mostly about whether you live in async code. This post compares them on a task and lists the SQLite specific settings that matter in both. It relates to the PostgreSQL oriented SQLx explained.

rusqlite 0.32, sqlx 0.8, Rust 1.90.

rusqlite

Cargo.tomlTOML
[dependencies]
rusqlite = { version = "0.32", features = ["bundled"] }
rust
use rusqlite::{params, Connection};

fn main() -> rusqlite::Result<()> {
    let conn = Connection::open_in_memory()?;
    conn.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT NOT NULL)", [])?;
    conn.execute("INSERT INTO notes (body) VALUES (?1)", params!["first"])?;

    let mut stmt = conn.prepare("SELECT id, body FROM notes")?;
    let rows = stmt.query_map([], |r| Ok((r.get::<_, i64>(0)?, r.get::<_, String>(1)?)))?;
    for row in rows {
        println!("{:?}", row?);
    }
    Ok(())
}

It prints (1, "first"). Everything is synchronous and runs on the calling thread. bundled compiles the SQLite C source, which needs a C compiler but removes the dependency on whatever SQLite the machine has.

SQLx

rust
use sqlx::sqlite::SqlitePoolOptions;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePoolOptions::new().max_connections(1).connect("sqlite::memory:").await?;
    sqlx::query("CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT NOT NULL)")
        .execute(&pool)
        .await?;
    sqlx::query("INSERT INTO notes (body) VALUES (?)").bind("first").execute(&pool).await?;
    let (id, body): (i64, String) = sqlx::query_as("SELECT id, body FROM notes").fetch_one(&pool).await?;
    println!("{id} {body}");
    Ok(())
}

Note max_connections(1): every connection to :memory: is its own empty database. With a file, a larger pool is fine for reads.

Differences that matter

rusqlitesqlx
APIsync, thinasync, pooled
Compile-time SQL checknoyes, against a dev database
Migrationsexternal cratebuilt in
Extension accesscustom functions, hooks, backup API, blobsfewer low level hooks
FitsCLI, desktop, embedded, sync servicesasync web services

Settings for either

Turn on WAL and set a timeout so readers do not block the writer and a busy database waits instead of failing: see SQLite WAL mode from Rust. Enable foreign keys explicitly, since SQLite has them off by default: PRAGMA foreign_keys = ON; per connection.

Pick

In an async service already using SQLx for PostgreSQL, SQLx for SQLite keeps one API and one migration system. In a program with no async runtime, adding Tokio just to talk to SQLite is cost without benefit: use rusqlite. If you need a feature like the online backup API or user defined functions, rusqlite exposes them directly.

Limits

SQLite on a network file system corrupts, and multi-process writers need busy_timeout. For multi-writer workloads at scale, you want a server database. See the project docs for rusqlite and SQLite's own guide, Write-Ahead Logging.

Share

Keep reading

Get new posts by email

We send one email when a new post is published. No spam.

Unsubscribe in one click. No spam, ever.