[pak9rabid]

I was able to accomplish this by doing each test within its own transaction session that gets rolled-back after each test. This way I'm allowed to modify the database to suit my needs for each test, then it gets magically reset back to its known state for the next test. Transaction rollbacks are very quick.

[hu3]

As a consultant, I saw many teams doing that and it works well.

The only detail is that autoincrements (SEQUENCEs for PotgreSQL folks) gets bumped even if the transaction rollsback.

So tables tend to get large ids quickly. But it's just dev database so no problem.

[francislavoie]

Unfortunately a lot of our tests use transactions themselves because we lock the user row when we do anything to ensure consistency, and I'm pretty sure nested transactions are still not a thing.

[hu3]

You can emulate nested transactions using save points. A client uses that in production. And others in unit tests.

[fanf2]

This doesn’t work for testing migrations because MySQL/MariaDB doesn’t support DDL inside transactions, unlike PostgreSQL.

[pak9rabid]

Migrations are kind of a different beast. In that case I just stand up a test environment in Docker that does what it needs, then just trash it once things have been tested/verified.