Came here to say this: I’ve found that using SQLite for tests is a good sweet spot, and maintaining dual DB implementations ensures my business logic remains generic. But there is no replacement for running the tests agains the real database periodically (maybe only in CI for example).

I wrote about this and some more in https://jmmv.dev/2023/07/unit-testing-a-web-service.html