Struct SqliteConnection 

pub struct SqliteConnection { /* private fields */ }

Connections for the SQLite backend. Unlike other backends, SQLite supported connection URLs are:

• File paths (test.db)
• URIs (file://test.db)
• Special identifiers (:memory:)

Supported loading model implementations

• [DefaultLoadingMode]

As SqliteConnection only supports a single loading mode implementation, it is not required to explicitly specify a loading mode when calling RunQueryDsl::load_iter() or [LoadConnection::load]

DefaultLoadingMode

SqliteConnection only supports a single loading mode, which loads values row by row from the result set.

use diesel::connection::DefaultLoadingMode;
{
    // scope to restrict the lifetime of the iterator
    let iter1 = users::table.load_iter::<(i32, String), DefaultLoadingMode>(connection)?;

    for r in iter1 {
        let (id, name) = r?;
        println!("Id: {} Name: {}", id, name);
    }
}

// works without specifying the loading mode
let iter2 = users::table.load_iter::<(i32, String), _>(connection)?;

for r in iter2 {
    let (id, name) = r?;
    println!("Id: {} Name: {}", id, name);
}

This mode does not support creating multiple iterators using the same connection.

use diesel::connection::DefaultLoadingMode;

let iter1 = users::table.load_iter::<(i32, String), DefaultLoadingMode>(connection)?;
let iter2 = users::table.load_iter::<(i32, String), DefaultLoadingMode>(connection)?;

for r in iter1 {
    let (id, name) = r?;
    println!("Id: {} Name: {}", id, name);
}

for r in iter2 {
    let (id, name) = r?;
    println!("Id: {} Name: {}", id, name);
}

Concurrency

By default, when running into a database lock, the operation will abort with a Database locked error. However, it’s possible to configure it for greater concurrency, trading latency for not having to deal with retries yourself.

You can use this example as blue-print for which statements to run after establishing a connection. It is important to run each PRAGMA in a single statement to make sure all of them apply correctly. In addition the order of the PRAGMA statements is relevant to prevent timeout issues for the later PRAGMA statements.

use diesel::connection::SimpleConnection;
let conn = &mut establish_connection();
// see https://fractaledmind.github.io/2023/09/07/enhancing-rails-sqlite-fine-tuning/
// sleep if the database is busy, this corresponds to up to 2 seconds sleeping time.
conn.batch_execute("PRAGMA busy_timeout = 2000;")?;
// better write-concurrency
conn.batch_execute("PRAGMA journal_mode = WAL;")?;
// fsync only in critical moments
conn.batch_execute("PRAGMA synchronous = NORMAL;")?;
// write WAL changes back every 1000 pages, for an in average 1MB WAL file.
// May affect readers if number is increased
conn.batch_execute("PRAGMA wal_autocheckpoint = 1000;")?;
// free some space by truncating possibly massive WAL files from the last run
conn.batch_execute("PRAGMA wal_checkpoint(TRUNCATE);")?;

Implementations

impl SqliteConnection

pub fn immediate_transaction<T, E, F>(&mut self, f: F) -> Result<T, E>

where F: FnOnce(&mut SqliteConnection) -> Result<T, E>, E: From<Error>,

Run a transaction with BEGIN IMMEDIATE

This method will return an error if a transaction is already open.

Example

conn.immediate_transaction(|conn| {
    // Do stuff in a transaction
    Ok(())
})

pub fn exclusive_transaction<T, E, F>(&mut self, f: F) -> Result<T, E>

where F: FnOnce(&mut SqliteConnection) -> Result<T, E>, E: From<Error>,

Run a transaction with BEGIN EXCLUSIVE

This method will return an error if a transaction is already open.

Example

conn.exclusive_transaction(|conn| {
    // Do stuff in a transaction
    Ok(())
})

pub fn register_collation<F>( &mut self, collation_name: &str, collation: F, ) -> Result<(), Error>

where F: Fn(&str, &str) -> Ordering + Send + 'static + UnwindSafe,

Register a collation function.

collation must always return the same answer given the same inputs. If collation panics and unwinds the stack, the process is aborted, since it is used across a C FFI boundary, which cannot be unwound across and there is no way to signal failures via the SQLite interface in this case..

If the name is already registered it will be overwritten.

This method will return an error if registering the function fails, either due to an out-of-memory situation or because a collation with that name already exists and is currently being used in parallel by a query.

The collation needs to be specified when creating a table: CREATE TABLE my_table ( str TEXT COLLATE MY_COLLATION ), where MY_COLLATION corresponds to name passed as collation_name.

Example

// sqlite NOCASE only works for ASCII characters,
// this collation allows handling UTF-8 (barring locale differences)
conn.register_collation("RUSTNOCASE", |rhs, lhs| {
    rhs.to_lowercase().cmp(&lhs.to_lowercase())
})

pub fn serialize_database_to_buffer(&mut self) -> SerializedDatabase

Serialize the current SQLite database into a byte buffer.

The serialized data is identical to the data that would be written to disk if the database was saved in a file.

Returns

This function returns a byte slice representing the serialized database.

pub fn deserialize_readonly_database_from_buffer( &mut self, data: &[u8], ) -> Result<(), Error>

Deserialize an SQLite database from a byte buffer.

This function takes a byte slice and attempts to deserialize it into a SQLite database. If successful, the database is loaded into the connection. If the deserialization fails, an error is returned.

The database is opened in READONLY mode.

Example

let connection = &mut SqliteConnection::establish(":memory:").unwrap();

sql_query("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")
    .execute(connection).unwrap();
sql_query("INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com'), ('Jane Doe', 'jane.doe@example.com')")
    .execute(connection).unwrap();

// Serialize the database to a byte vector
let serialized_db: SerializedDatabase = connection.serialize_database_to_buffer();

// Create a new in-memory SQLite database
let connection = &mut SqliteConnection::establish(":memory:").unwrap();

// Deserialize the byte vector into the new database
connection.deserialize_readonly_database_from_buffer(serialized_db.as_slice()).unwrap();

pub fn deserialize_database_from_buffer( &mut self, data: &[u8], ) -> Result<(), Error>

Trait Implementations

impl Connection for SqliteConnection

fn establish(database_url: &str) -> Result<SqliteConnection, ConnectionError>

Establish a connection to the database specified by database_url.

See SqliteConnection for supported database_url.

If the database does not exist, this method will try to create a new database and then establish a connection to it.

WASM support

If you plan to use this connection type on the wasm32-unknown-unknown target please make sure to read the following notes:

• The database is stored in memory by default.
• Persistent VFS (Virtual File Systems) is optional, see https://github.com/Spxg/sqlite-wasm-rs/blob/master/sqlite-wasm-rs/src/vfs/README.md for details

type Backend = Sqlite

The backend this type connects to

fn set_instrumentation(&mut self, instrumentation: impl Instrumentation)

Set a specific [Instrumentation] implementation for this connection

fn set_prepared_statement_cache_size(&mut self, size: CacheSize)

Set the prepared statement cache size to [CacheSize] for this connection

fn transaction<T, E, F>(&mut self, f: F) -> Result<T, E>

where F: FnOnce(&mut Self) -> Result<T, E>, E: From<Error>,

Executes the given function inside of a database transaction

fn begin_test_transaction(&mut self) -> Result<(), Error>

Creates a transaction that will never be committed. This is useful for tests. Panics if called while inside of a transaction or if called with a connection containing a broken transaction

fn test_transaction<T, E, F>(&mut self, f: F) -> T

where F: FnOnce(&mut Self) -> Result<T, E>, E: Debug,

Executes the given function inside a transaction, but does not commit it. Panics if the given function returns an error.

impl CustomizeConnection<SqliteConnection, Error> for EncryptedConnection

fn on_acquire(&self, conn: &mut SqliteConnection) -> Result<(), Error>

Called with connections immediately after they are returned from ManageConnection::connect.

fn on_release(&self, conn: C)

Called with connections when they are removed from the pool.

impl CustomizeConnection<SqliteConnection, Error> for NopConnection

fn on_acquire(&self, c: &mut SqliteConnection) -> Result<(), Error>

Called with connections immediately after they are returned from ManageConnection::connect.

fn on_release(&self, conn: C)

Called with connections when they are removed from the pool.

impl CustomizeConnection<SqliteConnection, Error> for UnencryptedConnection

fn on_acquire(&self, c: &mut SqliteConnection) -> Result<(), Error>

Called with connections immediately after they are returned from ManageConnection::connect.

fn on_release(&self, conn: C)

Called with connections when they are removed from the pool.

impl LoadConnection for SqliteConnection

type Cursor<'conn, 'query> = StatementIterator<'conn, 'query>

The cursor type returned by [LoadConnection::load]

type Row<'conn, 'query> = SqliteRow<'conn, 'query>

The row type used as Iterator::Item for the iterator implementation of [LoadConnection::Cursor]

impl MigrationConnection for SqliteConnection

Available on crate feature sqlite only.

fn setup(&mut self) -> Result<usize, Error>

Setup the following table:

impl R2D2Connection for SqliteConnection

Available on crate feature r2d2 only.

fn ping(&mut self) -> Result<(), Error>

Check if a connection is still valid

fn is_broken(&mut self) -> bool

Checks if the connection is broken and should not be reused

impl SimpleConnection for SqliteConnection

fn batch_execute(&mut self, query: &str) -> Result<(), Error>

Execute multiple SQL statements within the same string.

impl TransactionalKeyStore for SqliteConnection

type Store<'a> = SqlKeyStore<MutableTransactionConnection<'a>> where Self: 'a

fn key_store<'a>(&'a mut self) -> Self::Store<'a>

