Interacting with Databases in Rust using SQLite and Rusqlite

The rusqlite crate is the easiest way to interact with SQLite databases in Rust. It allows you to create tables, insert rows, query data, and handle results using type-safe Rust code.

When I needed to save user data in a Rust CLI app, I wanted something simple: no servers, no networking, just a file-based database. SQLite was perfect, and rusqlite made it easy. It felt low-overhead, fast, and very Rust-like with its safe, minimal API.

In this article, you will learn how to connect to a SQLite database, create tables, insert records, and query results, all using rusqlite.

Add rusqlite to Your Project

In Cargo.toml:

[dependencies]
rusqlite = { version = "0.30" }

This will allow you to use all the core features of SQLite in your Rust app.

Connect to a Database File

use rusqlite::Connection;

fn main() -> rusqlite::Result<()> {
    let conn = Connection::open("mydata.db")?;
    Ok(())
}

This opens or creates a file called mydata.db in your project directory.

For an in-memory database (no file):

let conn = Connection::open_in_memory()?;

Create a Table

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

This creates a user table if it does not exist.

Insert a Row

conn.execute(
    "INSERT INTO user (name, age) VALUES (?1, ?2)",
    (&"Alice", &30),
)?;

You can also use named parameters like :name, but position-based (?1, ?2) is simpler for small projects.

Query Data with Row Mapping

Use prepare and query_map:

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

for user in users {
    println!("{:?}", user?);
}

Define a matching struct:

#[derive(Debug)]
struct User {
    id: i32,
    name: String,
    age: i32,
}

Rust makes sure your fields match the data types and column order.

Use Parameters Safely

All values passed to execute or query_map are automatically escaped, so you do not need to worry about SQL injection.

Avoid building SQL strings manually with variables. Always use ?1, ?2, or :name placeholders.

Update and Delete Records

Update:

conn.execute(
    "UPDATE user SET age = ?1 WHERE name = ?2",
    (35, "Alice"),
)?;

Delete:

conn.execute("DELETE FROM user WHERE name = ?1", [&"Alice"])?;

These operations return the number of affected rows.

Handle Errors Gracefully

Use pattern matching to check for database errors:

match conn.execute("INSERT ...", params) {
    Ok(_) => println!("Insert successful"),
    Err(e) => eprintln!("Database error: {}", e),
}

This helps when dealing with duplicates, missing tables, or invalid data.

Optional: Use Transactions

For performance and safety, wrap multiple operations in a transaction:

let tx = conn.transaction()?;
tx.execute("INSERT INTO user ...", params)?;
tx.commit()?;

This ensures either all operations succeed or none are applied.

Summary

The rusqlite crate makes it easy to use SQLite in Rust. Whether you are building a desktop tool, a CLI app, or a simple prototype, it lets you store structured data with very little setup, fully embedded and fully safe.