Crate sql_middleware 

SQL Middleware

Lightweight async SQL wrappers for SQLite, PostgreSQL, Turso, and SQL Server.

The crate provides a small, shared execution surface over the supported backends: pooled connections, parameter binding, result sets, prepared execution, and a typed connection API for code that wants compile-time transaction-state tracking. This crate is not an ORM.

Most applications should start with prelude.

Backends and Features

Default features are sqlite and postgres.

# Default: SQLite + PostgreSQL
sql-middleware = "0"

# SQLite only
sql-middleware = { version = "0", default-features = false, features = ["sqlite"] }

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

Main API Shape

use sql_middleware::prelude::*;
pub async fn insert_and_read(cap: &ConfigAndPool) -> Result<ResultSet, SqlMiddlewareDbError> {
    // Acquire a pooled connection (Postgres, SQLite, Turso, or MSSQL have same code here).
    let mut conn = cap.get_connection().await?;

    conn.execute_batch("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")
        .await?;

    let params = [RowValues::Int(1), RowValues::Text("alice".to_string())];
    conn.query("INSERT INTO users (id, name) VALUES ($1, $2)")
        // translate between backends for $1 vs. ?1, depending on which backend is used
        .translation(TranslationMode::ForceOn)
        .params(&params)
        .dml()
        .await?;

    let params = [RowValues::Int(1)];
    conn.query("SELECT name FROM users WHERE id = $1")
        .translation(TranslationMode::ForceOn)
        .params(&params)
        .select()
        .await
}

Placeholder translation (like in above example) is optional. When enabled, the scanner rewrites placeholder styles for the target backend while skipping quoted strings and SQL comments. Use backend-native SQL for complex SQL bodies where lightweight translation is not enough.

More Examples

See the repository README for larger examples, free-from transaction styles, benchmark notes, and backend-specific setup: https://github.com/derekfrye/sql-middleware/blob/main/docs/README.md. Some smaller notes on the API follow.

Results and Values

Rows are returned as ResultSet values containing CustomDbRow entries. Column values use RowValues:

• Null
• Int(i64)
• Float(f64)
• Text(String)
• Bool(bool)
• Timestamp(chrono::NaiveDateTime)
• JSON(serde_json::Value)
• Blob(Vec<u8>)

Convenience accessors such as as_int, as_text, as_bool, as_timestamp, and get("column_name") are available for result inspection.

Builder Pattern

If you prefer backend builder pattern:

• ConfigAndPool::sqlite_builder and ConfigAndPool::sqlite_path_builder
• ConfigAndPool::postgres_builder
• ConfigAndPool::turso_builder and ConfigAndPool::turso_path_builder
• ConfigAndPool::mssql_builder

The new_* constructors take backend option structs, such as SqliteOptions, PostgresOptions, TursoOptions, and MssqlOptions. They do not take raw strings directly.

use sql_middleware::prelude::*;

pub async fn sqlite_without_checkout_validation() -> Result<ConfigAndPool, SqlMiddlewareDbError> {
    ConfigAndPool::sqlite_builder("app.db".to_string())
        .test_on_check_out(false)
        .build()
        .await
}

SQLite and Turso default to cached statements. For dynamic SQL workloads, set StatementCacheMode::Uncached at the pool level or override a single query:

use sql_middleware::prelude::*;

pub async fn sqlite_uncached_query() -> Result<ResultSet, SqlMiddlewareDbError> {
    let cap = ConfigAndPool::sqlite_builder("app.db".to_string())
        .statement_cache(StatementCacheMode::Uncached)
        .build()
        .await?;
    let mut conn = cap.get_connection().await?;

    conn.query("SELECT id FROM users WHERE name = ?1")
        .params(&[RowValues::Text("alice".to_string())])
        .statement_cache(StatementCacheMode::Cached)
        .select()
        .await
}

For lower-level construction, you can use the option structs directly:

use sql_middleware::prelude::*;

pub async fn sqlite_from_options() -> Result<ConfigAndPool, SqlMiddlewareDbError> {
    let opts = SqliteOptions::new("app.db".to_string())
        .with_translation(true)
        .with_pool_options(MiddlewarePoolOptions::new().with_test_on_check_out(false));

    ConfigAndPool::new_sqlite(opts).await
}

Typed API example

The typed API keeps idle and in-transaction connections as distinct Rust types, and the AnyIdle / AnyTx wrappers let generic code work across backends.

use sql_middleware::prelude::*;
use sql_middleware::typed::{AnyIdle, BeginTx, Queryable, TxConn, TypedConnOps};

pub async fn run_typed_flow(mut conn: AnyIdle) -> Result<AnyIdle, SqlMiddlewareDbError> {
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS typed_users (id BIGINT PRIMARY KEY, name TEXT)",
    )
    .await?;

    let mut tx = conn.begin().await?;
    let params = [RowValues::Int(1), RowValues::Text("alice".to_string())];
    tx.query("INSERT INTO typed_users (id, name) VALUES ($1, $2)")
        .translation(TranslationMode::ForceOn)
        .params(&params)
        .dml()
        .await?;

    tx.commit().await
}

Typed API transactions

The typed API provides a higher-level transaction model:

• BeginTx::begin() moves an idle connection into an in-transaction state.
• TxConn::commit() returns the corresponding idle connection.
• TxConn::rollback() returns the corresponding idle connection.
• Dropping a typed transaction without commit or rollback triggers a best-effort rollback.

The legacy transaction wrappers return TxOutcome on commit or rollback. This carries a restored type-erased connection when the backend needs one.

SQLite Worker Helpers

SQLite uses a worker thread so blocking rusqlite calls do not stall the async runtime. MiddlewarePoolConnection exposes SQLite-specific helpers when the active connection is SQLite.

use sql_middleware::prelude::*;

async fn demo() -> Result<(), SqlMiddlewareDbError> {
    let cap = ConfigAndPool::sqlite_builder("file::memory:?cache=shared".to_string())
        .build()
        .await?;
    let mut conn = cap.get_connection().await?;

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

    let mut prepared = conn
        .prepare_sqlite_statement("SELECT name FROM t WHERE id = ?1")
        .await?;
    let rows = prepared
        .select()
        .params(&[RowValues::Int(1)])
        .all()
        .await?;

    assert_eq!(rows.results[0].get("name").unwrap().as_text().unwrap(), "alice");
    Ok(())
}

Public Re-exports

The crate root and prelude re-export the common API:

• Pools and connections: ConfigAndPool, MiddlewarePool, MiddlewarePoolConnection, AnyConnWrapper, MiddlewarePoolOptions
• Querying: QueryBuilder, QueryAndParams, QueryTarget, BatchTarget, execute_batch
• Values and results: RowValues, ResultSet, CustomDbRow
• Translation: TranslationMode, PrepareMode, QueryOptions, StatementCacheMode, PlaceholderStyle, translate_placeholders
• Errors and metadata: SqlMiddlewareDbError, DatabaseType, ConversionMode, TxOutcome
• Typed API: typed, typed_api, and backend modules typed_postgres, typed_sqlite, typed_turso, and typed_mssql

Backend modules also expose lower-level helpers for callers that already manage native backend clients or need backend-specific prepared/transaction handles.

Re-exports

pub use typed as typed_api;

pub use middleware::MiddlewarePoolOptions;

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::StatementCacheMode;

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 sqlite::SqliteParamsBuf;

pub use conversion::convert_sql_params;

pub use translation::PlaceholderStyle;

pub use translation::PrepareMode;

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.