impl<'b, Changes, Output> UpdateAndFetchResults<Changes, Output> for SqliteConnection

where Changes: Copy + Identifiable + AsChangeset<Target = <Changes as HasTable>::Table> + IntoUpdateTarget, <Changes as HasTable>::Table: FindDsl<<Changes as Identifiable>::Id>, UpdateStatement<<Changes as HasTable>::Table, <Changes as IntoUpdateTarget>::WhereClause, <Changes as AsChangeset>::Changeset>: ExecuteDsl<SqliteConnection>, <<Changes as HasTable>::Table as FindDsl<<Changes as Identifiable>::Id>>::Output: LoadQuery<'b, SqliteConnection, Output>, <<Changes as HasTable>::Table as Table>::AllColumns: ValidGrouping<()>, <<<Changes as HasTable>::Table as Table>::AllColumns as ValidGrouping<()>>::IsAggregate: MixedAggregates<No, Output = No>,

Available on crate feature sqlite only.

fn update_and_fetch(&mut self, changeset: Changes) -> Result<Output, Error>

See the traits documentation.

impl WithMetadataLookup for SqliteConnection

fn metadata_lookup(&mut self) -> &mut <Sqlite as TypeMetadata>::MetadataLookup

Retrieves the underlying metadata lookup

impl Send for SqliteConnection