How to use SQLite in a Rust program

Short answer: add rusqlite with the bundled feature, open a Connection to a file path, and run SQL with execute for statements that write and prepare plus query_map for statements that return rows. There is no server to start and no port to pick: the database is one file on disk, and SQLite itself is compiled into the binary.

No server, no URL, one dependency

Everything so far in this chapter talks to Postgres: a server process listening on a port, a URL to connect wrong, a pool checked out per query, async everywhere. Whole classes of programs never want any of that — a CLI tool saving its state between runs, a desktop app’s local cache, a script gluing two files together. For those, SQLite is the standard answer: the entire database engine is a library, the database is one file, and any process that opens the file can read and write it.

rusqlite is the crate that speaks it. Of its features, bundled is the one that matters:

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

Without bundled, rusqlite links against whatever libsqlite3 the machine happens to have — a build that succeeds on one laptop and fails on the next. bundled compiles a pinned SQLite from source into the binary, trading a longer first build for a program that runs anywhere.

The first program creates a table, writes two rows, and reads them back:

src/main.rs

use rusqlite::{params, Connection};

fn main() {
    let conn = Connection::open("groceries.db").expect("failed to open groceries.db");

    conn.execute("DROP TABLE IF EXISTS items", []).expect("drop failed");
    conn.execute(
        "CREATE TABLE items (
            id   INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            qty  INTEGER NOT NULL
        )",
        [],
    )
    .expect("create table failed");

    for (name, qty) in [("milk", 2), ("bread", 1)] {
        conn.execute("INSERT INTO items (name, qty) VALUES (?1, ?2)", params![name, qty])
            .expect("insert failed");
    }

    let mut stmt = conn
        .prepare("SELECT name, qty FROM items ORDER BY id")
        .expect("prepare failed");
    let rows = stmt
        .query_map([], |row| Ok((row.get::<_, String>(0)?, row.get::<_, i64>(1)?)))
        .expect("query failed");

    for row in rows {
        let (name, qty) = row.expect("row failed");
        println!("{name} x{qty}");
    }
}

milk x2
bread x1

Connection::open("groceries.db") opens a file, and creates it first if it does not exist — the same thing the sqlite3 command-line tool does with a new path. execute runs a statement that returns no rows and reports how many rows changed. The ?1 and ?2 in the SQL are numbered bind parameters, filled by params![name, qty] — the same idea as the $1 parameters sqlx sends to Postgres, and with the same reason to exist: values travel as values, never spliced into the SQL text.

Reading rows is a two-step shape. prepare compiles the SQL into a Statement; query_map runs it and applies a closure to each row, where row.get::<_, T>(index) reads one column as an explicit type. SQLite’s own types are few, and rusqlite maps them strictly: INTEGER reads as i64, TEXT as String, and asking for a column as a type it is not stored as is an error returned through the closure’s Result — which is why the closure returns Ok((...?, ...?)) and why each get ends in a ?.

The file is the database

The second connection is where SQLite stops looking like a library and starts looking like a database. Close the first connection by dropping it, reopen the file, and everything the first connection wrote is still there:

src/main.rs

use rusqlite::{params, Connection};

fn main() {
    let conn = Connection::open("groceries.db").expect("failed to open groceries.db");
    conn.execute("DROP TABLE IF EXISTS items", []).expect("drop failed");
    conn.execute(
        "CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT NOT NULL, qty INTEGER NOT NULL)",
        [],
    )
    .expect("create table failed");
    conn.execute("INSERT INTO items (name, qty) VALUES (?1, ?2)", params!["milk", 2])
        .expect("insert failed");
    drop(conn);

    let conn = Connection::open("groceries.db").expect("failed to open groceries.db");
    conn.execute("INSERT INTO items (name, qty) VALUES (?1, ?2)", params!["bread", 1])
        .expect("insert failed");

    let count: i64 = conn
        .query_row("SELECT count(*) FROM items", [], |row| row.get(0))
        .expect("count failed");
    println!("{count} items, written by two connections");
}

2 items, written by two connections

The first connection created the table and wrote one row, then closed. The second opened the same path, saw both, and added another. There is no pool and no connect step — opening the database is opening the file. That is also the deployment story: backing up a SQLite database means copying the file, and moving the database means moving it.

query_row is the shortcut for the common case of exactly one row — SELECT count(*), a lookup by primary key — and returns the row without the Statement bookkeeping.

Transactions, without the server

The transaction page described sqlx’s bargain: begin, commit, and a drop handler that rolls back anything left open. rusqlite offers the same bargain, with one visible difference — transaction() takes &mut, because a transaction owns the connection’s session:

src/main.rs

use rusqlite::Connection;

fn main() {
    let mut conn = Connection::open_in_memory().expect("open failed");
    conn.execute("CREATE TABLE t (x INTEGER)", []).expect("create failed");

    let tx = conn.transaction().expect("begin failed");
    tx.commit().expect("commit failed");
}

commit ends the transaction and keeps its writes; a Transaction that is dropped while still open rolls back, exactly as with sqlx. The &mut is not ceremony: one file allows one writer at a time, and a transaction holds that write lock until it ends.

open_in_memory is worth knowing alongside open — it makes a database that lives only in RAM, empty on every call, which makes it a good stand-in in tests where a real file would leak state between runs.

Which one, when

The two halves of this chapter are not competitors. SQLite with rusqlite is for data that belongs to one program on one machine: state files, caches, small tools, and tests that need a real database with no setup. Postgres with sqlx is for data that many clients share over a network — the moment more than one machine writes, a file stops being the right shape. The SQL itself is portable between them for everyday tables, so what this chapter built on Postgres — inserts that return ids, bind parameters, transactions — translates almost line for line.