// The pool and transactions, against a real PostgreSQL when // LOUISE_PG_URL is set (skipped otherwise). import postgres as pg import postgres.{sql} import std/os import std/process.{type proc} import std/random.{type random} import std/result import std/test.{assert, assert_equal} @test fn the_pool_serves_statements :: -> () / { match os.env "LOUISE_PG_URL" { None -> () , Some(url) -> { let config = result.expect (pg.config url) "config" let pool = result.expect (pg.start_pool config 1) "the pool" // A size-1 pool, twice in a row: the connection comes back. // with_connection gives the action's own Result: unwrap both. let first = result.expect ( result.expect ( pg.with_connection pool fn { pg.query "select $1::int as n" [pg.int 41] } ) "first statement" ) "the query" match first.rows { [row] -> assert_equal (pg.row_int row "n") Ok(Some(41)) , _ -> panic "one row" } let second = result.expect ( result.expect ( pg.with_connection pool fn { pg.query_statement sql`select {41}::int + 1 as n` } ) "second statement" ) "the query" match second.rows { [row] -> assert_equal (pg.row_int row "n") Ok(Some(42)) , _ -> panic "one row" } } } } @test fn a_transaction_commits_and_rolls_back :: -> () / { match os.env "LOUISE_PG_URL" { None -> () , Some(url) -> { let config = result.expect (pg.config url) "config" let pool = result.expect (pg.start_pool config 1) "the pool" let _ = result.expect ( result.expect ( pg.with_connection pool fn { pg.execute "create table if not exists louise_pool (id int primary key)" [] } ) "create table" ) "the create" let _ = result.expect ( result.expect ( pg.with_connection pool fn { pg.execute "delete from louise_pool" [] } ) "delete" ) "the delete" // A failing transaction rolls its write back. match pg.transaction pool fn { use _ <- result.try (pg.execute "insert into louise_pool (id) values ($1)" [pg.int 1]) // A duplicate key ends the block with an Error. pg.execute "insert into louise_pool (id) values ($1)" [pg.int 1] } { Ok(_) -> panic "the duplicate key must fail" , Error(e) -> assert_equal (pg.sqlstate e) Some("23505") } // Rolled back: the table is empty. let count = result.expect ( result.expect ( pg.with_connection pool fn { pg.query "select count(*) as n from louise_pool" [] } ) "count" ) "the query" match count.rows { [row] -> assert_equal (pg.row_int row "n") Ok(Some(0)) , _ -> panic "one row" } // A succeeding one commits. result.expect ( pg.transaction pool fn { use _ <- result.try ( pg.execute_statement sql`insert into louise_pool (id) values (1)` ) use _ <- result.try ( pg.execute_statement sql`insert into louise_pool (id) values (2)` ) Ok(()) } ) "the committing transaction" let committed = result.expect ( result.expect ( pg.with_connection pool fn { pg.query "select count(*) as n from louise_pool" [] } ) "count again" ) "the query" match committed.rows { [row] -> assert_equal (pg.row_int row "n") Ok(Some(2)) , _ -> panic "one row" } } } } @test fn savepoints_roll_back_to_themselves :: -> () / { match os.env "LOUISE_PG_URL" { None -> () , Some(url) -> { let config = result.expect (pg.config url) "config" let pool = result.expect (pg.start_pool config 1) "the pool" // A failing savepoint keeps the transaction's earlier writes. result.expect ( pg.transaction pool fn { use _ <- result.try (pg.execute "insert into louise_pool (id) values (100)" []) match pg.savepoint fn { use _ <- result.try ( pg.execute "insert into louise_pool (id) values (101)" [] ) // A duplicate key rolls back to the savepoint, not the transaction. pg.execute "insert into louise_pool (id) values (100)" [] } { Ok(_) -> panic "the duplicate key must fail" , Error(e) -> assert_equal (pg.sqlstate e) Some("23505") } Ok(()) } ) "the outer transaction" let count = result.expect ( result.expect ( pg.with_connection pool fn { pg.query "select count(*) as n from louise_pool where id >= 100" [] } ) "the count" ) "the query" match count.rows { [row] -> assert_equal (pg.row_int row "n") Ok(Some(1)) // 100 stayed, 101 did not , _ -> panic "one row" } let _ = result.expect ( result.expect ( pg.with_connection pool fn { pg.execute "delete from louise_pool where id >= 100" [] } ) "cleanup" ) "the delete" } } }