[gitaarik]

If you're writing unit tests to test the business logic of your app, you shouldn't need a database at all. You should write your business logic so that it isn't dependent on a database, so you can really test the business logic and you don't have to mock the database.

If you're talking integration tests, then of course you should use an environment as close to production as possible.

[hakanderyal]

For some apps, business logic depends on database functionality. For example, I rely on Postgres to prevent duplicate records, or delete cascades.

In any case, using SQLite when doing TDD, and testing with postgres when you are done implementing is an acceptable trade-off for most use cases.

[jammycakes]

Unfortunately it is not always possible to maintain a clean separation between "business logic" and "data access logic." Considerations such as performance, what is and isn't supported, or data access semantics often mean that changing one necessitates changing the other.

Attempting to separate out the two often means that you end up miscategorising business logic as data access logic (resulting in poor test coverage) or data access logic as business logic (resulting in poor performance and unnecessary complexity).

[lgunsch]

I agree entirely.

As soon as your code touches the database (or a mock of it), it looses most of its re-usability. It suddenly becomes much harder to use it elsewhere. In most cases, the code is then tied to that particular framework (like Django, or Flask), and you cannot move it out into a separate independent module. This becomes a problem in large projects, and for example micro-service architectures.

It also mixes up the levels of abstraction. Getting and instantiating objects is mixed right (hard coupled) in with the code that uses them.