13 ms·

Database mocks are not worth it

178 points by shayonj 2y ago

javcasas 2y ago
For tests, first use a real database. If you can't because the database just doesn't want to (ahem mongo ahem) then use a fake. If you can't use a fake, stop lying and use a fake. Mocks for databases are extremely brittle and complicated.
- btilly 2y ago
  Ahem MongoDB? I must admit, I've never understood what MongoDB's real use case is supposed to be. Whenever I've looked at it, there has always been a better alternative. When I've had to use it, I've had to debug performance problems that wouldn't have existed with alternative solutions. It sounds cool. But running software isn't about sounding cool. A decade ago, it was really clear. As https://aphyr.com/posts/284-call-me-maybe-mongodb https://aphyr.com/posts/284-call-me-maybe-mongodb explains, MongoDB didn't really work. But they've fixed that. So now it runs acceptably accurately. I just don't know why I'd ever want to.
  - karmakurtisaani 2y ago
    In my company they use it to store a large number of varying sized jsons. You can create indexes to make search queries lighting fast. It's also been extremely stable. I wasn't involved in setting it up tho, so can't say anything about how difficult it is to work with on the technical side.
  - dakiol 2y ago
    I don’t know. Like any other tool, it depends on how you use it. I worked for a unicorn with presence in multiple countries and they were using Mongo as main db in multiple microservices. Around 1500 engineers. It worked fine. I’m not saying it was justified, but never had perf. issues.
    - nerdponx 2y ago
      We had some pretty bad performance with $lookup queries but it all magically went away after adding some indexes. I have a lot of grievances with teams that use MongoDB, but relatively few grievances with MongoDB itself along these lines.
  - nerdponx 2y ago
    I used MongoDB at a company where engineering policy was strictly that MongoDB was our only allowable database, including in cases where it was clearly not the best choice. Were there good aspects? Sure... kind of. It was super super easy to just throw data into the database. Need to add a field? Who cares, just add it. Need to remove a field? Who cares, just remove it -- as long as the calling code is null-safe. And it was likewise super easy to store denormalized data for fast lookups when it made sense to do so, as well as deeply-nested things and arbitrary JSON blobs synced from our CMS. And queries could be stored and manipulated as actual Python dicts instead of opaque strings, whereas you normally need an ORM or query builder to do that in SQL. And you could get the best-of-both-worlds-ish with the ODMantic framework, where you could spit out a warning (but not an error) and recover gracefully if there happened to be bad data in the database. Basically it allows you to forego any foresight or serious design, and instead just throw shit together. Which is great for getting a prototype going really fast and giving the appearance of a highly productive team. That is, until you run out of new microservices to prototype and now you have to start adding features to existing code and fixing bugs. IMO it was completely "penny-wise and pound-foolish" with respect to developer time, but I can at least see the appeal if you're a particular type of engineer operating under a particular set of incentives. As for using MongoDB for things it was actually meant for (writing a ton of schemaless JSON blobs really fast and figuring out reads later), I have no idea because we never used it for that and had nothing really resembling that use case.
  - lelandbatey 2y ago
    I worked full time on a pretty seriously-trafficed product based on MongoDB for 3 years and I still don't know of anywhere I'd want to use MongoDB. I'd basically always want either a DB with a schema or a super fast opaque-store style cache. Also, their hosted offerings (MongoDB Atlas) were not well operated and they took down our company for 2 days. Our MongoDB instance stopped accepting new connections and restarting our instance via their control panel didn't bring it back up and then their support literally said "we don't know why this happened or how to fix it" for like a day and a half, while we were on their highest tier ultra platinum support contract. Ultimately, we had to do 24 hours of research and then tell their own support how to fix the problem using their special shell access. If I recall correctly, it was some issue with WiredTiger (an internal component of the Mongo version we were using). After that experience, I'd never use anything produced by MongoDB for anything; we moved everything that we possibly could out of Mongo and into more traditional RDBMS (PostgreSQL) and never had to deal with issues like that again.
    - winrid 2y ago
      This is a pretty common experience with mongo tools and support, sadly. Recently I ran into a tool that spat out "you probably want to use this option!". We paid for enterprise support so I asked why this option was not documented, and they said because it is dangerous. Can you imagine if the "-f" for rm wasn't in the man pages? Ridiculous
  - computerfan494 2y ago
    MongoDB ships with horizontal sharding out-of-the-box, has idiomatic and well-maintained drivers for pretty much every language you could want (no C library re-use), is reasonably vendor-neutral and can be run locally, and the data modeling it encourages is both preferential for some people as well as pushes users to avoid patterns that don't scale very well with other models. Whether these things are important to you is a different question, but there is a lot to like that alternatives may not have answers for. If you currently or plan on spending > 10K per month on your database, I think MongoDB is one of the strongest choices out there. Also want to add that you can definitely use MongoDB (or any other database) in a way that doesn't scale well. I have personally run MongoDB at petabyte scale and had a relatively great experience.
    - jcgrillo 2y ago
      I prefer MangoDB from an operational standpoint. But I'll concede that, as you point out, MongoDB is after all WebScale.
    - btilly 2y ago
      For most use cases, PostgreSQL is cheaper and faster to run. A lot fewer are "web scale" than think they are. For ones who are, there are other competitors like Snowflake that work well. As for scaling, it depends what you want to do. If you want to do things that look like joins, maybe it wasn't the right choice. Though I've definitely succeeded.
  - javcasas 2y ago
    Sometimes you cannot choose. Sometimes you are handed down some decisions already made, and reverting them may have a cost the business doesn't want to pay.
  - LtWorf 2y ago
    They used it in a startup where I used to work. Their data was fully relational, and they were doing the one thing that really really kills performance in mongodb: growing documents. Also it had no constraints so the data was all fucked up by the various bugs that were in the code over the years. Ah yes they used sharding. Probably there wouldn't have been a need for it if they had just used postgres, since the data was not that much.
    - winrid 2y ago
      PG and Mongo will vertically scale about the same depending on your queries. They were probably using sharding with tiny instances which is dumb. Also, large documents doesn't really hurt performance with mongo, except maybe with writes, or with large array fields due to replication implication.
      - LtWorf 2y ago
        Not large documents, but documents where the most common operation is to add a number to a list of numbers.
        winrid 2y ago
        Yeah that's a bad idea. You should instead just create a doc for each number. Each time you add a number the entire list needs copied to disk and to each secondary, so the cost grows quickly with each write.
        LtWorf 2y ago
        I know, but the "seniors" in that place didn't
- a57721 2y ago
  > If you can't because the database just doesn't want to (ahem mongo ahem) Could you elaborate what is the problem with mongo here? You can run it with testcontainers like any other database.
- cryptonector 2y ago
  It's trivial to spin up a PG instance on an AF_LOCAL (Unix domain) socket, create the DB, populate it with schema and test data, then run your app's tests. If all the tests have different non-overlapping data then you can even share the one instance for all the tests.
senex 2y ago
Does anyone have experience making tests against real databases fast? 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!
- lbriner 2y ago
  A real database should not be slow. Even with our tests running against a hosted SQL Server on a separate server, the database is never the slow part. For other tests, we run with the same database in a local Docker container with Docker Compose and it is fast and isolated/resettable. Most tests should be unit tests, which are super fast. Integration and UI tests that might use the database should be fewer and if the database is slow, it might be related to your specific application or unoptimized database queries, our database calls are usually < 10ms
- pdimitar 2y ago
  On my machine -- which is quite middle range at this point, not even high end -- I get by just fine up to 3000 tests, with Elixir at least. When I was at that contract the ~3200 tests ran in something like 40 seconds. What kinds of troubles do you have with using a real DB for testing?
- shayonj 2y ago
  Doesn’t directly answer your question but at least in Postgres I am curious about UNLOGGED mode and see if it results in faster specs. Trade off being, crash recovery doesn’t work but that’s fine in CI. There is also something to be said about keeping database transactions atomic (no 3rd party network calls, etc) to keep flakey specs to none. I have some ad hoc thoughts on this, will try to frame it proper in a post.
  - peterldowns 2y ago
    Here's what I've found — https://github.com/peterldowns/pgtestdb#how-do-i-make-it-go-faster https://github.com/peterldowns/pgtestdb#how-do-i-make-it-go-... If you come up with any better options please let me know so I can update this readme!
