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).
Why is maintaining dual DB implementations worth it? Like what is the tangible benefit of "my business logic remains generic"? Do you have to deploy the app in different environments with different DBs?
I wrote about this and some more in https://jmmv.dev/2023/07/unit-testing-a-web-service.html