State- and migration-based database deployments: When to use each for optimal change management

In any pipeline that includes a database, it’s critical to consider how it is kept updated to support the applications, teams, and data streams it serves. There are two methods to determine how a database must change to keep up:

• State-based, which compares the current database state to an intended state and deduces the change needed
• Migration-based, in which small, atomic changes are bundled and explicitly outlined

Choosing the right database change management method isn’t an absolute or philosophical decision – it’s a matter of the right approach for the job. 

State-based deployments offer a quick, simplistic way to determine change scripts and are useful for analysis and drift detection. Due to its simplicity, though, it’s unreliable and risky to use as a primary change management approach. Migration-based deployments are more complicated to write, deploy, and test, yet engage a DevOps-aligned approach. Since they enable control, flexibility, collaboration, and traceability that protect data integrity, the migration-based method best serves the primary change management function.      

As the backbone of database change management, a migration-based approach is best because it packages schema and data evolutions into formatted, collaborative, incremental changes that support database DevOps workflows. Even more useful, these migration-derived changes can be packaged as artifacts to be tested and deployed through CI/CD pipeline integration. 

All that to say, it’s no longer a “this or that” approach when it comes to state- vs. migration-based database deployments. Here’s what that means – but first, an overview of each approach. 

What is a state-based database deployment?

The state-based approach is all about the snapshot – “compare and sync,” it’s often called. 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.

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

To get technical, even a state-based deployment is a “migration” in that it’s migrating the schema of the database. 

Main difference: It’s about the snapshot

State-based deployments focus on reaching a desired target state for the database, inclusive of whatever changes are needed to achieve that state. It’s all about reaching the target based on the snapshot. Simply, the process would flow as:

1. Snapshot the current state of the database
2. Define the target, ideal state including all desired structures and data configurations
3. Compare the current snapshot to the target state
4. Generate the changes needed on the snapshot state to bring it into the target state

The goal is to ensure the database conforms precisely to the defined endpoint, minimizing discrepancies and ensuring consistency. This method provides a clear, high-level overview of the desired final state, allowing for comprehensive planning and execution.

Challenges of state-based deployments

State-based deployments, while able to quickly derive lists of changes to be made, become less efficient when you consider the amount of oversight required to make them work in an overall pipeline. Because tools must make assumptions about user intent based on what they 'see' in the database, state based tools typically require longer review processes when compared to migration scripts. Further, they often require parallel processes for data changes, effectively duplicating work rather than saving overall process time. Plus, they are less suited for small, incremental updates that are central to DevOps practices, as they tend to handle larger, more comprehensive changes. 

In addition, collaboration can be challenging when multiple environments are involved, as different team members may be working with varied snapshots of the database, leading to inconsistencies and difficulties in maintaining synchronization.

The core issue with state-based deployments is that the change process is not repeatable from environment to environment. With a new, diff-derived script being built in a unique way every time, state-based changes lack traceability and risk data quality and integrity. To mitigate these risks, the state-based changes – even after being automatically derived – require manual review, which chips away at the process’s efficiency. 

To see how state-based changed can easily become problematic, simply imagine a developer renames a column. The state-based tool 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.

Alternatively, the developer could have used a migration-based approach to explicitly rename the column, saving the data from being lost and avoiding a big hassle for the Ops team. As it happens, by the time the state-based change is manually reviewed, it ends up going through the same code checks as a migration-based change would when pushed through the automated pipeline. 

Imagine another common scenario: a problematic change script needs to be undone, necessitating a database change rollback. When changes are developed through the state-based approach, tricky questions arise, pointing to the inherent risks of relying on state-based as a primary method. For instance, 

• Which snapshot of the database will be compared to undo the changes? 
• How do you ensure that you are using the correct snapshot?
• Will the rollback script be pre-generated, too – introducing more uncertainty?
• What kind of tests need to be built for the pre-generated rollback?
• Will the state-based change script just be an inverse of the problematic changes?
• What if you only want to rollback a subset of the changes, not the whole set?

Without granularity and traceability, a state-based rollback is a recipe for trouble. 

What is a migration-based database deployment?

The database migration approach is all about the procedure. 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.” 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.

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.

