The production DB is MSSQL, and we scaffold it in SQLite through EF Core. The resulting SQLite DB is close enough to our production DB that we are able to catch invalid defaults, missing foreign keys, etc. in unit tests instead of later on in our testing pipeline, which helps massively in accelerating our development. It could be even better if SQLite would actually tell you which foreign key constraint failed instead of its somewhat unhelpful 'foreign key constraint failed' error, but as it is we at least know something is wrong in our code.
And sure, we could probably refactor it to use transactions to shave a few seconds of running our test suite, but it'd add some additional mental complexity to our codebase to do so. In general, I prefer to keep the actual running code as simple as possible, and have any complexity that is required for unit tests be handled by the unit tests. By just recreating the database the unit tests currently handle all that complexity.
The idea is definitely not a bad one though, and if your scaffolds are big enough to actually cause performance issues with your unit tests it might definitely be a consideration to not recreate the database every time.
And sure, we could probably refactor it to use transactions to shave a few seconds of running our test suite, but it'd add some additional mental complexity to our codebase to do so. In general, I prefer to keep the actual running code as simple as possible, and have any complexity that is required for unit tests be handled by the unit tests. By just recreating the database the unit tests currently handle all that complexity.
The idea is definitely not a bad one though, and if your scaffolds are big enough to actually cause performance issues with your unit tests it might definitely be a consideration to not recreate the database every time.