The Definitive Guide to Database Version Control — Part 4

This is part four of a seven part series on database version control. In part one, two, and three, we reviewed how the methods for creating software evolved into Agile and DevOps, what separates the database from application code, and the challenges that must be addressed when managing database changes.

We are now going through the most popular methods being used and the shortfalls of each. We will then reveal the correct way to practice database version control in order to maximize value and eliminate risk to the organization.

Another common approach is to use XML files, which use an abstract language for the change and keep track of the execution. The most common open source solution for this is Liquibase.

With the XML files, Liquibase separates the logical change from the physical change, and allows the developer to write the change without knowing the database-specific command. At execution time, it converts the XML to the specific RDBMS language to perform the change. Changes are grouped into a changelog, and they can be in a single XML file or many XML files referred by a major XML file which contains the order of the changes.

The XML files can be saved using the existing file-based version control, which offers the same benefits as the basic approach. In addition, based on the Liquibase execution track, it knows which changelog(s) have already been deployed and shouldn’t run again, and which have not been deployed yet and should be deployed.

Let’s see if Liquibase answers the challenges:

X   Ensures all database code is covered – Managing changes to reference content is not supported in the XML files used by Liquibase, and must be handled as an external addition, which can result in changes being forgotten.

X   Ensures the version control repository can act as the single source of truth – Liquibase doesn’t have any version control functionality. It depends on third-party version control tools to manage the XML files, so you have the same challenges when it comes to making sure the file-based version control repository reflects the version that was tested. The process that will ensure the version control repository can be the single source of truth required by developers to check-in changes in order to test them. This can result in work-in-progress changes being deployed to the next environment.

X   Ensures the deployment script being executed is aware of the environment status when the script is executing – Liquibase knows which changelogs have been deployed and will not execute them again. However, if the logical change is to add a date column and the column exists in varchar format, then the deployment will fail. Also, overrides of out-of-process changes cannot be prevented.

X   Ensures the deployment script handles conflicts and merges them – Any change being made to the database outside of Liquibase can cause a conflict, which will not be handled by Liquibase.

[Out of process changes are not handled]

X   Generates deployment scripts for only relevant changes – Changes can be skipped at the changelog level, but breaking a changelog into several changelogs requires writing a new XML file, which creates the need for more tests.

X   Ensures the deployment script is aware of the database dependencies – The order of the changes is maintained manually during the development of the changelog XML.

Bottom line: using a system that tracks change execution does not address the challenges associated with database development and, as a result, does not meet the deployment requirements.

In part five of the series we will examine another common approach—generating the database change script automatically by comparing the source environment to the target environment—and see if it meets the challenges of the database.