Pipeline Automation: The Keys to Unlocking the Right Outcome For Your CI/CD Process

We know the challenge: You wish to fully automate your pipeline, but you haven’t considered what it would take to integrate relational database changes into your CI/CD process. Skipping this step is a recipe for disaster — but often, this part of the integration is overlooked, postponed, or perhaps even ignored. This may be due to the sheer enormity of the work required to integrate relational database changes into any automated process.

While there are several challenges to overcome in this process, we’ll focus on the following:

• How to rollback the changes when required, and;
• How to coordinate database modifications with the associated application release

Let’s take a closer look at both:

How to rollback required changes

There are tools available to deploy database changes as part of an automated process, or you can even develop your own. Whichever method you choose to integrate database changes into your automated process, the fundamental question remains: What do you do if the release needs to be rolled back?

Undoing database changes can be complex. If you added a new column to an existing table, removing that column is straightforward. But what if you removed an existing column? How do you get the data restored for the dropped column?

One way to do this is to restore a recent backup and recover the data by copying from the restored backup to the primary server. But this process involves time and probably extensive validation, and most likely is very manual in nature.

Or, how about modifying data for the new release? When the updates are straightforward — for example, the value “A” changed to the value “Z” — then the rollback is straightforward when “Z” is a new value.

But what happens when updating the value of “A” to “Z” when “Z” was previously available? Now, the only way back may be a path similar to what we said above — restoring a backup, then updating the database based on the backup — an arduous task requiring time, validation, and manual intervention.

So how can we make this task easier to accomplish? One potential solution to the above issues might be to avoid the removal of database columns until a release AFTER the code change that no longer utilizes the object is considered stable. This still requires planning so that the cleanup is not forgotten but, hopefully, once several releases have been completed, you do not need to roll them all back. The changed data issue might also be resolved by saving the modified data prior to the update in a “temporary” table that includes the primary key of the table and the data value prior to the update. Then the rollback is a straightforward update to recover, and the “temporary” table can be removed in a later release.

How to coordinate database modifications with the associated application release

Unless you happen to have a single tool to deploy both your database changes and your application changes, you will need to find a way for the application deployment process to verify that the necessary database changes are in place. One potential solution would be to connect the application deployment process to the database, and verify that the appropriate changes are in place. While this would resolve the issue, it would also be cumbersome, and require a different validation check for every release.

A more straightforward and consistent solution would be to have the release process check a table in for a specific value that coincides with the application release. Implementing this type of check should be relatively simple, and would allow the same check for every release.

Wrapping it up

Integrating your relational database changes into a fully-automated CI/CD pipeline can be difficult, with both technical and procedural obstacles to overcome. We hope in presenting these examples above, and some potentially more efficient solutions, you will find that the payoff is a cleaner, faster, more efficient CI/CD pipeline.

If Blue Sentry can help you automate your CI/CD pipeline, give us a call.