The Definitive Guide to Database Version Control – Part 3

this is part three of a seven-part series on database version control. in part one and two , 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.

the most basic method for managing database changes is to save the alter command in a script or set of scripts, and manage them in the existing file-based version control. this guarantees a single repository that stores all the application component assets. developers have the same functionality when checking-in changes for the database as they do when they check-in changes for .net or java, such as linking the change to the reason (cr, defect#, user story, etc.). almost any file-based version control solution today has a merge notification when several developers change the same file.

but let’s see if this solution actually overcomes the challenges for the database, and avoids the potential pitfalls:

✓ ensures all database code is covered – since the developer or dba writes the script, they can make sure it will handle all database code.

x ensures the version control repository can act as the single source of truth – not really, as the developer/dba can log in directly to the database (in any environment) and make changes directly in the database.

[manually-written sql scripts]

changes made to the deployment scripts as part of scope changes, branch merges, or re-works are done manually and require additional testing.

two sets of scripts must be maintained – the create script and the alter script for the specific change for the release. having two sets of scripts for the same change is a recipe for disaster.

x ensures the deployment script being executed is aware of the environment status when the script is executing – this depends on the developer and how the script is written. if the script just contains the relevant alter command, then it is not aware of the environment status when it is executed. this means it may try to add the column although it already exists. writing scripts that will be aware of the environment status at execution time significantly complicates script development.

x ensures the deployment script handles conflicts and merges them – although the file-based version control provides the ability to merge conflicts, this is not relevant to the database as the version control repository is not 100% accurate and cannot be the single source of truth. the script might override a hot fix performed by another team, leaving no evidence that something went wrong.

x generates deployment scripts for only relevant changes – scripts are generated as part of development. ensuring the scripts include only relevant and authorized changes – based on the tasks being approved – requires changing the script(s), which creates more risk to the deployment and wastes time.

x ensures the deployment script is aware of the database dependencies – developers must be aware of database dependencies during the development of the script. if a single script is being used, then the change usually is being appended. this can result in many changes to the same objects. if many scripts are being used, then the order of the scripts is critical and is maintained manually.

bottom line: not only does this basic approach fail to solve the database challenges, it’s also error-prone, time-consuming, and requires an additional system to keep track of the scripts being executed.

in part four of the series, we will examine another common approach – utilizing a changelog activities tracking system with liquibase – and see if it meets the challenges of the database.