Database Version Control Guide

Introduction: The Modern Software Factory

There are two key elements to any software experience: the application and the data. Both elements need to be present for a valid and 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 to end users. However, unlike the application, the data component cannot simply be overwritten. Data is a persistent and valuable resource that is stored in a database. Unlike applications, databases are stateful and work hard to persist the organization’s treasured data, and as a result, the database is one of the most valuable and important assets to the organization – therefore database version control is needed.

In trying to accelerate the pace of software delivery, organizations need to manage both application and database changes. To this end, there has been sharp growth in version control tools to bring transparency and automation to application code as it moves from development to production – but 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 is hold organizations back from faster and better 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, 71% of 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.

A good database schema version control solution is necessary to make it easy to understand what has been deployed to any given database. Such visibility is crucial in reducing the chances of downtime caused by application failures that result from an underlying database that has been improperly configured. Effective database 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.

Requirements for Database Version Control Tools

Database schema control is a unique problem that cannot simply be addressed with application release automation (ARA) tools. ARA tools cannot validate SQL code nor can they safely manage the state of the database. If you rely on application release automation to deploy SQL scripts, you can easily corrupt, delete, or otherwise bring down a database. Given that application release automation tools cannot address the needs for SQL database version control, a solution that is built to handle the stateful nature of databases is necessary. In adding a database schema version control solution to your software release process, it’s important to consider the following:

  • The database version control tool should allow 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 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’s
  • The SQL database version control 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. As such, it’s important that whether it was a lapse in user judgment or a panicked response that resulted in an out-of-band update to a database schema, that the SQL database version control tool detect and foster reconciliation. Without the ability to detect drift, a database version control system 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 Methods

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

  1. State-based Database 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, it is a very poor fit 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 performing a 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.

  1. Migration-based Database Version Control

Instead of 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.

Database Version Control Tools

There are a mix of open source and commercial database schema version control tools that can be used to version the database and allow teams to track changes over time. Given that migration-based database 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 a migration-based open source database version control solution that relies on a changelog to track what changesets have been deployed to a database, and what additional changesets needs 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 another DBMS platforms. As an open source solution, Liquibase is ideal for smaller teams and projects or for teams that have ample time to invest in extending and adapting the open source capability to meet their needs.

Datical (Commercial):

Datical is a migration-based enterprise solution that aggressively extends Liquibase. It includes a number of essential database schema control capabilities required for large teams and projects typically found in mid or large size enterprises. Datical 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. Datical also includes an accessible database monitoring console so that all stakeholders can get instant insight into the version of each database across the software development pipeline.

Summary

As organizations are pushed to accelerate software delivery while maintaining quality and stability, they have turned to Agile and DevOps methodologies and tools. While many organizations have invested in capabilities focused on accelerating application code changes, the database schema change process has remained stagnant. In order to achieve real improvements in overall software delivery, organizations are having to move beyond a slow, manual, ad-hoc database version control process. To this end, database release automation tools such as Datical can equip enterprise teams with the necessary capabilities to track and manage database changes so that they flow through the software development pipeline quickly, safely, and alongside application changes.

To learn more about how Datical, the leading commercial migration-based database solution, can enable your DevOps and Digital Transformation initiatives, see how Database Source Control Enables Agile Applications.

Seeing is believing.