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.
... 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