Struct SqliteConnection 

pub struct SqliteConnection { /* private fields */ }

Available on crate feature __sqlite-shared only.

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 Self) -> 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 Self) -> 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 last_insert_rowid(&self) -> Option<NonZeroI64>

Returns the rowid of the most recent successful INSERT on this connection.

Returns None if no successful INSERT into a rowid table has been performed on this connection, and Some(rowid) otherwise.

See the SQLite documentation for details.

Caveats

• Inserts into WITHOUT ROWID tables are not recorded
• Failed INSERT (constraint violations) do not change the value
• INSERT OR REPLACE always updates the value
• Within triggers, returns the rowid of the trigger’s INSERT; reverts after the trigger completes

Example

use core::num::NonZeroI64;
use diesel::connection::SimpleConnection;
let conn = &mut SqliteConnection::establish(":memory:").unwrap();
conn.batch_execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL)")?;
conn.batch_execute("INSERT INTO users (name) VALUES ('Sean')")?;
let rowid = conn.last_insert_rowid();
assert_eq!(rowid, NonZeroI64::new(1));
conn.batch_execute("INSERT INTO users (name) VALUES ('Tess')")?;
let rowid = conn.last_insert_rowid();
assert_eq!(rowid, NonZeroI64::new(2));

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

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], ) -> QueryResult<()>

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 unsafe fn with_raw_connection<R, F>(&mut self, f: F) -> R

where F: FnOnce(*mut sqlite3) -> R,

Provides temporary access to the raw SQLite database connection handle.

This method provides a way to access the underlying sqlite3 pointer, enabling direct use of the SQLite C API for advanced features that Diesel does not wrap, such as the session extension, hooks, or other advanced APIs.

Why Diesel Doesn’t Wrap These APIs

Certain SQLite features, such as the session extension, are optional and only available when SQLite is compiled with specific flags (e.g., -DSQLITE_ENABLE_SESSION and -DSQLITE_ENABLE_PREUPDATE_HOOK for sessions). These compile-time options determine whether the corresponding C API functions exist in the SQLite library’s ABI.

Because Diesel must work with any SQLite library at runtime—including system-provided libraries that may lack these optional features—it cannot safely provide wrappers for APIs that may or may not exist. Doing so would either:

• Cause linker errors at compile time if the user’s libsqlite3-sys wasn’t compiled with the required flags, or
• Cause undefined behavior at runtime if Diesel called functions that don’t exist in the linked library.

While feature gates could theoretically solve this problem, Diesel already has an extensive API surface with many existing feature combinations. Each new feature gate adds a combinatorial explosion of test configurations that must be validated, making the library increasingly difficult to maintain. Therefore, exposing the raw connection is the preferred approach for niche SQLite features.

By exposing the raw connection handle, Diesel allows users who know they have access to a properly configured SQLite build to use these advanced features directly through their own FFI bindings.

Safety

This method is marked unsafe because improper use of the raw connection handle can lead to undefined behavior. The caller must ensure that:

• The connection handle is not closed during the callback.
• The connection handle is not stored beyond the callback’s scope.
• Concurrent access rules are respected (SQLite connections are not thread-safe unless using serialized threading mode).
• Transaction state is not modified — do not execute BEGIN, COMMIT, ROLLBACK, or SAVEPOINT statements via the raw handle. Diesel’s AnsiTransactionManager tracks transaction nesting internally, and bypassing it will cause Diesel’s view of the transaction state to diverge from SQLite’s actual state.
• Diesel’s prepared statements are not disturbed — do not call sqlite3_finalize() or sqlite3_reset() on statements that belong to Diesel’s StatementCache. Doing so will cause use-after-free or double-free when Diesel later accesses those statements.

Example

use diesel::sqlite::SqliteConnection;
use diesel::Connection;

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

// SAFETY: We do not close or store the connection handle,
// and we do not modify Diesel-managed state (transactions, cached statements).
let is_valid = unsafe {
    conn.with_raw_connection(|raw_conn| {
        // The raw connection pointer can be passed to SQLite C API functions
        // from your own `libsqlite3-sys` (native) or `sqlite-wasm-rs` (WASM)
        // dependency — for example, `sqlite3_get_autocommit(raw_conn)` or
        // `sqlite3session_create(raw_conn, ...)`.
        !raw_conn.is_null()
    })
};
assert!(is_valid);

Platform Notes

This method works identically on both native and WASM targets. However, you must depend on the appropriate FFI crate for your target:

• Native: Add libsqlite3-sys as a dependency
• WASM (wasm32-unknown-unknown): Add sqlite-wasm-rs as a dependency

Both crates expose a compatible sqlite3 type that can be used with the pointer returned by this method.

Trait Implementations

impl Connection for SqliteConnection

fn establish(database_url: &str) -> ConnectionResult<Self>

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 for details

type Backend = Sqlite

The backend this type connects to

type TransactionManager = AnsiTransactionManager

The transaction manager implementation used by this connection

