Part IV: Utilizing a Changelog Activities Tracking System
XML files make for another common approach to managing database changes. XML files use an abstract language to reflect changes and track their 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, Liquibase converts the XML to the specific RDBMS language to perform the change. Changes are grouped into a changelog and can be stored in a single XML file or ordered via a major file that refers to many smaller XML files.
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) already have been deployed and shouldn’t run again, and which were not been deployed yet and should be deployed.
The Changelogging Checklist
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 database version control repository can be the single source of truth requires developers to check-in changes in order to test them. This can result in work-in-progress changes being deployed to next environment.
X Ensures the deployment script being executed is aware of the environment status when 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.
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.
Using a system that tracks change execution does not address the challenges associated with database development and, as a result, does not meet the best practice deployment requirements.
This is part four of a seven part series on database version control. In parts 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.
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.
What about database source control? Read our next post to learn more.