Comment by martypitt

We use TestContainers for this, and it's superb. It's a full instance of the DB, started for each unit test, running inside a docker container. TC does smart things to make sure it doesn't slow the suite too much.

we have the same strategy for testing against Kafka., etc.

where we care about data, we seed the db with data for a specific group of tests. Otherwise, we just nuke the db between each test.

Prior to doing this, we'd use in-memory db for tests, and a real db for runtime, using JPA / Hibernate to make things transferrable. But this was leaky, and some things would pass in tests then fail at runtime (or vice versa)

TestContainers has been so much better, as we're running against a real version of the database, so much smaller chance of test and runtime diverging.