fn execute_returning_count<T>(&mut self, source: &T) -> QueryResult<usize>

where T: QueryFragment<Self::Backend> + QueryId,

Execute a single SQL statements given by a query and return number of affected rows

fn transaction_state(&mut self) -> &mut AnsiTransactionManager

where Self: Sized,

Get access to the current transaction state of this connection

fn instrumentation(&mut self) -> &mut dyn Instrumentation

Get the instrumentation instance stored in this connection

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) -> QueryResult<()>

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

fn load<'conn, 'query, T>( &'conn mut self, source: T, ) -> QueryResult<Self::Cursor<'conn, 'query>>

where T: Query + QueryFragment<Self::Backend> + QueryId + 'query, Self::Backend: QueryMetadata<T::SqlType>,

Executes a given query and returns any requested values

impl<'query, V, T, QId, Op, U, B, const STATIC_QUERY_ID: bool> LoadQuery<'query, SqliteConnection, U, B> for (No, InsertStatement<T, BatchInsert<V, T, QId, STATIC_QUERY_ID>, Op>)

where T: Table + QueryId + 'static, InsertStatement<T, SqliteBatchInsertWrapper<V, T, QId, STATIC_QUERY_ID>, Op>: LoadQuery<'query, SqliteConnection, U, B>, Self: RunQueryDsl<SqliteConnection>,

type RowIter<'conn> = <InsertStatement<T, SqliteBatchInsertWrapper<V, T, QId, STATIC_QUERY_ID>, Op> as LoadQuery<'query, SqliteConnection, U, B>>::RowIter<'conn>

Return type of LoadQuery::internal_load

fn internal_load( self, conn: &mut SqliteConnection, ) -> QueryResult<Self::RowIter<'_>>

Load this query

impl<'query, V, T, QId, Op, U, B, Target, ConflictOpt, const STATIC_QUERY_ID: bool> LoadQuery<'query, SqliteConnection, U, B> for (No, InsertStatement<T, OnConflictValues<BatchInsert<V, T, QId, STATIC_QUERY_ID>, Target, ConflictOpt>, Op>)

where T: Table + QueryId + 'static, InsertStatement<T, OnConflictValues<SqliteBatchInsertWrapper<V, T, QId, STATIC_QUERY_ID>, Target, ConflictOpt>, Op>: LoadQuery<'query, SqliteConnection, U, B>, Self: RunQueryDsl<SqliteConnection>,

type RowIter<'conn> = <InsertStatement<T, OnConflictValues<SqliteBatchInsertWrapper<V, T, QId, STATIC_QUERY_ID>, Target, ConflictOpt>, Op> as LoadQuery<'query, SqliteConnection, U, B>>::RowIter<'conn>

Return type of LoadQuery::internal_load

fn internal_load( self, conn: &mut SqliteConnection, ) -> QueryResult<Self::RowIter<'_>>

Load this query

impl<'query, V, T, QId, Op, U, B, const STATIC_QUERY_ID: bool> LoadQuery<'query, SqliteConnection, U, B> for (Yes, InsertStatement<T, BatchInsert<Vec<ValuesClause<V, T>>, T, QId, STATIC_QUERY_ID>, Op>)

where T: Table + Copy + QueryId + 'static, Op: Copy + QueryId + QueryFragment<Sqlite>, InsertStatement<T, ValuesClause<V, T>, Op>: LoadQuery<'query, SqliteConnection, U, B>, Self: RunQueryDsl<SqliteConnection>,

type RowIter<'conn> = IntoIter<Result<U, Error>>

Return type of LoadQuery::internal_load

fn internal_load( self, conn: &mut SqliteConnection, ) -> QueryResult<Self::RowIter<'_>>

Load this query

impl<'query, V, T, QId, Op, U, B, Target, ConflictOpt, const STATIC_QUERY_ID: bool> LoadQuery<'query, SqliteConnection, U, B> for (Yes, InsertStatement<T, OnConflictValues<BatchInsert<Vec<ValuesClause<V, T>>, T, QId, STATIC_QUERY_ID>, Target, ConflictOpt>, Op>)

where T: Table + Copy + QueryId + 'static, T::FromClause: Copy, Op: Copy, Target: Copy, ConflictOpt: Copy, InsertStatement<T, OnConflictValues<ValuesClause<V, T>, Target, ConflictOpt>, Op>: LoadQuery<'query, SqliteConnection, U, B>, Self: RunQueryDsl<SqliteConnection>,

type RowIter<'conn> = IntoIter<Result<U, Error>>

Return type of LoadQuery::internal_load

fn internal_load( self, conn: &mut SqliteConnection, ) -> QueryResult<Self::RowIter<'_>>

Load this query

impl<'query, V, T, QId, Op, O, U, B, const STATIC_QUERY_ID: bool> LoadQuery<'query, SqliteConnection, U, B> for InsertStatement<T, BatchInsert<Vec<ValuesClause<V, T>>, T, QId, STATIC_QUERY_ID>, Op>

where T: QuerySource, V: ContainsDefaultableValue<Out = O>, O: Default, (O, Self): LoadQuery<'query, SqliteConnection, U, B>,

type RowIter<'conn> = <(O, InsertStatement<T, BatchInsert<Vec<ValuesClause<V, T>>, T, QId, STATIC_QUERY_ID>, Op>) as LoadQuery<'query, SqliteConnection, U, B>>::RowIter<'conn>

Return type of LoadQuery::internal_load

fn internal_load( self, conn: &mut SqliteConnection, ) -> QueryResult<Self::RowIter<'_>>

Load this query

impl MigrationConnection for SqliteConnection

fn setup(&mut self) -> QueryResult<usize>

Setup the following table:

impl MultiConnectionHelper for SqliteConnection

fn to_any<'a>( lookup: &mut <Self::Backend as TypeMetadata>::MetadataLookup, ) -> &mut (dyn Any + 'a)