Migration-based deployments take a more granular and iterative approach by merging smaller updates with the database pipeline. A series of change scripts are created that outline the explicit, atomic steps needed to bring the database into its updated, intended state. Instead of a single, complex change directive derived from a diff comparison, migration-based deployments make changes one by one in a specific order. 

The migrations-based approach makes software development faster and developers much more productive, enabling change management automation and self-service deployments. No more “hurry up and wait” ticketing process for a DBA to review the code. By adopting as-code methods for database change, which have been proven and refined over the last decade of software engineering, teams more closely align with 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

Main difference: It’s about the step-by-step procedure

Migration-based deployments emphasize the process of implementing changes through a series of incremental updates. This approach involves:

• Developers creating individual change scripts for each update, capturing every incremental modification in a detailed, step-by-step manner
• Submitting the change script to a database version control system to be managed similarly to application code, allowing precise control over the order and application of changes
• Continuous integration with the rest of the pipeline’s updates, ensuring robust testing and validation without disruption or delay

The explicitly defined sequence and packaging of migration-based deployments allows for greater flexibility and control, as each change is explicitly defined and managed, making it easier to track, review, and test individual updates. This method supports rapid feedback and reduces the risk of large-scale deployment failures.

Challenges of migration-based deployments

However precise and DevOps-aligned, migration-based deployments are not without their challenges. Managing a large number of change scripts can be complex and requires meticulous handling to avoid conflicts and errors. Additionally, as the number of changes increases, maintaining an organized and efficient process can become increasingly difficult. 

Despite these challenges, treating migration scripts as artifacts can optimize the deployment process further. By capturing changes as first-class artifacts, similar to application code, teams can enhance control, ensure consistency, and streamline the deployment pipeline for even greater efficiency and reliability.

Artifacts & automations: Getting the best from state- and migration-based deployments

For the most advanced DevOps methods and capabilities, Liquibase automates database change management by enhancing the migration-based approach into its artifact-based approach. 

The artifact-based approach builds on the principles of migration-based deployments by treating each change script as a first-class artifact, similar to application code. These changes - ChangeSets in Liquibase terms - are organized into batches called ChangeLogs. The ChangeLogs are then versioned, tested, and deployed with the same rigor and control as application binaries. With artifacts, or ChangeSets, Liquibase helps teams fully embrace the “build once, deploy many” philosophy. 

By treating migration scripts as ChangeSets, the deployment process benefits from improved control and consistency. The granularity and atomicity of Liquibase’s ChangeSets help break up monolithic scripts to manage changes individually. Each ChangeSet is carefully documented and tracked, allowing for precise rollbacks, automated validation, and enhanced collaboration among team members. This approach ensures that every change is transparent, traceable, and easily integrated into continuous integration and delivery (CI/CD) pipelines.

The right method at the right time

While state-based approaches offer a simpler initial setup, in the fast-paced world of CI/CD, the control, reliability, and maintainability of migration-based deployments provide significant advantages. By managing migration scripts as code through a typical CI/CD pipeline (database CI/CD), you can ensure smooth and predictable database schema changes.

Summary of techniques by phase of pipeline

State-based deployments are best used for:

Development

• Database analysis
• Script generation

Testing

• Drift detection
• Environment reset

Production

• Drift detection

‍

Migration-based deployments are best used for:

Development

• Script grooming/paramaterizing
• Script testing
• Merging contributions

Testing

• Script testing
• Script refinement
• Deployment rehearsal

Production

• Deployment

For the primary change management workflow, migration-based changes give teams granularity, traceability, and reliability that accelerates pipelines.

The right tool for the job

As a complete database DevOps solution, Liquibase uses a migrations-driven approach to database schema management. By shifting database deployment scripts to the left, developers 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 that build and deploy database schemas using these migrations.

Migration-based changes are the foundation of many advanced Liquibase features including:

• Change creation
• Version control
• Quality Checks (automated testing and policy enforcement)
• Structured Logging
• Tracking & observability

Liquibase leverages state-based change derivation – outlining the changes, but not necessarily deploying them – for features including:

• Drift detection
• Drift alerts
• Diff (compare)
• Environment reset (testing)

For an in-depth expert discussion on when to use migration- and state-based database changes to avoid risk, accelerate delivery, and improve pipeline integrity, catch the webinar. It includes a live demo of Liquibase in action with both state- and migration-based change examples.