Making Schema Modifications Using Database Change Scripts
In this post, we’ll examine some popular strategies for updating database schemas, including using database change scripts. We’ll also see how one of these strategies can be integrated into a deployment process that uses Octopus. This is the fifth post in a multi-part series about using Octopus Deploy.
Databases are a key part of many applications. Naturally, releasing a new version often requires a schema change. One way we can accomplish this is to use change scripts, so we’re going to look at how this works. We will see how to name and organize change scripts so they are deployed in the proper order. And we’ll also briefly touch on how Octopus supports a change script strategy.
When our Octopus Deploy pilot started, one of my first questions was exactly how Octopus would update our database schemas. Fortunately, I found that Octopus has a lot of flexibility and supports schema changes very well (as one might hope.)
It seems there may be almost as many different ways to change a schema as there are software companies. I know that each place I’ve worked has found their own unique ways of doing this. Sometimes, separate projects within a company are even done differently.
But all schema changes seem to boil down one of two basic strategies. The first strategy is to look at the desired database state, compare that to the current state, and then use a difference-based approach. The second strategy is modifying the schema directly using DDL in database change scripts.
I’ve used both strategies. But where I currently work, we’ve been using change scripts for some time. So it made a lot of sense for us to stick with that approach during our pilot.
I’ll Take Your Order Now
A successful change script strategy must make sure that scripts run in the correct order. For instance, you can’t add a foreign key reference before the foreign table exists. A typical way to ensure that a set of scripts runs in order is by alphabetizing.
While this might seem easy, good naming can get in the way of strict alphabetization. So perhaps the easiest way to order scripts is to prefix script names with a sequencing number. Using this method, then script 0005_CreateOctopusPartTable.sql will always run before script 0010_AddColumnToOctopusPartTable.sql, no matter what the rest of the name following the numbers is.
Picks and Shovels
To make this work, you of course need to pick a tool or process that implements the alphabetic convention. In the next post, I’ll cover using DbUp, which provides a very simple way to do exactly that. As we’ll see, it turns out that DbUp is also highly compatible with Octopus.
The intent of this blog post was to provide information about Octopus Deploy’s support for schema changes. This post explores using database change scripts for such migrations.
Naturally, there are other ways to do this, which were only briefly discussed here, so I’m providing links to other resources. A future post will discuss in detail a specific way to leverage change scripts that is highly compatible with Octopus Deploy.
This is the fifth post in the multi-part series – “Practical Octopus Deploy.” The focus of this series is from the perspective of a developer who wishes to use Octopus to deploy an application. The next post in this series discusses deploying change scripts using DbUp, a tool for packaging database change scripts. The first post of this series, with table of contents, is Practical Octopus Deploy – Introduction.
As mentioned, one of my earliest concerns during our Octopus Deploy pilot was exactly how we were going to use Octopus to keep our database schemas in sync. Fortunately, I quickly learned that there are many resources available on the internet that discuss precisely this. In fact, there are almost too many options.
The biggest problem then became selecting an approach, sorting the wheat from the chaff, and getting down to a reasonable reading and viewing list. Here are some curated resources that I found especially helpful.
- I’m so strongly in favor of using change scripts that I didn’t delve much into the other available methods. If you’d like a nice overview of some other options (you’ll find several alternatives tools mentioned in the second half of this) I recommend:
Octopus Deploy Documentation – SQL Server Databases