🎓 A1Learner
🔍

Rust Querying SQLite

Querying SQLite reads rows back from a table. A query can shape each row into a Rust struct.

Rows become structs

query_map runs your closure once per row. The closure builds one Pet from each row.

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

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

Only the rows you ask for

A slot in the WHERE clause filters the rows. Only pets aged 3 or older come back here.

let mut stmt = conn.prepare("SELECT name FROM pet WHERE age >= ?1")?;
let names = stmt.query_map(params![3], |row| row.get::<_, String>(0))?;
for name in names {
    println!("{}", name?);
}

Read a column by name

get accepts a column name too. The name must appear in the SELECT list.

let pets = stmt.query_map([], |row| {
    Ok(Pet { id: row.get("id")?, name: row.get("name")?, age: row.get("age")? })
})?;

A missing row is an Err

query_row promises exactly one row. Nobody is not in the table. The promise fails as a value.

let age = conn.query_row(
    "SELECT age FROM pet WHERE name = ?1",
    params!["Nobody"],
    |row| row.get::<_, i64>(0),
);
println!("{:?}", age);

An UPDATE reports its work

execute returns the count of changed rows. One pet matched the name. One birthday was added.

let changed = conn.execute(
    "UPDATE pet SET age = age + 1 WHERE name = ?1",
    params!["Miso"],
)?;
println!("changed = {changed}");

Example

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

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

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])?;
    conn.execute("INSERT INTO pet (name, age) VALUES (?1, ?2)", params!["Pudding", 2])?;
    let mut stmt = conn.prepare("SELECT id, name, age FROM pet WHERE age >= ?1 ORDER BY age")?;
    let rows = stmt.query_map(params![3], |row| {
        Ok(Pet { id: row.get(0)?, name: row.get(1)?, age: row.get(2)? })
    })?;
    for pet in rows {
        let pet = pet?;
        println!("{} is {}", pet.name, pet.age);
    }
    Ok(())
}

Key points

  • Use prepare to turn SQL text into a Statement.
  • Use query_map on the statement to run the query and shape each row.
  • Inside the query_map closure, row.get(0) reads the first column.
  • Counting starts at 0.
  • It works just like array indexes.
  • row.get can take a column name instead, like row.get("name").
  • Names read better when a query returns many columns.
  • query_map hands you one Result per row.
  • A row with the wrong shape arrives as an Err.
  • It never crashes the loop.
  • Use query_row when you expect exactly one row.
  • No matching row gives an Err.
  • Its kind is QueryReturnedNoRows.
  • An UPDATE that reports 0 changed nothing.
  • The WHERE clause probably matched no row.
  • Querying reads table rows back into Rust values.

In this example, the program builds the same pet table as the last lesson. It adds a third pet this time. Pudding is 2 years old. The query asks only for pets aged 3 or more. It also sorts the rows by age. query_map turns each row into a Pet struct. The loop prints Miso first. Biscuit comes second. Pudding never leaves the database. The filter did that work. The Rust code did not.

Note

execute returns the number of rows a statement changed.

Try it Yourself

Edit the code, then press Run.

Running is simulated · Rust Playground

Exercise

query_map hands you one item for each row. What is each item?