SQLite is a very lightweight embedded database. It does not require a special database management system for you to be able to use it. Databases created by SQLite are represented as files or as in memory database, and you don't need to connect to an external remote endpoint or a local socket connection to use a database. It serves a different target audience than traditional client-server database engines such as MySQL or PostgreSQL, and is the go-to solution for use cases where an application needs to store data locally but in a secure and efficiently retrievable manner. The Android platform is a heavy user of SQLite and allows mobile applications to store preferences or configurations for users within an application. It is also used by many desktop applications that need to store any kind of state with durability guarantees.

The Rust community provides us with a couple of options for connecting to and interacting with a SQLite database. We'll go with the rusqlite crate, which is available on crates.io at https://crates.io/crates/rusqlite. This crate supports SQLite version 3.6.8 and above. Its API cannot be considered an ORM, but can be considered a mid-level abstraction of what an ORM provides, as it helpfully hides many of the details of the actual SQLite API. In contrast to many other relational database systems, SQLite's type system is dynamic. This means that columns do not have types, but each individual value does. Technically, SQLite separates storage classes from data types, but this is mainly an implementation detail, and we can just think in terms of types without being too far from the truth.

The rusqlite crate provides the FromSql and ToSql traits for converting objects between SQLite and Rust types. It also provides the following implementations out of the box for most standard library types and primitives:

With the basics of the rusqlite crate covered, let's see it in action.

We'll create a new project by running cargo new rusqlite_demo. Our program takes a properly formatted Comma Separated Values (CSV) list of book data from the standard input, stores it in SQLite, and then retrieves a subset of the data using a filter SQL query. First, let's create our table creation and deletion QueryNStrings and our Book struct, which will store the data retrieved from the query:

// rusqlite_demo/src/main.rs

use std::io;
use std::io::BufRead;

use rusqlite::Error;
use rusqlite::{Connection, NO_PARAMS};

const CREATE_TABLE: &str = "CREATE TABLE books 
                            (id INTEGER PRIMARY KEY,
                            title TEXT NOT NULL,
                            author TEXT NOT NULL,
                            year INTEGER NOT NULL)";

const DROP_TABLE: &str = "DROP TABLE IF EXISTS books";

#[derive(Debug)]
struct Book {
    id: u32,
    title: String,
    author: String,
    year: u16
}

We have two constants, CREATE_TABLE and DROP_TABLE, defined, which contain raw SQL queries for creating a books table and deleting it, respectively. We then have the book struct, which contains the following fields:

• id: This acts as the primary key and can be used to distinguish books from one another when inserting a book into our books table
• title: The title of the book
• author: The author of the book
• year: The year of publication

Next, let's look at our main function:

// rusqlite_demo/src/main.rs

fn main() {
    let conn = Connection::open("./books").unwrap();
    init_database(&conn);
    insert(&conn);
    query(&conn);
}

First, we open our connection to our SQLite database by calling Connection::open and providing a path, "./books", to create the database in the current directory. Next, we call init_database(), passing a reference to conn, which is defined as follows:

fn init_database(conn: &Connection) {
    conn.execute(CREATE_TABLE, NO_PARAMS).unwrap();
}

We then call the insert method, passing our conn. Finally, we call the query method, which queries our books database.

Here's our insert function method:

fn insert(conn: &Connection) {
    let stdin = io::stdin();
    let lines = stdin.lock().lines();
    for line in lines {
        let elems = line.unwrap();
        let elems: Vec<&str> = elems.split(",").collect();
        if elems.len() == 4 {
            let _ = conn.execute(
                "INSERT INTO books (id, title, author, year) VALUES (?1, ?2, ?3, ?4)",
                &[&elems[0], &elems[1], &elems[2], &elems[3]],
            );
        }
    }
}

In insert, we first get a lock on stdout and iterate over the lines. Each line is split with commas. Following that, we call execute on conn, passing in an insert query string. In the query string, we use the template variables ?1, ?2, and so on, whose corresponding values are taken from the elems vector. If the number of elements collected is up to 4, we insert the book using the raw SQL query and provide the corresponding values from the elems Vec for template variables.

