Mocks come from a place where logic is heavily intertwined with IO. It means your code is so coupled that you can’t test logic without touching IO.

IO should be a dumb layer as much as possible. It should be Extremely general and as simple as fetching and requesting in the stupidest way possible. Then everything else should be covered by unit tests while IO is fundamentally not unit testable.

If you have a lot of complex sql statements or logic going on in the IO layer (for performance reasons don’t do this if you can’t help it) it means your IO layer needs to be treated like a logical layer. Make sure you have stored procedures and unit tests written in your database IO language. You code should interface only with stored procedures as IO and those stored procedures should be tested as unit tests in the IO layer.

I use both the mocked and real database approaches illustrated here because they ultimately focus on different things: the mocked approach validates that the model is internally consistent with itself, and the real database approach validates that the same model is externally consistent with the real world.

It may seem like a duplication of effort to do that, but tests are where you really should Write Everything Twice in a world where it's expected that you Don't Repeat Yourself.

Mocks are wishful thinking incarnate most of the time, though here and there they are absolutely needed (like 3rd party APIs without sandbox environments, or quite expensive API, or most of the time: both).

Just pick a task runner -- I use just[0] -- and make a task that brings up both Docker and your containers, then run your test task, done. Sure it's a bit fiddly the first time around but I've seen juniors conquer that in a day maximum and then your tests actually work with the real world 99% of the time.

Mocks in general are rarely worth it, the DB ones: 10x so.

[0] https://github.com/casey/just

It won't help you with database specific differences. But there should be very few of those if you're using a framework that abstracts away the database. Like Django.

Mocks for databases are extremely brittle and complicated.

A static type system that assists with that design style is nice as well. It's really useful to be able to look at a function and know right away what I/O it performs, if any. This is probably my #1 missing feature in Python, now that static type hints work well enough for most purposes.

That said, setting up and maintaining a database test harness in the developer environment can be really annoying and time consuming. Even moreso when your database is a proprietary cloud thing (e.g. Snowflake). But I have never ever regretted spending the time to set it up, whereas I have definitely regretted not having it.

Sometimes it's unavoidable that your "unit" tests need to access the database to be able to properly work out the "unit" under test. Watching those tests go from failing to passing is such a sweet feeling, it makes the pain of maintaining the test harness feel worthwhile. And then of course when you hit the actual integration tests in CI, everything almost always passes, because your unit tests are that good.

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.

There are unit tests with mocks on repository calls, and integration testing with a fresh pg database bootstraped with docker and .sql seeds.

One is fast to code, one gives more realistic feedbacks on real life execution. Both solve issues.

This is not the end of the game tho, as performance and back pressure issues arises whith a successful project... Testing is a long and interesting process.

I resonate with the sentiment of this article, but have struggled to find an alternative that’s fast enough as the test suite grows, isn’t flakey in CI, and is able to share the production schema definition for relevant relations.

I’d love to hear more from anyone that’s solved for some of these constraints!

I agree that tests should occur in the environment they are to be run in, when ever possible. Otherwise not...

There is no science in testing. There are no standardised definitions. We agree it is good, we often ignore the downsides (they exist - there are costs to tests)

I have struck examples where the testing frameworks of a language (Dart in this case) ruled out using a live HTTPS client to test. I kept getting 404 errors running it against my (test) site. I made a bug report. The good folks at Google (who I am sure are very smart - if a little stupid) had decided that the HTTPS client would always return 404 when run as part of the testing framework.

I argued, but apparently I am not so smart (I am less stupid, IMO). The HTTPS client testing had to either occur outside the test framework, or I rewrite the HTTPS client, or I wrote a mock up of some sort, all things I would have to pay for with time and effort - for no gain.

I get the point about mocking. But I am in business. I am not interested in always testing in isolation, (sometimes, sure), I want my tests to depend on the other parts of my business.

If the database goes wrong and my Gui tool test discovers it, that is a good thing, not a bad thing

Sure, there's no substitute for optimizing things for a particular query engine and EVERY database engine has SUBSTANTIAL quirks (cough, cough Redshift), but you should be crawling and walking before you try running.

Mocked tests here tend to be many times as long as the actual implementation, grow with the size of the implementation, and also tend to break immediately if you change the internals of your code (because now new things are being called, or not being called). Having hundreds of such tests can make refactors and serving new requirements a lot more annoying.

The little "trick" of using SQLite to speed up integration tests has also bit me multiple times. Turns out the various RDBMSes are not in fact, all the same, and the details start to matter a lot very quickly.

