- What happens when all the code is right, but the database is down?
- What happens when all the code is right, but the data in the database (or the time) has changed (
select count(1) from table where date > sysdate - 1
)?
- What happens when two unit tests run at once?
- What happens when one test terminates early and doesn't clean up?
All of these things make the actual database much harder to use for unit testing and you would find yourself spending a significant amount of time trying to account for them. For automated tests, the can create false negatives - where the test fails but everything is working correctly. "The build broke, time to check if the database was up last night at 2am..." and all the joys of going through that to try to figure out what went wrong.
The key to automated testing is being able to reproduce it. No matter when it runs, or what the situation of the rest of the network, the automated test shouldn't fail if the code is correct. And if you are going to have databases that might be up or down, or data that might have changed - the additional effort to try to make it work using an external database becomes excessive.
So, spin up a database in memory. Load it up with exactly the right data. Have it only be for that instance of the tests (and if another build fires off while its running, it gets its own instance). This makes it easier to rerun the test at any time.
The big danger of automated tests failing because of something beyond the control of the programmer is that those false negatives will be a "oh, the test failed, might be the database - I'll check it later" and that starts leading to ignored failing tests and a longer time frame between the failing test and the bug fix (when there is a problem with the code).
There are libraries for making testing against a database easier - that you fire up a new database schema just for your test. Though this has the additional cost of maintenance on the database (and if the database is down, it still fails) and only partially mitigates the bullet points at the start.