Using clones to improve database testing

Clones will improve the speed, accuracy and efficiency of your database test cycles

SQL Clone makes database tests much easier and faster to run because it is designed to fit into CI processes, where the resource, in this case a database, can be spun up for a quick, specific task, modified as required, and then destroyed and recreated.

Every development task ends up with a series of tests, designed to prove that your code always works properly and performs adequately, meets the functional requirements, and adheres to all business rules, even when confronted with bad data. Typically, before merging new functionality into 'main' to add a new feature to a database, you will start by unit testing the individual components, such as tables, views or functions, and then increase your reach to bring in other components in order to test processes and their integration.

Whatever methodology a database development team uses, these tests are essential, but tricky, especially when the database is very large. Each test run requires a set up that creates a copy of the database at the right version, including the data, runs the test, assesses the result, and then tears down the test set up, resetting the database to its original state, ready for the next test. Also, tests don't generally pass first time. There are usually a series of failures, corrections, reruns, until the code passes. This can mean creating and resetting a database many times. Because, traditionally, these tests are so difficult and time consuming to set up accurately, they are often neglected, due to time pressures.

If you need to test out a routine that has large repercussions for the data, but then quickly return that data to its previous state, running the test on a clone is ideal. If you work with a very large database, as a source image, and run tests that make extensive changes to the data, then the size of the clone can grow rapidly (as all the modified data pages are stored locally - see How Clones Work). However, you can immediately clean up the space after the test by removing the clone. This is like using a clone as a 'punchbag', hitting it repeatedly and having it spring back to its original form each time:

Developers can also, for example, spin up multiples clones of a database for a test cell, and run tests on each one, in parallel. Migration scripts can be applied to each clone, for example if several variants of the same release candidate need to be tested.

Using clones in these ways, the following sort of testing become much faster and easier to perform:

  • Test the behavior of several different versions of the same database - meaning that we need to recreate each different version, and run the tests on it, and compare the results. For example:
    • Test different implementations of the same set of functions or stored procedures – you can spin up multiple identical clones, side by side, install different versions of the procedure in each one and then run the tests simultaneously.
    • Verify table refactoring - run tests to prove that two versions of a database, before and after refactoring, still return the same, correct set of results.
    • Test several variations of the same release – such as 4 different versions of a payroll system, for different legislative areas
  • Run an integration test to test a business process end-to end – simply spin up a clone of a known database version, with a known dataset and then run the process, check the result is as expected, and then revert the database to its original state.
  • Test branches thoroughly before merging – create a clone from the current image, run on it the migration scripts to apply the changes for the current branch, and then run all the necessary tests. This reduces the risk that merging the branch into 'main' will introduce errors that subsequently break the build.
  • Reproduce difficult production bugs, and develop fixes, faster – images can be masked automatically, at the point of creation, so teams can develop and test fixes on clones with real, but obfuscated, data, so that sensitive data is never carelessly exposed.

Next Steps:

Unit Testing and Integration Testing with Clones

Using clones for branch-based development and testing

Using clones for testing migration scripts

Didn't find what you were looking for?