Integration testing is also important because the closer you get to running things in a production environment, the more likely you are to detect issues. Some things just won't be apparent until you start running code in production or near production. The thing to understand though, is that you want to do both if you can, not either-or.

This makes it a unit test.

> However, testing against a real database uncovers hidden pitfalls that can appear as the application matures.

This makes real-database testing not a unit test. Maybe it's integration testing.

So basically the author is against unit tests (or isn't quite sure of the difference from integration testing)

I wonder if there's a database that works sorta like git, giving one "commit" point at the start of every test and then "branching off" so each test can do its own thing, make transactions, commit them, whatever, and finally be able to roll the whole thing back.

If that can be done then each unit test can use a full database clone with actual valid data and not just test data that was fast enough to set up before the test ran.

The Java business code still often had to call the old PL/SQL stored procedures, and the state of the application was serialized on the same database using Hibernate. It made for a pretty delicate db transaction handling - in Java it was mostly declarative EJB3 transactions + some custom AOP code, and in PL/SQL we had strict guidelines of never commiting/rollbacking the transaction globally - instead every stored procedure had a savepoint at the beginning and only rollbacked to that savepoint in case of a problem.

Ensuring the system adhered to these guidlines with mocks would be very hard. Instead we wrote a special test-mode-only AOP code that was wrapping around every java -> PL/SQL call in test mode, and checked if the transactions were correctly handled (it added a programatic savepoint before the call and checked if it's still there after the call). It caught some transaction handling errors that would be VERY hard to find otherways.

It also let us find possible deadlocks during testing.

BTW writing tests for PL/SQL made me love Postgres for its transactional DDL. It's very frustrating to make repeatable tests when you can't rollback DDL changes and have to undo them one by one after the test or re-create the whole database.

For simple use cases where I mostly just read/write from the database and don't expect any of those issues mentioned in the article (constraint violations or concurrency issues, because the application is quite simple tbh, plus I already have some testcontainers based integration tests for the components that deal with a real db and I reuse them) writing tests with an in-memory db implementation is quite nice - the setup is simple and running the whole test suite is instantaneous (literally something like 1-2s for a couple thousand tests, I don't need any framework for those kind of tests).

And on the other hand if I'm relying on something like an optimistic/pesimisstic lock or a framework feature, I will write a test with the real thing, using test containers. Also have a pretty good coverage with the components that deal with queues and databases specifically with testcontainers. And on top of that just a few e2e flows written using testcontainers as well.

I'm working on a project that has ~22,000 tests that operate on Docker container of the same version of Postgres used in production. In CI it completes all the database tests in under a minute.

Unfortunately our "integration" tests are even worse, corrupting data all over and flaky as hell.

There's a continuum between extreme integration testing and extreme unit testing, each with their strengths and weaknesses.

There's a reason we don't do all testing through integration testing: it's slower, more flaky, and cripples developer velocity to a near standstill.

There's a reason we don't do all testing through pure unit testing: you have no idea whether the e2e plumbing works as expected.

DBs aren't special in this regard. That being said, it is true that investing in a light weight unit-testable setup for your DB is a good idea.

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.

Some ORMs and frameworks provide magic to do this, but as soon as you deviate from the framework it gets complex. You can build geospatial applications, time series, document, and full text search - iow leverage advanced features of Postgres extensions that most ORMs can't touch. Their behaviors can be complex and are part of the application, not simply a storage layer.

I wouldn't structure a team that way now.

The whole point of mocks for data base is not to test schema changes, it's to test a unit of code. These most certainly do not change the additional requirement of integration testing with real database tables. That usually happens after all the unit tests, obviously, because why spin up a big heavy database test pipeline if the code logic fails.

1) The subtle issues with constraint violations, default values and indexes are part of the RDBMS, not the testing strategy. The article suggests stepping on these rakes in test so that you hopefully don't step on them in prod. Another way to avoid them in prod (and to also regain the speed/reliability/simplicity of your test suite) is to not use an RDBMS in prod.

2) If you want to persist something, ask yourself if what you really have is tabular data. Do you have a lot of nulls and/or default values? Catch-all json fields? You probably have objects, not relations. Postgres may have a lot of wonderful features that relate to relations, and an ORM might have a lot of wonderful features (which appear to save you time). But if you stop requiring your objects to be relations at rest, you don't need the features, which is even faster.

That allows me to somewhat mock the database within unit tests, while still being able to test database-specific errors that might occur, like foreign key errors.