Rust SQLite: Safe and Efficient DB Access

SQLite is a great choice if you need to access a database from a Rust application and are looking for a solution that is fast, safe, and easy to use. The Rust ecosystem provides fantastic bindings to SQLite through libraries like rusqlite and sqlite.

In this guide, we will explore using Rust and SQLite to build a sample database application. We will cover:

• Creating a SQLite database and tables
• Inserting and querying data
• Using transactions for data integrity
• Performance considerations

We will use the rusqlite crate in all the examples. Our sample application will be a simple database to track employees and departments in a company.

Creating a SQLite Database with Rust

First, let’s create a new SQLite database file called company.db:

use rusqlite::Connection;

let conn = Connection::open("company.db").unwrap(); 

The open method will create a new database if it doesn’t already exist. Next we need to create our tables. Here is the schema we will use for the company database:

Employees

Departments

We can create the tables like this:

conn.execute(
    "CREATE TABLE employees (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                dept_id INTEGER
            )",
    []).unwrap();

conn.execute(
    "CREATE TABLE departments (
                id INTEGER PRIMARY KEY, 
                name TEXT NOT NULL
            )", 
    []).unwrap();

And that’s it! We now have an empty SQLite database ready for data.

Inserting and Querying Data into SQLite with Rust

Now let’s add some departments. We’ll use the SQLite INSERT statement.

conn.execute(
    "INSERT INTO departments (name) VALUES (?1)",
    &["Engineering"]).unwrap();

conn.execute(  
    "INSERT INTO departments (name) VALUES (?1)",
    &["Sales"]).unwrap();   

We can use ?1 placeholders for parameters to avoid string formatting or concatenation. Next insert some employees:

let dept_id = conn.last_insert_rowid().unwrap(); 

conn.execute(
    "INSERT INTO employees (name, dept_id) VALUES (?1, ?2)",
    &["Jane", &dept_id]).unwrap();
    
conn.execute(
    "INSERT INTO employees (name, dept_id) VALUES (?1, ?2)", 
    &["Jim", &dept_id]).unwrap();

To query the data and print the results:

let mut stmt = conn.prepare("SELECT e.name, d.name 
                           FROM employees e, departments d 
                           WHERE e.dept_id = d.id").unwrap();
                           
let emps = stmt.query_map(rusqlite::NO_PARAMS, |row| {    
    let name: String = row.get(0).unwrap();
    let dept: String = row.get(1).unwrap();
        
    println!("Name: {}, Department: {}", name, dept);
    
    Ok(())
}).unwrap();

Which prints:

Name: Jane, Department: Engineering  
Name: Jim, Department: Engineering

Easy! Next we’ll look at using transactions.

Using Transactions in SQLite with Rust

Transactions allow executing multiple SQL statements as an atomic unit – either all statements succeed or the entire transaction is rolled back. This helps preserve database consistency and handle errors.

Here is an example transaction to transfer Jane to the Sales department:

let tx = conn.transaction().unwrap();

tx.execute(
    "UPDATE employees SET dept_id = (SELECT id FROM departments WHERE name = 'Sales')  
        WHERE name = 'Jane'", 
    []).unwrap();
        
tx.commit().unwrap();    

If the UPDATE fails, the transaction automatically rolls back. Transactions can ensure operations like transfers happen atomically.

Performance Considerations

For most applications, SQLite offers great performance out of the box. However, there are a few best practices to optimize throughput and latency:

• Use prepared statements instead of emitting raw SQL queries. The rusqlite crate makes this easy.
• Set synchronous PRAGMAs appropriately for your application’s durability needs.
• Use WRITE AHEAD logging for very high write throughput.

Additionally, if you need extremely high performance and don’t require SQL, a key-value store like Sled may be better than SQLite.

Of course, many more optimizations are possible, but these tips will provide good performance for most applications.

Conclusion

Rust and SQLite make for an extremely productive combination to build database-driven applications. SQLite brings a compact, embeddable, and full-featured SQL engine. Rust provides excellent safety guarantees while maintaining high performance. Libraries like rusqlite make interfacing with SQLite a breeze.

We walked through creating a SQLite database, executing SQL queries and transactions, and some performance best practices. This just scratches the surface of what you can do with SQLite and Rust – happy coding!