Unless you rely on transactions in your code. I’m going to take the schema route for a spin. You essentially utilize a schema as a namespace and destroy it when tests are complete (as opposed to the “public” default schema)

This way you don’t need to worry about provisioning a stand-alone db for each test run, but still get ephemerality. Plus the ability to run transactions.

> You essentially utilize a schema as a namespace and destroy it when tests are complete (as opposed to the “public” default schema)

Unless you rely on schemas as static namespaces in your code (even if the schemas your code expects includes the "public" common schema).

I prefer using dedicated schemas for dedicated micro-apps/services/components. Say, one for the event log, one for the (business) metadata, one for the reports.

> This way you don’t need to worry about provisioning a stand-alone db for each test run

Practically, `CREATE DATABASE tmp_xxx; \c tmp_xxx` isn't much different from `CREATE SCHEMA tmp_xxx; SET search_path TO tmp_xxx, public;`. They both do essentially the same thing when the dust settles. But the latter requires migrations to be applied to the new schema, while the former allows you to short-circuit that with a simple `CREATE DATABASE tmp_xxx FROM TEMPLATE seeded_test_db`.

We modified our code base to give us nested transactions using postgres savepoints, came in handy for automatically wrapping tests in transactions too (though you can opt-out per test)

https://buttondown.email/nelhage/archive/notes-on-some-postg... - Be aware that there is a very bad performance cliff in postgres when using savepoints.

thanks for that awesome read

Unless your transactional code relies on deferred constraints. They don’t get checked until commit time so any test that rolls back upon “success” will not reflect an error.

Deferred constraints can be awesome, but as you point out, they can be a massive blind spot in tests that rely on transactions to keep the database clean.

However, I've had success with using the `SET CONSTRAINTS ALL IMMEDIATE`[0] statement prior to rolling back the transaction.

[0]: https://www.postgresql.org/docs/current/sql-set-constraints....

There are test frameworks which automate this behavior -- both Django and Rails provide test-case libraries which run each test in a transaction, and roll back to reset. (NB they also have to provide ways to disable this -- in order to, e.g., test application code which itself can conditionally roll back -- so both can also reset database state "the hard way", at a performance penalty.)