Accessing a SQLite database

The source code for this section is found in the sqlite_example project. To run it, open its folder and type in cargo run.

This will create the sales.db file in the current folder. This file contains a SQLite database. Then, it will create the Products and Sales tables in this database, it will insert a row into each of these tables, and it will perform a query on the database. The query asks for all the sales, joining each of them with its associated product. For each extracted row, a line will be printed onto the console, showing the timestamp of the sale, the weight of the sale, and the name of the associated product. As there is only one sale in the database, you will see just the following line printed:

At instant 1234567890, 7.439 Kg of pears were sold. 

This project only uses the rusqlite crate. Its name is a contraction of Rust SQLite. To use this crate, the Cargo.toml file must contain the following line:

rusqlite = "0.23"

Implementing the project

Let's look at how the code for the sqlite_example project works. The main function is quite simple:

fn main() -> Result<()> {
    let conn = create_db()?;
    populate_db(&conn)?;
    print_db(&conn)?;
    Ok(())
}

It invokes create_db to open or create a database with its empty tables, and to open and return a connection to this database.

Then, it invokes populate_db to insert rows into the tables of the database referred to by that connection.

Then, it invokes print_db to execute a query on this database and prints the data extracted by that query.

The create_db function is long but easy to understand:

fn create_db() -> Result<Connection> {
    let database_file = "sales.db";
    let conn = Connection::open(database_file)?;
    let _ = conn.execute("DROP TABLE Sales", params![]);
    let _ = conn.execute("DROP TABLE Products", params![]);
    conn.execute(
        "CREATE TABLE Products (
            id INTEGER PRIMARY KEY,
            category TEXT NOT NULL,
            name TEXT NOT NULL UNIQUE)",
        params![],
    )?;
    conn.execute(
        "CREATE TABLE Sales (
            id TEXT PRIMARY KEY,
            product_id INTEGER NOT NULL REFERENCES Products,
            sale_date BIGINT NOT NULL,
            quantity DOUBLE PRECISION NOT NULL,
            unit TEXT NOT NULL)",
        params![],
    )?;
    Ok(conn)
}

The Connection::open function simply uses a path to a SQLite database file to open a connection. If this file does not exist, it will be created. As you can see, the created sales.db file is very small. Typically, empty databases of DBMSes are 1,000 times larger.

To perform a data manipulation command, the execute method of the connection is called. Its first argument is a SQL statement, possibly containing some parameters, specified as $1, $2, $3, and so on. The second argument of the function is a reference to a slice of values that are used to replace such parameters.

Of course, if there are no parameters, the parameter values list must be empty. The first parameter value, which has an index of 0, replaces the $1 parameter, the second one replaces the $2 parameter, and so on.

Notice that the arguments of a parameterized SQL statement can be of different data types (numeric, alpha-numeric, BLOBs, and so on), but Rust collections can only contain objects of the same data type. Therefore, the params! macro is used to perform a bit of magic. The data type of the second argument of the execute method must be that of a collection that can be iterated over and whose items implement the ToSql trait. The objects implementing this trait, as its name implies, can be used as parameters of a SQL statement. The rusqlite crate contains an implementation of this trait for many Rust basic types, such as numbers and strings.

So, for example, the params!(34, "abc") expression generates a collection that can be iterated over. The first item of this iteration can be converted into an object containing the number 34, and that number can be used to replace a SQL parameter of a numeric type. The second item of this iteration can be converted into an object containing the "abc" string, and that string can be used to replace a SQL parameter of an alpha-numeric type.

Now, let's look at the populate_db function. It contains statements to insert rows into the database. Here is one of those statements:

conn.execute(
    "INSERT INTO Products (
        id, category, name
        ) VALUES ($1, $2, $3)",
    params![1, "fruit", "pears"],
)?;

As explained before, this statement will have the effect of executing the following SQL statement:

INSERT INTO Products (
        id, category, name
        ) VALUES (1, 'fruit', 'pears')

At last, we see the whole print_db function, which is more complex than the others:

fn print_db(conn: &Connection) -> Result<()> {
    let mut command = conn.prepare(
        "SELECT p.name, s.unit, s.quantity, s.sale_date
        FROM Sales s
        LEFT JOIN Products p
        ON p.id = s.product_id
        ORDER BY s.sale_date",
    )?;
    for sale_with_product in command.query_map(params![], |row| {
        Ok(SaleWithProduct {
            category: "".to_string(),
            name: row.get(0)?,
            quantity: row.get(2)?,
            unit: row.get(1)?,
            date: row.get(3)?,
        })
    })? {
        if let Ok(item) = sale_with_product {
            println!(
                "At instant {}, {} {} of {} were sold.",
                item.date, item.quantity, item.unit, item.name
            );
        }
    }
    Ok(())
}

To perform a SQL query, first, the SELECT SQL statement must be prepared by calling the prepare method of the connection, to convert it into an efficient internal format, with the Statement data type. This object is assigned to the command variable. A prepared statement must be mutable to allow the following replacement of parameters. In this case, however, we don't have any parameters.

A query can generate several rows, and we want to process one at a time, so we must create an iterator from this command. It is performed by calling the query_map method of the command. This method receives two arguments—a slice of parameter values and a closure—and it returns an iterator. The query_map function performs two jobs—first, it replaces the specified parameters, and then it uses the closure to map (or transform) each extracted row into a more handy structure. But in our case, we have no parameters to replace, and so we just create a specific structure with the SaleWithProduct type. To extract the fields from a row, the get method is used. It has a zero-based index on the fields specified in the SELECT query. This structure is the object returned by the iterator for any row extracted by the query, and it is assigned to the iteration variable named sale_with_product.

Now that we have learned how to access a SQLite database, let's check the PostgreSQL database management system.