🎓 A1Learner
🔍

Rust SQLite Databases

SQLite is a small database that lives in one file. The rusqlite crate lets a Rust program use SQLite.

Open a database in memory

open_in_memory makes a fresh, empty database. It lives only while the program runs.

let conn = Connection::open_in_memory().unwrap();

Create a table

execute runs one statement. CREATE TABLE names each column and its type.

conn.execute(
    "CREATE TABLE pet (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)",
    (),
).unwrap();

Insert a row with params

The ?1 and ?2 are empty slots. params! fills them with values, in order.

conn.execute(
    "INSERT INTO pet (name, age) VALUES (?1, ?2)",
    params!["Miso", 3],
).unwrap();

Named slots read better

A slot can carry a name instead of a number. named_params! fills each named slot.

conn.execute(
    "INSERT INTO pet (name, age) VALUES (:name, :age)",
    named_params! { ":name": "Biscuit", ":age": 5 },
).unwrap();

Ask a question, get one value

query_row fetches a single row. Here it counts the pets in the table.

let count: i64 = conn.query_row("SELECT COUNT(*) FROM pet", (), |row| row.get(0)).unwrap();
println!("pets = {count}");

Example

// Cargo.toml: rusqlite = { version = "0.40", features = ["bundled"] }
use rusqlite::{params, Connection, Result};

fn main() -> Result<()> {
    let conn = Connection::open_in_memory()?;
    conn.execute(
        "CREATE TABLE pet (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)",
        (),
    )?;
    conn.execute("INSERT INTO pet (name, age) VALUES (?1, ?2)", params!["Miso", 3])?;
    conn.execute("INSERT INTO pet (name, age) VALUES (?1, ?2)", params!["Biscuit", 5])?;
    let count: i64 = conn.query_row("SELECT COUNT(*) FROM pet", (), |row| row.get(0))?;
    println!("pets = {count}");
    let oldest: String = conn.query_row(
        "SELECT name FROM pet ORDER BY age DESC LIMIT 1",
        (),
        |row| row.get(0),
    )?;
    println!("oldest = {oldest}");
    Ok(())
}

Key points

  • Add the crate in Cargo.toml: rusqlite = { version = "0.40", features = ["bundled"] }.
  • The bundled feature builds SQLite into your program.
  • No database software needs installing first.
  • Use Connection::open("pets.db") to open a database in a file.
  • Use Connection::open_in_memory() for a throwaway database in memory.
  • The in-memory one vanishes when the program ends.
  • Use execute to run one SQL statement.
  • CREATE TABLE and INSERT are statements.
  • execute returns how many rows the statement changed.
  • Always pass values with params! or named_params!.
  • Never paste values into SQL text with format!.
  • Pasted text can carry extra SQL commands.
  • A bound value cannot.
  • Most rusqlite calls return rusqlite::Result.
  • Write fn main() -> Result<()> and database errors can travel up with ?.
  • Your program reads and writes the database file itself.
  • That is why it fits small apps, command-line tools and tests so well.
  • A database keeps data in tables.
  • A query asks a table a question and gets back only matching rows.
  • SQLite keeps a whole database in a single file.

In this example, the program opens a database in memory. It creates a pet table with three columns. Two inserts add Miso and Biscuit. Each insert passes its values with params!. COUNT(*) asks how many rows the table holds. The answer is 2. The last query sorts the pets by age. ORDER BY age DESC puts the oldest first. LIMIT 1 keeps just that one row. So the program prints Biscuit as the oldest pet.

Note

SQLite is not a server.

Try it Yourself

Edit the code, then press Run.

Running is simulated · Rust Playground

Exercise

Why should a name typed by a user never be pasted into SQL text with format!?