My DB heavy app, when I run a Go unit test, it spins up a DB instance, populates it, runs the tests, and drops the DB. Never ever any mocks. The best part about unit tests isn't testing the Go code. It is testing the SQL.
We do the same thing. The core of our service is ingesting data and applying transformations to it. There’s so many permutations and complex interactions in here that the only way to ensure a refactor hasn’t broken one of these interactions is to document those edge cases by piping data through the system and reading it back out. We have thousands of these tests and it’s all tidy controlled via docker compose. It takes about 15 minutes to run the test suite. Sure I wish it was faster - but the real unlock is that we can make big sweeping refactors without breaking behaviors of the system. The organizational speed unlock this kind of safety net is well worth a bit of slowness in CI.
We also have mocks/stubs/spies in our unit tests. Those are great for producing hard-to-trigger edge cases. But contract testing? The contract is the data flow. In the end it’s all about using the right tool for the right test. There is no one-size-fits-all.
If your SQL is isolated to one place then you only need to test that single package with a real DB and can use mocks everywhere else. Your mocks can be tested with the exact same test suite as the SQL package, so you know it conforms to the same contract.
If you have SQL scattered all over the place... Leave the spaghetti for dinner.
Yes, it takes longer to run your tests. So be it.