Crate sqll 

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

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.

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.
• 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.
• 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.

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 sqll::Connection;

let c = Connection::open_memory()?;

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

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

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_memory()?;
c.execute("
    CREATE TABLE users (name TEXT, age INTEGER);

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

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

let mut results = Vec::new();

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

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

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

assert_eq!(results, expected);

License

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

Structs

Code

Error code.

Connection

A sqlite database connection.

Error

An error.

FixedBytes

A helper to read at most a fixed number of N bytes from a column. This allocates the storage for the bytes read on the stack.

Null

A marker type representing a NULL value.

OpenOptions

Options that can be used to customize the opening of a SQLite database.

Prepare

A collection of flags use to prepare a statement.

Statement

A prepared statement.

Type

The type of a value.

Value

A dynamic value.

Enums

State

The state after stepping a statement.

Traits

Bindable

A type suitable for binding to a prepared statement.

Borrowable

A type suitable for borrow directly out of a prepared statement.

Gettable

A type suitable for reading from a prepared statement.

Sink

Trait governing types which can be written to in-place.

Functions

version

Return the version number of SQLite.

Type Aliases

Result

A result type.