schema changes

Schema Changes in DevOps: Including Your Database in your Development Model

554 VIEWS

· · ·

One of the main benefits of instituting a DevOps culture is an increase in the speed at which application changes are deployed. This is achieved through the adoption of shorter development cycles combined with applying Continuous Integration (CI) and Continuous Delivery (CD) strategies that speed up the process of integrating code changes, allowing for quicker testing and deployment to other environments. It is easy to see how this process works with code changes to an application. But how would it work in regard to database schema changes?

Why It’s Different

Schema changes are changes to the structure of the database, such as adding a column to a table, changing the datatype of a column in a table, adding a new table to the database, etc. While these changes are easy to make in a development environment where preservation of data, coordinating with database administrators, and things of that nature aren’t of the utmost importance, they come with their own set of challenges when migrating changes to other environments. Below I will point out some challenges associated with fitting schema changes into the DevOps process and how to mitigate the issues that may arise.

Migration-Based Database Delivery

From a developer’s perspective, a development database is often a no holds barred playground for the application it supports. It is a space where data often consists of test entries that can be lost or adjusted without giving it a second thought. Thus, changes to the schema can be done without thinking twice. For example, imagine an instance in which the datatype of a column must be changed in an existing table. In a development environment, the developer may just find it to be an easier option to drop the existing table and re-run the create SQL with the new datatype for the column being adjusted. This approach for schema changes in development environments is similar to migrating application code to any environment. The application code is removed and then re-deployed using the new deployment artifacts. For database changes, however, this does not work in other environments such as production where the data does not only consist of test entries. Here the table containing the data cannot be dropped and re-created. The data in that table must be preserved.

One solution for this can be found through writing upgrade scripts to make schema changes and utilizing migration-based database change techniques. A migration-based strategy results in a set of SQL scripts that can be executed in sequential order to alter existing database objects and data. They are then run in a particular order so as to not put the data at risk.

Imagine we are working on a project where, in our next deployment, we need to do the following:

  • Update the datatype of a column in a pre-existing table named Table1.
  • Create Table2.
  • Create a view that pulls information from Table1 and Table2 by joining the tables.

Migration-based deployment would result in a set of three scripts to be run in order to alter Table1 and its data, create Table2, and create View1. These scripts would be written to do the following:

  • Script 1 contains an alter statement to change the datatype of the column to Table1 while also updating the data to support the datatype change.
  • Script 2 contains a create statement to create Table2.
  • Script 3 creates the view to pull from Table1 and Table2 by joining the two tables.

In the next deployment, the scripts should be run as follows:

Script 1 → Script 2 → Script 3

Finally, these scripts should be committed into source control, never to be changed again. When deployed to any subsequent environment, they should be run one time in the order listed above. This can be done with the help of a tool to assist in CI and CD for your database modifications. (More about that below.)

Continuous Integration and Continuous Delivery for Your Database

Now that we have a strategy for writing and storing the scripts for altering the database schema, we can talk about another necessary tactic for integrating schema changes into your DevOps process—the use of a tool for assisting in implementing Continuous Integration and Continuous Delivery for your schema changes. Currently, there are many useful tools on the market.

One such tool, if you are utilizing a migration-based approach and Microsoft SQL Server as your DBMS, is Redgate ReadyRoll. This tool assists in the creation of sequentially named scripts for making changes to the database schema and the data contained in the database tables. ReadyRoll is especially useful when each developer on the team is using their own instance of the database for the application.

Upgrade scripts are first created by a developer through the use of ReadyRoll. When the developer is done testing they can commit to source control. Other team members can then sync their instance of the database with source control to pull in recently committed changes. Then, when you find yourself ready to migrate your schema changes to a subsequent environment, ReadyRoll can produce the necessary file for deployment, which will serve to take a lot of the time and complexity of doing this off of the plate of your DBA. From there, the changes can simply be deployed manually by the DBA or through the use of an automated deployment tool such as Octopus Deploy.

This is just one example of how CI and CD techniques can be employed with regard to schema changes. Regardless of the tools chosen to do the job, the end goal is to utilize version control for your database schema and give your team the proper tools to continuously integrate any database changes. In doing so, you will be able to deliver application changes that require modifications to the database at greater speed and with more reliability, thus preventing your database modifications from slowing down the software delivery lifecycle.

Conclusion

Development shops looking to fold database changes into their DevOps process should focus on taking two important steps in the right direction—the first of which is utilizing version control for all database schema changes and choosing an approach (such as migration-based) for making these changes. The second step is finding a tool or tools that work with that approach to assist in CI and CD strategies for integrating and delivering database modifications. When these two steps are taken, and the choices are employed properly, the development team will find themselves with a schema integration and deployment process that fits very much into the DevOps model.

Do you think you can beat this Sweet post?

If so, you may have what it takes to become a Sweetcode contributor... Learn More.

Scott Fitzpatrick has over 5 years of experience as a software developer. He has worked with many languages, including Java, ColdFusion, HTML/CSS, JavaScript and SQL.


Discussion

Click on a tab to select how you'd like to leave your comment

Leave a Comment

Your email address will not be published. Required fields are marked *

Menu