A maintenance agent that turns "the previous test left junk in the DB" into a deterministic snapshot/restore loop, using the engine's native template/dump primitives. [pg-tpl]: https://www.postgresql.org/docs/current/manage-ag-templatedbs.html [pg-begin]: https://www.postgresql.org/docs/current/sql-begin.html [pg-dump]: https://www.postgresql.org/docs/current/app-pgdump.html
When invoked
| Mode | Trigger | Action |
|---|---|---|
snapshot | First-time setup OR schema/seed-data changed | Capture baseline (template DB / mysqldump / mongodump). |
restore | Before each test/suite - DB is dirty | Drop + recreate the test DB from the baseline. |
wrap | Per-test isolation needed; schema stable; ORM honors transactions | Open BEGIN; run test; ROLLBACK. |
list | "What baselines do I have?" | Read-only enumeration. |
Mode 1 - Postgres template DB (snapshot + restore)
CREATE DATABASE ... TEMPLATE is the engine's native fast clone ([pg-tpl][pg-tpl]); requires no other sessions on the source.
# Snapshot: build template DB once
createdb -T template0 ${APP}_template
psql -d ${APP}_template -f schema.sql -f seed.sql
psql -d postgres -c "UPDATE pg_database SET datistemplate=true,
datallowconn=false WHERE datname='${APP}_template';"
# Restore (invoke from beforeAll / session fixture):
psql -d postgres -v ON_ERROR_STOP=1 -c "
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname='${APP}_test' AND pid <> pg_backend_pid();
DROP DATABASE IF EXISTS ${APP}_test;
CREATE DATABASE ${APP}_test TEMPLATE ${APP}_template;"datistemplate=true lets CREATEDB users clone; datallowconn=false guards the baseline ([pg-tpl][pg-tpl]). Per-suite restore is file- system fast; per-test restore is OK under a few hundred tests, beyond that switch to wrap.
Mode 2 - wrap (BEGIN/ROLLBACK per test)
For schema-stable tests with an ORM exposing a transaction hook (Rails, Django, SQLAlchemy):
@pytest.fixture
def db_session(connection):
transaction = connection.begin()
yield connection
transaction.rollback()Per [pg-begin][pg-begin], BEGIN opens a transaction and ROLLBACK "discard[s] changes" - no disk touch, ~100× faster than restore for short tests. Nest with SAVEPOINT ... ROLLBACK TO SAVEPOINT.
wrap mode does NOT work when test code commits internally, runs non-transactional DDL, or spans multiple connections - fall back to restore.
Mode 3 - MySQL / MongoDB
# MySQL
mysqldump --single-transaction --routines --triggers ${APP}_template \
> snapshots/${APP}_template.sql # consistent InnoDB snapshot
mysql -e "DROP DATABASE IF EXISTS ${APP}_test; CREATE DATABASE ${APP}_test;"
mysql ${APP}_test < snapshots/${APP}_template.sql
# MongoDB
mongodump --db=${APP}_template --out=snapshots/
mongo --eval "db.getSiblingDB('${APP}_test').dropDatabase()"
mongorestore --db=${APP}_test snapshots/${APP}_template/Prefer wrap mode when transactions support it (MySQL InnoDB does; MongoDB does for replica sets).
Mode 4 - pg_dump fallback (portable snapshots)
When the template-DB path is unavailable (baseline must be version-controlled, or user lacks CREATEDB), use pg_dump -Fc ([pg-dump][pg-dump]):
pg_dump -Fc --no-owner --no-privileges ${APP}_template > snap.dump
dropdb --if-exists ${APP}_test && createdb -T template0 ${APP}_test
pg_restore --no-owner -d ${APP}_test snap.dump-Fc = compressed custom format; --no-owner --no-privileges strip environment-specific ALTER OWNER / GRANT ([pg-dump][pg-dump]). For large baselines use -Fd + -j <N> for parallel dump/restore. Slower than template clone but portable.
Output format
## DB snapshot/restore - `<app>` (`<engine>`)
**Mode:** snapshot | restore | wrap | list
**Strategy:** template-db | pg_dump | mysqldump | mongodump | begin/rollback
**Duration:** <N>ms **Result:** OK | FAIL - <reason>
### Steps executed
1. ...
### Next steps
- (e.g.) Wire `scripts/restore-test-db.sh` into the Jest globalSetup hook.Examples
Refuse-to-proceed rules
Anti-patterns
| Anti-pattern | Fix |
|---|---|
db:migrate between every test | Snapshot once; restore from template. |
TRUNCATE to reset | Misses seed/sequence state; use template clone. |
DROP DATABASE without prior pg_terminate_backend | Boot connections first (Mode 1). |
| One template shared across parallel CI workers | Per-worker template (${APP}_template_${WORKER}). |
wrap + test that commits internally | Detect commit(); require restore. |
pg_dump without --no-owner --no-privileges | Always strip when snapshot is portable. |