4

I'm writing integration tests for an already existing product. I'm using the Spring Integration Testing suite for this purpose, which allows testing a real DB connection by creating a transaction and rolling it back after the test execution.

This looks safe to be used to test also against a production database, however I searched far and wide on the web and I didn't find any reference about which environment to use for performing this kind of test.

I thought about what would be the best fit, and I realized that there are good reasons for both production databases and test databases.

Pro of using a production database:
  • Any misalignment between the test and the prod DB is revealed
  • Test on real data
  • Test on real DB configuration
Pro of using a test database:
  • Production keys are kept away from the test environment
  • If the integration test is misconfigured, the production DB won't be corrupted

So I found good reasons for both of them, and I didn't decide which DB to use for my case. Also, I didn't find any bibliography even if I've read lots of bibliographies about integration tests.

My question is: is performing integration tests on a production DB a bad practice?

4
  • 10
    "If the integration test is misconfigured, the production DB won't be corrupted" pretty much answers the question, doesn't it? Jun 6, 2022 at 13:42
  • 2
    If you want production data for testing purposes, copy some of it from your production database to your test database, after redacting the PII (subject to Sarbanes Oxley and HIPAA). Jun 6, 2022 at 14:08
  • 1
    What do you mean with production DB? 1: a copy of a production DB running in your test environment? 2: a live DB running for your clients? 1: wouldn't be a good idea but sometimes acceptable, 2: that would be a big nono.
    – Pieter B
    Jun 7, 2022 at 10:46
  • ... and rolling it back after the test execution. Do you know that some changes can not be rolled back? Right? If your ID's generation relies on sequences, you might run into troubles sooner than you expect, overall if you do continuous integration, a practice that usually runs tests periodically. Leave alone load or long run tests. These can easily drain the resources dedicated to the DB and the max number of open connections, undermining the performance of the whole system. I have seen this before. Bear in mind that your scheme is not the DB, the DB is a shared resource with many schemes
    – Laiv
    Jun 7, 2022 at 13:13

1 Answer 1

10

It depends, but testing with production databases is typically a bad idea.

Tests are for building confidence in your software

The point of QA practices such as conducting tests is to build confidence that the system delivers the value it is supposed to deliver. For example, a bank might want to ensure that transactions are reflected correctly in the account balances.

The problem here is that we are running the tests because we don't yet have confidence that it works correctly. Within reason, we have to assume that the system has bugs that our tests might discover. So, we must contain the “blast radius” of a failed test – it should not be possible for a test to impact production workloads. For example, the bank might set up a test environment in order to verify the transaction management logic. If a bug ends up deleting all accounts in the test environment, that's a fixable problem and not a “resume-generating event”.

Eventually, there will be enough confidence to deploy the system in the production environment. If you already have enough confidence, then sure, running some final tests in production is fine.

Safeguarding against problems when testing in production

With automated tests, there is a “what tests the tests?” problem. Automated tests are software too, and might contain bugs. If you rely on features like database transactions to isolate test workloads, there's always a chance that your code doesn't use these isolation features correctly and accidentally leaks changes into the production environment. So if you have to test in production for whatever reason, it would make sense to think about strategies to verify that your testing methods work as expected. Some other isolation methods might be more difficult to use incorrectly, for example if the database can provide a test access without any write permissions.

Sometimes, testing with production systems is inevitable. For example, you might be integrating with external systems that cannot provide a test environment, or setting up a testing environment with sufficient fidelity might be prohibitively expensive (i.e. more expensive than dealing with the fallout of a test gone wrong in production). It is still possible to manage risks here, in particular by testing other parts of the system first, and by also using other QA techniques than just tests, for example more thorough code reviews on those areas that can only be tested in production.

There are some strategies that can help build confidence when running on production systems, following the general idea that we want to minimize the impact of errors caused in production.

  • Most importantly, there should be a solid and tested backup strategy that makes it possible to quickly recover. There might also be application-level features to fix mistakes, for example event logs and admin dashboards. But good backup and disaster recovery practices typically mean that it would also be straightforward to set up a test environment with the same data.
  • Event-driven architectures that can always recompute the current state from an append-only event stream have an advantage here. They also make it easier to test new functionality with read-only access to data.
  • New changes for which you have little confidence shouldn't be applied in one go, but should likely use a gradual rollout – if there are problems, they will only affect a fraction of your system. For example, the bank might initially roll out a new feature to 100 customers, and then wait for a while for possible errors to appear. A live system might also have a number of dummy accounts for testing or demo purposes, but handled carelessly they could also cause problems.

Balancing costs and risks

So how to arrive at a decision on testing in production vs setting up a separate test environment? There are a number of costs, benefits, risks, and mitigations that have to be balanced against each other.

  • For testing in production, we have to consider the benefits of the high-fidelity tests made possible in production, but also the costs and risks of causing problems in production, and the costs of implementing additional mitigations and safeguards.

  • For testing in a dedicated test environment, we have the cost of setting up a test environment with sufficient fidelity.

Simply because there are fewer risks involved, setting up a dedicated test environment typically has lower expected costs. For example, testing in a production system might have small but non-zero chance of causing a business-critical (or even life-threatening) problems. It is difficult to account well for low-probability but high-impact risks. As an approximation, we can summarize that testing in production is appropriate when the following relation holds:

cost of setting up a separate test environment
>
cost × risk of causing problems in production
+ cost of mitigations and safeguards for production testing

It is worth recalling a point made above: as the system progresses through a QA process, our confidence in the system increases and the risks of using that system decrease. Thus, some degree of testing in production is probably appropriate very late in a QA process.

It may also be worth considering that as an organization and its software development processes mature, risks and costs shift. For a hobby project or a startup in stealth mode, production problems have fairly low impact, making tests in production less scary. Most companies do have customers and business-critical processes though, so that the risks make testing in production less affordable. But as an organization gains confidence to contain problems in production and to recover easily from such problems, some degree of “shift-right” QA becomes attractive.

2
  • +1 for "resume-generating event"! :D
    – Tom
    Jun 7, 2022 at 23:14
  • +1 for “Tests are for building confidence in software.” Any test that convinces me I don’t have to read code is a good test. Jun 9, 2022 at 0:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.