Database Deployments: State or Database Migration?

Most state-based versus migration-based content floating around the internet is based on a paradigm that references old technology and old processes. Imagine reading a story about approaches to trans-continental travel in the 1930s. There were not many airplanes that were capable of making the trip. Today’s 12-hour flight from London to Singapore would have taken a staggering 8 days with 22 separate layovers. Things have changed! Let’s look at what’s going on in the world of database deployments today for a reality check.

First, let’s define each approach

State-based database deployments

The state-based approach is all about the destination. You begin with the ideal state, use a tool to compare the ideal with the current state of the database, and then the tool analyzes the difference between the two and generates all the scripts to change the database.

This is approach is often embraced by DBAs as it offers them a chance to tightly manage the process and keep a keen eye on all changes that are being deployed to their databases.

Database migration for database deployments

The database migration approach is all about the journey. This approach is super simple in concept and very appealing to teams on the Development side of the IT house: “Capture individual change scripts during development.”

Migrations-first tools generally make it much easier for humans to customize scripts — making it easier for teams to control exactly what happens and how many steps are taken to make it happen.

Developers love this approach. It allows them to develop database code just like they develop application code and means they get fast feedback on their code as their scripts are deployed quicker.

The difference between State and Database Migration approaches

Both approaches, at their root, are about the source of truth: the definition of how you want the database to look at the end, or the scripts you produce to handle database change.

Now that you’ve got the basics, let’s look a bit closer at the differences between how these work in practice. Let’s go back to the idea that the state-based approach is about the destination and the migrations-based approach is about the journey.

State-based: The Destination

Many of us have a love/hate relationship with Waze and Google Maps. I love having them because it’s nice to have an idea of where I’m going. However, they have both ruined my day more than once. The state-based approach is like Waze. You punch in the destination and Waze calculates the “shortest” set of directions to get you there. Not necessarily the best directions, mind you. In some cases, it has no idea how impossibly hard it is to make a left at certain intersections during rush hour. Google Maps has its drawbacks, too as it recently sent a caravan of people trying to get to the Denver airport on a detour down a dirt road where they got stuck and missed their flights.

Database Migrations: The Journey

The database migration approach is more like getting directions from a friend or relative who is very familiar with the area. I’m from a rural part of Michigan where GPS has always been spotty. When I go back to visit family and stay with relatives, I ask a human for directions to the nearest store. I usually get something like the following:

• When you get out to the main road, take a left.
• When you see the “Sippola” camp sign, take a right.
• You’ll go about two miles and you’ll see a fork in the road. Normally, you’d take the right fork, but the bridge got washed out this spring, so take the middle road for three miles and take a right when you see the metal rooster.
• The Crossroads store will be on the left another mile down.

There is no way I’d make it to the store quickly and safely with the state-based approach. However, there is no way I will ever remove Waze from my phone. You need both approaches for different circumstances and for different reasons. Context is important.

For some crazy reason, I turn on Waze every day for my drive home from work and ignore the directions. Every time. It always wants me to take a “shortcut” that I know from experience is a bad idea that forces me to be a jerk several times to cut into traffic at weird points in Austin. It might only save me two minutes and it has actually been slower when I’ve attempted it in the past.

I think a similar thing is going on with Operations folks. They want the map (state-based tools). It’s a security thing. People want to know where they’re going. Totally understandable! However, if you live by the state-only model, you die by the state-only model and end up stuck on a dirt road in the middle of Colorado and you’ll miss your flight.

Can you use both approaches?

Absolutely. Today’s tools are working on the weaknesses in their solutions all the time. Progress! (In fact, Liquibase is an open source database migration tool that allows you to do rollbacks and diffs.)

Even though you can use both, you do have to decide the cases where you should use the state-based model and the cases where you want to use the migrations-based model. Many tools are now claiming hybrid status and that just means that they offer two different tools or one tool that uses one approach with features that help with the pitfalls inherent in that approach. The lines are getting very blurred, which can make it confusing.

Where you’re starting from matters

If you have huge differences (database drift) between your test, development, and production environments already and you have a certain process for the state-based approach, it will probably feel way more comfortable. The downside to sticking with this exclusively is that you’ll never address the real problems and realize faster releases with fewer errors this way.

An example: A developer renames a column

With the state-based approach, the tooling believes a column has been dropped and a new one was created. Without careful, manual, (read: costly) vetting, the tool will create a SQL script that will cause data loss.

With a database migrations approach, the developer is required to explicitly rename the column. This saves the data and avoids hassle and heartache for the operations teams.

Try to use database migrations for changes whenever possible

The migrations-based approach makes software development faster and developers much more productive. No more hurry up and wait for a DBA to review the code.

This approach helps teams more closely align with Agile and DevOps best practices:

• Small, incremental changes
• Use the same process for all code delivery
• Enables fast feedback loops
• Granular control of features
• Enables better testing
• Eliminates drift

Liquibase approach to automated database change

Liquibase Enterprise uses a migrations-driven approach to database schema management. There are many benefits to using a database migration approach to database deployments. The biggest is that by shifting your database deployment scripts to the left, developers now have much more control over exactly how database changes are deployed. These migration scripts are also tested early for fast feedback. Our tools and integrations ensure migrations are checked in and out of source control right alongside the actual source code so that both the source and schema are versioned together. CI and CD can easily be set up with pre-built tasks from Datical that build and deploy database schemas using these migrations.

Customers receive access to advanced capabilities like automatic enforcement of predefined DBA rules (improving productivity), a tool that forecasts the impact of database changes before they are deployed (reducing risk and eliminating downtime), and automated tracking and reporting for every database deployment (simplifying audits).

Liquibase Enterprise offers more than powerful software; it offers built-in, consultative support that gets you to database release automation success faster with lower risk. Liquibase partners with your whole team to ensure your mission-critical database changes are deployed safely and seamlessly. Contact us and we’ll be happy to walk through your needs and let you know if we’re a good fit for your using Datical.

Check out our whitepaper for more: Understanding State-based vs. Migrations-based Database Deployments