Unit testing is useful because (a) it's easier to test all the code paths in a unit when that unit is isolated, and (b) unit tests don't need to be modified in response to changes in unrelated modules. But (a) is irrelevant since we can easily put dummy data in the DB, and (b) isn't a concern since the relevant DB schemas and queries are tightly coupled to the module in question anyway.
Primarily slowness and brittleness. Tests with side effects are much more likely to be flaky, and flaky tests are the worst. Especially if they're slow and hence hard to debug.
Of course, you do test your use of the database, but in a much smaller test suite. For the business logic tests you just spin up a copy of your app with fake adapters, and you're done. Quick, deterministic tests.
If the database interactions are trivial, I agree—just use stubs. But if you've got non-trivial joins, then you'll need a separate database test suite anyway. And if there's stateful logic involving the database and you want to use fakes in unit tests, you need a whole extra set of tests to validate that the fakes behave the same way that the real database does.
I do actually prefer to avoid relying on the database for unit tests—it's cleaner, it's faster—but often testing with the database is simpler & reliable, and I can't justify the extra lines of code required for fully isolated unit tests. That's extra surface area for tech debt that could lead to stuff like fakes behaving differently from their production equivalent.
You get 90% of benefit from two kinds of tests: - verifying components work (unit tests) - verifying the system works from its interface (automation)