Available on crate feature i-implement-a-third-party-backend-and-opt-into-breaking-changes only.

Convert the lookup type to any

fn from_any( lookup: &mut dyn Any, ) -> Option<&mut <Self::Backend as TypeMetadata>::MetadataLookup>

Available on crate feature i-implement-a-third-party-backend-and-opt-into-breaking-changes only.

Get the lookup type from any

impl R2D2Connection for SqliteConnection

Available on crate feature r2d2 only.

fn ping(&mut self) -> QueryResult<()>

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<V, T, QId, Op, O, const STATIC_QUERY_ID: bool> RunQueryDsl<SqliteConnection> for (O, InsertStatement<T, BatchInsert<Vec<ValuesClause<V, T>>, T, QId, STATIC_QUERY_ID>, Op>)

where T: QuerySource, V: ContainsDefaultableValue<Out = O>, O: Default, InsertStatement<T, BatchInsert<Vec<ValuesClause<V, T>>, T, QId, STATIC_QUERY_ID>, Op>: RunQueryDsl<SqliteConnection>,

fn execute(self, conn: &mut Conn) -> QueryResult<usize>

where Conn: Connection, Self: ExecuteDsl<Conn>,

Executes the given command, returning the number of rows affected.

fn load<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>

where Self: LoadQuery<'query, Conn, U>,

Executes the given query, returning a Vec with the returned rows.

fn load_iter<'conn, 'query: 'conn, U, B>( self, conn: &'conn mut Conn, ) -> QueryResult<Self::RowIter<'conn>>

where U: 'conn, Self: LoadQuery<'query, Conn, U, B> + 'conn,

Executes the given query, returning an Iterator with the returned rows.

fn get_result<'query, U>(self, conn: &mut Conn) -> QueryResult<U>

where Self: LoadQuery<'query, Conn, U>,

Runs the command, and returns the affected row.

fn get_results<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>

where Self: LoadQuery<'query, Conn, U>,

Runs the command, returning an Vec with the affected rows.

impl<V, T, QId, Op, O, Target, ConflictOpt, const STATIC_QUERY_ID: bool> RunQueryDsl<SqliteConnection> for (O, InsertStatement<T, OnConflictValues<BatchInsert<Vec<ValuesClause<V, T>>, T, QId, STATIC_QUERY_ID>, Target, ConflictOpt>, Op>)

where T: QuerySource, V: ContainsDefaultableValue<Out = O>, O: Default, InsertStatement<T, OnConflictValues<BatchInsert<Vec<ValuesClause<V, T>>, T, QId, STATIC_QUERY_ID>, Target, ConflictOpt>, Op>: RunQueryDsl<SqliteConnection>,

fn execute(self, conn: &mut Conn) -> QueryResult<usize>

where Conn: Connection, Self: ExecuteDsl<Conn>,

Executes the given command, returning the number of rows affected.

fn load<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>

where Self: LoadQuery<'query, Conn, U>,

Executes the given query, returning a Vec with the returned rows.

fn load_iter<'conn, 'query: 'conn, U, B>( self, conn: &'conn mut Conn, ) -> QueryResult<Self::RowIter<'conn>>

where U: 'conn, Self: LoadQuery<'query, Conn, U, B> + 'conn,

Executes the given query, returning an Iterator with the returned rows.

fn get_result<'query, U>(self, conn: &mut Conn) -> QueryResult<U>

where Self: LoadQuery<'query, Conn, U>,

Runs the command, and returns the affected row.

fn get_results<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>

where Self: LoadQuery<'query, Conn, U>,

Runs the command, returning an Vec with the affected rows.

impl SimpleConnection for SqliteConnection

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

Execute multiple SQL statements within the same string.

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

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

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

See the traits documentation.

impl WithMetadataLookup for SqliteConnection

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

Available on crate feature i-implement-a-third-party-backend-and-opt-into-breaking-changes only.

Retrieves the underlying metadata lookup

impl ConnectionSealed for SqliteConnection

impl Send for SqliteConnection