Intro to Database Version Control/Database Versioning
There are two key elements to any software experience: the application and the data. 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. 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.
According to Google Cloud’s DevOps Research and Assessment (DORA) team in their 2021 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.
What is database versioning?
Database versioning begins with database schema, the structure of the database. In order to effectively version a database, you need to track and understand the changes that are happening.
Why Version Control for the Database?
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
In trying to accelerate the pace of software delivery, organizations need to manage both application and database changes. 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 is increasingly becoming a bottleneck that holds organizations back from faster software releases.
It’s possible to believe that database schema version control isn’t something that applies 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.
4 Requirements for Database Version Control
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:
- 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.
- 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 – it’s important for database code 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.
- The tool needs to be able to generate an immutable, idempotent artifact for downstream deployment. This is a core DevOps tenant and 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.
- 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.
Methods for 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 a comparison between 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.
A central tenant 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. 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.
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).
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:
Liquibase Open Source
Liquibase is an open-source, migration-based version control database solution. It relies on a changelog to track what changesets 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 an XML model for defining changesets so that database schema changes can easily be translated to other DBMS platforms. As an open source solution, Liquibase 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 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 don’t have to slow down for your 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
- Enhanced stored logic capabilities
- SQL*Plus integration
Liquibase Enterprise
Liquibase Enterprise is a migration-based enterprise solution that extends Liquibase Open Source by adding enterprise-friendly features and advanced capabilities (like the rules engine). It includes a number of essential database schema control capabilities required for large teams and projects typically found in mid or large-size enterprises. Liquibase Enterprise has an extensible rules engine to enable automated validation of database changes, a change management simulator to simulate database schema changes to ensure that database deployments do not result in errors or rule violations, and a database code packager that builds validated database schema changes into an immutable artifact for downstream deployment. Liquibase Enterprise also includes an on-prem database monitoring console so that all stakeholders can get instant insight into the version of each database across the software development pipeline.
To learn more about how Liquibase fits into your existing CI/CD software workflow and integrates with the tools your team already uses, check out our white paper: How Database Release Automation Fits into the Application Toolchain.
