sql-middleware

Sql-middleware is a lightweight async wrapper for tokio-postgres, rusqlite, libsql, experimental turso, and tiberius (SQL Server), with deadpool connection pooling (except Turso, which doesn't have deadpool backend yet), and an async api. A slim alternative to SQLx; fewer features, but striving toward a consistent api.

Motivated from trying SQLx, not liking some issue others already noted, and wanting an alternative.

Goals

Convenience functions for common SQL query patterns
Keep underlying flexibility of deadpool
Minimal overhead (ideally, just syntaxs sugar/wrapper fns)

Feature Flags

By default, postgres and sqlite database backends are enabled. You can selectively enable only the backends you need:

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

Available features:

sqlite: Enables SQLite support
postgres: Enables PostgreSQL support
mssql: Enables SQL Server support
libsql: Enables LibSQL support (local or remote)
turso: Enables Turso (in-process, SQLite-compatible). Experimental. No deadpool support (yet).
default: Enables common backends (sqlite, postgres). Enable others as needed.
test-utils: Enables test utilities for internal testing

Examples

More examples available in the tests dir, and this is in-use with a tiny little website app, rusty-golf.

Importing

You can use the prelude to import everything you need, or import item by item.

use sql_middleware::prelude::*;

Get a connection from the pool

Similar api regardless of db backend.

let mut cfg = deadpool_postgres
    ::Config::new();
cfg.dbname = Some("test_db"
    .to_string());
cfg.host = Some("192.168.2.1"
    .to_string());
cfg.port = Some(5432);
cfg.user = Some("test user"
    .to_string());
cfg.password = Some("passwd"
    .to_string());

let c = ConfigAndPool
    ::new_postgres(cfg)
    .await?;
let conn = MiddlewarePool
    ::get_connection(&c.pool)
    .await?;

let cfg = 
    "file::memory:?cache=shared"
    .to_string();
// Or file-based:
// let cfg = "./data.db".to_string();





// same api for connection
// sqlite just has fewer required 
// config items (no port, etc.)
let c = ConfigAndPool::
    new_sqlite(cfg)
    .await?;
let conn = MiddlewarePool
    ::get_connection(&c.pool)
    .await?;

Note: The SQLite example applies to SQLite, LibSQL, and Turso. Swap the constructor as needed: new_sqlite(path), new_libsql(path), or new_turso(path). For Turso, there’s no deadpool pooling; get_connection creates a fresh connection.

Batch query w/o params

Same api regardless of db backend.

// simple api for batch queries
let ddl_query =
    include_str!("/path/to/test1.sql");
conn.execute_batch(&ddl_query).await?;

Parameterized Queries

Consistent API using QueryAndParams. Only the placeholder syntax differs.

// PostgreSQL uses $-style placeholders
let q = QueryAndParams::new(
    "INSERT INTO test (espn_id, name
        , ins_ts) VALUES ($1, $2, $3)",
    vec![
        RowValues::Int(123456),
        RowValues::Text(
            "test name".to_string()),
        RowValues::Timestamp(
            parse_from_str(
            "2021-08-06 16:00:00",
            "%Y-%m-%d %H:%M:%S",
        )?),
    ],
);

// Execute directly with RowValues
conn.execute_dml(&q.query, &q.params)
    .await?;

// SQLite-compatible backends use ? or ?N
let q = QueryAndParams::new(
    "INSERT INTO test (espn_id, name
        , ins_ts) VALUES (?1, ?2, ?3)",
    vec![
        RowValues::Int(123456),
        RowValues::Text(
            "test name".to_string()),
        RowValues::Timestamp(
            parse_from_str(
            "2021-08-06 16:00:00",
            "%Y-%m-%d %H:%M:%S",
        )?),
    ],
);

// The same for all `sqlite` variants
conn.execute_dml(&q.query, &q.params)
    .await?;

Note: For LibSQL, construct with ConfigAndPool::new_libsql(path). For Turso, use ConfigAndPool::new_turso(path); there is no deadpool pooling for Turso — get_connection creates a new connection.

Queries without parameters

You can issue no-parameter queries directly, the same for PostgreSQL, SQLite, LibSQL, and Turso:

