I don’t think many organisations or application teams practices CICD or even version their SQL codes as a best practice while dealing with Database changes.
As we go to AWS or any other cloud platform, the code deployments will be completely CICD driven, i.e. code releases will be at the developer's will. Hence, the complementing DB changes should be part of the pipeline / release candidate itself, and we should NOT look at the DB changes and code changes as two separate entities - to get the full benefit of CICD in our quest for 'Speed-As-Habit'.
The database SQL's, unlike other software components is not often versioned. Thus, Continuous Integration (CI) & Delivery ( CD ) for databases has taken a back seat to automated deployments, leaving database automation play a catch up game.
If you have a full-fledged release program mgt. team managing and co-ordinating these changes manually, I think you really need to anticipate the world ahead.
Consider these AWS or any other cloud scenarios:
• AWS application deployments will be completely CICD driven.
• AWS way of doing deployments expects everything in an automated fashion Code/ IaC etc.
• Manual /synchronisation between DB and App teams will be very painstaking as - CI for code will be at the developers will and hence the releases (mostly!)
• Hence, the SQL queries should also be part of the release / deployment automation.
Introducing Versioning of SQL:
Version or source-controlling database changes is the first step in introducing databases to the DevOps / CICD process. If your database changes and application code are in source control ( e.g. GitHub ), you can have a working build anytime, because you will always have a version to roll back to, and you will also have audit trail.
There are many branching strategies you may adopt to suit your development process while introducing a new SQL repo, which is part of your same development stream.
This is the foundation to bring a transparent audit / approval / feedback mechanism in a true DevOps culture.
Proposing a new process:
A process somewhat similar to the below can ensure that - the database changes which is versioned in GitHub, will get reviewed by the DBA’s even before the branch merge. The depicted automation ensures that the CI gets triggered only when the merge happens. This not only helps in early review but also brings the real skin of our DBA’s in the game and ensures that the accountability and ownership get fairly distributed across the right stake-holders.
As a best practice you can create a pool of 2 engineers each from DBA's and Developers to facilitate the code review and subsequent PR approvals.
Introducing CICD:
Below could be a ‘Big-Picture’ , binding all the components together.
Introducing Flyway DB:
FlywayDB is an Apache v2 licensed open-source tool that makes database migrations easy. You can think of Flyway as version control for your database.It integrates wonderfully with Maven. It supports a very wide range of databases. In the above diagram you can see that Spinnaker triggers triggers the FlywayDB instance to manage the changes.
I highly recommend you read more about this tool in the below link –
Now when subsequent changes comes , this is how FlywayDB ensures that the changes are safe.
The only overhead is that - FlywayDB maintains a small Schema version table with each of your databases.
This may be a quick small approach towards integrating database changes as part of your DevOps process, which can accelerate the delivery of database changes and promote agility across the entire code-release cycle. I am sure based on the tools and your own IT landscape, this might vary slightly.
Happy to get your thoughts around this and discuss further.
However one thing is for sure - failure to include database changes as part of the DevOps /CICD practice will certainly slow down your release cycle!