btilly 2y ago
I've found that replacing the database with in memory SQLite for tests is a sweet spot. Almost as fast as a mock, catches a lot of database issues. And it's really easy to do if you're using something like Django that makes automatically generating database migrations easy. 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.
- rikthevik 2y ago
  > But there should be very few of those if you're using a framework that abstracts away the database. But I really want that database-specific behaviour. :) PostgreSQL does so many amazing things (recursive CTEs, jsonb, etc) that actively make our system better. If there was a fork of Django that optimized for leveraging advanced postgres features, I'd use it.
  - ariofrio 2y ago
    Does something like PGlite work for your use case? https://pglite.dev/ https://pglite.dev/
    - devoutsalsa 2y ago
      I want test with the same API as my database, which means using the same database. Setting up an instance of Postgres/MySQL/whatever isn't hard. Maybe harder if you're using an online database that doesn't fit into a container, but that's a different problem.
    - smackeyacky 2y ago
      You can just simply run postgres, why bother with pglite? postgres installs easily on WSL2 or whatever Linux distribution you're using.
      - fweimer 2y ago
        You don't even need to install it. It doesn't take a lot of code to run initdb to create a temporary instance, write a suitable configuration file, launch the postmaster process, and delete the temporary database directory tree after terminating the database after testing is complete. On a not-too-fast Linux system, the time for all that is around half a second. Too much overhead for individual unit tests, but fast enough to run once per test suite run.
pdimitar 2y ago
I thought this was common knowledge and that it became even easier after Docker became a thing? 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 https://github.com/casey/just
- shayonj 2y ago
  +1 for this being common knowledge but there is still decent bit mocking that happens IME.
  - withinboredom 2y ago
    Mocks are great for testing specific code paths, but if you need to do that, there is usually a better way of doing it. Mocks hide contractual incongruities. For example, a function that returns either "fizz" or "buzz" that gets mocked out. If it gets changed to also return "bar", and you forget to update the mock, you've got a time-bomb in your code.
    - pdimitar 2y ago
      Completely agreed, that's why I have advocated for periodically (once a month) revisiting the test suites with them by doing re-captures of real data. There are frameworks that help with this. Is it a chore? Absolutely. But peace of mind is important.
    - 9rx 2y ago
      > If it gets changed to also return "bar", and you forget to update the mock, you've got a time-bomb in your code. In that case the problem isn't so much that you forgot to update your mock as much as the changed function no longer satisfies the interface. Ensuring that an implementation properly satisfies the interface is not within the scope of tests where mocking would be useful.
