SQLite

Operating system packages are required to compile and run the SQLite3 examples which use the rusqlite crate.

Debian / Ubuntu

sudo apt install sqlite3 libsqlite3-dev

Fedora

sudo dnf install sqlite sqlite-devel

Add these crates to your own project:

cargo add anyhow rusqlite

Create a SQLite database

Use the rusqlite crate to open SQLite databases. See crate for compiling on Windows.

Connection::open will create the database file cats.db in the current working directory if it doesn't already exist.

Then the two tables are created if they don't already exist. The color of a specific cat must be defined in the cat_colors table before a cat row is inserted.

use anyhow::Result;
use rusqlite::Connection;

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

    conn.execute(
        "create table if not exists cat_colors (
             id integer primary key,
             name text not null unique
         )",
        [],
    )?;
    conn.execute(
        "create table if not exists cats (
             id integer primary key,
             name text not null,
             color_id integer not null references cat_colors(id)
         )",
        [],
    )?;

    Ok(())
}

Insert and Select data

Connection::open will open the database cats created in the earlier recipe.

This example inserts data into cat_colors and cats tables using the execute method of Connection. First, the data is inserted into the cat_colors table. After a record for a color is inserted, last_insert_rowid method of Connection is used to get current_color_row_id of the last color inserted. This current_color_row_id is used while inserting data into the cats table. Then, the select query is prepared using the prepare method which gives a statement struct.

Then, a query is executed using query_map method of statement, and then the results are printed.

use anyhow::Result;
use rusqlite::Connection;
use std::collections::HashMap;

#[derive(Debug)]
struct Cat {
    name: String,
    color: String,
}

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

    // Delete all rows from the cats and cat_colors tables.
    conn.execute("delete from cats", [])?;
    conn.execute("delete from cat_colors", [])?;

    let mut cat_data = HashMap::new();
    cat_data.insert(String::from("Blue"), vec!["Tigger", "Sammy"]);
    cat_data.insert(String::from("Black"), vec!["Oreo", "Biscuit"]);

    for (color, catnames) in &cat_data {
        conn.execute(
            "INSERT INTO cat_colors (name) values (?1)",
            &[&color.to_string()],
        )?;
        let current_color_row_id: String = conn.last_insert_rowid().to_string();

        for cat in catnames {
            conn.execute(
                "INSERT INTO cats (name, color_id) values (?1, ?2)",
                &[&cat.to_string(), &current_color_row_id],
            )?;
        }
    }

    // Alias cats table as 'c', and cat_color table as 'cc'.
    let mut stmt = conn.prepare(
        "SELECT c.name, cc.name from cats c
         INNER JOIN cat_colors cc
         ON cc.id = c.color_id;",
    )?;

    let found_cats = stmt.query_map([], |row| {
        Ok(Cat {
            name: row.get(0)?,
            color: row.get(1)?,
        })
    })?;

    for cat_result in found_cats {
        if let Ok(this_cat) = cat_result {
            println!("Found cat {}, {}", this_cat.name, this_cat.color);
        }
    }

    Ok(())
}

Using transactions

Connection::open will open the cats.db database from the top recipe, and assumes the cat_colors table exists.

Begin a transaction with Connection::transaction. Transactions will roll back unless committed explicitly with Transaction::commit.

In the following example, colors are added to a table having a unique constraint on the color name. When an attempt to insert a duplicate color is made, the transaction rolls back.

At the end of successful_tx() the cat_colors table should have exactly two rows, with the colors 'lavender' and 'blue'.

At the end of rolled_back_tx(), the cat_colors table should still have just 'lavender' and 'blue', since the the DELETE and the INSERTs will not have been committed.

use anyhow::{Error, Result};
use rusqlite::Connection;

fn main() -> Result<()> {
    let mut conn = Connection::open("cats.db")?;

    successful_tx(&mut conn)?;
    println!("successful transaction is successful");

    let res = rolled_back_tx(&mut conn);
    assert!(res.is_err());
    println!("rolled back transaction is an error");

    Ok(())
}

fn successful_tx(conn: &mut Connection) -> Result<()> {
    let tx = conn.transaction()?;

    tx.execute("DELETE FROM cat_colors", [])?;
    tx.execute("INSERT INTO cat_colors (name) VALUES (?1)", &[&"lavender"])?;
    tx.execute("INSERT INTO cat_colors (name) VALUES (?1)", &[&"blue"])?;

    tx.commit().map_err(Error::new)
}

fn rolled_back_tx(conn: &mut Connection) -> Result<()> {
    let tx = conn.transaction()?;

    tx.execute("DELETE FROM cat_colors", [])?;
    tx.execute("INSERT INTO cat_colors (name) VALUES (?1)", &[&"grey"])?;
    tx.execute("INSERT INTO cat_colors (name) VALUES (?1)", &[&"black"])?;

    // Since this table has a UNIQUE constaint on color names, this will fail.
    tx.execute("INSERT INTO cat_colors (name) VALUES (?1)", &[&"grey"])?;

    tx.commit()?;
    Ok(())
}

Since this program uses anyhow for error handling, the rustqlite::Result returned by the tx.commit() must be converted to a anyhow::Result. This can be done in a couple different ways as shown by the two transaction functions, and they are both equivalent to this code:

#![allow(unused)]
fn main() {
match tx.commit() {
    Ok(x) => Ok(x),
    Err(err) => Err(anyhow::Error::new(err)),
}
}