sqlite-ll

Low-level interface to the SQLite database.

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

Why do we need another sqlite interface?

It is difficult to set up and use prepared statements with existing crates, because they are all implemented in a manner which requires the caller to borrow the connection in use.

Prepared statements can be expensive to create and should be cached and re-used to achieve the best performance. Statements can also benefit from using the Prepare::PERSISTENT option This library uses sqlite3_close_v2 when the connection is dropped, causing the closing of the connection to be delayed until resources associated with it has been closed.

We've also designed this library to avoid intermediary allocations. So for example calling execute doesn't allocate externally of the sqlite3 bindings. This was achieved by porting the execute implementation from the sqlite library and works because sqlite actually uses UTF-8 internally but this is not exposed in the legacy C API that other crates use to execute statements.

Example

Open an in-memory connection, create a table, and insert some rows:

use sqlite_ll::Connection;

let c = Connection::open_memory()?;

c.execute(
    r#"
    CREATE TABLE users (name TEXT, age INTEGER);
    INSERT INTO users VALUES ('Alice', 42);
    INSERT INTO users VALUES ('Bob', 69);
    "#,
)?;

Querying data using a parepared statement with bindings:

use sqlite_ll::State;
let mut stmt = c.prepare("SELECT * FROM users WHERE age > ?")?;

let mut results = Vec::new();

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

    while let State::Row = stmt.step()? {
        results.push((stmt.read::<String>(0)?, stmt.read::<i64>(1)?));
    }
}

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

assert_eq!(results, expected);