If I'm using Django I let Django's default test harness handle that for me - it runs each test in a transaction and rolls it back at the end of the test, which is pretty fast. https://docs.djangoproject.com/en/5.0/topics/testing/overvie...

For my other projects I'm generally using SQLite where starting a new in-memory database is so fast it's effectively free.

How does that work when the system under test uses transactions itself?

A lot of databases these days support nested transactions using savepoints, which Django's test framework can take advantage of.

There's a separate mechanism for writing tests where you need to explicitly test transaction mechanics: https://docs.djangoproject.com/en/5.0/topics/testing/tools/#...

I do this a lot for Postgres testing. In my setup, I create a single database for the entire test run. Each test creates its own schema in that database and applies the latest table definitions.

With this setup, I only eat the container creation once, while allowing every test to operate in isolation from one another, be parallelized, and test against a real database.

I do a similar trick for S3 containers by applying a unique guid prefix to the buckets in each test.

doesn't it take a lot of time to create the schema and populate it with enough data to get going?

It depends on what you’re testing. Applying the schema is pretty fast (30-40ms), compared to the container creation (1-2 seconds). If you need a lot of test data it would take time, but most of the time Im only applying enough rows to hit my test conditions. For crud apps I usually orchestrate the test setup using the public APIs of my application against the fresh instance.

Do the whole test in a transaction and roll it back at the end.