Next, our query function is defined as follows:

fn query(conn: &Connection) {
    let mut stmt = conn
        .prepare("SELECT id, title, author, year FROM books WHERE year >= ?1")
        .unwrap();
    let movie_iter = stmt
        .query_map(&[&2013], |row| Book {
            id: row.get(0),
            title: row.get(1),
            author: row.get(2),
            year: row.get(3),
        })
        .unwrap();

    for movie in movie_iter.filter_map(extract_ok) {
        println!("Found book {:?}", movie);
    }
}

The query function takes in conn, on which we call prepare, passing in the raw SQL query string. Here, we're filtering books that are greater than a given year. We store this query in stmt. Next, we call query_map on this type, passing in a reference to an array that contains just the number 2013, which represents the year we want to filter the books against. As you can see, the API is a bit uneasy here. The second argument to query_map is a closure that is a Row type. Within the closure, we extract the respective fields from the row instance and construct a Book instance out of it. This returns an iterator, which we store in movie_iter. Finally, we iterate over our movie_iter, filtering any failed values using the extract_ok helper method. This is defined as follows:

fn extract_ok(p: Result<Book, Error>) -> Option<Book> {
    if p.is_ok() {
        Some(p.unwrap())
    } else {
        None
    }
}

Then, we print the books. The full code is as follows:

// rusqlite_demo/src/main.rs

use std::io;
use std::io::BufRead;

use rusqlite::Error;
use rusqlite::{Connection, NO_PARAMS};

const CREATE_TABLE: &str = "CREATE TABLE IF NOT EXISTS books 
                            (id INTEGER PRIMARY KEY,
                            title TEXT NOT NULL,
                            author TEXT NOT NULL,
                            year INTEGER NOT NULL)";

#[derive(Debug)]
struct Book {
    id: u32,
    title: String,
    author: String,
    year: u16,
}

fn extract_ok(p: Result<Book, Error>) -> Option<Book> {
    if p.is_ok() {
        Some(p.unwrap())
    } else {
        None
    }
}

fn insert(conn: &Connection) {
    let stdin = io::stdin();
    let lines = stdin.lock().lines();
    for line in lines {
        let elems = line.unwrap();
        let elems: Vec<&str> = elems.split(",").collect();
        if elems.len() > 2 {
            let _ = conn.execute(
                "INSERT INTO books (id, title, author, year) VALUES (?1, ?2, ?3, ?4)",
                &[&elems[0], &elems[1], &elems[2], &elems[3]],
            );
        }
    }
}

fn init_database(conn: &Connection) {
    conn.execute(CREATE_TABLE, NO_PARAMS).unwrap();
}

fn query(conn: &Connection) {
    let mut stmt = conn
        .prepare("SELECT id, title, author, year FROM books WHERE year >= ?1")
        .unwrap();
    let movie_iter = stmt
        .query_map(&[&2013], |row| Book {
            id: row.get(0),
            title: row.get(1),
            author: row.get(2),
            year: row.get(3),
        })
        .unwrap();

    for movie in movie_iter.filter_map(extract_ok) {
        println!("Found book {:?}", movie);
    }
}

fn main() {
    let conn = Connection::open("./books").unwrap();
    init_database(&conn);
    insert(&conn);
    query(&conn);
}

We also have a books.csv file in the same directory. We can run it by running:

cargo run < books.csv

Here's the output of the program upon running:

This is nowhere near a representative real-world database-backed application and is just for demonstrating the usage of the library. A real-world application wouldn't be reading from the standard input and the querying routines would have better error handling.

This was a brief demonstration of how to use a SQLite database with Rust by using the rusqlite crate. The API is not very strongly typed, but currently it's the only solution we've got. Next, we'll look at one of the big brothers of SQLite, which is the PostgreSQL database management system.