SQLite

Create a SQLite database

Use therusqlite crate to open SQLite databases. Seecrate for compiling on Windows.

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

use rusqlite::{Connection, Result};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 databasecats created in the earlier recipe.This recipe inserts data intocat_colors andcats tables using theexecute method ofConnection. First, the data is inserted into thecat_colors table. After a record for a color is inserted,last_insert_rowid method ofConnection is used to getid of the last color inserted. Thisid is used while inserting data into thecats table. Then, the select query is prepared using theprepare method which gives astatement struct. Then, query is executed usingquery_map method ofstatement.

use rusqlite::{params, Connection, Result};use std::collections::HashMap;#[derive(Debug)]struct Cat {    name: String,    color: String,}fn main() -> Result<()> {    let conn = Connection::open("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],        )?;        let last_id = conn.last_insert_rowid();        for cat in catnames {            conn.execute(                "INSERT INTO cats (name, color_id) values (?1, ?2)",                params![cat, 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 {        if let Ok(found_cat) = cat {            println!(                "Found cat {:?} {} is {}",                 found_cat,                found_cat.name,                found_cat.color,                );        }    }    Ok(())}

Using transactions

Connection::open will open thecats.db database from the top recipe.

Begin a transaction withConnection::transaction. Transactions willroll back unless committed explicitly withTransaction::commit.

In the following example, colors add to a table havinga unique constraint on the color name. When an attempt to inserta duplicate color is made, the transaction rolls back.

use rusqlite::{Connection, Result};fn main() -> Result<()> {    let mut conn = Connection::open("cats.db")?;    successful_tx(&mut conn)?;    let res = rolled_back_tx(&mut conn);    assert!(res.is_err());    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()}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()}