Introductory Handbook for Database Continuous Integration

The following is a guest blog post by Eric Boersma.

Development teams across the world sing the praises of continuous integration. They praise the reduced time from development to deployment. They love that they ship features to customers more quickly, and more accurately. Managers adore that their teams spend less time deploying code. Their developers are much more focused on delivering code, instead of getting that code to customers. But what about their database administrators? For many teams, their databases are still stuck in the past. They haven’t seen any benefit from the move to continuous integration. Instead, they’re still deploying database changes the same way they always have. But what about database continuous integration? Can database teams reap the same benefits from continuous integration that development teams do?

Absolutely! In this post, we’re going to talk about the ways that teams adopt database continuous integration models, and the benefits they bring. But first, we’re going to talk about how current database management processes are failing development teams.

I like to refer to the opposite of continuous integration as “stilted integration.” Instead of smoothly inserting new code into the running application all of the time, shops that practice stilted integration lurch forward around the time of every release. They hold their code back from customers. Then, suddenly, on the day of a release, it all goes out at once. Even many organizations that practice continuous delivery for their code don’t do the same for their databases. This, naturally, restricts how continuously they release their code. Did you write a new feature that’s limited to the UI? That’s in front of customers the next day. Write something that depends on the database? You’re going to have to wait.

So, why are teams afraid of continuous integration in their database workflow? It’s about the data! Deploying new code rarely comes with irreversible consequences. If you roll out some new feature that contains a critical bug, it’s pretty easy to roll that back. The same isn’t true if you have code changes that alter database data. Sure, it might be possible to roll back your entire database to a previous version. But this is an approach that usually comes with some downsides. For instance, data rollbacks often mean losing critical data collected between the time of the backup and the time of the rollback.

This fear of permanence keeps teams rooted in the past. They’re afraid to take steps that will improve their overall delivery timelines because the consequences of a mistake are so high. But there are some tools in the continuous integration toolbox available to teams that help them on their journey toward better, smoother releases.

The first tool in our toolbox is one of the oldest and most popular. Teams around the world use migrations to store the schema of their database in code. The database is defined with a starting schema, then new changes are incrementally applied until the database arrives at the current configuration. Database migrations first rose to mainstream popularity along with Ruby on Rails more than a decade ago. Today, they’re ubiquitous.

One mistake that many development teams make is to couple their migrations to their code. Developers often write database migrations as part of feature development. They assume that those migrations also need to be deployed along with the code that utilizes them. In reality, database migrations should be decoupled from feature releases. Decoupling database migrations from code allows teams to deploy migrations quickly and efficiently. This is the promise of continuous integration. When teams continually integrate their code, they save time and effort down the road. Sending new migrations to production as soon as possible means you’re never stuck trying to figure out which columns on a table you need to include with a new feature.

Source control is a critical part of the continuous integration playbook. This is true regardless of the type of continuous integration. Teams that practice stilted integration rarely save database scripts in their source control. They might use a migration to create a new column on a table. Then, someone, maybe a developer or database administrator, will write a script to populate that column. They run this script in a few environments, then are lost forever. This kind of attitude cripples productivity in any environment missed when running the script. Instead of an update that takes a few minutes, developers need to install a whole new database backup.

Instead, those scripts should go into source control alongside all other code. What’s more, they should include rollback logic for any situation where rolling back is possible. That logic helps protect against our big fear up above: that we’ll need to roll back the database and lose data. Instead, when there’s a problem with a script we can roll back just those changes.

Of course your organization uses database backups. More than likely, you snapshot your production database every night. If you’re not, you should get on that. Losing a production database is the kind of thing that can destroy a business.

But nightly backups aren’t enough in the world of continuous integration. Like we noted above: we’re scared that a bad deploy will knock out the production database, taking critical data with it. A restore means losing data between the time we took the snapshot and when we restored it. The solution for this is obvious: take more snapshots. In reality, you should create a new database backup before every deploy. An aggressive backup strategy like this means that instead of losing half a day’s data with a bad deploy, you’ll instead only lose a few minutes. Your customers are much more likely to understand a few minutes of lost data than a day or even a week.

Sometimes, despite your best efforts, database changes have too much impact. Maybe they fundamentally change the shape of your data. Maybe, they’re ready to go, but the code that utilizes them is mired in testing. This is when a tool like feature flags saves the day. Feature flags are configurable switches that route code logic. That new database migration doesn’t need to wait for the code it depends on to pass QA. Instead, the migration and the code both merge into controller and deploy to production. However, the team locks that code behind a feature flag which they turn off. The code doesn’t see the light of day until testing completes. When QA approves the code, the team uses a tool like CloudBees Feature Management to enable the feature flag. The new code takes over and starts sending data to the new structure.

Your deployments don’t need to last two hours. They don’t need to come with horrible headaches. By utilizing the tools in your tool box, your team will deploy more quickly, and more predictably. Leverage migrations, source code, backups, and feature flags to simplify your database management. In the process, you’ll unlock your database administrators, too. They’ll spend more time working on bigger problems, like improving database performance. When freed from spending all their time worrying about deploys, you’ll find that they improve your software, too.

Teams of any size benefit from database continuous integration. They think about the ways they deploy database changes, and consciously choose to organize those changes for consistency and predictability. Your team would benefit, too. There’s no better time to start than today!

Eric Boersma is a software developer and development manager who's done everything from IT security in pharmaceuticals to writing intelligence software for the US government to building international development teams for non-profits. He loves to talk about the things he's learned along the way, and he enjoys listening to and learning from others as well.