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?;
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);
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(())
}