// Either build a QueryAndParams
let query = QueryAndParams::new_without_params("SELECT * FROM users");
let results = conn.execute_select(&query.query, &[]).await?;

// Or pass the SQL string directly
let results2 = conn.execute_select("SELECT * FROM users", &[]).await?;

Transactions with custom logic

Here, the APIs differ a bit, because the underlying libraries are different. It doesn't appear easy to make these consistent without hiding underlying library capabilities. This is the most similar way to do queries w this middleware if you need custom app logic between transaction() and commit().

See further examples in the tests directory:

// Get db-specific connection
let pg_conn = match &conn {
    Postgres(pg)
        => pg,
    _ => panic!("Expected connection"),
};

// Get client
// (N/A for Turso; no deadpool yet)
sql_middleware::turso::begin_transaction(t)
let client: &mut tokio_postgres::Client 
    = pg_conn.as_mut();

// Begin transaction
// Turso: `let tx = 
//  turso::begin_transaction(t).await?;`
let tx = client.transaction().await?;

// could run custom logic between stmts

// Prepare statement
// Turso: `let mut stmt 
//    = tx.prepare("... ?1, ?2 ...").await?;`
let stmt = tx.prepare(&q.query).await?;

// Convert params (Postgres)
// Turso/LibSQL not necessary
let converted_params = 
    convert_sql_params::<PostgresParams>(
        &q.params,
        ConversionMode::Execute
    )?;

// Execute query
// Turso: `tx.execute_prepared(
//  &mut stmt, &q.params).await?;`
// LibSQL: `tx.execute_prepared(
//  &stmt, &q.params).await?;`
let rows = tx.execute(
    &stmt, 
    converted_params.as_refs()
).await?;

// Commit
tx.commit().await?;

// Get SQLite-specific connection
let sqlite_conn = match &conn {
    Sqlite(sqlite)
        => sqlite,
    _ => panic!("Expected connection"),
};

// Use interact for async tx
let rows = sqlite_conn
    .interact(move |conn| {
        let tx = conn.transaction()?;
        
        // Convert parameters
        let converted_params = 
            convert_sql_params::
                <SqliteParamsExecute>(
                &q.params,
                ConversionMode::Execute
            )?;
            
// could run custom logic between stmts

        // Create parameter references
        let param_refs: Vec<&dyn ToSql> =
            converted_params.0.iter()
                .map(|v| v as &dyn ToSql)
                .collect();
        
        // Prepare and execute
        let rows = {
            let mut stmt = tx.prepare(
                &q.query)?;
            stmt.execute(&param_refs[..])?
        };
        
        tx.commit()?;
        
        Ok::<_, SqlMiddlewareDbError>(rows)
    })
    .await?;

Using the AsyncDatabaseExecutor trait

The AsyncDatabaseExecutor trait provides a consistent interface for database operations:

// This works for PostgreSQL, SQLite, LibSQL, and Turso connections
async fn insert_user<T: AsyncDatabaseExecutor>(
    conn: &mut T,
    user_id: i32,
    name: &str
) -> Result<(), SqlMiddlewareDbError> {
    let query = QueryAndParams::new(
        // Use appropriate parameter syntax for your DB
        // PostgreSQL: "VALUES ($1, $2)"
        // SQLite/LibSQL/Turso: "VALUES (?, ?)" or "VALUES (?1, ?2)"
        "INSERT INTO users (id, name) VALUES ($1, $2)",
        vec![
            RowValues::Int(user_id),
            RowValues::Text(name.to_string()),
        ]
    );
    
    // Execute query through the trait. Placeholder style in `query.query`
    // must match the active backend.
    conn.execute_dml(&query.query, &query.params).await?;
    
    Ok(())
}

Design Documents

Async Design Decisions - Explains why some functions are marked with #[allow(clippy::unused_async)] and our async API design philosophy.

Developing and Testing

Build with defaults (sqlite, postgres): cargo build
Include Turso backend: cargo build --features turso
Run tests (defaults): cargo test
Run with Turso: cargo test --features turso
Run with LibSQL: cargo test --features libsql

Release Notes

0.1.9 (unreleased): Switched the project license from BSD-2-Clause to MIT and added third-party notice documentation.

sql-middleware 0.2.0