Quality Assurance for Large SQL Script Releases

Question

I'm often releasing large SQL scripts for projects and minor works - my problem is that there's nothing (except the logs) to indicate that the release was successful. There could be an object missing, or metadata unregistered, or data inconsistencies. Eyeballing the database afterwards helps but, it introduces a lot of human error.

I'm looking for advice on the best way to implement QA on large scale SQL scripts so that I can understand the state of the database after the release. Ideally, something to confirm the expected database states afterwards.

I've looked into it via Google and other community forums but everything I've found has conflated the topic with Unit Testing (and dismissed it in regards to SQL), OR they go into semantics of QA rather than an approach to the problem.

Answer 1

The key of the problem is here:

Eyeballing the database afterwards helps but, it introduces a lot of human error.

Making this eyeballing more automated and systematic requires a good specification of the target situation to check. But this is not so easy as it seems:

• Script outcome depends on the db schema and your test system might not be aligned. Having a test system aligned with production schema to test the script ahead, reduces a lot the risks. But the production schema must also be recovered to retest the script if some fine-tuning was required.

• There are even case where there is not only one single comprehensive target scheme (e.g. your software allows customer to extend the tables, and you just need to adapt some columns in the common base). So a dump of your target scheme might not be sufficient to analyse if it always works.

• The script might also depend on production data, e.g. when shortening a column, or adding new referential integrity constraints. A copy of the production data is not always at hand (and sometimes it's not available for test due to sensitive information in it).

The only safe ways is therefore to build the script with the same practice as any development work:

• Think of what can go wrong. Then, in the script, check the preconditions and assumptions.
• Define the acceptance criteria for the script execution and check in the script that these criteria and other post-conditions are met. It's like with user-stories except that the user is the DBA. Checks might also require querying the data dictionary to check if the columns and tables are created/changed as expected (see example here).
• Add a log for error diagnosis in case any of the above fails.
• Let the script do these auto-tests and confirm in the end if or not it worked.
• Test the script like any piece of software. At least once in an acceptance testing environment, as close to prod as possible, even if it's with a small set of test data.
• Before running in production, make sure you are able to recover if something really messes up.

Of course, this makes the script a lot more expensive to engineer, and this cost must be balanced compared to the risks at stake. So you can remove any of the above suggestion, staying aware that it means increased risks.

Answer 2

You can write small tests in sql at the end of the script and roll back if they fail.

You can test migration scripts by comparing the database schema against a known good state (your already migrated test db) (use a db compare tool)

You can put functional tests in your codebase (assuming there is one) that require the db changes to be successfully applied for the functionality to work.

It's all do able, but it can require a large amount of effort to write tests and rollback scripts for every change

Answer 3

Ensuring the test environment aligns closely with production is critical for reliable SQL script releases, as mentioned by @Christophe. This includes using anonymised or synthetic datasets that mirror production in size and diversity. Such datasets help simulate real-world conditions and ensure the scripts perform as expected. Database versioning tools like Liquibase and dbt are great for maintaining schema consistency, in my experience. They can identify and report schema drift between environments, providing detailed comparisons that highlight discrepancies. Liquibase also offers schema versioning and rollback capabilities, which are very useful for managing large-scale releases and ensuring database integrity throughout the deployment process.

Automating the QA process reduces human error and helps to ensure reproducibility. Versioning tools, play a pivotal role in automating schema validation and rollback management. For pre-deployment validation, other tools like SQLFluff can be used to analyse script syntax and detect potential issues. Post-deployment validation is equally important, where checks for data consistency and schema integrity are performed to confirm that the database is in the expected state. For instance, checksum-based comparisons can help identify any discrepancies between pre- and post-deployment data. Additionally, automated queries can be used to validate critical operations and ensure the release has not introduced unexpected issues. By combining these tools and practices, the QA process can become robust and systematic, minimising risks and ensuring consistent outcomes.