You should do, and I usually do, both "pure" unit tests (with all I/O - database calls, reading/writing local files, 3rd party API calls, etc - being mocked), and integration tests (with ideally all I/O really happening). More of the former, and less of the latter, in line with the "testing pyramid" approach.
There is value in testing "assuming that the database returns 3 rows with Foo IDs and Foo Descriptions, my code should serve a JSON response containing the Foo IDs and the Foo Descriptions concatenated together". And there is also value in testing "when my code executes SELECT id, description FROM foo LIMIT 3, the real database should return 3 rows with Foo IDs and Foo Descriptions". Granted, there's also a big advantage in that the former can run much faster, with zero environment setup / teardown complexity required. But my main point is, different test suites should test different things.
However, if you're unable or unwilling to write two different test suites, and if you're able to easily and reliably have a database available in dev / CI, then ok, I concede, just write integration tests.
Well said, agreed. One point from my experience — once you can do "as fast as unit" or "as fast as pure" tests that exercise your database, you can basically stop doing integration tests, because using the real database gives you so much confidence in the correctness of your system.
There is value in testing "assuming that the database returns 3 rows with Foo IDs and Foo Descriptions, my code should serve a JSON response containing the Foo IDs and the Foo Descriptions concatenated together". And there is also value in testing "when my code executes SELECT id, description FROM foo LIMIT 3, the real database should return 3 rows with Foo IDs and Foo Descriptions". Granted, there's also a big advantage in that the former can run much faster, with zero environment setup / teardown complexity required. But my main point is, different test suites should test different things.
However, if you're unable or unwilling to write two different test suites, and if you're able to easily and reliably have a database available in dev / CI, then ok, I concede, just write integration tests.