SQLite

Recipe	Crates	Categories
Create a SQLite database
Insert and select data
Using transactions

rusqlite provides an API to SQLite and gives access to advanced SQlite features.

Create a SQLite database

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

rusqlite::Connection::open⮳ will create the database if it doesn't already exist.

use std::fs;

use rusqlite::Connection;

pub fn main() -> anyhow::Result<()> {
    if !fs::exists("temp")? {
        fs::create_dir("temp")?;
    }
    let conn = Connection::open("temp/cats.db")?;

    conn.execute(
        "create table if not exists cat_colors (
                 id integer primary key,
                 name text not null unique
             )",
        (), // Empty list of parameters.
    )?;
    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)
                 )",
        (), // Empty list of parameters.
    )?;
    println!("Tables created.");
    Ok(())
}

Insert and select data

rusqlite::Connection::open⮳ will open the database cats created in the earlier recipe. This recipe inserts data into cat_colors and cats tables using the rusqlite::Connection::execute⮳ method of rusqlite::Connection⮳. First, the data is inserted into the cat_colors table. After a record for a color is inserted, rusqlite::Connection::last_insert_rowid⮳ method of rusqlite::Connection⮳ is used to get id of the last color inserted. This id is used while inserting data into the cats table. Then, the select query is prepared using the rusqlite::Connection::prepare⮳ method which gives a rusqlite::Statement⮳ struct. Then, query is executed using rusqlite::Statement::query_map⮳ method of rusqlite::Statement⮳

use std::collections::HashMap;

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

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

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

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

    for (color, catnames) in &cat_colors {
        conn.execute("INSERT INTO cat_colors (name) values (?1)", [
            &color.to_string()
        ])?;
        let last_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(), &last_id],
            )?;
        }
    }
    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 cats = stmt.query_map([], |row| {
        Ok(Cat {
            name: row.get(0)?,
            color: row.get(1)?,
        })
    })?;

    for cat in cats {
        println!("Found cat {:?}", cat);
    }

    Ok(())
}

Using transactions

rusqlite::Connection::open⮳ will open the cats.db database from the top recipe.

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

In the following example, colors add 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.

use std::fs;

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

pub fn main() -> Result<()> {
    if !fs::exists("temp")? {
        fs::create_dir("temp")?;
    }
    let mut conn = Connection::open("temp/cats.db")?;

    successful_tx(&mut conn)?;
    println!("Successful transaction.");

    let res = rolled_back_tx(&mut conn);
    assert!(res.is_err());
    println!(
        "Attempt to insert the same name in a unique column fails. The transaction was rolled-back."
    );

    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()?;
    Ok(())
}

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)", [&"lavender"])?;
    tx.execute("insert into cat_colors (name) values (?1)", [&"blue"])?;
    tx.execute("insert into cat_colors (name) values (?1)", [&"lavender"])?;
    tx.commit()?;
    Ok(())
}