Database Version Control

There are two vital elements to any software experience: the application and the data pipeline. Both elements need to be present for a functional end-user experience.

The application component is stateless, so teams can simply overwrite the application with the latest version when releasing new software experiences. However, unlike the application, the database component cannot simply be overwritten. Data is a persistent and valuable resource.

Intro to database version control (database versioning)

Unlike applications, databases are stateful. As a result, the database is one of the most valuable and important assets to any organization. Therefore, database version control is needed to ensure nothing is lost, broken, or unintentionally changed as changes to the database are deployed.

According to Google Cloud’s DevOps Research and Assessment (DORA) team in their  2023 Accelerate State of DevOps Report, Elite DevOps performers are 3.4 times more likely to incorporate database change management into their process than low performers. In a recent webinar, State of DevOps 2023: User-Centric Database Change Management, DORA discussed how its philosophies align with Liquibase on the value of database version control as part of a robust database release automation strategy that embraces database DevOps. If that sounds difficult to achieve, worry not – version control tools for the database make versioning simple and automatic. 

In this guide to database version control, you can learn:

• What is database version control?
• Why do teams need database version control?
• Why do databases need version control?
• The importance of implementing SQL version control early
• Methods for database version control
• Challenging elements of database version control
• Version control tools
• How to bring database version control to your pipelines

What is database version control?

Database versioning is applying version numbers to snapshot states of a database schema. Database version control systematically manages these versioned changes to a database's schema and objects, enabling teams to track, apply, and revert changes with precision and reliability. This capability ensures that database environments remain consistent across development, testing, and production stages, facilitating collaboration among team members, reducing deployment risks, and extending DevOps to the database workflow.

Database version control emphasizes the tools and processes involved in tracking, managing, and applying changes to the database. It's about the mechanisms that allow for the safe, collaborative development of database schemas and objects, similar to how version control systems like Git manage changes to source code – hence the term Git for databases (database GitOps). 

Why do databases need version control?

Today’s application developers wouldn’t dream of working without version control. Most software developers have been reaping the benefits of easier collaboration and increased productivity. Most people on the database development side haven’t had the right tools or processes in place yet.

The database release bottleneck

Organizations need to manage both application and database changes to accelerate the pace of software delivery. To this end, there has been a sharp growth in database source control and version control database tools to bring transparency and automation to application code as it moves from development to production. 

However, the same is not true for database schema changes and database schema version control. The tools and techniques for database version management have remained relatively manual and stagnant. As a result, with application code releases accelerating, the database increasingly becomes a bottleneck that holds organizations back from faster software releases.

The importance of implementing SQL version control early

It’s possible to believe that database schema version control doesn’t apply to you because your database releases are not holding your organization back. At best, this is shortsighted thinking. A market study from Dimensional Research – The State of Database Deployments in Application Delivery – found that database releases become a bigger issue as application releases accelerate – those releasing applications weekly or faster report issues with their database release process much more than those who release monthly or slower. 

While bringing traceable version control to SQL code may initially seem unimportant, take heed and implement database version management before it’s too late. After all, most organizations reported that half of all significant application changes require database changes – meaning there is no avoiding database version control in accelerating overall software delivery and quality.

Methods of database version control

Fundamentally, there are two ways to define and manage changes to the database: state-based and migrations-based.

State-based version control

The state-based approach begins with developers declaring the ideal database state and relying on tooling to generate SQL scripts based on comparing the ideal database state definition and a target database. 

While the state-based approach allows for a formal declaration of the database state that developers and other stakeholders can quickly access and understand, only using state-based comparisons makes it harder for teams attempting to bring their database release process in line with an agile DevOps software release process.

How well does it track releases?

A central tenet of DevOps is to “build once, deploy often.” By solely relying on comparison and generating a SQL script for each database release, there is no way for teams to ensure a consistent, repeatable database release process. By deploying potentially different SQL changes to different database environments, the state-based approach often falls short of effectively tracking and managing the database schema version of the databases that are part of a software development pipeline.

Migrations-based version control

Instead of only tracking the ideal state of a database, an alternative approach to database version control is to track the specific changes that have been made to each database. Known as the migration-based approach, the actual database schema changes, SQL code changes, reference data changes, and other database changes are authored, built and traced from development to production. Learn more in this guide to database schema migration. 

How well does it track releases?

This methodology embodies the “build once, deploy often” DevOps philosophy, and allows teams to better understand exactly what has been deployed to each database. By using an artifact to encapsulate the changes, the migration-based approach also provides a less risky, repeatable, and consistent approach to database releases.

Hybrid approach: state-based and migrations-based

You can use both approaches together! Liquibase starts with a migrations-based approach, promoting small changes via changesets and changelogs that can be tracked via source control. Liquibase also allows teams to compare database schemas via diff commands to more easily detect malicious code (like ransomware) and database drift. 

