Crate sql_middleware 

SQL Middleware - A lightweight wrapper for SQL backends

Keywords: postgres, sqlite, libsql, turso, deadpool, bb8, async, pool, query builder

This crate provides a lightweight async wrapper for SQLite, PostgreSQL, LibSQL, Turso (experimental), and SQL Server (tiberius). The goal is a similar, async-compatible API consistent across databases. Full examples on github.

Features

• Similar API regardless of backend (as much as possible)
• Asynchronous (where available)
• Connection pooling via bb8 (Postgres/SQLite) and deadpool (LibSQL/SQL Server)
• Transaction support
• Not an ORM

Feature Flags

Default features are sqlite and postgres. Enable others as needed:

# Only SQLite and LibSQL
sql-middleware = { version = "0", features = ["sqlite", "libsql"] }

# All backends
sql-middleware = { version = "0", features = ["sqlite", "postgres", "mssql", "libsql", "turso"] }

Additional flags:

• libsql: LibSQL (local has been tested, remote is present)
• turso: Turso (in-process, SQLite-compatible). Experimental; no remote support.
• test-utils: Test helpers for internal testing
• mssql: SQL Server via tiberius (untested, but present)
• benchmarks: Criterion helpers for benches

Example

use chrono::NaiveDateTime;
use sql_middleware::prelude::*;

pub struct ScoreChange {
    pub espn_id: i64,
    pub score: i32,
    pub updated_at: NaiveDateTime,
}

/// Update scores across any supported backend without copy/pasting per-database functions.
pub async fn set_scores_in_db(
    config_and_pool: &ConfigAndPool,
    updates: &[ScoreChange],
) -> Result<ResultSet, SqlMiddlewareDbError> {
    // Acquire a pooled connection (Postgres, SQLite, LibSQL, or Turso).
    let mut conn = config_and_pool.get_connection().await?;

    // Author once; translation rewrites placeholders per backend.
    let insert_sql = "INSERT INTO scores (espn_id, score, updated_at) VALUES ($1, $2, $3)";
    let fetch_sql = "SELECT espn_id, score, updated_at FROM scores ORDER BY updated_at DESC LIMIT $1";

    // Reuse the same binding logic for every backend.
    for change in updates {
        let params = vec![
            RowValues::Int(change.espn_id),
            RowValues::Int(i64::from(change.score)),
            RowValues::Timestamp(change.updated_at),
        ];
        let statement = QueryAndParams::new(insert_sql, params);
        conn.query(&statement.query)
            .translation(TranslationMode::ForceOn)
            .params(&statement.params)
            .dml()
            .await?;
    }

    // Fetch the latest rows to confirm the write path succeeded.
    let limit = updates.len().max(1) as i64;
    let latest = QueryAndParams::new(fetch_sql, vec![RowValues::Int(limit)]);
    conn.query(&latest.query)
        .translation(TranslationMode::ForceOn)
        .params(&latest.params)
        .select()
        .await
}

SQLite worker helpers

SQLite pooling runs through a worker thread so blocking rusqlite calls never stall the async runtime. Two helpers expose that surface:

use sql_middleware::prelude::*;

let cap = ConfigAndPool::sqlite_builder("file::memory:?cache=shared".into())
    .build()
    .await?;
let mut conn = cap.get_connection().await?;

// Borrow the raw rusqlite::Connection on the worker for batched work.
conn.with_blocking_sqlite(|raw| {
    let tx = raw.transaction()?;
    tx.execute_batch("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT);")?;
    tx.execute("INSERT INTO t (name) VALUES (?1)", ["alice"])?;
    tx.commit()?;
    Ok::<_, SqlMiddlewareDbError>(())
})
.await?;

// Prepare once and reuse via the worker queue.
let prepared = conn
    .prepare_sqlite_statement("SELECT name FROM t WHERE id = ?1")
    .await?;
let rows = prepared.query(&[RowValues::Int(1)]).await?;
assert_eq!(rows.results[0].get("name").unwrap().as_text().unwrap(), "alice");

For more in-depth examples (batch queries, query builder usage, benchmarks), see the project README: https://github.com/derekfrye/sql-middleware/blob/main/docs/README.md

Re-exports

pub use typed as typed_api;

pub use middleware::AnyConnWrapper;

pub use middleware::BatchTarget;

pub use middleware::ConfigAndPool;

pub use middleware::ConversionMode;

pub use middleware::CustomDbRow;

pub use middleware::DatabaseType;

pub use middleware::MiddlewarePool;

pub use middleware::MiddlewarePoolConnection;

pub use middleware::ParamConverter;

pub use middleware::QueryAndParams;

pub use middleware::QueryBuilder;

pub use middleware::QueryTarget;

pub use middleware::ResultSet;

pub use middleware::RowValues;

pub use middleware::SqlMiddlewareDbError;

pub use middleware::TxOutcome;

pub use middleware::execute_batch;

pub use middleware::PgConfig;

pub use middleware::PostgresOptions;

pub use middleware::PostgresOptionsBuilder;

pub use middleware::SqliteOptions;

pub use middleware::SqliteOptionsBuilder;

pub use conversion::convert_sql_params;

pub use translation::PlaceholderStyle;

pub use translation::QueryOptions;

pub use translation::TranslationMode;

pub use translation::translate_placeholders;

Modules

conversion

Parameter conversion utilities.

error

middleware

pool

postgres

PostgreSQL backend glue.

prelude

Convenient imports for common functionality.

query

sqlite

SQLite backend glue backed by a bb8 pool of rusqlite connections.

test_helpers

Helper utilities for testing and development.

translation

tx_outcome

typed

Backend-agnostic typestate traits and enums for typed connections.

typed_postgres

Back-compat shim: the Postgres typed API now lives at postgres::typed.

typed_sqlite

Back-compat shim: the SQLite typed API now lives at sqlite::typed.