Roll Back the Database or Fix Forward?

An ounce of prevention is worth a pound of cure.

– Benjamin Franklin

How many times has your team had to roll back a database change? If your answer is greater than zero, there’s a problem. Rolling back changes is a common way to deal with database problems. But now, there’s a new approach that’s gaining popularity: Fixing forward.

Database as code

One of our favorite mottos here is “treat database code like application code”. When we say this, we mean “use the same process for all code: check it into source control and use the same CI/CD process.”

However, we can’t pretend like there’s not a long tradition of dealing with the database very differently. There is a lot to unpack here. One does not simply change their database change process overnight.

Approaches to database changes are changing

When it comes to rolling back changes, application code and database code are very different. For example, rolling back application code is easy to do. You can simply redeploy the old code and you’re done. You can’t do that with databases, though, because data is constantly changing in your database and it’s important that you don’t lose it.

DBAs have been fighting this battle for decades:

• Developer writes code that requires a database update
• Developer writes database code for update
• DBA gets inundated with database code reviews and misses a bad script
• Bad database code gets deployed
• Database gets taken down (due to vulnerability or other problems caused by bad database code)
• DBAs spend hours trying to find the offending code
• Bad code is identified
• DBAs write a script to migrate the existing database from the undesirable state back to the way it was before the bad code caused problems

Read our blog: Inviting the Database Team to the DevOps Party

Common approaches to database issues

Here are the most common approaches DBAs take when tackling bad database deployments.

Approach 1: Restoring from backup

Many companies keep a working backup so that if there’s ever a problem with the database, they can bring the backup online. This approach is often considered a last resort because of its significant downsides:

• Downtime – Once the main backup goes down, it takes time to restore it from backup. This time depends on how large the database is, etc.
• Data loss – Any data that was created or updated after the backup was taken is lost.
• Productivity loss – It takes a lot of time to find the issue and restore the database.

Approach 2: Rollback scripts

This approach requires developers to write a rollback script with every upgrade script that’s written. This forces teams to consider potential rollbacks before deployment and allows testing of rollback scripts to happen in advance. Some migration tools allow these scripts to be chained together, automating the upgrade or downgrade process for every change.

However, this approach adds a lot of overhead for developers. Writing the rollback script itself doesn’t take long, but let’s say you need to drop a column. That drops the data, too. That means the team needs to figure out where to put the data and write code to move that data. Or, maybe they’ll need to move it to a different column or database. This snowball effect forces developers to spend more time working on code that will likely never get used. And if the initial database change script doesn’t work, it doesn’t instill a lot of confidence that the rollback script will work as designed, either.

In a competitive market where executives are focused on developer productivity and trying to cut down on unnecessary work, this approach isn’t very attractive to most DevOps teams.

What if teams only created rollback scripts when they needed them?

Since creating upgrade scripts upfront can add significant overhead, some teams prefer to only create the downgrade scripts on an as-needed basis. Often, these scripts can be generated using a comparison tool like Microsoft Visual Studio SQL Data Tools or Redgate SQL Compare.

However, if there are complex table migrations to consider or there is new data (added/updated after the deployment) as well as old data (added/updated before the deployment), it gets complicated and time-consuming. Plus, teams would be performing this task under pressure. When people are under pressure, there is a higher likelihood of human error.

Approach 3: Fixing forward

Since rolling back database changes is complicated, time-consuming, and error-prone, the fixing forward approach is very quickly getting very popular.

Fixing forward is lower-risk.
DBAs aren’t trying to get the database back to the old state. Instead, the focus is on getting to a good working state (with all that updated data).

Fixing forward eliminates overhead.
Teams can become more agile with their development for database code, making it very popular with the DevOps, CI/CD, and Agile development communities.
The fix forward method works best when changes are broken into small chunks that are deployed independently and automatically. If you’re starting from a software development environment where you have one years’ worth of work about to deploy, this may not be the approach you adopt right now. However, there are tools that can help you break up your database scripts and schema changes into small, trackable chunks that make this approach much more accessible to companies that are ready to try this out.

Database release automation enables teams to fix forward

Database rollbacks should be used for emergencies only, but it’s important to have a rollback process in place. Datical customers don’t rollback. They don’t need to. Their environments are in-sync because they have a tool that allows them to make a lot of small changes quickly instead of rolling them up into one massive change. They fix forward.

Liquibase empowers teams to use best practices for database DevOps. Our tools address each database DevOps best practice to give our customers the best return on their investment possible. Datical customers don’t need to rely on daily firefighting. We focus on helping customers prevent rollbacks and save money. Database automation brings consistency to deployments, eliminating configuration drift. Our Database Change Forecast allows teams to review proposed changes before they are pushed to production.

Read our full whitepaper Database Changes: Roll Back or Fix Forward?