Challenging elements of database version control

Driven by undeniable benefits in speed, consistency, accuracy, and reliability, teams might charge ahead with a version control process without taking a thoughtful and strategic approach. Some things to consider as you work to bring version control to your database pipelines include:

• Tracking complexity that increases as you scale and evolve, especially in environments with frequent deployments
• Coordination across distributed teams that lack unified visibility throughout the pipeline and change process
• How to easily rollback database changes without impacting the integrity of the environment or data
• Detecting drift that can occur from out-of-process changes, improper access permissions, or other unintentional updates that cause the actual state to differ from the expected version-controlled state

Luckily, the right version control tool minimizes these risks through automation, customization, governance, and observability capabilities. 

Tools for database version control

There is a mix of open-source and commercial database version control tools that can be used to allow teams to track changes over time. Given that migration-based version control tools are best suited for organizations attempting to accelerate software delivery, consider looking into the leading open-source or commercial solution if you are ready for database version control.

Four requirements for database version control tools

Visibility into your database versioning is crucial in reducing the chance of downtime caused by application failures that result from improperly configured databases. Effective DB version control also decreases the chances of irrecoverable data loss from updates that accidentally drop or delete data. 

Ultimately, it is critical, especially when trying to accelerate software releases, to have a good SQL database version control solution in place so that you can provide a consistent, high-quality end-user experience while allowing your business to function smoothly without interruption.

Here are four requirements you should aim for when considering database version control solutions:

1. Look for a tool that allows database code to flow through a unified, transparent pipeline along with application code. This enables better ROI from existing application release automation (ARA) investments, allows teams to keep application and SQL changes in sync, reduces errors or confusion if a feature set needs to be accelerated or dropped for a release, and eliminates the overhead of two separate and duplicate processes for promoting code from development to production.
2. The database version control tool needs to be able to validate SQL code with automation. Without this ability, SQL code will continue to lag behind the application code. Database schema versioning alone is not enough – database code needs to move at the same pace as application code, which is only achievable if SQL code validation is as automated as it is for application code.
3. The tool needs to be able to generate an immutable, idempotent artifact for downstream deployment. This core DevOps tenant is an important aspect of database version management. With a packaged artifact of validated and versioned database code, it’s easy for teams to maintain and manage the evolution of database schema.
4. The tool needs to be able to detect and help address drift. When making process changes, it’s common for users to regress to manual, undocumented SQL changes. Regardless of how it happened (a lapse in user judgment or a panicked response that resulted in an out-of-band update to a database schema), As such, it’s important when there are updates to a database schema that the SQL database version control tool detects and fosters reconciliation. Without the ability to detect drift, this can give teams a false sense of security, and worse can be complicit in an outage caused by a database change colliding against an undocumented change that was previously deployed.

Database version control with Liquibase 

Liquibase is an open-source, migration-based version control database solution. It lets you organize your changes into changelogs, which are rearrangeable, editable sets of changes that track which have been deployed to a database, and what additional changesets need to be applied to a database in order to migrate a database schema to a specific version. Liquibase supports XML, JSON, YAML, and SQL for defining changesets so that database schema changes can easily be translated to other DBMS platforms.

Liquibase Open Source

Liquibase Open Source is a great starting point for teams addressing the challenges that come with managing database changes. It works well for smaller teams and projects that have ample time to invest in extending and adapting the open source automation capability to meet their needs.

Liquibase Pro

Liquibase Pro adds expert support and advanced capabilities that enhance and extend the Liquibase Open Source features, so you can enable DevOps best practices at the database. These include:

• Quality checks for database changes
• Targeted rollbacks for cherry-picking one change or set of changes
• Database drift detection alerts to help guard against potential malware and ransomware
• Database observability including Structured Logs and Deployment Reports
• Enhanced stored logic capabilities
• SQL*Plus integration

Other database version control options

While Liquibase is a leading option for database version control, some teams decide to go the route of building a solution in-house. While that can be successful, these teams tend to get stuck in developing and implementing their tool while Liquibase users are already automating their first workflows. The resource and opportunity cost of devoting engineering time to create a version control tool tends to be much less appealing than trusting database version control to technology that’s been downloaded more than 100 million times and in use across some of the most highly regulated industries since 2006. The ease of setting up and achieving value with Liquibase makes for a much shorter, more efficient runway. 

Application and DevOps teams might also consider Liquibase vs. Flyway, but they tend to find it offers less advanced features and supports fewer database types. Without capabilities like workflows, structured logging, and other database DevOps features, Flyway falls short of the pipeline-accelerating powers of Liquibase. 

Bring database version control to your pipelines

The best way to enable database version control in your change management process is with an automation solution like Liquibase. Find out how Liquibase works to enable version control, CI/CD, observability, and more.