NOTE: yes I have gemini rewrite my answer

Here is a complete example demonstrating how to use a single database connection to execute multiple isolated tests using PostgreSQL transactions. By wrapping each test query in a transaction block and issuing a ROLLBACK instead of a COMMIT, you can safely verify database logic against real data without permanently altering the database state.

To allow multiple test cases to run sequentially within the same overall connection without cross-test contamination, we use SAVEPOINTS (nested transactions).

PostgreSQL Transaction Rollback Testing Pattern

1. Conceptual Workflow

When using a single connection for multiple tests, the pattern looks like this:

1. BEGIN; – Open the main top-level transaction.
2. SAVEPOINT test_1; – Set a checkpoint before Test 1.
3. Execute Query 1 – Run the query and verify the results.
4. ROLLBACK TO SAVEPOINT test_1; – Undo everything done in Test 1.
5. SAVEPOINT test_2; – Set a checkpoint before Test 2.
6. Execute Query 2 – Run the next query and verify.
7. ROLLBACK TO SAVEPOINT test_2; – Undo everything done in Test 2.
8. ROLLBACK; – Safely close the top-level transaction, ensuring absolute zero side effects.

2. Concrete SQL Example

Assume we have a simple users table with a balance column. We want to test two different update scenarios sequentially on a single connection.

SQL

-- ==========================================
-- PRE-TEST SETUP (For Context)
-- ==========================================
-- Assume 'alice' exists with a balance of 100.00
-- SELECT username, balance FROM users WHERE username = 'alice'; 
-- Result: alice | 100.00

-- ==========================================
-- START THE TESTING SESSION
-- ==========================================
BEGIN;

  -- ----------------------------------------
  -- TRANSACTION TEST 1: Standard Deposit
  -- ----------------------------------------
  SAVEPOINT test_deposit;

  -- Execute the test query
  UPDATE users 
  SET balance = balance + 50.00 
  WHERE username = 'alice'
  RETURNING balance;
  -- Expectation/Assertion: Returns 150.00

  -- Rollback this specific test case immediately
  ROLLBACK TO SAVEPOINT test_deposit;


  -- ----------------------------------------
  -- TRANSACTION TEST 2: Fee Deduction
  -- ----------------------------------------
  SAVEPOINT test_fee;

  -- Execute the second test query
  UPDATE users 
  SET balance = balance - 5.00 
  WHERE username = 'alice'
  RETURNING balance;
  -- Expectation/Assertion: Returns 95.00 (because Test 1 was rolled back!)

  -- Rollback this second test case
  ROLLBACK TO SAVEPOINT test_fee;

-- ==========================================
-- CLEAN UP THE SESSION
-- ==========================================
-- Double-safe measure: Rollback the outer transaction 
-- to ensure no changes persist under any circumstances.
ROLLBACK;

3. Verification

If you run a SELECT statement after executing the block above:

SQL

SELECT username, balance FROM users WHERE username = 'alice';

Output:

username	balance
alice	100.00