SQLx SQLite Connection Manager

A minimal wrapper around SQLx that enforces pragmatic SQLite connection policies for mobile and desktop applications. Not dependent on Tauri — usable in any Rust project needing SQLx connection management.

Features

• Single instance per database path: Prevents duplicate pools and idle threads

• Read pool: Concurrent read-only connections (default: 6, configurable)

• Write connection: Single exclusive writer via WriteGuard

  Wait! Why? From SQLite docs: "SQLite ... will only allow one writer at any instant in time."

• WAL mode: Enabled on first acquire_writer() call

• Idle timeout: Connections close after 30s inactivity (configurable)

• No perpetual caching: Zero minimum connections (prevents idle thread sprawl)

Delegates to SQLx's SqlitePoolOptions and SqliteConnectOptions wherever possible — minimal wrapper logic.

Usage

use sqlx_sqlite_conn_mgr::SqliteDatabase;
use sqlx::query;
use std::sync::Arc;

#[tokio::main]
async fn main() -> Result<(), sqlx_sqlite_conn_mgr::Error> {
    // Connect (creates if missing, returns Arc<SqliteDatabase>)
    let db = SqliteDatabase::connect("example.db", None).await?;

    // Multiple connects to same path return same instance
    let db2 = SqliteDatabase::connect("example.db", None).await?;
    assert!(Arc::ptr_eq(&db, &db2));

    // Read queries use the pool (concurrent)
    let rows = query("SELECT * FROM users")
        .fetch_all(db.read_pool()?)
        .await?;

    // Write queries acquire exclusive access (WAL enabled on first call)
    let mut writer = db.acquire_writer().await?;
    query("INSERT INTO users (name) VALUES (?)")
        .bind("Alice")
        .execute(&mut *writer)
        .await?;
    // Writer released on drop

    db.close().await?;
    Ok(())
}

Custom Configuration

use sqlx_sqlite_conn_mgr::{SqliteDatabase, SqliteDatabaseConfig};
use std::time::Duration;

let config = SqliteDatabaseConfig {
    max_read_connections: 10,  // default: 6
    idle_timeout: Duration::from_secs(60),  // default: 30s
};
let db = SqliteDatabase::connect("example.db", Some(config)).await?;

Migrations

Run SQLx migrations directly:

use sqlx_sqlite_conn_mgr::SqliteDatabase;

// Embed migrations at compile time (reads ./migrations/*.sql)
static MIGRATOR: sqlx::migrate::Migrator = sqlx::migrate!("./migrations");

async fn run() -> Result<(), sqlx_sqlite_conn_mgr::Error> {
    let db = SqliteDatabase::connect("example.db", None).await?;
    db.run_migrations(&MIGRATOR).await?;
    Ok(())
}

Migrations are tracked in _sqlx_migrations — calling run_migrations() multiple times is safe (already-applied migrations are skipped).

Note: When using the Tauri plugin, migrations are handled automatically via Builder::add_migrations(). The plugin starts migrations at setup and waits for completion when load() is called.

Attached Databases

Attach other SQLite databases to enable cross-database queries. Attached databases are connection-scoped and automatically detached when the guard is dropped.

use sqlx_sqlite_conn_mgr::{SqliteDatabase, AttachedSpec, AttachedMode, acquire_reader_with_attached};
use sqlx::query;
use std::sync::Arc;

async fn example() -> Result<(), sqlx_sqlite_conn_mgr::Error> {
    // You must first connect to the main database and any database(s) you intend to
    // attach.
    let main_db = SqliteDatabase::connect("main.db", None).await?;
    let orders_db = SqliteDatabase::connect("orders.db", None).await?;

    // Attach orders database for read-only access
    let specs = vec![AttachedSpec {
        database: orders_db,
        schema_name: "orders".to_string(),
        mode: AttachedMode::ReadOnly,
    }];

    let mut conn = acquire_reader_with_attached(&main_db, specs).await?;

    // Cross-database query
    let rows = query(
        "SELECT u.name, o.total
         FROM main.users u
         JOIN orders.orders o ON u.id = o.user_id"
    )
    .fetch_all(&mut *conn)
    .await?;

    // Attached database automatically detached when conn is dropped
    Ok(())
}

Attached Modes

• AttachedMode::ReadOnly: Attach for read access only. Can be used with both reader and writer connections.
• AttachedMode::ReadWrite: Attach for write access. Can only be used with writer connections. Acquires the attached database's writer lock to ensure exclusive access.

Safety Guarantees

1. Lock ordering: Multiple attachments are acquired in alphabetical order by schema name to prevent deadlocks
2. Mode validation: Read-only connections cannot attach databases in read-write mode (returns CannotAttachReadWriteToReader error)
3. Automatic cleanup: SQLite automatically detaches databases when connections close; no manual cleanup required

Caution: Do not bypass this API by executing raw ATTACH DATABASE '/path/to/db.db' AS alias SQL commands directly. Doing so circumvents the connection manager's policies and will result in unpredictable behavior, including potential deadlocks.

API Reference

SqliteDatabase

WriteGuard

RAII guard for exclusive write access. Derefs to SqliteConnection. Connection returned to pool on drop.

Attached Database Functions

Returns AttachedConnection or AttachedWriteGuard respectively. Both guards deref to SqliteConnection and automatically detach databases on drop.

Design Details

Read-Only Pool

The read pool opens connections with read_only(true), preventing write operations and ensuring data integrity.

WAL Mode and Synchronous Setting

WAL mode is enabled on first acquire_writer() call (idempotent, safe across sessions). This library sets PRAGMA synchronous = NORMAL instead of FULL:

• Performance: 2-3x faster writes — syncs only the WAL file, not after every checkpoint
• Safety in WAL mode: WAL transactions are atomic at the WAL file level; crashes recover from intact WAL on next open (unlike rollback journal mode where NORMAL could cause corruption)
• Mobile/Desktop context: NORMAL provides the best balance; FULL is for unreliable storage or power-loss-mid-fsync scenarios

See SQLite WAL Performance Considerations for details.

Exclusive Writes

The write pool has max_connections=1. Callers to acquire_writer() block asynchronously until the current WriteGuard is dropped.

Tracing

Uses tracing with release_max_level_off — all logs compiled out of release builds. Install a tracing-subscriber in your app to see logs during development.

Development

Follows Silvermine Rust coding standards.

cargo build                          # Build
cargo test                           # Test
cargo lint-clippy && cargo lint-fmt  # Lint
cargo doc --open                     # Documentation