sqlx-ext 0.1.0

Poll-scoped implicit SQLx transactions
Documentation
//! Run with `DATABASE_URL=postgres://... cargo test --test postgres_scoped`.

use futures::TryStreamExt;
use sqlx::{Postgres, QueryBuilder, Row};
use sqlx_ext::{Database, DbError, ScopedQuery, ScopedScalarQuery, Transaction};
use std::sync::{Arc, Mutex};

#[tokio::test]
async fn postgresql_scoped_transaction_commits_and_rolls_back() -> Result<(), DbError> {
    let Ok(url) = std::env::var("DATABASE_URL") else {
        eprintln!("skipping PostgreSQL integration test: DATABASE_URL is not set");
        return Ok(());
    };

    Database::<Postgres>::reset_global();
    let db = Database::<Postgres>::connect(&url).await?;

    // This intentionally runs before `set_global`: `Database::connect()` must
    // be sufficient to open a transaction and provide its scoped executor.
    db.transaction(move |_| {
        Box::pin(async move {
            sqlx::query::<Postgres>("SELECT 1").execute_scoped().await?;
            Ok(())
        })
    })
    .await?;
    Database::<Postgres>::set_global(db.clone())?;

    let one = sqlx::query::<Postgres>("SELECT 42 AS value")
        .fetch_one_scoped()
        .await?;
    assert_eq!(one.try_get::<i32, _>("value").unwrap(), 42);
    assert!(
        sqlx::query::<Postgres>("SELECT 42 AS value WHERE FALSE")
            .fetch_optional_scoped()
            .await?
            .is_none()
    );
    assert_eq!(
        sqlx::query::<Postgres>("SELECT generate_series(1, 3) AS value")
            .fetch_all_scoped()
            .await?
            .len(),
        3
    );
    let mut builder = QueryBuilder::<Postgres>::new("SELECT ");
    builder.push_bind(7_i32).push(" AS value");
    assert_eq!(
        builder
            .build()
            .fetch_one_scoped()
            .await?
            .try_get::<i32, _>("value")
            .unwrap(),
        7
    );
    let streamed: Vec<i32> = sqlx::query_scalar::<Postgres, i32>("SELECT generate_series(1, 3)")
        .fetch_scoped()
        .try_collect()
        .await?;
    assert_eq!(streamed, vec![1, 2, 3]);
    sqlx::query::<Postgres>("CREATE TABLE IF NOT EXISTS sqlx_scoped_test (id INTEGER PRIMARY KEY)")
        .execute_scoped()
        .await?;
    sqlx::query::<Postgres>("DELETE FROM sqlx_scoped_test")
        .execute_scoped()
        .await?;
    #[allow(deprecated)]
    {
        let many = sqlx::query::<Postgres>("SELECT 1 AS value")
            .fetch_many_scoped()
            .await?;
        assert!(!many.is_empty());
        let affected = sqlx::query::<Postgres>("UPDATE sqlx_scoped_test SET id = id WHERE FALSE")
            .execute_many_scoped()
            .await?;
        assert_eq!(affected.len(), 1);
    }

    db.transaction(move |_| {
        Box::pin(async move {
            sqlx::query::<Postgres>("INSERT INTO sqlx_scoped_test (id) VALUES (1)")
                .execute_scoped()
                .await?;
            let count: i64 =
                sqlx::query_scalar::<Postgres, i64>("SELECT COUNT(*) FROM sqlx_scoped_test")
                    .fetch_one_scoped()
                    .await?;
            assert_eq!(count, 1);
            Ok(())
        })
    })
    .await?;

    let committed: i64 =
        sqlx::query_scalar::<Postgres, i64>("SELECT COUNT(*) FROM sqlx_scoped_test")
            .fetch_one_scoped()
            .await?;
    assert_eq!(committed, 1);

    let rollback = db
        .transaction(|_| {
            Box::pin(async {
                sqlx::query::<Postgres>("INSERT INTO sqlx_scoped_test (id) VALUES (2)")
                    .execute_scoped()
                    .await?;
                Err::<(), DbError>(DbError::TransactionClosed)
            })
        })
        .await;
    assert!(matches!(rollback, Err(DbError::TransactionClosed)));

    let after_rollback: i64 =
        sqlx::query_scalar::<Postgres, i64>("SELECT COUNT(*) FROM sqlx_scoped_test")
            .fetch_one_scoped()
            .await?;
    assert_eq!(after_rollback, 1);

    // Nested calls intentionally reuse the outer transaction (no savepoint).
    let nested_db = db.clone();
    db.transaction(move |_| {
        Box::pin(async move {
            sqlx::query::<Postgres>("INSERT INTO sqlx_scoped_test (id) VALUES (3)")
                .execute_scoped()
                .await?;
            let inner = nested_db
                .transaction(|_| {
                    Box::pin(async {
                        sqlx::query::<Postgres>("INSERT INTO sqlx_scoped_test (id) VALUES (4)")
                            .execute_scoped()
                            .await?;
                        Err::<(), DbError>(DbError::TransactionClosed)
                    })
                })
                .await;
            assert!(matches!(inner, Err(DbError::TransactionClosed)));
            Ok(())
        })
    })
    .await?;
    let nested_count: i64 =
        sqlx::query_scalar::<Postgres, i64>("SELECT COUNT(*) FROM sqlx_scoped_test")
            .fetch_one_scoped()
            .await?;
    assert_eq!(nested_count, 3);

    let leaked: Arc<Mutex<Option<Transaction<Postgres>>>> = Arc::new(Mutex::new(None));
    let retained = Arc::clone(&leaked);
    let leak_result = db
        .transaction(move |tx| {
            *retained.lock().unwrap() = Some(tx.clone());
            Box::pin(async { Ok(()) })
        })
        .await;
    assert!(matches!(leak_result, Err(DbError::TransactionLeaked)));
    drop(leaked.lock().unwrap().take());

    sqlx::query::<Postgres>("DROP TABLE sqlx_scoped_test")
        .execute_scoped()
        .await?;
    Database::<Postgres>::reset_global();
    Ok(())
}