sqll

Efficient interface interface to SQLite that doesn't get in your way.

Usage

The two primary methods to interact with an SQLite database through this crate is through execute and prepare.

execute is used for batch statements, and allows for multiple queries to be specified. prepare only allows for a single statement to be specified, but in turn permits reading rows and binding query parameters.

Special consideration needs to be taken about the thread safety of connections. You can read more about that in the Connection documentation.

You can find simple examples of this below.

Examples

• examples/persons.rs - A simple table with users, a primary key, inserting and querying.
• examples/axum.rs - Create an in-memory database connection and serve it using axum. This showcases how do properly handle external synchronization for the best performance.

Connecting and querying

Here is a simple example of setting up an in-memory connection, creating a table, insert and query some rows:

use sqll::{Connection, Result};

let c = Connection::open_in_memory()?;

c.execute(r#"
    CREATE TABLE users (name TEXT, age INTEGER);

    INSERT INTO users VALUES ('Alice', 42);
    INSERT INTO users VALUES ('Bob', 52);
"#)?;

let results = c.prepare("SELECT name, age FROM users ORDER BY age")?
    .iter::<(String, u32)>()
    .collect::<Result<Vec<_>>>()?;

assert_eq!(results, [("Alice".to_string(), 42), ("Bob".to_string(), 52)]);

The FromRow trait.

The FromRow trait can be used to conveniently read rows from a statement using next. It can be conveniently implemented using the FromRow derive.

use sqll::{Connection, FromRow};

#[derive(FromRow)]
struct Person<'stmt> {
    name: &'stmt str,
    age: u32,
}

let mut c = Connection::open_in_memory()?;

c.execute(r#"
    CREATE TABLE users (name TEXT, age INTEGER);

    INSERT INTO users VALUES ('Alice', 42);
    INSERT INTO users VALUES ('Bob', 52);
"#)?;

let mut results = c.prepare("SELECT name, age FROM users ORDER BY age")?;

while let Some(person) = results.next::<Person<'_>>()? {
    println!("{} is {} years old", person.name, person.age);
}

The Bind trait.

The Bind trait can be used to conveniently bind parameters to prepared statements, and it can conveniently be implemented for structs using the Bind derive.

use sqll::{Bind, Connection, FromRow};

#[derive(Bind, FromRow, PartialEq, Debug)]
#[sql(named)]
struct Person<'stmt> {
    name: &'stmt str,
    age: u32,
}

let c = Connection::open_in_memory()?;

c.execute(r#"
   CREATE TABLE persons (name TEXT, age INTEGER);
"#)?;

let mut stmt = c.prepare("INSERT INTO persons (name, age) VALUES (:name, :age)")?;
stmt.execute(Person { name: "Alice", age: 30 })?;
stmt.execute(Person { name: "Bob", age: 40 })?;

let mut query = c.prepare("SELECT name, age FROM persons ORDER BY age")?;

let p = query.next::<Person<'_>>()?;
assert_eq!(p, Some(Person { name: "Alice", age: 30 }));

let p = query.next::<Person<'_>>()?;
assert_eq!(p, Some(Person { name: "Bob", age: 40 }));

Efficient use of prepared Statements

Correct handling of prepared statements are crucial to get good performance out of sqlite. They contain all the state associated with a query and are expensive to construct so they should be re-used.

Using a Prepare::PERSISTENT prepared statement to perform multiple queries:

use sqll::{Connection, Prepare};

let c = Connection::open_in_memory()?;

c.execute(r#"
    CREATE TABLE users (name TEXT, age INTEGER);

    INSERT INTO users VALUES ('Alice', 42);
    INSERT INTO users VALUES ('Bob', 52);
"#)?;

let mut stmt = c.prepare_with("SELECT * FROM users WHERE age > ?", Prepare::PERSISTENT)?;

let mut rows = Vec::new();

for age in [40, 50] {
    stmt.bind(age)?;

    while let Some(row) = stmt.next::<(String, i64)>()? {
        rows.push(row);
    }
}

let expected = vec![
    (String::from("Alice"), 42),
    (String::from("Bob"), 52),
    (String::from("Bob"), 52),
];

assert_eq!(rows, expected);

Features

• std - Enable usage of the Rust standard library. Enabled by default.
• alloc - Enable usage of the Rust alloc library. This is required and is enabled by default. Disabling this option will currently cause a compile error.
• derive - Add a dependency to and re-export of the FromRow derive macro.
• bundled - Use a bundled version of sqlite. The bundle is provided by the sqll-sys crate and the sqlite version used is part of the build metadata of that crate[^sqll-sys].
• threadsafe - Enable usage of sqlite with the threadsafe option set. We assume any system level libraries have this build option enabled, if this is disabled the bundled feature has to be enabled. If threadsafe is disabled, Connection and Statement does not implement Send. But it is also important to understand that if this option is not set, sqlite may not be used by multiple threads at all even if threads have distinct connections. To disable mutexes instead which allows for efficient one connection per thread the OpenOptions::no_mutex option should be used instead[^sqll-sys].
• strict - Enable usage of sqlite with the strict compiler options enabled[^sqll-sys].

[^sqll-sys]: This is a forwarded sqll-sys option, see https://docs.rs/sqll-sys.

Why do we need another sqlite interface?

For other low-level crates, it is difficult to set up and use prepared statements, They are mostly implemented in a manner which requires the caller to borrow the connection in use.

This library implements database objects through the v2 API which ensures that the database remains alive for as long as objects associated with it are alive. This is implemented in the SQLite library itself.

Prepared statements can be expensive to create and should be stored and re-used to achieve the best performance. This is something that crates like rusqlite implements, but can easily be done manually, with no overhead, by simply storing the Statement object directly behind a mutex. Statements can also benefit from using the Prepare::PERSISTENT option which this library supports through prepare_with.

This library is designed to the extent possible to avoid intermediary allocations. For example calling execute doesn't allocate externally of the sqlite3 bindings or we require that c-strings are used when SQLite itself doesn't provide an API for using Rust strings directly. It's also implemented as a thing layer on top of SQLite with minimal added abstractions ensuring you get the best possible performance.

License

This is a rewrite of the sqlite crate, and components used from there have been copied under the MIT license.