寻求PostgreSQL外部事务特性：无需改源码的测试后数据库回滚方案

Great question—this is a super common pain point when scaling test suites, and PostgreSQL has some perfect solutions that let you ditch those slow db migrate rollback && db migrate && db seed cycles without touching your app code. Let’s walk through the best options:

1. 全局事务包裹（单连接场景，最快最直接） #

If your test setup uses a single database connection for all app requests (or you can configure your test environment to do this), this is the easiest win:

• Before tests run: Execute BEGIN; in your test script’s setup hook.
• Run all tests: Every database write from your app will live inside this single transaction—none of it gets committed to the database yet.
• After tests finish: Run ROLLBACK; to erase all changes in one go.

This is blazingly fast because rolling back a transaction is just a metadata operation for PostgreSQL, not a full database rebuild. No app code changes needed—you just add these two commands to your test wrapper script.

2. 多连接场景（比如 app 用连接池） #

If your app uses multiple database connections (like a web server with a connection pool), a single global transaction won’t cover all operations. Here are two solid workarounds:

Option A: Template Database Reset #

This isn’t strictly transaction-based, but it’s way faster than your current migrate/rollback flow:

• Pre-test setup: Create a "clean" template database once (after running your db migrate && db seed):

  CREATE DATABASE test_template WITH TEMPLATE your_test_db OWNER your_test_user;
  

• Before each test run: Spin up a fresh copy of the template:

  CREATE DATABASE current_test_run WITH TEMPLATE test_template;
  

• Update your test config: Point your app’s database connection string to current_test_run for the duration of the tests.
• After tests: Drop the temporary database:

  DROP DATABASE current_test_run;
  

This works because copying a PostgreSQL database is optimized (it uses filesystem-level copies under the hood), so it’s way quicker than re-running migrations every time. No app code changes required—just adjust your test script’s database setup steps.

Option B: Snapshot Isolation (PostgreSQL 13+) #

If you want to stick with transaction-like behavior across multiple connections, PostgreSQL 13 introduced snapshot sharing:

1. Start a base transaction in your test setup and export a snapshot:

   BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   SELECT pg_export_snapshot(); -- Returns a snapshot ID like '00000001-1'
   

2. Modify your app’s connection string to include this snapshot: add options='-c snapshot=00000001-1' to the connection URL.
3. Run tests: All app connections will use this fixed snapshot, so they won’t see any changes from other transactions. Any writes the app makes will be isolated to their own transactions, which you can roll back individually or let PostgreSQL clean up if you don’t commit them.

Note: This works best if your app doesn’t explicitly commit transactions (or you can disable autocommit in the test environment via options='-c autocommit=off').

3. Bonus: Use pgTAP for Database-Level Tests #

If you’re writing database-specific tests with pgTAP, it handles transaction isolation automatically. Every test runs inside its own transaction that gets rolled back at the end—no manual setup required, and zero app code changes.

• DDL Operations: PostgreSQL auto-commits transactions when you run DDL (like CREATE TABLE). If your tests include DDL (or your migrations run DDL), the global transaction approach won’t work—stick with the template database method instead.
• Autocommit: Make sure your app’s test environment doesn’t have autocommit enabled (most frameworks let you disable this via config, or you can set it via PostgreSQL connection params). If autocommit is on, your app will commit changes mid-test, which can’t be rolled back with a single global rollback.

内容的提问来源于stack exchange，提问作者Владимир Говорухин