Crate sqll

Expand description

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

The execute function 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 to properly handle external synchronization for the best performance in a real-world scenario.
examples/tokio_async.rs - Using sqll in an asynchronous context with tokio.
examples/pool.rs - Using the high-level Pool to share read-only connections and serialize writes from asynchronous tasks.

§Connecting and querying

Here is a simple example of setting up an in-memory connection, creating a table, inserting and querying back 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 `Row` trait.

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

use sqll::{Connection, Row};

#[derive(Row)]
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, Row};

#[derive(Bind, Row, 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.reset()?;

stmt.execute(Person { name: "Bob", age: 40 })?;
stmt.reset()?;

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 }));

query.reset()?;

§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 PrepareWith::persistent prepared statement to perform multiple queries:

use sqll::Connection;

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 > ?")
    .persistent()
    .build()?;

let mut rows = Vec::new();

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

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

    stmt.reset()?;
}

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

assert_eq!(rows, expected);

§Use in asynchronous contexts

In order for sqlite to be used in asynchronous contexts, the Statement object usually needs to be Send and external synchronization necessary. Since sqlite is a synchronous library we have to defer any work done to a thread pool such as the one provided by tokio::task::spawn_blocking. To make a Statement Send you can use Statement::into_send, but using it is unsafe since correct behavior depends on the build and runtime configuration of the sqlite library in use.

See the tokio_async example for a complete example.

§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 Row 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¹.
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 which allows for efficient one connection per thread the OpenOptions::no_mutex option should be used instead¹.
strict - Enable usage of sqlite with the strict compiler options enabled¹.
pool - Enable the high-level connection Pool and the Statements derive for declaring reusable collections of prepared statements. This pulls in a dependency on the sync feature of tokio. Enabled by default.

§License

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

This is a forwarded sqll-sys option, see https://docs.rs/sqll-sys. ↩ ¹ ² ³

Modules§

ty: Module used which provides marker types for use with the Type associated type in FromColumn or FromUnsizedColumn.

Structs§

CapacityError: Error raised when failing to convert a string into a FixedBlob.
Code: Error code.
Column: Defines the index for a column.
Connection: A SQLite database connection.
DatabaseNotFound: Indicates that a database was not found.
EmptySetuppool: An empty connection setup that does nothing.
Error: An error.
ExclusiveGuardpool: An exclusive guard to the pool.
FixedBlob: A byte slice type which can store at most N bytes from a column.
FixedText: A Text type which can store at most N bytes from a column.
Index: Defines a parameter index for binding values to a statement.
NotThreadSafe: Error raised when attempting to convert a database object into a thread-safe container, but the database is not configured to be thread-safe.
Null: A marker type representing NULL.
OpenOptions: Opening an SQLite connection.
OwnedBytesalloc: A slice of bytes that has been allocated with the sqlite allocator.
Poolpool: A pool of connections to the database.
PoolBuilderpool: Builder for a Pool. The pool needs to know how to prepare the statements for the read and write connections, and this builder provides the necessary information. The pool will prepare the statements on each connection up front, so the schema of the database must be compatible with the statements in the builder at the time the pool is constructed.
PoolErrorpool: Errors raised by the high-level Pool API.
Prepare: A collection of flags use to prepare a statement.
PrepareWith: A builder for customizing a prepared Statement.
SendConnection: A Connection that can be sent between threads.
SendStatement: A Statement that can be sent between threads.
SharedGuardpool: An shared guard to the pool.
Statement: A prepared statement.
Text: A SQLite text value.
Value: A dynamic value.
ValueType: The type of a value.

Enums§

State: The state after stepping a statement.

Traits§

Bind: This allows a type to be used for structured binding of multiple parameters into a Statement using bind.
BindValue: A type suitable for binding to a prepared statement.
ConnectionSetuppool: The trait governing how a Connection is set up before its statements are prepared.
FromColumn: A type suitable for reading a single value from a prepared statement.
FromUnsizedColumn: A type suitable for borrow directly out of a prepared statement.
IsReadOnlypool: A marker trait for Statements that only ever read from the database.
Row: This allows a type to be constructed from a Statement using next, iter, or row.
Statementspool: A collection of prepared statements that can be built from a Connection.

Functions§

lib_version: Return the version string of the SQLite library in use.
lib_version_number: Return the version number of the SQLite library in use.

Type Aliases§

Result: A result type alias.

Derive Macros§

Bindderive: Derive macro for Bind.
Rowderive: Derive macro for Row.
Statementsderive and pool: Derive macro for the Statements trait.

Crate sqll

Crate sqll Copy item path

§Usage

§Examples

§Connecting and querying

§The Row trait.

§The Bind trait.

§Efficient use of prepared Statements

§Use in asynchronous contexts

§Features

§License

Modules§

Structs§

Enums§

Traits§

Functions§

Type Aliases§

Derive Macros§

Crate sqll

§The `Row` trait.

§The `Bind` trait.