In my previous blog post, I highlighted the significance of Database DevOps and the value of Redgate's Flyway as a DevOps Automation tool. Today, we'll delve deeper into the technical aspect of the process and explore the integration of unit testing within your CI/CD pipeline. We'll discuss why unit testing is crucial and how it can be seamlessly implemented using Flyway. As a Database Automation specialist at Redgate, I'm thrilled to share insights, best practices, and code examples to help you optimise your database development workflows and make the most of Redgate and/or your Database for a repeatable and reliable database experience. So, let's dive in and explore the immense potential of using a tool like Flyway.
Unit Testing: Ensuring Database Quality
Unit testing is a vital component of software development, and the same holds true for databases. Incorporating unit testing into your database development process allows you to catch issues early, ensure data / schema integrity, and maintain the overall quality of your Database. Errors or causes for failure can be looped backinto automated testing so these issues are prevented earlier. With Redgate's Flyway, you have a powerful tool that enables the seamless implementation of unit testing in your CI/CD pipeline, facilitating the essential concept of "left-shifting" your Database. With Flyway, we can have not only seamless deployment of the needed T-SQL objects to non live environments, but also the automatic deployment to further endpoints after the successful round of testing.
"Left-shifting" embodies the principles of DevOps, aiming to move processes as early as possible in the pipeline. Instead of conducting testing in live environments further down the pipeline, the goal is to shift it leftwards, closely aligning it with development. This minimizes the chances of errors reaching production
The initial diagram represents a typical scenario observed in less mature database pipelines. While these pipelines may already implement DevOps practices, there is room for improvement. One primary issue is the timing of merging and integration among developers. Shifting this process to the left by utilizing a robust version control system allows for early merging and provides a stable code base to detect bugs, code and structural issues, and gain visibility across the entire project. It enables developers to understand potential concerns and delays in the delivery process while aligning other teams responsible for delivery. Shifting left when developers merge is always a good idea, but I'm open to exploring use cases where it may not be the best approach.
The subsequent shift focuses on testing, which is the main topic of this discussion. The objective is to shift testing as far left as possible, keeping bugs and issues within the development phase whenever feasible. Thoroughly testing everything before it progresses beyond development reduces the complications that may arise during deployment in downstream environments. As depicted in the diagram below, attempting to roll back and rectify changes when issues are detected in downstream environments can be challenging since they have already passed through multiple databases and merged.
The diagram above illustrates a project of my own personal creation, called Project Tundra. Leveraging Flyway's version control, automated deployments, and integration capabilities with other solutions, I can merge early and automate T-SQLT unit tests and COP tests as they flow to a build server all with just access to Redgates Flyway, all the other tools come with it.
Project Tundra, which comes from a very bad joke (T-UNit - TUNdra), but it kind of stuck. Every time a customer asks me if we can do something if the value resonates with me I add it into the project. In this particular use case, we are looking at Project Tundra: Unit Testing. The complete version of this Project will be shown in another post as its very extensive.
Why Unit Testing Matters for Databases
In the realm of database development, incorporating unit testing in a non-live environment is not just beneficial but essential. It plays a pivotal role in ensuring the integrity, reliability, and overall quality of the database. Let's explore why unit testing in a non-live environment is crucial for successful database development.
Early Validation of Changes: By conducting unit testing in a non-live environment, we can validate proposed changes before they are deployed to the live environment. This early validation allows us to catch and address potential issues, ensuring that the database will deploy as intended. It provides an opportunity to fine-tune the deployment process and establish reliable rollback mechanisms if needed.
Dependency Management: Databases often have dependencies on other components or systems. Unit testing in a non-live environment allows us to validate the correct deployment of these dependencies while preserving the structure and relationships of the database. This validation ensures that the database remains consistent and fully functional, even after applying changes.
Behavioural Analysis: Unit testing in a non-live environment enables us to assess the behaviour of the database under various scenarios. It helps identify potential risks, uncover hidden bugs or issues, and validate the expected behaviour of the database. By thoroughly testing different aspects of the database's behaviour, we gain confidence in its reliability and stability.
Test Automation: Performing unit testing in a non-live environment provides an opportunity to automate tests, saving time and effort in the long run. With automation, we can streamline the testing process, run tests regularly, and quickly identify any regressions or deviations from expected behaviour. This level of automation improves the efficiency and effectiveness of the unit testing process.
Reduced Errors in Live Environment: By thoroughly testing changes in a non-live environment, we significantly reduce the chances of errors or issues surfacing in the live environment. Catching and resolving problems early on minimizes the impact on end-users and avoids potential downtime or disruptions. It helps maintain a stable and reliable live environment.
Incorporating unit testing in a non-live environment, along with the use of tools like Flyway, empowers database developers to ensure the quality and integrity of their databases. It allows for early validation, effective dependency management, comprehensive behavioural analysis, and the automation of tests. Ultimately, this approach leads to a smoother and more reliable deployment cycle, minimizing errors and ensuring a high level of confidence in the structure and changes of the database.
Best Practice
To maximise the effectiveness of unit testing in your CI/CD pipeline, consider the following best practices:
Test Coverage: Aim for comprehensive test coverage by creating tests for critical and frequently used database objects. Focus on areas that are prone to errors or have a significant impact on your application.
Isolation and Independence: Ensure that each unit test is independent of others, meaning it can run without relying on the state of the database or the results of previous tests. This allows tests to be executed in any order.
Test Data Management: Use a combination of mock data and test data generation tools to set up the necessary data for your unit tests. This helps create consistent and reproducible test environments.
Continuous Improvement: Regularly review and update your unit tests to reflect changes in the database schema or business logic. Test-driven development (TDD) can be a valuable approach to ensure tests are created before implementing any changes.
LoopBack Errors: Every time an error or bug is found, this should be looped back into our testing process and have tests generated to prevent this kind of issue from happening again, evolving our Testing procedures.
So, how did I do it?
This project, like most of the projects and blogs I talk about come up when a customer asks me if we can do something. When it comes to Flyway, the answer to the question is often 'Yes, let me try and put something together and show you'. And we did try, the customer was a rather large enterprise account located in South Africa, a customer who had a huge interest in Unit Testing.
But, when discussing this with a customer was presented with an interesting dilemma, they did not want Flyway to deploy those T-SQL objects into Production, for understandable reasons, and they wanted to be able to fully automate the tests. The second issue was a no-brainer, obviously, we want to automate the tests, it's T-SQL. We can easily automate a way to run the tests, but not being able to deploy it into production means I need a creative way to deploy those objects to a Build Database, whilst also reminding Flyway not to deploy elsewhere, somewhat going against what Version Scripts are trying to do.
The workaround was actually very elegant, and not one I came up with on my own. My colleague Dave Ong pointed out Flyway's ability to read specific configurations for specific scripts, meaning I can specify that certain scripts should be ignored by databases, allowing the versioning of scripts to carry on without having to deploy certain changes to all environments, I've drawn a diagram below to highlight the workflow we were able to accomplish:
We still wanted to have every other object deploy to our downstream environments, but with Flyway I was able to maintain my versioned scripts whilst also deploying specific objects for my testing procedures. Once the objects made its way to Build, I was able to run my suite of tests against my Build DB which represented the newest version of Dev, and once my tests passed, I then deploy to QA.
And the end result? Something I think is incredibly cool and useful, but needed in every Database pipeline if you want to be able to rely on your deployments:
Project Tundra: A complete DevOps Project.
If you are interested, my GitHub Repo is Live and has a documented quickstart guide on how to set up this Flyway project and use my code to implement Project Tundra into your pipeline.
https://github.com/HuxleyKendell/Templatised-Unit-Testing-
Comments