Struct Connection 

pub struct Connection { /* private fields */ }

A SQLite database connection.

For detailed information on how to safetly use a connection, including complex topics such as Thread Safety and asynchronous use, see OpenOptions.

Examples

Opening a connection to a filesystem path:

use sqll::Connection;

let c = Connection::open("database.db")?;

c.execute(r#"
    CREATE TABLE test (id INTEGER);
"#)?;

Opening an in-memory database:

use sqll::Connection;

let c = Connection::open_in_memory()?;

c.execute(r#"
    CREATE TABLE test (id INTEGER);
"#)?;

Implementations

impl Connection

pub unsafe fn into_send(self) -> Result<SendConnection, NotThreadSafe>

Coerce this statement into a SendConnection which can be sent across threads.

Errors

This return an error if neither full_mutex or no_mutex are set, or if the sqlite library is not configured to be thread safe.

use sqll::OpenOptions;

let mut c = OpenOptions::new()
    .create()
    .read_write()
    .open_in_memory()?;

let e = unsafe { c.into_send().unwrap_err() };
assert!(matches!(e, sqll::NotThreadSafe { .. }));

Safety

This is unsafe because it required that the caller ensures that any database objects are synchronized. The exact level of synchronization depends on how the connection was opened:

• If full_mutex was set and no_mutex was not set, no external synchronization is necessary, but calls to the statement might block if it’s busy.
• If no_mutex was set, the caller must ensure that the Statement is fully synchronized with respect to the connection that constructed it. One way to achieve this is to wrap all the statements behind a single mutex.

Examples

The following example showcases how you can share a single connection in a multi-threaded asynchronous application.

In this example, statements are compiled and executed on-the-fly. See Statement::into_send for an example which is more idiomatic and uses prepared statement.

use std::sync::Arc;
use sqll::{OpenOptions, Prepare, SendConnection};
use anyhow::Result;
use tokio::task;
use tokio::sync::Mutex;

#[derive(Clone)]
struct Database {
    c: Arc<Mutex<SendConnection>>,
}

fn setup_database() -> Result<Database> {
    let c = OpenOptions::new()
        .create()
        .read_write()
        .no_mutex()
        .open_in_memory()?;

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

        INSERT INTO users VALUES ('Alice', 60), ('Bob', 70), ('Charlie', 20);
        "#,
    )?;

    // SAFETY: We serialize all accesses to the connection behind a mutex.
    let c = unsafe {
        c.into_send()?
    };

    Ok(Database {
        c: Arc::new(Mutex::new(c)),
    })
}

#[tokio::main]
async fn main() -> Result<()> {
    let db = setup_database()?;

    let mut tasks = Vec::new();

    for _ in 0..10 {
        _ = task::spawn({
            let db = db.clone();

            async move {
                let mut c = db.c.lock_owned().await;

                let task = task::spawn_blocking(move || {
                    let mut update = c.prepare("UPDATE users SET age = age + ?")?;
                    update.execute(2)
                });

                Ok::<_, anyhow::Error>(task.await??)
            }
        });

        let t = task::spawn({
            let db = db.clone();

            async move {
                let mut c = db.c.lock_owned().await;

                let task = task::spawn_blocking(move || -> Result<Option<i64>> {
                    let mut select = c.prepare("SELECT age FROM users ORDER BY age")?;
                    Ok(select.next::<i64>()?)
                });

                task.await?
            }
        });

        tasks.push(t);
    }

    for t in tasks {
        let first = t.await??;
        assert!(matches!(first, Some(20..=40)));
    }

    Ok(())
}

pub fn open(path: impl AsRef<Path>) -> Result<Connection>

Available on crate feature std only.

Open a database to the given path.

Note that it is possible to open an in-memory database by passing ":memory:" here, this call might require allocating depending on the platform, so it should be avoided in favor of using open_in_memory. To avoid allocating for regular paths, you can use open_c_str, however you are responsible for ensuring the c-string is a valid path.

This is the same as calling:

use sqll::OpenOptions;

let c = OpenOptions::new()
    .extended_result_codes()
    .read_write()
    .create()
    .open(path)?;

pub fn open_c_str(name: &CStr) -> Result<Connection>

Open a database connection with a raw c-string.

This can be used to open in-memory databases by passing c":memory:" or a regular open call with a filesystem path like c"/path/to/database.sql".

This is the same as calling:

use sqll::OpenOptions;

let c = OpenOptions::new()
    .extended_result_codes()
    .read_write()
    .create()
    .open_c_str(name)?;

pub fn open_in_memory() -> Result<Connection>

Open an in-memory database.

This is the same as calling

This is the same as calling:

use sqll::OpenOptions;

let c = OpenOptions::new()
    .extended_result_codes()
    .read_write()
    .create()
    .open_in_memory()?;

pub fn database_read_only(&self, name: &CStr) -> Result<bool, DatabaseNotFound>

Check if the database connection is read-only.

Examples

use sqll::{Connection, Code, OpenOptions, DatabaseNotFound};

let c = OpenOptions::new().read_write().open_in_memory()?;

assert!(!c.database_read_only(c"main")?);
let e = c.database_read_only(c"not a db").unwrap_err();
assert!(matches!(e, DatabaseNotFound { .. }));

let c = OpenOptions::new().read_only().open_in_memory()?;

assert!(c.database_read_only(c"main")?);
let e = c.database_read_only(c"not a db").unwrap_err();
assert!(matches!(e, DatabaseNotFound { .. }));

pub fn execute(&self, stmt: impl AsRef<str>) -> Result<()>

Execute a batch of statements.

Unlike prepare, this can be used to execute multiple statements separated by a semi-colon ; and is internally optimized for one-off queries.

Errors

If any of the statements fail, an error is returned.

use sqll::{Code, Connection};

let c = Connection::open_in_memory()?;

let e = c.execute(":)").unwrap_err();
assert_eq!(e.code(), Code::ERROR);

Examples

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', 72);
"#)?;

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

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

pub fn extended_result_codes(&mut self, enabled: bool) -> Result<()>

Enable or disable extended result codes.

This can also be set during construction with OpenOptions::extended_result_codes.

Examples

use sqll::{OpenOptions, Code};

let mut c = OpenOptions::new().create().read_write().open_in_memory()?;

let e = c.execute("
    CREATE TABLE users (name TEXT);
    CREATE UNIQUE INDEX idx_users_name ON users (name);

    INSERT INTO users VALUES ('Bob');
");

let e = c.execute("INSERT INTO users VALUES ('Bob')").unwrap_err();
assert_eq!(e.code(), Code::CONSTRAINT_UNIQUE);
assert_eq!(c.error_message(), "UNIQUE constraint failed: users.name");

c.extended_result_codes(false)?;
let e = c.execute("INSERT INTO users VALUES ('Bob')").unwrap_err();
assert_eq!(e.code(), Code::CONSTRAINT);
assert_eq!(c.error_message(), "UNIQUE constraint failed: users.name");

pub fn error_message(&self) -> &Text

Get the last error message for this connection.

When operating in multi-threaded environment, the error message seen here might not correspond to the query that failed unless some kind of external synchronization is in use which is the recommended way to use sqlite.

This is only meaningful if an error has occured. If no errors have occured, this returns a non-erronous message like "not an error" (default for sqlite3).

Examples

use sqll::{Connection, Code};

let c = Connection::open_in_memory()?;

let e = c.execute("
    CREATE TABLE users (name TEXT);
    CREATE UNIQUE INDEX idx_users_name ON users (name);

    INSERT INTO users VALUES ('Bob');
");

let e = c.execute("INSERT INTO users VALUES ('Bob')").unwrap_err();
assert_eq!(e.code(), Code::CONSTRAINT_UNIQUE);
assert_eq!(c.error_message(), "UNIQUE constraint failed: users.name");

pub fn prepare(&self, stmt: impl AsRef<str>) -> Result<Statement>

Build a prepared statement.

This is the same as calling prepare_with with Prepare::EMPTY.

The database connection will be kept open for the lifetime of this statement.

Errors

If the prepare call contains multiple statements, it will error. To execute multiple statements, use execute instead.

use sqll::{Connection, Code};

let c = Connection::open_in_memory()?;

let e = c.prepare("CREATE TABLE test (id INTEGER) /* test */; INSERT INTO test (id) VALUES (1);").unwrap_err();

assert_eq!(e.code(), Code::MISUSE);

Examples

use sqll::{Connection, Prepare};

let c = Connection::open_in_memory()?;

c.execute(r#"
    CREATE TABLE test (id INTEGER);
"#)?;

let mut insert_stmt = c.prepare("INSERT INTO test (id) VALUES (?);")?;
let mut query_stmt = c.prepare("SELECT id FROM test;")?;

drop(c);

insert_stmt.execute(42)?;

query_stmt.bind(())?;
assert_eq!(query_stmt.iter::<i64>().collect::<Vec<_>>(), [Ok(42)]);

pub fn prepare_with( &self, stmt: impl AsRef<str>, flags: Prepare, ) -> Result<Statement>

Build a prepared statement with custom flags.

For long-running statements it is recommended that they have the Prepare::PERSISTENT flag set.

The database connection will be kept open for the lifetime of this statement.

Errors

If the prepare call contains multiple statements, it will error. To execute multiple statements, use execute instead.

use sqll::{Connection, Code, Prepare};

let c = Connection::open_in_memory()?;

let e = c.prepare_with("CREATE TABLE test (id INTEGER); INSERT INTO test (id) VALUES (1);", Prepare::PERSISTENT).unwrap_err();
assert_eq!(e.code(), Code::MISUSE);

Examples

use sqll::{Connection, Prepare};

let c = Connection::open_in_memory()?;

c.execute(r#"
    CREATE TABLE test (id INTEGER);
"#)?;

let mut insert_stmt = c.prepare_with("INSERT INTO test (id) VALUES (?)", Prepare::PERSISTENT)?;
let mut query_stmt = c.prepare_with("SELECT id FROM test", Prepare::PERSISTENT)?;

drop(c);

/* .. */

insert_stmt.bind(42)?;
assert!(insert_stmt.step()?.is_done());

query_stmt.bind(())?;
assert_eq!(query_stmt.iter::<i64>().collect::<Vec<_>>(), [Ok(42)]);

pub fn changes(&self) -> usize

Return the number of rows inserted, updated, or deleted by the most recent INSERT, UPDATE, or DELETE statement.

Examples

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', 72);
"#)?;

assert_eq!(c.changes(), 1);

pub fn total_changes(&self) -> usize

Return the total number of rows inserted, updated, and deleted by all INSERT, UPDATE, and DELETE statements since the connection was opened.

Examples

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', 72);
"#)?;

assert_eq!(c.total_changes(), 2);

pub fn last_insert_rowid(&self) -> i64

Return the rowid of the most recent successful INSERT into a rowid table or virtual table.

Examples

If there is no primary key, the last inserted row id is an internal identifier for the row:

use sqll::Connection;

let c = Connection::open_in_memory()?;

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

    INSERT INTO users VALUES ('Alice');
    INSERT INTO users VALUES ('Bob');
"#)?;
assert_eq!(c.last_insert_rowid(), 2);

c.execute(r#"
    INSERT INTO users VALUES ('Charlie');
"#)?;
assert_eq!(c.last_insert_rowid(), 3);

let mut stmt = c.prepare("INSERT INTO users VALUES (?)")?;
stmt.execute("Dave")?;

assert_eq!(c.last_insert_rowid(), 4);

If there is a primary key, the last inserted row id corresponds to it:

use sqll::Connection;

let c = Connection::open_in_memory()?;

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

    INSERT INTO users (name) VALUES ('Alice');
    INSERT INTO users (name) VALUES ('Bob');
"#)?;
assert_eq!(c.last_insert_rowid(), 2);

c.execute("INSERT INTO users (name) VALUES ('Charlie')")?;
assert_eq!(c.last_insert_rowid(), 3);

c.execute("INSERT INTO users (name) VALUES ('Dave')")?;
assert_eq!(c.last_insert_rowid(), 4);

let mut select = c.prepare("SELECT id FROM users WHERE name = ?")?;
select.bind("Dave")?;

for id in select.iter::<i64>() {
    assert_eq!(id?, 4);
}

c.execute("DELETE FROM users WHERE id = 3")?;
assert_eq!(c.last_insert_rowid(), 4);

c.execute("INSERT INTO users (name) VALUES ('Charlie')")?;
assert_eq!(c.last_insert_rowid(), 5);

select.bind("Charlie")?;

while let Some(id) = select.next::<i64>()? {
    assert_eq!(id, 5);
}

pub fn busy_handler<F>(&mut self, callback: F) -> Result<()>

where F: FnMut(usize) -> bool + Send + 'static,

Available on crate feature alloc only.

Set a callback for handling busy events.

The callback is triggered when the database cannot perform an operation due to processing of some other request. If the callback returns true, the operation will be repeated.

The busy callback should not take any actions which modify the database connection that invoked the busy handler. In other words, the busy handler is not reentrant. Any such actions result in undefined behavior.

Since this needs to allocate space to store the closure the alloc feature has to be enabled.

Examples

use sqll::Connection;

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

c.busy_handler(|attempts| {
    println!("busy attempt: {attempts}");
    attempts < 5
})?;

pub fn clear_busy_handler(&mut self) -> Result<()>

Clear any previously registered busy handler.

Examples

use sqll::Connection;

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

c.busy_handler(|attempts| {
    println!("busy attempt: {attempts}");
    attempts < 5
})?;

c.clear_busy_handler()?;

pub fn busy_timeout(&mut self, ms: c_int) -> Result<()>

Set an implicit callback for handling busy events that tries to repeat rejected operations until a timeout expires.

Examples

use sqll::Connection;

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

c.busy_timeout(5000)?;

Trait Implementations

impl Debug for Connection

fn fmt(&self, f: &mut Formatter<'_>) -> Result

Formats the value using the given formatter.

impl Drop for Connection

fn drop(&mut self)

Executes the destructor for this type.

impl Send for Connection

Available on crate feature threadsafe only.

Connection is Send.