My #1 rule: tests should "just work". A new contributor should not have to run any setup, just a single test command. I don't care what kind of tests they are or what you prefer to call them as long as they work (in CI AND locally).

The vast majority of projects fail at this, often because some external dependency like a database than needs some manual set up.

To me, mocking out the db is a relatively cheap way to achieve rule #1. Yes, it has pitfalls, but I am unwilling to compromise on rule #1 so it's purely a question of cost vs fully automating db setup and teardown.