We would start a Postgres Process. We would create a new database, run all of our migrations and basic data bring up. Then we would create a new Database per Test Suite, using the one we just ran migrations as the Template.
This meant the initial bring up was a few seconds, but then each test suite would get a new database in a dozen milliseconds (IIRC).
That's probably more or less the same thing in terms of what actually happens in PostgreSQL.
All things considered, an actual database probably gives you the least gray hair, but with some careful test setup I have had good success using the savepoint/rollback trick (and it trivially supports nested fixtures as well).
With multiple databases you get the advantage of being able to run tests in parallel, so even though they might be the same under the hood they offer interfaces that suit quite different use cases
Sql server has snapshot database for something similar, and you don't have to play with transactions (that may or not breaks everything in case of errors)
If there's an error, the transaction just rolls back. I've been using this strategy for years and I have never once had a transaction "break everything."
They are many ways for a database to behave in a not obvious way...
Typically error handling, xabort (in sql server, especially if you don't want to get blocking orphan connections etc).
Comments
Another trick is to use (possibly nested) savepoints and rollback after each test, never actually committing any data.
At a $previous_job I basically also did what the post is describing.
The "best" thing we did was actually using a "template database": https://www.postgresql.org/docs/14/manage-ag-templatedbs.htm...
We would start a Postgres Process. We would create a new database, run all of our migrations and basic data bring up. Then we would create a new Database per Test Suite, using the one we just ran migrations as the Template.
This meant the initial bring up was a few seconds, but then each test suite would get a new database in a dozen milliseconds (IIRC).
That's probably more or less the same thing in terms of what actually happens in PostgreSQL.
All things considered, an actual database probably gives you the least gray hair, but with some careful test setup I have had good success using the savepoint/rollback trick (and it trivially supports nested fixtures as well).
With multiple databases you get the advantage of being able to run tests in parallel, so even though they might be the same under the hood they offer interfaces that suit quite different use cases
The catch with this is you can't test any behaviour with deferred constraints. Any activity that doesn't take place until the commit will never run.
Should be possible with "SET CONSTRAINTS".
Sql server has snapshot database for something similar, and you don't have to play with transactions (that may or not breaks everything in case of errors)
If there's an error, the transaction just rolls back. I've been using this strategy for years and I have never once had a transaction "break everything."
They are many ways for a database to behave in a not obvious way... Typically error handling, xabort (in sql server, especially if you don't want to get blocking orphan connections etc).