The usual reason is that if you actually require a disk-based DB to exist to run your tests it affects a) how easy it is to run them (e.g. on a CI build agent t

The usual reason is that if you actually require a disk-based DB to exist to run your tests it affects a) how easy it is to run them (e.g. on a CI build agent that's probably a light weight container with minimal software installed etc.) b) how quickly they run - if I can't run my entire test suite in less than a couple of minutes it's probably reducing productivity c) how much set-up code my tests need and d) it needs some mechanism to ensure tests can still safely run in parallel, esp. if your database schema isn't designed to cope with multiple independent instances sharing a DB - and having each test create and use its very own DB is almost certainly going to impact performance. Being able to use a memory-based DB that still supports enough of the SQL your code uses is a decent compromise much of the time, but it's annoying having to write tests for 90% of your functionality one way and 10% another because it relies on behavior that's different between your memory-based and disk-based DBs. Personally I still prefer to able to unit test code as much as possible without needing a DB at all, which generally means separating out the code that transfers state between the DB and in-memory structures and code that operates on said structures, but there are definitely times that's not entirely practical. Either way, I can't imagine being keen about mixing UI level code with SQL queries, even if I understand your annoyance with having to define a bunch of DTOs just to handle specific queries (I will say that typescript is actually pretty good for that sort of thing, but not sure if it could help in your case).