Sqlite Reader Writer Concurrency (RWC)

This crate presents an opinionated approach on how to interact with a sqlite database to maximize the reader writer concurrency.

Setup

Sqlite only supports on write operation at any given time. However, in WAL mode multiple readers can operate on the database without blocking the writer and vice versa.

To leverage this, the [ConnectionPool] maintains a list of read only connections and one write connection. This allows multiple readers to access the database and we enforce exclusive access to the writer connection.

While Sqlite does have internal mechanisms to enforce exclusive write access, they are a bit limited and mostly rely on an sleep-retry loop. By enforcing exclusive access to the writer at this level, we can have more predictable writer access behaviors and we only have to deal with SQLITE_BUSY errors from another process.

Finally, since all other connections are read only, we also ensure that it is not possible to accidentally write something outside of the designated writer.

Async

This crate also contains a basic async wrapper for the [ConnectionPool], where each connection is assigned its own thread and communication occurs over a channel.

This can be enabled with the async feature.

Example

use sqlite_rwc::{SyncConnectionPool, ConnectionPoolConfig};
use sqlite_rwc::drivers::rusqlite::RusqliteDriver;
use tempdir::TempDir;

let temp_dir = TempDir::new("sqlite-wrc-example").unwrap();
let db_path = temp_dir.path().join("sqlite.db");

let config = ConnectionPoolConfig {
    max_read_connection_count: 4,
    file_path: db_path,
    connection_acquire_timeout: None,
    #[cfg(feature="watcher")]
    watcher: sqlite_watcher::watcher::Watcher::new().unwrap(),
};

let pool = SyncConnectionPool::<RusqliteDriver>::new(config).unwrap();
let mut connection = pool.connection().unwrap();

//unscoped read operation ...
connection.execute_batch("SQL");

connection.read_transaction_closure(|conn| -> rusqlite::Result<()>{
    ///scoped read operation
    Ok(())
}).unwrap();

connection.transaction_closure(|conn| -> rusqlite::Result<()>{
    //transaction operation
    Ok(())
}).unwrap();