Comment by micvbang
16 days ago
I've been on teams where we've done this (very successfully in my opinion!) by creating helper code that automates creating a separate Postgres schema for each test, running all migrations, then running your test function before tearing it all down again. This all runs on CI/CD and developer machines, no credentials to any actual environments.
A major benefit of doing separate schemas for each test is that you can run them in parallel. In my experience, unless you have a metric ton of migrations to run for each test, the fact that your database tests can now run in parallel makes up (by a lot!) for the time you have to spend running the migrations for each test.
EDIT: usually we also make utilities to generate entities with random values, so that it's easy to make a test that e.g. tests that when you search for 5 entities among a set of 50, you only get the 5 that you know happen to match the search criteria.
Running all migrations before every tests can take you a surprisingly long way.
Once that gets a bit too slow, running migrations once before every suite and then deleting all data before each test works really well. It's pretty easy to make the deleting dynamic by querying the names of all tables and constructing one statement to clear the data, which avoids referential integrity issues. Surprisingly, `TRUNCATE` is measurably slower than `DELETE FROM`.
Another nice touch is that turning off `fsync` in postgres makes it noticeably faster, while maintaining all transactional semantics.