I think most would agree that integration tests are better. The problem is they tend to be slower. Having to initialize the system appropriately for every test (e.g. writing to the database) tends to limit the number of tests you can have.
Unit tests scale a lot better. That's why most generally use a pyramid structure: lots of unit tests, a moderate amount integration tests, and a few end-to-end tests.
An approach of “almost” integration tests can be much faster. I.e. rather than spinning up a full-blown web server or database, use fake objects for those. Uncle bob describes refactoring the architecture of a project to make integration tests faster by decoupling the web server.
Mentally, something like clojure’s ring framework make this easier to grasp: the abstraction it provides is dictionary-in, dictionary-out. Once you have something like this, there’s no need to spin up a web server to do integration testing: you just shove a bunch of dictionaries and and make sure the output dictionaries are what you expected.
A good approach is to use each technique where you get the most bang-for-buck: use unit tests only for pure functions, and decouple your system in a way that integration tests can be reduced to simple data-in data-out (which also makes them very fast)
Unit tests also tend to have fewer dependencies and are therefore more portable and robust.
If your integration tests can be reasonably be set up with a couple containers, great, but not every system is that flexible. And not every data store is that simple to provision.
Unit tests scale a lot better. That's why most generally use a pyramid structure: lots of unit tests, a moderate amount integration tests, and a few end-to-end tests.