Deukhoofd 2y ago
Yeah, I ran in very much the same issues. Setting up a database for unit tests can be even more of a bother though (especially for CI pipelines, I don't want to have to run a full database there), so I took a middle road, and use my ORM to throw up a temporary in-memory SQLite database that is mostly similar to our actual database. Each unit tests scaffolds its own database, and deletes it when it's done. 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.
- pdimitar 2y ago
  Why a separate DB for each test? Just have only one DB and each test opens a transaction and then rolls it back when it's done. That way you also achieve isolation of tests so they don't interfere with each other.
  - Deukhoofd 2y ago
    The code being tested also uses transactions internally at times, so it'd mean additional complexity in the code being tested to allow for unit testing, which is not great. In my experience throwing up a database including all tables in an in-memory SQLite db is extremely fast, so it's not really a major concern.
    - pdimitar 2y ago
      OK, but is your production DB also SQLite? If not, I would not. I found the differences between it and PostgreSQL too big and was getting too many false positives. Also code complexity on a few levels of recursion of transactions is an easy thing to abstract away with almost zero performance penalty -- depending on your programming language of choice.
      - Deukhoofd 2y ago
        The production DB is MSSQL, and we scaffold it in SQLite through EF Core. The resulting SQLite DB is close enough to our production DB that we are able to catch invalid defaults, missing foreign keys, etc. in unit tests instead of later on in our testing pipeline, which helps massively in accelerating our development. It could be even better if SQLite would actually tell you which foreign key constraint failed instead of its somewhat unhelpful 'foreign key constraint failed' error, but as it is we at least know something is wrong in our code. And sure, we could probably refactor it to use transactions to shave a few seconds of running our test suite, but it'd add some additional mental complexity to our codebase to do so. In general, I prefer to keep the actual running code as simple as possible, and have any complexity that is required for unit tests be handled by the unit tests. By just recreating the database the unit tests currently handle all that complexity. The idea is definitely not a bad one though, and if your scaffolds are big enough to actually cause performance issues with your unit tests it might definitely be a consideration to not recreate the database every time.
worik 2y ago
This risks unleashing a bit of a "religious" war. 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
nerdponx 2y ago
This is one of many reasons why "isolating" I/O within your application is so important, whether that means pushing it to the edges or otherwise carefully corralling it in known-critical areas where it can't be removed. 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.
andy_ppp 2y ago
As per usual Elixir does this correctly and even has a setup that allows all tests to run against the database in a pristine way in parallel: https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.Sandbox.html https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.Sandbox.html
- napsterbr 2y ago
  A hobby project of mine (in Elixir) uses SQLite as primary database. Each test runs in its own fully isolated SQLite database. No mocking (or transaction rolling back) needed. Most of these tests take less than 1ms to run (and when they take longer, it's because of something else). This kind of setup makes the usual Ecto Sandbox approach feel slow, but I do agree that the way Elixir approaches this is great!
  - ryan-duve 2y ago
    Do you have a link you can share that demonstrates the details of this approach?
    - napsterbr 2y ago
      The entire project is open source, so sure! :D I actually have two projects that use this approach, FeebDB (which is the library I wrote to manage a "one SQLite database per client" approach) and HackerExperience (a game under development that uses FeebDB). The overall idea is simple: 1. Before tests start running, create a prop of each database. 2. The prop contains the "starting database" for each test. It may contain seed data (optional). 3. For each test, copy the prop and assign it a unique shard identifier (say, cp /props/user.db /test_data/user/874125.db). 4. The test knows the `shard_id` and can do whatever it wants with it; no one else will bother it. 5. Once ExUnit is finished, delete all shards. Both projects follow a similar approach (I wrote it first in FeebDB and copied into HackerExperience, which has some sections commented out -- I need to clean up this part of the codebase). For both projects, you will find steps 1/5 in `test/support/db.ex`, step 2 in `test/support/db/prop.ex` and steps 3/4 in `test/support/case/db.ex`. - FeebDB: https://github.com/renatomassaro/FeebDB/ https://github.com/renatomassaro/FeebDB/ - HackerExperience: https://github.com/HackerExperience/HackerExperience/ https://github.com/HackerExperience/HackerExperience/ Email is in profile in case you have follow up questions/comments :)
mrkeen 2y ago
Two points: 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.
- Volundr 2y ago
  > constraint violations You call these rakes and suggest removing them from prod, I call these vital safeguards. Defining constraints at the database layer gives me confidence that these will truly be invariants, and I won't one day discover that I've got millions of rows in my database missing critical data, with no way of knowing what should be there. Been there, done that, no desire to revisit it.
  - mrkeen 2y ago
    My coworkers call it "data integrity". At best it's a local maximum for consistency. Right now it's how we reject true facts about the business. Our partner bank tells us a customer deactivated? Our database says "no", someone gets a 500, and we get a support ticket when the customer can't sign up again (because they're still in our system).
    - oasisaimlessly 2y ago
      If this is a common problem, you should have some sort of "dead letter queue" where events like this can be held until your data model is updated to accept them.
seeknotfind 2y ago
This is not only about databases. Mocks are almost always a compromise.
Lvl999Noob 2y ago
I faced problems with flaky unit tests that used a common unit test database for the whole project. Since all the tests ran at once, the tests would sometimes fail because of concurrency issues. I never got the time to look too deeply into it. 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.
- shayonj 2y ago
  Starting each test in its own transaction and then rolling back at the end, and ensuring no 3rd party network calls in application logic in rails and Postgres apps have worked with a lot of success in my experience.
- whatnotests2 2y ago
  Transaction per test is how that works in ruby on rails. Also, the rspec parallel gem will create N test databases for running tests - one per process.
  - masklinn 2y ago
    That doesn't work if you need to commit for one reason or an other. And while in some cases you might be able to substitute subtransactions via some sort of test mode, it's not always an option depending what you're doing / testing exactly.
    - whatnotests2 2y ago
      Nested transactions handle commits. At the beginning of a test, a new transaction is begun, and it is rolled back at the end of the test. As others have pointed out in this discussion, it's pretty standard with Ruby on Rails apps, especially those which use rspec for testing.
- 2y ago
kerblang 2y ago
Was dealing with mocking voodoo garbage this morning as a result of fixing a bug. What a horrible mess just to claim a few lines of "test coverage" while ignoring critical db functionality. I wouldn't mind if the mocking frameworks weren't absurdly complex in their own right. Unfortunately our "integration" tests are even worse, corrupting data all over and flaky as hell.
isotropy 2y ago
I've seen a db mock work when 1) there was a small subteam in charge of the OR-mapping, schema structure, and included a DBA; and 2) also a design policy from the architect that all objects had to come out of factories. Under those specific circumstances, having the mock - used solely as a per-developer object cache imitating the factory interface - was critical for unblocking the people working on business logic and front-end. I wouldn't structure a team that way now.
astine 2y ago
Testing against a real database is an example of integration testing. Using mocks is for unit testing. Ideally, you want to do both. Unit testing entails isolating particular components for testing which is important because it let's you be more precise in what exactly you're testing. Using mocks also makes it easier to run automated tests because it means you don't need to have a database or credentials handy during the build process. 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.
- nwatson 2y ago
  Then just use a database for unit testing as well.
  - globular-toast 2y ago
    Then you get a slow test suite. It's important to have a fast test suite to be able to do proper test driven development (which I still believe is the most efficient and effective way to write software, in general). Unit tests should be near 100% coverage. That means a lot of tests.
    - peterldowns 2y ago
      That's not always true — database-backed tests can be extremely fast, see https://github.com/peterldowns/pgtestdb https://github.com/peterldowns/pgtestdb.
    - bluGill 2y ago
      databases can run hundreds of test in a ms. Sure without you could get to thousands in that ms - who cares.
  - astine 2y ago
    Because then you wouldn't be doing unit testing; you'd be doing integration testing. You'd also probably not be testing the database in a realistic configuration and thereby missing the whole point.
theogravity 2y ago
Just use Testcontainers (https://testcontainers.com/ https://testcontainers.com/). We use it for quickly spinning up a temporary postgres instance to run our db tests against.
diddid 2y ago
The whole point of the mocking the database is to not test the database! If you need to test the database then test the database! Just like mocking an API can hide hidden issues with the API… which is again the exact point of mocking the API. This article should really be named “mocking your database isn’t testing your database” which seems like it should be obvious.
- liontwist 2y ago
  Which is why any testing involving mocking should have low purity. You get 90% of benefit from two kinds of tests: - verifying components work (unit tests) - verifying the system works from its interface (automation)
- bccdee 2y ago
  The point of mocking the database is to avoid the hassle of spinning up a database to test against. If we ignore that, why not test our use of the database? Are we disinterested in the schema and query errors we might catch? Unit testing is useful because (a) it's easier to test all the code paths in a unit when that unit is isolated, and (b) unit tests don't need to be modified in response to changes in unrelated modules. But (a) is irrelevant since we can easily put dummy data in the DB, and (b) isn't a concern since the relevant DB schemas and queries are tightly coupled to the module in question anyway.
  - wesselbindt 2y ago
    > why not test our use of the database Primarily slowness and brittleness. Tests with side effects are much more likely to be flaky, and flaky tests are the worst. Especially if they're slow and hence hard to debug. Of course, you do test your use of the database, but in a much smaller test suite. For the business logic tests you just spin up a copy of your app with fake adapters, and you're done. Quick, deterministic tests.
    - bccdee 2y ago
      I agree that integrated tests tend to be brittle, but if we need database tests either way, the database harness can't be flaky. So any flakiness there is something that has to be fixed regardless. Slowness, I agree, but unless you're spinning up new containers for every test, the overhead of each query being real is going to be small. If the database interactions are trivial, I agree—just use stubs. But if you've got non-trivial joins, then you'll need a separate database test suite anyway. And if there's stateful logic involving the database and you want to use fakes in unit tests, you need a whole extra set of tests to validate that the fakes behave the same way that the real database does. I do actually prefer to avoid relying on the database for unit tests—it's cleaner, it's faster—but often testing with the database is simpler & reliable, and I can't justify the extra lines of code required for fully isolated unit tests. That's extra surface area for tech debt that could lead to stuff like fakes behaving differently from their production equivalent.
kapperchino 2y ago
Just mock the repository which calls the db, and to test the queries themselves, use the real db as a docker container, or integration test with an existing db
perrygeo 2y ago
Mocking the database can be done, but only if you use the database as a generic storage layer. It's not trivial to keep the mocks updated in most applications. 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.
avidphantasm 2y ago
At least in the Python world, it’s trivial to bring up an isolated DB instance using a docker compose file and a simple pytest plugin. Hardly seems worth the effort to mock the DB in cases like this.
fcmgr 2y ago
I've had some good experience with a mix of those approaches, maybe not using mocks per se, but an "in-memory database implementation" (just a wrapper around the hash map that implements the same behaviors as a repository that deals with a real database) on one hand, and testcontainers on the other. (Still, using an in-memory db is way better than mocking, the tests are not coupled to the implementation details/the underlying model). 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.
- mrkeen 2y ago
  My take is that your business logic shouldn't know about your storage tech. (Like, dependency inversion 101 right?) > Still, using an in-memory db is way better than mocking, the tests are not coupled to the implementation details/the underlying model. Isn't this backwards? The fact that you've backed your storage with a HashMap (which is 100% what I shoot for too) means your service-under-test cannot know if it's talking to an SQL database.
  - fcmgr 2y ago
    I think we're talking about the same thing :D? The service/business logic/whatever you might want to call it interacts with the storage via an interface - in prodcution that interface is implemented by a component that can talk to a real SQL database, in tests I can just create a wrapper around a hash map and use that. EDIT: What I meant when I wrote that tests are not coupled to the underlying model/details is that with a mock you have to explicitly specify "when called with this return that". With an in memory database implementation you don't need to do anything, the code will just use the interface methods like "getX" or "saveY".
brettgriffin 2y ago
I'd be really surprised if most applications that rely on databases can't just use a containerized image of their production database in any environment, including CI. In fact, the only example I can really think of is when the production database can't be Dockerized (e.g. a proprietary SaaS database like BigQuery or Snowflake). 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.
- thom 2y ago
  Are these parallelised? <3ms overhead for a clean database and test code to interact with it is very good!
- peterldowns 2y ago
  How are you doing it? Asking because I wrote an open source project (see me ~shilling~ linking it elsewhere in this comment section) for doing this, but it sounds like you've maybe come up with an even faster way.
p0w3n3d 2y ago
of course one can do the query testing separated, but I have run once or twice into problems covered by hibernate, and showing up on sql dialect on real database. Hence as always the answer is: 'it depends'
hamandcheese 2y ago
My #1 rule: tests should "just work". A new contributor should not have to run any setup, just a single test command. I don't care what kind of tests they are or what you prefer to call them as long as they work (in CI AND locally). 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.
- Threadbare 2y ago
  Tell me you've never heard of test containers without telling me you've never heard of..
- arp242 2y ago
  I agree on the "just work" principle, but stuff like "docker compose" is easy enough to use, and "standard enough". Typing "docker compose up -d" once isn't that much effort. And make sure the connection string is configurable through an environment variable so people can use $something_else if they really want/need to. Practicality beats purity, and all of that. Especially when it saves quite of complexity in the code itself – new contributors are going to struggle with that, too. In short, the setup doesn't need to be manual.
  - hamandcheese 2y ago
    The problem with this attitude (and please don't take this as a personal attack) is once a few people with this attitude come through a codebase and make their "practicality" compromises, the codebase devolves into mediocrity. For a little more context, I work on dev tools for an org of ~400 engineers. There is always someone trying to do things the quick and dirty way and move on without consideration for how the other 399 people may be impacted.
calmbonsai 2y ago
Replace "database mocks" with ANY <X> distributed API service calls in your execution flow and there's still ZERO excuse to NOT to be using SQLite or LocalStack mocks in your basic unit and pre-integration testing. 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.
- mrkeen 2y ago
  > Replace "database mocks" with ANY <X> distributed API service calls in your execution flow and ... so far so good - you're inviting the comparison of "use a real database" to "use a real X", where X could be anything - a bank, or a nuke. > ... there's still ZERO excuse to NOT to be using SQLite or LocalStack mocks in your basic unit and pre-integration testing. And I'm lost. Do we hope for a BankLite and NukeLite implementations to exist?
  - calmbonsai 2y ago
    > so far so good - you're inviting the comparison of "use a real database" to "use a real X", where X could be anything - a bank, or a nuke. I should've used different angle-brackets. "ANY <X distributed API service call>" would've been better. > And I'm lost. Do we hope for a BankLite and NukeLite implementations to exist? No, but it's still worth mocking BankLite and NukeLite with the same call interface and suitable test data, variance, and jitter. The overriding point is nothing will ever replace "the real thing", but that's not what mocks are for and it's even worth mocking 3rd party network services which can be, if not more, "quirky" than SQL query engines.
elevatedastalt 2y ago
This article seems to be implying that there's something special about DBs that makes them only testable directly instead of through mocks. 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.
nrr 2y ago
I think it's probably worth mentioning that the principal concern for tests should be proving out the application's logic, and unless you're really leaning on your database to be, e.g., a source of type and invariant enforcement for your data, any sort of database-specific testing can be deferred to integration and UAT. 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.
- jcgrillo 2y ago
  The database is often the thing that enforces the most critical application invariants, and is the primary source of errors when those invariants are violated. For example, "tenant IDs are unique" or "updates to the foobars are strictly serializable". The only thing enforcing these invariants in production is the interplay between your database schema and the queries you execute against it. So unless you exercise these invariants and the error cases against the actual database (or a lightweight containerized version thereof) in your test suite, it's your users who are actually testing the critical invariants. I'm pretty sure "don't repeat yourself" thinking has led to the vast majority of the bad ideas I've seen so far in my career. It's a truly crippling brainworm, and I wish computer schools wouldn't teach it.
  - nrr 2y ago
    > The only thing enforcing these invariants in production is the interplay between your database schema and the queries you execute against it." I'm unsure that I agree. The two examples you gave, establishing that IDs are unique and that updates to entities in the system are serializable (and linearizable while we're here), are plenty doable without having to touch the real database. (In fact, as far as the former is concerned, this dual approach to testing is what made me adopt having a wholly separate "service"[0] in my applications for doling out IDs to things. I used to work in a big Kafka shop that you've almost certainly heard of, and they taught me how to deal with the latter.) That said, I'd never advocate for just relying on one approach over the other. Do both. Absolutely do both. > I'm pretty sure "don't repeat yourself" thinking has led to the vast majority of the bad ideas I've seen so far in my career. It's a truly crippling brainworm, and I wish computer schools wouldn't teach it. I brought up WET mostly to comment that, if there's one place in software development where copying and pasting is to be encouraged, testing is it. I'd like to shelve the WET vs. DRY debate as firmly out of scope for this thread if that's alright. 0: It's a service inasmuch as an instance of a class implementing an interface can be a service, but it opens up the possibility of more easily refactoring to cross over into running against multiple databases later.
jaza 2y ago
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.
- peterldowns 2y ago
  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.
binary132 2y ago
I think the sweet spot is simple relational abstractions that do not necessarily depend on a SQL implementation. Those are extremely quick and easy to write unit tests against and to use, and don’t burden the developer. Then the library implementing them can itself be well-tested and straightforward, and even portable to other backing stores. I’m not saying “use an ORM”.
viccis 2y ago
Can anyone here who might know more than me explain the difference between dependency injection and the author's suggested pattern at the end? Is it just the same thing? Seems like that's the most common way I see to isolate external services and stub them for testing.
- globular-toast 2y ago
  Dependency injection is where functions explicitly receive abstract dependencies that they need to do their job. This is usually like a abstract repository or factory or something. In statically typed languages you'll see things like AbstractRepository etc. When the function is called it is passed a concrete implementation, like a PostgresRepository, while in testing it might be passed a fake like FakeRepository. This is opposed to when modules have implicit dependencies via importing concrete dependencies directly. In this case the only way to mock is to "patch" the import somehow. The trouble is software written this way usually has very strong ties to the concrete implementation and doesn't expect to be using a mock, which is why the author argues against it. So, no, the article doesn't do dependency injection. It's gone out of fashion for some reason even though I think it's a much better way to write software (you depend on abstractions not concrete implementations).
  - elevatedastalt 2y ago
    Like you explained, DI simply means that the function would depend on an abstract dependency which is supplied at runtime or test time. In runtime you can supply the actual DB, in test, you can supply a mock DB, OR an actual DB. Both are possible approaches. It's about how you want to wire up your tests. The author seems to be arguing for using the actual DB.
  - viccis 2y ago
    Makes sense. I do have a working understanding of DI and strongly prefer to use it (especially after having to write some unit tests for a external dependency heavy code base not designed with it earlier in the month), but I was just not quite understanding how those RSpec tests at the bottom were working with the instance_double. But looking at it closer now, there's no providing that stub to a function, it's just being created and used immediately. It seems like you could use instance_double with DI though, as basically an alternative factory for your dependencies when you want to give a faked implementation to your function. It's been ages since I've done much Ruby, but I recall them being particularly prone to monkeypatching and similar metaprogramming mocking strategies over DI or inversion of control in generation though...
lopatin 2y ago
Some valid points here. I’m awaiting a response post in 2 days called “Database mocks are worth it” which will also have some valid points. Perhaps I’m a little burned out by tech blogging lately.
- diggan 2y ago
  I mean, that's kind of engineering for you, choosing between different tradeoffs. I guess reading blogposts with various points is kind of the point, so you can easier know when to apply what, and what tradeoffs they come with.
- breckenedge 2y ago
  It’s healthy to be skeptical of patterns you read about. Don’t lose that. It’s worse to cargo-cult them.
ajuc 2y ago
I worked on a 2-layer C++ + PL/SQL system with lots of legacy business logic in PL/SQL that was slowly migrated to 3-layer architecture with Java EJB business logic using Hibernate for database operations. 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.
nabn 2y ago
yes, integration tests cover more surface area than unit tests
jokesterfr 2y ago
We do both at work (~300k sloc, >70% unit test coverage). 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.
- nerdponx 2y ago
  Mocking out a repository is IMO a lot less bad than mocking out an actual database connection. That's probably one of the biggest arguments in favor of the repository pattern: the ability to replace the repository with a test double of some kind.
ninetyninenine 2y ago
Don’t code with mocks period. Structure your code such that it has a functional core and imperative shell. All logic is unit testable (functional core). All IO and mutation Is not unit testable (imperative shell). 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.
- ljm 2y ago
  so what you should do instead is write a load of pure functions and intersperse them with impure functions, and when it comes to testing that, if someone writes a different impure function than the one you wanted, you just fail the test and trigger a post to HN: "don't code with mocks."
  - ninetyninenine 2y ago
    You failed to read and/or understand what I wrote. Also how do I fail a test for a new code addition I didn’t even write? Unit tests dont test impure functions all they do is test pure functions. Impure functions aren’t unit tested period. So when someone writes an impure function I don’t like what happens? Nothing. It wasn’t part of unit tests annyway, understand? If you’re referring to integration tests well a newly written impure function isn’t part of integration tests anyway so nothing happens in this case either. I found your last comment about how I’m triggered to write such a comment rude and lacking in even understanding what I wrote. Please try to understand my point before saying something like that… if you can’t please refrain from replying in such a way as it’s against the rules here. Thanks for reading.
esafak 2y ago
I suggest using real databases with testcontainers and dagger.
parasense 2y ago
No... for pete sake no! 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.
- bluGill 2y ago
  Nobody cares about your precious units they care that the system works. Thus you should test units that are as large as possible. That way if you add a feature you know the old ones didn't break. I've seen too many bugs where all the unit tests passed. If you are doing TDD 1000 integration test failures are no problem - the last thing you changed broke them all: either undo or fix it. the only reason to write a unit test is if the integration test would run too long.
dgunay 2y ago
In addition to what I've read here in the comments (that mocks are inaccurate, etc) I'll say that mocks also tend to just be plain overly verbose, at least in my stack. We do a lot of our heaviest testing on our usecase logic, which is almost all very imperative and calling the DB + 3rd party APIs in various ways. 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.
jerryandtom 2y ago
[dead]
temporallobe 2y ago
The one thing I greatly appreciate about Rspec (in RoR apps) is its ability to fully test the data layer. I maintain several such apps and database mocks are completely unnecessary, even going so far as to doing complete db setup and teardown between cases. Other apps I work on don’t really have this paradigm, so we resort to a combination of unit testing (JUnit) — which does involve significant mocking — and behavioral testing (Selenium) which of course indirectly tests database interaction. In the end, both methods accomplish the same goal of database testing but ideally you want “all of the above” to uncover edge cases, especially with critical infrastructure apps.
tpoacher 2y ago
> Mocking is faster and often feels more straightforward. 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)

178 points by shayonj 2y ago

For tests, first use a real database. If you can't because the database just doesn't want to (ahem mongo ahem) then use a fake. If you can't use a fake, stop lying and use a fake. Mocks for databases are extremely brittle and complicated.

btilly 2y ago
Ahem MongoDB? I must admit, I've never understood what MongoDB's real use case is supposed to be. Whenever I've looked at it, there has always been a better alternative. When I've had to use it, I've had to debug performance problems that wouldn't have existed with alternative solutions. It sounds cool. But running software isn't about sounding cool. A decade ago, it was really clear. As https://aphyr.com/posts/284-call-me-maybe-mongodb https://aphyr.com/posts/284-call-me-maybe-mongodb explains, MongoDB didn't really work. But they've fixed that. So now it runs acceptably accurately. I just don't know why I'd ever want to.
- karmakurtisaani 2y ago
  In my company they use it to store a large number of varying sized jsons. You can create indexes to make search queries lighting fast. It's also been extremely stable. I wasn't involved in setting it up tho, so can't say anything about how difficult it is to work with on the technical side.
- dakiol 2y ago
  I don’t know. Like any other tool, it depends on how you use it. I worked for a unicorn with presence in multiple countries and they were using Mongo as main db in multiple microservices. Around 1500 engineers. It worked fine. I’m not saying it was justified, but never had perf. issues.
  - nerdponx 2y ago
    We had some pretty bad performance with $lookup queries but it all magically went away after adding some indexes. I have a lot of grievances with teams that use MongoDB, but relatively few grievances with MongoDB itself along these lines.
- nerdponx 2y ago
  I used MongoDB at a company where engineering policy was strictly that MongoDB was our only allowable database, including in cases where it was clearly not the best choice. Were there good aspects? Sure... kind of. It was super super easy to just throw data into the database. Need to add a field? Who cares, just add it. Need to remove a field? Who cares, just remove it -- as long as the calling code is null-safe. And it was likewise super easy to store denormalized data for fast lookups when it made sense to do so, as well as deeply-nested things and arbitrary JSON blobs synced from our CMS. And queries could be stored and manipulated as actual Python dicts instead of opaque strings, whereas you normally need an ORM or query builder to do that in SQL. And you could get the best-of-both-worlds-ish with the ODMantic framework, where you could spit out a warning (but not an error) and recover gracefully if there happened to be bad data in the database. Basically it allows you to forego any foresight or serious design, and instead just throw shit together. Which is great for getting a prototype going really fast and giving the appearance of a highly productive team. That is, until you run out of new microservices to prototype and now you have to start adding features to existing code and fixing bugs. IMO it was completely "penny-wise and pound-foolish" with respect to developer time, but I can at least see the appeal if you're a particular type of engineer operating under a particular set of incentives. As for using MongoDB for things it was actually meant for (writing a ton of schemaless JSON blobs really fast and figuring out reads later), I have no idea because we never used it for that and had nothing really resembling that use case.
- lelandbatey 2y ago
  I worked full time on a pretty seriously-trafficed product based on MongoDB for 3 years and I still don't know of anywhere I'd want to use MongoDB. I'd basically always want either a DB with a schema or a super fast opaque-store style cache. Also, their hosted offerings (MongoDB Atlas) were not well operated and they took down our company for 2 days. Our MongoDB instance stopped accepting new connections and restarting our instance via their control panel didn't bring it back up and then their support literally said "we don't know why this happened or how to fix it" for like a day and a half, while we were on their highest tier ultra platinum support contract. Ultimately, we had to do 24 hours of research and then tell their own support how to fix the problem using their special shell access. If I recall correctly, it was some issue with WiredTiger (an internal component of the Mongo version we were using). After that experience, I'd never use anything produced by MongoDB for anything; we moved everything that we possibly could out of Mongo and into more traditional RDBMS (PostgreSQL) and never had to deal with issues like that again.
  - winrid 2y ago
    This is a pretty common experience with mongo tools and support, sadly. Recently I ran into a tool that spat out "you probably want to use this option!". We paid for enterprise support so I asked why this option was not documented, and they said because it is dangerous. Can you imagine if the "-f" for rm wasn't in the man pages? Ridiculous
- computerfan494 2y ago
  MongoDB ships with horizontal sharding out-of-the-box, has idiomatic and well-maintained drivers for pretty much every language you could want (no C library re-use), is reasonably vendor-neutral and can be run locally, and the data modeling it encourages is both preferential for some people as well as pushes users to avoid patterns that don't scale very well with other models. Whether these things are important to you is a different question, but there is a lot to like that alternatives may not have answers for. If you currently or plan on spending > 10K per month on your database, I think MongoDB is one of the strongest choices out there. Also want to add that you can definitely use MongoDB (or any other database) in a way that doesn't scale well. I have personally run MongoDB at petabyte scale and had a relatively great experience.
  - jcgrillo 2y ago
    I prefer MangoDB from an operational standpoint. But I'll concede that, as you point out, MongoDB is after all WebScale.
  - btilly 2y ago
    For most use cases, PostgreSQL is cheaper and faster to run. A lot fewer are "web scale" than think they are. For ones who are, there are other competitors like Snowflake that work well. As for scaling, it depends what you want to do. If you want to do things that look like joins, maybe it wasn't the right choice. Though I've definitely succeeded.
- javcasas 2y ago
  Sometimes you cannot choose. Sometimes you are handed down some decisions already made, and reverting them may have a cost the business doesn't want to pay.
- LtWorf 2y ago
  They used it in a startup where I used to work. Their data was fully relational, and they were doing the one thing that really really kills performance in mongodb: growing documents. Also it had no constraints so the data was all fucked up by the various bugs that were in the code over the years. Ah yes they used sharding. Probably there wouldn't have been a need for it if they had just used postgres, since the data was not that much.
  - winrid 2y ago
    PG and Mongo will vertically scale about the same depending on your queries. They were probably using sharding with tiny instances which is dumb. Also, large documents doesn't really hurt performance with mongo, except maybe with writes, or with large array fields due to replication implication.
    - LtWorf 2y ago
      Not large documents, but documents where the most common operation is to add a number to a list of numbers.
      - winrid 2y ago
        Yeah that's a bad idea. You should instead just create a doc for each number. Each time you add a number the entire list needs copied to disk and to each secondary, so the cost grows quickly with each write.
        LtWorf 2y ago
        I know, but the "seniors" in that place didn't
a57721 2y ago
> If you can't because the database just doesn't want to (ahem mongo ahem) Could you elaborate what is the problem with mongo here? You can run it with testcontainers like any other database.
cryptonector 2y ago
It's trivial to spin up a PG instance on an AF_LOCAL (Unix domain) socket, create the DB, populate it with schema and test data, then run your app's tests. If all the tests have different non-overlapping data then you can even share the one instance for all the tests.

senex 2y ago

Does anyone have experience making tests against real databases fast? 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!

lbriner 2y ago
A real database should not be slow. Even with our tests running against a hosted SQL Server on a separate server, the database is never the slow part. For other tests, we run with the same database in a local Docker container with Docker Compose and it is fast and isolated/resettable. Most tests should be unit tests, which are super fast. Integration and UI tests that might use the database should be fewer and if the database is slow, it might be related to your specific application or unoptimized database queries, our database calls are usually < 10ms
pdimitar 2y ago
On my machine -- which is quite middle range at this point, not even high end -- I get by just fine up to 3000 tests, with Elixir at least. When I was at that contract the ~3200 tests ran in something like 40 seconds. What kinds of troubles do you have with using a real DB for testing?
shayonj 2y ago
Doesn’t directly answer your question but at least in Postgres I am curious about UNLOGGED mode and see if it results in faster specs. Trade off being, crash recovery doesn’t work but that’s fine in CI. There is also something to be said about keeping database transactions atomic (no 3rd party network calls, etc) to keep flakey specs to none. I have some ad hoc thoughts on this, will try to frame it proper in a post.
- peterldowns 2y ago
  Here's what I've found — https://github.com/peterldowns/pgtestdb#how-do-i-make-it-go-faster https://github.com/peterldowns/pgtestdb#how-do-i-make-it-go-... If you come up with any better options please let me know so I can update this readme!

btilly 2y ago

I've found that replacing the database with in memory SQLite for tests is a sweet spot. Almost as fast as a mock, catches a lot of database issues. And it's really easy to do if you're using something like Django that makes automatically generating database migrations easy. 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.

rikthevik 2y ago
> But there should be very few of those if you're using a framework that abstracts away the database. But I really want that database-specific behaviour. :) PostgreSQL does so many amazing things (recursive CTEs, jsonb, etc) that actively make our system better. If there was a fork of Django that optimized for leveraging advanced postgres features, I'd use it.
- ariofrio 2y ago
  Does something like PGlite work for your use case? https://pglite.dev/ https://pglite.dev/
  - devoutsalsa 2y ago
    I want test with the same API as my database, which means using the same database. Setting up an instance of Postgres/MySQL/whatever isn't hard. Maybe harder if you're using an online database that doesn't fit into a container, but that's a different problem.
  - smackeyacky 2y ago
    You can just simply run postgres, why bother with pglite? postgres installs easily on WSL2 or whatever Linux distribution you're using.
    - fweimer 2y ago
      You don't even need to install it. It doesn't take a lot of code to run initdb to create a temporary instance, write a suitable configuration file, launch the postmaster process, and delete the temporary database directory tree after terminating the database after testing is complete. On a not-too-fast Linux system, the time for all that is around half a second. Too much overhead for individual unit tests, but fast enough to run once per test suite run.

pdimitar 2y ago

I thought this was common knowledge and that it became even easier after Docker became a thing? 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 https://github.com/casey/just

shayonj 2y ago
+1 for this being common knowledge but there is still decent bit mocking that happens IME.
- withinboredom 2y ago
  Mocks are great for testing specific code paths, but if you need to do that, there is usually a better way of doing it. Mocks hide contractual incongruities. For example, a function that returns either "fizz" or "buzz" that gets mocked out. If it gets changed to also return "bar", and you forget to update the mock, you've got a time-bomb in your code.
  - pdimitar 2y ago
    Completely agreed, that's why I have advocated for periodically (once a month) revisiting the test suites with them by doing re-captures of real data. There are frameworks that help with this. Is it a chore? Absolutely. But peace of mind is important.
  - 9rx 2y ago
    > If it gets changed to also return "bar", and you forget to update the mock, you've got a time-bomb in your code. In that case the problem isn't so much that you forgot to update your mock as much as the changed function no longer satisfies the interface. Ensuring that an implementation properly satisfies the interface is not within the scope of tests where mocking would be useful.

Deukhoofd 2y ago

Yeah, I ran in very much the same issues. Setting up a database for unit tests can be even more of a bother though (especially for CI pipelines, I don't want to have to run a full database there), so I took a middle road, and use my ORM to throw up a temporary in-memory SQLite database that is mostly similar to our actual database. Each unit tests scaffolds its own database, and deletes it when it's done. 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.

pdimitar 2y ago
Why a separate DB for each test? Just have only one DB and each test opens a transaction and then rolls it back when it's done. That way you also achieve isolation of tests so they don't interfere with each other.
- Deukhoofd 2y ago
  The code being tested also uses transactions internally at times, so it'd mean additional complexity in the code being tested to allow for unit testing, which is not great. In my experience throwing up a database including all tables in an in-memory SQLite db is extremely fast, so it's not really a major concern.
  - pdimitar 2y ago
    OK, but is your production DB also SQLite? If not, I would not. I found the differences between it and PostgreSQL too big and was getting too many false positives. Also code complexity on a few levels of recursion of transactions is an easy thing to abstract away with almost zero performance penalty -- depending on your programming language of choice.
    - Deukhoofd 2y ago
      The production DB is MSSQL, and we scaffold it in SQLite through EF Core. The resulting SQLite DB is close enough to our production DB that we are able to catch invalid defaults, missing foreign keys, etc. in unit tests instead of later on in our testing pipeline, which helps massively in accelerating our development. It could be even better if SQLite would actually tell you which foreign key constraint failed instead of its somewhat unhelpful 'foreign key constraint failed' error, but as it is we at least know something is wrong in our code. And sure, we could probably refactor it to use transactions to shave a few seconds of running our test suite, but it'd add some additional mental complexity to our codebase to do so. In general, I prefer to keep the actual running code as simple as possible, and have any complexity that is required for unit tests be handled by the unit tests. By just recreating the database the unit tests currently handle all that complexity. The idea is definitely not a bad one though, and if your scaffolds are big enough to actually cause performance issues with your unit tests it might definitely be a consideration to not recreate the database every time.

worik 2y ago

This risks unleashing a bit of a "religious" war. 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

nerdponx 2y ago

This is one of many reasons why "isolating" I/O within your application is so important, whether that means pushing it to the edges or otherwise carefully corralling it in known-critical areas where it can't be removed. 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.

andy_ppp 2y ago

As per usual Elixir does this correctly and even has a setup that allows all tests to run against the database in a pristine way in parallel: https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.Sandbox.html https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.Sandbox.html

napsterbr 2y ago
A hobby project of mine (in Elixir) uses SQLite as primary database. Each test runs in its own fully isolated SQLite database. No mocking (or transaction rolling back) needed. Most of these tests take less than 1ms to run (and when they take longer, it's because of something else). This kind of setup makes the usual Ecto Sandbox approach feel slow, but I do agree that the way Elixir approaches this is great!
- ryan-duve 2y ago
  Do you have a link you can share that demonstrates the details of this approach?
  - napsterbr 2y ago
    The entire project is open source, so sure! :D I actually have two projects that use this approach, FeebDB (which is the library I wrote to manage a "one SQLite database per client" approach) and HackerExperience (a game under development that uses FeebDB). The overall idea is simple: 1. Before tests start running, create a prop of each database. 2. The prop contains the "starting database" for each test. It may contain seed data (optional). 3. For each test, copy the prop and assign it a unique shard identifier (say, cp /props/user.db /test_data/user/874125.db). 4. The test knows the `shard_id` and can do whatever it wants with it; no one else will bother it. 5. Once ExUnit is finished, delete all shards. Both projects follow a similar approach (I wrote it first in FeebDB and copied into HackerExperience, which has some sections commented out -- I need to clean up this part of the codebase). For both projects, you will find steps 1/5 in `test/support/db.ex`, step 2 in `test/support/db/prop.ex` and steps 3/4 in `test/support/case/db.ex`. - FeebDB: https://github.com/renatomassaro/FeebDB/ https://github.com/renatomassaro/FeebDB/ - HackerExperience: https://github.com/HackerExperience/HackerExperience/ https://github.com/HackerExperience/HackerExperience/ Email is in profile in case you have follow up questions/comments :)

mrkeen 2y ago

Two points: 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.

Volundr 2y ago
> constraint violations You call these rakes and suggest removing them from prod, I call these vital safeguards. Defining constraints at the database layer gives me confidence that these will truly be invariants, and I won't one day discover that I've got millions of rows in my database missing critical data, with no way of knowing what should be there. Been there, done that, no desire to revisit it.
- mrkeen 2y ago
  My coworkers call it "data integrity". At best it's a local maximum for consistency. Right now it's how we reject true facts about the business. Our partner bank tells us a customer deactivated? Our database says "no", someone gets a 500, and we get a support ticket when the customer can't sign up again (because they're still in our system).
  - oasisaimlessly 2y ago
    If this is a common problem, you should have some sort of "dead letter queue" where events like this can be held until your data model is updated to accept them.

seeknotfind 2y ago

This is not only about databases. Mocks are almost always a compromise.

Lvl999Noob 2y ago

I faced problems with flaky unit tests that used a common unit test database for the whole project. Since all the tests ran at once, the tests would sometimes fail because of concurrency issues. I never got the time to look too deeply into it. 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.

shayonj 2y ago
Starting each test in its own transaction and then rolling back at the end, and ensuring no 3rd party network calls in application logic in rails and Postgres apps have worked with a lot of success in my experience.
whatnotests2 2y ago
Transaction per test is how that works in ruby on rails. Also, the rspec parallel gem will create N test databases for running tests - one per process.
- masklinn 2y ago
  That doesn't work if you need to commit for one reason or an other. And while in some cases you might be able to substitute subtransactions via some sort of test mode, it's not always an option depending what you're doing / testing exactly.
  - whatnotests2 2y ago
    Nested transactions handle commits. At the beginning of a test, a new transaction is begun, and it is rolled back at the end of the test. As others have pointed out in this discussion, it's pretty standard with Ruby on Rails apps, especially those which use rspec for testing.
2y ago

kerblang 2y ago

Was dealing with mocking voodoo garbage this morning as a result of fixing a bug. What a horrible mess just to claim a few lines of "test coverage" while ignoring critical db functionality. I wouldn't mind if the mocking frameworks weren't absurdly complex in their own right. Unfortunately our "integration" tests are even worse, corrupting data all over and flaky as hell.

isotropy 2y ago

I've seen a db mock work when 1) there was a small subteam in charge of the OR-mapping, schema structure, and included a DBA; and 2) also a design policy from the architect that all objects had to come out of factories. Under those specific circumstances, having the mock - used solely as a per-developer object cache imitating the factory interface - was critical for unblocking the people working on business logic and front-end. I wouldn't structure a team that way now.

astine 2y ago

Testing against a real database is an example of integration testing. Using mocks is for unit testing. Ideally, you want to do both. Unit testing entails isolating particular components for testing which is important because it let's you be more precise in what exactly you're testing. Using mocks also makes it easier to run automated tests because it means you don't need to have a database or credentials handy during the build process. 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.

nwatson 2y ago
Then just use a database for unit testing as well.
- globular-toast 2y ago
  Then you get a slow test suite. It's important to have a fast test suite to be able to do proper test driven development (which I still believe is the most efficient and effective way to write software, in general). Unit tests should be near 100% coverage. That means a lot of tests.
  - peterldowns 2y ago
    That's not always true — database-backed tests can be extremely fast, see https://github.com/peterldowns/pgtestdb https://github.com/peterldowns/pgtestdb.
  - bluGill 2y ago
    databases can run hundreds of test in a ms. Sure without you could get to thousands in that ms - who cares.
- astine 2y ago
  Because then you wouldn't be doing unit testing; you'd be doing integration testing. You'd also probably not be testing the database in a realistic configuration and thereby missing the whole point.

theogravity 2y ago

Just use Testcontainers (https://testcontainers.com/ https://testcontainers.com/). We use it for quickly spinning up a temporary postgres instance to run our db tests against.

diddid 2y ago

The whole point of the mocking the database is to not test the database! If you need to test the database then test the database! Just like mocking an API can hide hidden issues with the API… which is again the exact point of mocking the API. This article should really be named “mocking your database isn’t testing your database” which seems like it should be obvious.

liontwist 2y ago
Which is why any testing involving mocking should have low purity. You get 90% of benefit from two kinds of tests: - verifying components work (unit tests) - verifying the system works from its interface (automation)
bccdee 2y ago
The point of mocking the database is to avoid the hassle of spinning up a database to test against. If we ignore that, why not test our use of the database? Are we disinterested in the schema and query errors we might catch? Unit testing is useful because (a) it's easier to test all the code paths in a unit when that unit is isolated, and (b) unit tests don't need to be modified in response to changes in unrelated modules. But (a) is irrelevant since we can easily put dummy data in the DB, and (b) isn't a concern since the relevant DB schemas and queries are tightly coupled to the module in question anyway.
- wesselbindt 2y ago
  > why not test our use of the database Primarily slowness and brittleness. Tests with side effects are much more likely to be flaky, and flaky tests are the worst. Especially if they're slow and hence hard to debug. Of course, you do test your use of the database, but in a much smaller test suite. For the business logic tests you just spin up a copy of your app with fake adapters, and you're done. Quick, deterministic tests.
  - bccdee 2y ago
    I agree that integrated tests tend to be brittle, but if we need database tests either way, the database harness can't be flaky. So any flakiness there is something that has to be fixed regardless. Slowness, I agree, but unless you're spinning up new containers for every test, the overhead of each query being real is going to be small. If the database interactions are trivial, I agree—just use stubs. But if you've got non-trivial joins, then you'll need a separate database test suite anyway. And if there's stateful logic involving the database and you want to use fakes in unit tests, you need a whole extra set of tests to validate that the fakes behave the same way that the real database does. I do actually prefer to avoid relying on the database for unit tests—it's cleaner, it's faster—but often testing with the database is simpler & reliable, and I can't justify the extra lines of code required for fully isolated unit tests. That's extra surface area for tech debt that could lead to stuff like fakes behaving differently from their production equivalent.

kapperchino 2y ago

Just mock the repository which calls the db, and to test the queries themselves, use the real db as a docker container, or integration test with an existing db

perrygeo 2y ago

Mocking the database can be done, but only if you use the database as a generic storage layer. It's not trivial to keep the mocks updated in most applications. 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.

avidphantasm 2y ago

At least in the Python world, it’s trivial to bring up an isolated DB instance using a docker compose file and a simple pytest plugin. Hardly seems worth the effort to mock the DB in cases like this.

fcmgr 2y ago

I've had some good experience with a mix of those approaches, maybe not using mocks per se, but an "in-memory database implementation" (just a wrapper around the hash map that implements the same behaviors as a repository that deals with a real database) on one hand, and testcontainers on the other. (Still, using an in-memory db is way better than mocking, the tests are not coupled to the implementation details/the underlying model). 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.

mrkeen 2y ago
My take is that your business logic shouldn't know about your storage tech. (Like, dependency inversion 101 right?) > Still, using an in-memory db is way better than mocking, the tests are not coupled to the implementation details/the underlying model. Isn't this backwards? The fact that you've backed your storage with a HashMap (which is 100% what I shoot for too) means your service-under-test cannot know if it's talking to an SQL database.
- fcmgr 2y ago
  I think we're talking about the same thing :D? The service/business logic/whatever you might want to call it interacts with the storage via an interface - in prodcution that interface is implemented by a component that can talk to a real SQL database, in tests I can just create a wrapper around a hash map and use that. EDIT: What I meant when I wrote that tests are not coupled to the underlying model/details is that with a mock you have to explicitly specify "when called with this return that". With an in memory database implementation you don't need to do anything, the code will just use the interface methods like "getX" or "saveY".

brettgriffin 2y ago

I'd be really surprised if most applications that rely on databases can't just use a containerized image of their production database in any environment, including CI. In fact, the only example I can really think of is when the production database can't be Dockerized (e.g. a proprietary SaaS database like BigQuery or Snowflake). 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.

thom 2y ago
Are these parallelised? <3ms overhead for a clean database and test code to interact with it is very good!
peterldowns 2y ago
How are you doing it? Asking because I wrote an open source project (see me ~shilling~ linking it elsewhere in this comment section) for doing this, but it sounds like you've maybe come up with an even faster way.

p0w3n3d 2y ago

of course one can do the query testing separated, but I have run once or twice into problems covered by hibernate, and showing up on sql dialect on real database. Hence as always the answer is: 'it depends'

hamandcheese 2y ago

My #1 rule: tests should "just work". A new contributor should not have to run any setup, just a single test command. I don't care what kind of tests they are or what you prefer to call them as long as they work (in CI AND locally). 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.

Threadbare 2y ago
Tell me you've never heard of test containers without telling me you've never heard of..
arp242 2y ago
I agree on the "just work" principle, but stuff like "docker compose" is easy enough to use, and "standard enough". Typing "docker compose up -d" once isn't that much effort. And make sure the connection string is configurable through an environment variable so people can use $something_else if they really want/need to. Practicality beats purity, and all of that. Especially when it saves quite of complexity in the code itself – new contributors are going to struggle with that, too. In short, the setup doesn't need to be manual.
- hamandcheese 2y ago
  The problem with this attitude (and please don't take this as a personal attack) is once a few people with this attitude come through a codebase and make their "practicality" compromises, the codebase devolves into mediocrity. For a little more context, I work on dev tools for an org of ~400 engineers. There is always someone trying to do things the quick and dirty way and move on without consideration for how the other 399 people may be impacted.

calmbonsai 2y ago

Replace "database mocks" with ANY <X> distributed API service calls in your execution flow and there's still ZERO excuse to NOT to be using SQLite or LocalStack mocks in your basic unit and pre-integration testing. 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.

mrkeen 2y ago
> Replace "database mocks" with ANY <X> distributed API service calls in your execution flow and ... so far so good - you're inviting the comparison of "use a real database" to "use a real X", where X could be anything - a bank, or a nuke. > ... there's still ZERO excuse to NOT to be using SQLite or LocalStack mocks in your basic unit and pre-integration testing. And I'm lost. Do we hope for a BankLite and NukeLite implementations to exist?
- calmbonsai 2y ago
  > so far so good - you're inviting the comparison of "use a real database" to "use a real X", where X could be anything - a bank, or a nuke. I should've used different angle-brackets. "ANY <X distributed API service call>" would've been better. > And I'm lost. Do we hope for a BankLite and NukeLite implementations to exist? No, but it's still worth mocking BankLite and NukeLite with the same call interface and suitable test data, variance, and jitter. The overriding point is nothing will ever replace "the real thing", but that's not what mocks are for and it's even worth mocking 3rd party network services which can be, if not more, "quirky" than SQL query engines.

elevatedastalt 2y ago

This article seems to be implying that there's something special about DBs that makes them only testable directly instead of through mocks. 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.

nrr 2y ago

I think it's probably worth mentioning that the principal concern for tests should be proving out the application's logic, and unless you're really leaning on your database to be, e.g., a source of type and invariant enforcement for your data, any sort of database-specific testing can be deferred to integration and UAT. 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.

jcgrillo 2y ago
The database is often the thing that enforces the most critical application invariants, and is the primary source of errors when those invariants are violated. For example, "tenant IDs are unique" or "updates to the foobars are strictly serializable". The only thing enforcing these invariants in production is the interplay between your database schema and the queries you execute against it. So unless you exercise these invariants and the error cases against the actual database (or a lightweight containerized version thereof) in your test suite, it's your users who are actually testing the critical invariants. I'm pretty sure "don't repeat yourself" thinking has led to the vast majority of the bad ideas I've seen so far in my career. It's a truly crippling brainworm, and I wish computer schools wouldn't teach it.
- nrr 2y ago
  > The only thing enforcing these invariants in production is the interplay between your database schema and the queries you execute against it." I'm unsure that I agree. The two examples you gave, establishing that IDs are unique and that updates to entities in the system are serializable (and linearizable while we're here), are plenty doable without having to touch the real database. (In fact, as far as the former is concerned, this dual approach to testing is what made me adopt having a wholly separate "service"[0] in my applications for doling out IDs to things. I used to work in a big Kafka shop that you've almost certainly heard of, and they taught me how to deal with the latter.) That said, I'd never advocate for just relying on one approach over the other. Do both. Absolutely do both. > I'm pretty sure "don't repeat yourself" thinking has led to the vast majority of the bad ideas I've seen so far in my career. It's a truly crippling brainworm, and I wish computer schools wouldn't teach it. I brought up WET mostly to comment that, if there's one place in software development where copying and pasting is to be encouraged, testing is it. I'd like to shelve the WET vs. DRY debate as firmly out of scope for this thread if that's alright. 0: It's a service inasmuch as an instance of a class implementing an interface can be a service, but it opens up the possibility of more easily refactoring to cross over into running against multiple databases later.

jaza 2y ago

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.

peterldowns 2y ago
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.

binary132 2y ago

I think the sweet spot is simple relational abstractions that do not necessarily depend on a SQL implementation. Those are extremely quick and easy to write unit tests against and to use, and don’t burden the developer. Then the library implementing them can itself be well-tested and straightforward, and even portable to other backing stores. I’m not saying “use an ORM”.

viccis 2y ago

Can anyone here who might know more than me explain the difference between dependency injection and the author's suggested pattern at the end? Is it just the same thing? Seems like that's the most common way I see to isolate external services and stub them for testing.

globular-toast 2y ago
Dependency injection is where functions explicitly receive abstract dependencies that they need to do their job. This is usually like a abstract repository or factory or something. In statically typed languages you'll see things like AbstractRepository etc. When the function is called it is passed a concrete implementation, like a PostgresRepository, while in testing it might be passed a fake like FakeRepository. This is opposed to when modules have implicit dependencies via importing concrete dependencies directly. In this case the only way to mock is to "patch" the import somehow. The trouble is software written this way usually has very strong ties to the concrete implementation and doesn't expect to be using a mock, which is why the author argues against it. So, no, the article doesn't do dependency injection. It's gone out of fashion for some reason even though I think it's a much better way to write software (you depend on abstractions not concrete implementations).
- elevatedastalt 2y ago
  Like you explained, DI simply means that the function would depend on an abstract dependency which is supplied at runtime or test time. In runtime you can supply the actual DB, in test, you can supply a mock DB, OR an actual DB. Both are possible approaches. It's about how you want to wire up your tests. The author seems to be arguing for using the actual DB.
- viccis 2y ago
  Makes sense. I do have a working understanding of DI and strongly prefer to use it (especially after having to write some unit tests for a external dependency heavy code base not designed with it earlier in the month), but I was just not quite understanding how those RSpec tests at the bottom were working with the instance_double. But looking at it closer now, there's no providing that stub to a function, it's just being created and used immediately. It seems like you could use instance_double with DI though, as basically an alternative factory for your dependencies when you want to give a faked implementation to your function. It's been ages since I've done much Ruby, but I recall them being particularly prone to monkeypatching and similar metaprogramming mocking strategies over DI or inversion of control in generation though...

lopatin 2y ago

Some valid points here. I’m awaiting a response post in 2 days called “Database mocks are worth it” which will also have some valid points. Perhaps I’m a little burned out by tech blogging lately.

diggan 2y ago
I mean, that's kind of engineering for you, choosing between different tradeoffs. I guess reading blogposts with various points is kind of the point, so you can easier know when to apply what, and what tradeoffs they come with.
breckenedge 2y ago
It’s healthy to be skeptical of patterns you read about. Don’t lose that. It’s worse to cargo-cult them.

ajuc 2y ago

I worked on a 2-layer C++ + PL/SQL system with lots of legacy business logic in PL/SQL that was slowly migrated to 3-layer architecture with Java EJB business logic using Hibernate for database operations. 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.

nabn 2y ago

yes, integration tests cover more surface area than unit tests

jokesterfr 2y ago

We do both at work (~300k sloc, >70% unit test coverage). 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.

nerdponx 2y ago
Mocking out a repository is IMO a lot less bad than mocking out an actual database connection. That's probably one of the biggest arguments in favor of the repository pattern: the ability to replace the repository with a test double of some kind.

ninetyninenine 2y ago

Don’t code with mocks period. Structure your code such that it has a functional core and imperative shell. All logic is unit testable (functional core). All IO and mutation Is not unit testable (imperative shell). 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.

ljm 2y ago
so what you should do instead is write a load of pure functions and intersperse them with impure functions, and when it comes to testing that, if someone writes a different impure function than the one you wanted, you just fail the test and trigger a post to HN: "don't code with mocks."
- ninetyninenine 2y ago
  You failed to read and/or understand what I wrote. Also how do I fail a test for a new code addition I didn’t even write? Unit tests dont test impure functions all they do is test pure functions. Impure functions aren’t unit tested period. So when someone writes an impure function I don’t like what happens? Nothing. It wasn’t part of unit tests annyway, understand? If you’re referring to integration tests well a newly written impure function isn’t part of integration tests anyway so nothing happens in this case either. I found your last comment about how I’m triggered to write such a comment rude and lacking in even understanding what I wrote. Please try to understand my point before saying something like that… if you can’t please refrain from replying in such a way as it’s against the rules here. Thanks for reading.

esafak 2y ago

I suggest using real databases with testcontainers and dagger.

parasense 2y ago

No... for pete sake no! 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.

bluGill 2y ago
Nobody cares about your precious units they care that the system works. Thus you should test units that are as large as possible. That way if you add a feature you know the old ones didn't break. I've seen too many bugs where all the unit tests passed. If you are doing TDD 1000 integration test failures are no problem - the last thing you changed broke them all: either undo or fix it. the only reason to write a unit test is if the integration test would run too long.

dgunay 2y ago

In addition to what I've read here in the comments (that mocks are inaccurate, etc) I'll say that mocks also tend to just be plain overly verbose, at least in my stack. We do a lot of our heaviest testing on our usecase logic, which is almost all very imperative and calling the DB + 3rd party APIs in various ways. 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.

jerryandtom 2y ago

[dead]

temporallobe 2y ago

The one thing I greatly appreciate about Rspec (in RoR apps) is its ability to fully test the data layer. I maintain several such apps and database mocks are completely unnecessary, even going so far as to doing complete db setup and teardown between cases. Other apps I work on don’t really have this paradigm, so we resort to a combination of unit testing (JUnit) — which does involve significant mocking — and behavioral testing (Selenium) which of course indirectly tests database interaction. In the end, both methods accomplish the same goal of database testing but ideally you want “all of the above” to uncover edge cases, especially with critical infrastructure apps.

tpoacher 2y ago

> Mocking is faster and often feels more straightforward. 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)