Rock’n’Rule and the challenge of introducing code analysis across the DB DevOps process
Written by Alicja Leszczyńska & Andrea Angella
Down Tools Week and this year’s winner
Down Tools Week is an event when Redgaters take a break from their regular product work and focus on a number of new initiatives. The undertaken projects address problems which have never been tackled but which a number of Redgate employees find worthwhile and inspiring. Everyone gets a chance to work on the projects they are the most interested in and collaborate with people they don’t ordinarily work with. Additional surprises such as a chocolate fountain (yay!) and a VR headset make the week even more fun. At the end of the week, teams present their achievements and a winner is selected.
This year’s winning team — Rock’n’Rule — undertook the challenge of introducing code analysis rules across the DB DevOps process. Here is a short analysis of how the team worked and what was achieved.
Bringing SQL code analysis from development to release
In November 2017 static analysis was added to SQL Prompt, making it possible for SQL developers to check their code against a set of rules. The tool made it easier to discover SQL issues and deviations from adopted standards early, which limited the number of potential problems at release.
However, code analysis doesn’t have to (and shouldn’t) be limited to development — it has the potential to become a team standardisation feature across the entire DevOps process. It became clear that Redgate is missing a big business opportunity as there are already other products on the market that offer code analysis capabilities at release time.
The Rock’n’Rule team believed that introducing the rules capability would:
- Give development teams the ability to enforce organizational standards across the DevOps process.
- Give DBAs increased confidence and control at the database release stage.
It was decided that the team would achieve these goals by introducing code analysis at the Continuous Integration (CI) and the release stage.
The team and the project
Like every year the team started by selecting a name (Rock’n’Rule) and preparing their t-shirts (which was more challenging than it sounds). Tom Walsh was nominated as team captain and made sure that everyone agreed on the project’s scope before the week began. It was decided that it was a priority to introduce code analysis at the release stage and if there was enough time, the team would add it to the CI stage as well.
The first step was to extend the code analysis feature in SQL Prompt with issue severity. Before that users could only turn rules on or off but now they could change each rule’s status to ‘warning’ or ‘error’. Additionally, new customisable rules were added to the list. At release, it became possible to generate a report in SQL Change Automation (SCA) that listed all code analysis issues found in a database deployment script, with any issues classified as ‘warning’ or ‘error’.
Here is the summary of what the team achieved:
- Support for rule severity in SQL Prompt
- Extending the rules with new customisable rules
- DBAs are now able to review code analysis rules run as part of the SQL Change Automation review/approval step
- Implemented in both PowerShell and the SQL Code Automation plug-ins!
- Released in SCA behind the safety of a feature flag
How the team worked
The team worked across 5 different Github repositories: the Report UI, the Code Analysis engine, SQL Prompt, SCA in Visual Studio and SCA PowerShell. It adopted an XP like approach to development using pair and mob programming extensively, writing production-quality code and merging small changes in master multiple times a day using very short lived pull requests. In order to do this, the team adopted trunk-based development and put all new code behind a feature flag. This allowed us to ship the changes to all customers even if the new feature were not 100% complete. This approach means that our pre-sales team can showcase the new feature to clients. The initial draft of the feature was shipped in SQL Change Automation PowerShell version 3.0.4.
Technically, the only change that needs to be made to use this new feature on the latest version of SCA Powershell (or any of the supported plugins) is to create the environment variable RG_UseCodeAnalysis and set it to “true”. When this environment variable is set, SCA automatically runs code analysis on all deployment scripts both for SQL Source Control and SCA projects and generates an HTML report with all the identified issues. The code analysis report is also included as part of the release artifact object created by the New-DatabaseReleaseArtifact cmdlet and it can be successfully exported and imported to disk for later use.
Biggest challenges
One of the biggest challenges the team faced was working across five different repositories. This required a lot of coordination and made things slower by forcing developers to wait for the CI server to produce pre-release NuGet packages needed to properly test the work before merging to master. Supporting SCA projects turned out to be quite challenging as the report was generated in a different way to SQL Source Control projects and the code analysis engine did not work on sqlcmd scripts. The team used mob programming to solve this problem and pragmatic design helped to overcome the technical debt in the repository and implement code analysis support for sqlcmd scripts. Overall, it was a very rewarding technical challenge and the satisfaction of having created something extremely useful that can help our sales team is priceless.
What remains to be done
As the project lasted just one week there are still quite a few tasks that remain to be completed until the project can be regarded a complete success. First of all, the team focused exclusively on the release stage, meaning the CI part of the story still remains incomplete. Secondly, even though some new rules were added, we expect more deployment-specific rules to be introduced in the future. Thirdly, although the team made it possible to set rule severities in SQL Prompt, this capability still needs to be merged into the product. Finally, it would be good to port the code analysis rules to Oracle as well!
