This is an implementation of this pattern for go: https://github.com/DATA-DOG/go-txdb
Ruby on Rails has this built-in, of course.
Granted, there are pros/cons (you also can't test code that issues txn begins/commits, although that is rare), but personally that particular con outweighs the pros imo/for me.
Personally, what I do is create a new database at the beginning of the tests, and have the tests use that. There are some downsides; if you use a random database name, you can run as many tests as you like in parallel, but some percentage of test runs will never run the cleanup code and you will have stale test databases around that need to be cleaned up. (You can simply not retain files written by your database after the CI run, of course, but then you can't debug the failing tests as easily.) If you use a fixed database name, then you will only ever have one copy of the data and won't need to clean anything up. But you can't run tests in parallel -- "go test ./..." or whatever does run tests in parallel by default, so you will see conflicts between tests this way. (In the past, I've done things the second way, but upon writing this comment, I would probably do things the first way in the future.)
Overall, I strongly agree with the advice to run your tests against an as-real-as-possible database. You detect a lot of issues this way -- queries that don't parse, database settings that affect the results (things as dumb as time zones, for example), and you get actual error codes. For example, I used to run all of my queries in a block that retried them 3 times on retryable errors, and gradually built up a list of non-retriable error codes; "syntax error", "column doesn't have a default", etc. This list is easy to build while directly developing against a database with the same settings as production, but will never work when running against mocks or sqlite. (In theory, your database vendor should provide some client library that does things like this... but nobody does.)
Let's imagine (I think) a common scenario: a simple Go project, composed of a number of modules owned by different team members or small groups, and shared packages etc.
Now, I need to add some stateful component and I reason whether to use, say, MySQL or something else.
If I choose MySQL and I need a real MySQL to run tests against it, now I need to prepare some docker-compose or equivalent scaffolding and shove it down ever other team member's throat.
I want them to be able to run all tests without having to stop and think what tests their change (possibly in a shared package) might affect.
Sure, CI will catch things. But deferring all tests failure detection to the CI stage adds latency, and often troubleshooting issues that happen on CI is hard if it's hard to rerun the same thing locally.
I witnessed a "pressure" towards preferring pure-go solutions so that the team doesn't have to switch to a more "complex" build/test harness.
Granted, this is only a problem if you managed so far to do all you needed to do with the pure Go build system (which I have to say, I like very much and I do need to have a pretty good reason before I abandon/"upgrade" to something else).
Compared to maintaining mocks that don't actually detect issues like invalid queries, or missing defaults, or mapping semantics between language types and database types, this is a lot simpler. You just need one thing running, and you only need to set it up once. Every feature that is available to production code is now available to your tests.
You can also have your tests launch a database container for you. I found this slow, and that the setup overhead of one instruction in the README was worthwhile.
Finally, you might not need the database for every test. If you have Service 1 that depends on the database and Service 2 that only depends on Service 1, writing fakes for Service 1 for the Service 2 tests to use to avoid the database is productive. Service 2 will want to test the error cases for Service 1 anyway, so you will have to have the provision for things like "make the next call to service 1 hang indefinitely", etc. so you will be writing that anyway. Some people like all integration tests to go all the way to the bottom of the stack, but I prefer testing only the boundary in depth and making simpler end-to-end tests. YMMV.
Here we're talking about record/replay mocks, which are based on a real database. So, of course somebody will have to run that container with MySQL so that the tests will be run in record mode (e.g. when developing the tests). But, then if your teammates don't develop those tests, they don't technically need a real database. You still want them to run your tests because those tests might depend on some course your teammates touch (e.g. some common library code)
For years I took the mocking route, but it means there is a whole class of bugs that your tests won't catch, and the scaffolding was often fragile. Few years back I switched to just running a real DB in a container - very happy with this way.
Nothing to manage for the regular application/test developer, no persistent background services, nothing exposed outside of the local machine, no dependency on Docker. You just need to have Postgres on your $PATH, and even that is handled for you by Nix.
We use the same approach to transparently also run the same tests against MSSQL, and it is mostly transparent (except for all the ugly hacks involved to run an isolated MSSQL instance outside of Docker container).
Yes, there is some scaffolding involved, but it contains zero model logic, and ~never has to be touched again once it works.
I still don't understand why in the software world we keep reinventing the same patterns again and again ad nauseum.