Rust SQLite Tutorial: rusqlite From cargo new to Transactions (Tested)

Ninad Pathak

October 7, 2026

Rust doesn’t ship an SQLite module, so the first step is picking a crate. rusqlite is the crate to start with, and one of its feature flags decides whether it builds on a machine without SQLite installed. In this article I’ll walk you through a small rusqlite program from cargo new to transactions, with the output from a real run.

TLDR

Add the crate with cargo add rusqlite --features bundled. bundled compiles SQLite into your program, so there’s nothing else to install.
Open a file with Connection::open("company.db"). It’s created if it doesn’t exist.

Run statements with conn.execute(sql, params![…]), using ?1, ?2 placeholders.
Read rows with prepare and query_map, or one value with query_row.
Group writes with conn.transaction() and tx.commit().

What is rusqlite and what will we build?

“Rusqlite is an ergonomic wrapper for using SQLite from Rust” (rusqlite README). It calls SQLite’s C library directly, and returns errors as rusqlite::Error values that work with ?.

Its bundled feature “uses a bundled version of SQLite”, which the README calls “a good option for cases where linking to SQLite is complicated, such as Windows”. Without it, rusqlite links to the SQLite library already installed on the system.

To show you the main operations, I will build a small company database with departments and employees.

Prerequisites

Requirement	Version tested
Rust and Cargo	1.99.0
rusqlite	0.40.2, with `libsqlite3-sys` 0.38.2 (bundled SQLite 3.53.2)
C compiler	Needed by `bundled` to compile SQLite. The `rust:slim` image already has one

Building the company database

The program is one file, built up step by step.

Step 1: Create the project and add rusqlite

$ cargo new company
    Creating binary (application) `company` package
$ cargo add rusqlite --features bundled
      Adding rusqlite v0.40.2 to dependencies

The rest of the steps are sections of src/main.rs, shown in full after Step 7.

Step 2: Open the database and create tables

Connection::open creates company.db if it’s missing. execute_batch runs several statements in one call. PRAGMA foreign_keys = ON is per connection, because SQLite ships with foreign key checks off.

Step 3: Insert rows with parameters

params! passes values for the ?1, ?2 placeholders, so they’re never pasted into the SQL text. last_insert_rowid() returns the id SQLite assigned to the last row.

Step 4: Query rows into a struct

prepare compiles a query once, and query_map turns each row into a Rust value. row.get(0) converts the column to whatever type the field expects.

Step 5: Read a single value

query_row returns the first row. For a lookup that might find nothing, .optional() turns “no rows” into None instead of an error.

Step 6: Use a transaction for many inserts

conn.transaction() starts a transaction. The prepared insert runs once per name, and tx.commit() writes them all together. If the function returns early with an error, the transaction is rolled back when it’s dropped.

Step 7: Handle errors

The last insert points at department 42, which doesn’t exist, so the foreign key check rejects it. Matching on the Result prints the error instead of stopping the program.

The complete src/main.rs:

use rusqlite::{params, Connection, OptionalExtension, Result};

struct Employee {
    id: i64,
    name: String,
    department: String,
}

fn main() -> Result<()> {
    // Step 2: open (or create) the database file
    let mut conn = Connection::open("company.db")?;
    conn.execute_batch(
        "PRAGMA foreign_keys = ON;
         CREATE TABLE IF NOT EXISTS departments (
             id INTEGER PRIMARY KEY,
             name TEXT NOT NULL UNIQUE
         );
         CREATE TABLE IF NOT EXISTS employees (
             id INTEGER PRIMARY KEY,
             name TEXT NOT NULL,
             dept_id INTEGER NOT NULL REFERENCES departments(id)
         );",
    )?;

    // Step 3: insert rows with parameters
    conn.execute("INSERT INTO departments (name) VALUES (?1)", params!["Engineering"])?;
    let engineering = conn.last_insert_rowid();
    conn.execute("INSERT INTO departments (name) VALUES (?1)", params!["Sales"])?;
    let sales = conn.last_insert_rowid();
    conn.execute(
        "INSERT INTO employees (name, dept_id) VALUES (?1, ?2), (?3, ?4)",
        params!["Ada", engineering, "Grace", sales],
    )?;
    println!("departments: {engineering}, {sales}");

    // Step 4: query rows into a struct
    let mut stmt = conn.prepare(
        "SELECT e.id, e.name, d.name FROM employees e JOIN departments d ON d.id = e.dept_id ORDER BY e.id",
    )?;
    let employees = stmt.query_map([], |row| {
        Ok(Employee { id: row.get(0)?, name: row.get(1)?, department: row.get(2)? })
    })?;
    for employee in employees {
        let e = employee?;
        println!("#{} {} ({})", e.id, e.name, e.department);
    }
    drop(stmt);

    // Step 5: a single value, or None when there is no row
    let count: i64 = conn.query_row("SELECT count(*) FROM employees", [], |row| row.get(0))?;
    let missing: Option<String> = conn
        .query_row("SELECT name FROM employees WHERE id = ?1", [99], |row| row.get(0))
        .optional()?;
    println!("employees: {count}, id 99: {missing:?}");

    // Step 6: a transaction for many inserts
    let tx = conn.transaction()?;
    {
        let mut insert = tx.prepare("INSERT INTO employees (name, dept_id) VALUES (?1, ?2)")?;
        for name in ["Linus", "Margaret", "Barbara"] {
            insert.execute(params![name, engineering])?;
        }
    }
    tx.commit()?;
    let count: i64 = conn.query_row("SELECT count(*) FROM employees", [], |row| row.get(0))?;
    println!("employees after transaction: {count}");

    // Step 7: errors come back as rusqlite::Error
    match conn.execute("INSERT INTO employees (name, dept_id) VALUES (?1, ?2)", params!["Nobody", 42]) {
        Ok(_) => println!("inserted"),
        Err(e) => println!("error: {e}"),
    }

    let version: String = conn.query_row("SELECT sqlite_version()", [], |row| row.get(0))?;
    println!("SQLite version: {version}");
    Ok(())
}

Running it

cargo run builds the program, compiling SQLite on the first build, and runs it:

$ cargo run --quiet
departments: 1, 2
#1 Ada (Engineering)
#2 Grace (Sales)
employees: 2, id 99: None
employees after transaction: 5
error: FOREIGN KEY constraint failed
SQLite version: 3.53.2

The versions Cargo resolved, from Cargo.lock:

name = "rusqlite"
version = "0.40.2"
name = "libsqlite3-sys"
version = "0.38.2"

Running it a second time stops at the first department insert, because name is UNIQUE and Engineering already exists. The ? passes the error up, and main returns it:

$ cargo run --quiet
Error: SqliteFailure(Error { code: ConstraintViolation, extended_code: 2067 }, Some("UNIQUE constraint failed: departments.name"))

Delete company.db to start again, or use INSERT … ON CONFLICT DO NOTHING.

Common problems

FOREIGN KEY constraint failed: a row points at a parent that doesn’t exist. It appeared in the first run.

UNIQUE constraint failed: the row repeats a unique value, as on the second run above.
Linker errors mentioning sqlite3 when building without bundled: the system SQLite library or its headers aren’t installed. Turn on bundled, or install the library. This wasn’t reproduced here.
database is locked: another connection is writing. See database is locked.

Conclusion

Start with rusqlite and its bundled feature when you need SQLite in Rust. Use execute with params! for writes and query_map for reads. Group many writes with transaction(). If you want async queries checked against your schema at compile time, sqlx is the other common choice (not tested here). For SQLite-compatible hosting, see libSQL.