Database Version Drift — What it is and how to avoid it!
Database Version Drift happens when the state of your (dev|test|prod) database deviates from the state of the source files in version control. This article explains how this happens in your development process and what measures can prevent it.
What is database version drift?
Making a small manual hotfix in production without adding it to the version control system or changing an issue in version control without deploying it into production is how database version drift happens. It can be something small like correcting a typo or adding a commit into a procedure directly on the database. If the change doesn’t happen in version control and is then deployed onto all environments, database version drift happens.
What are the possible causes of database version drift?
🚩 Manual changes
Manual changes are evil. If no deployment automation is in place, chances are high that changes won’t make it into a version control system or that certain environments won’t be provisioned with a new change. During a hotfix or emergency change, developers can easily forget specific environments. Or even worse, if manual changes happen and deployment automation is in place, chances are high that developers won’t know about the change, which will possibly lead to an erroneous future deployment.
🚩 Stopped features
Developing a feature and finding out in the test environment that it’s not suitable for production is something every developer has experienced. Stopping a feature early is a safety mechanism to guarantee a reliable production environment. Those features usually go back into development or are entirely removed from the pipeline. But having changes deployed on a test environment that aren’t in production causes database version drift.
🚩 Branching
In theory, branching allows developers to work independently on their assigned tasks. But building a system as a team requires frequent coordination and communication. With long-running branches, you implement a system of long-running communication. Chances are high that once you want to merge your changes, you run into conflicts with changes made in the meantime on the mainline.
The more you branch, the more likely you will experience database version drift!
Database changes depend on a particular database state represented on the mainline of your version control. If you create a new change, it depends on the previous changeset. With branching and especially long-running branches, chances increase that some of your changes will be erroneous by the time you want to merge them with the mainline. You are causing rework and wasting time.
But is Trunk-based development the solution? Trunk-based development requires adding small changes often and therefore reduces the risk of invalid merging changes because the state on the mainline changed. With development teams, communication is vital — and the organisation of implementing tasks essential to avoid redundant work.
However, trunk-based development will not solve the issue when a feature cannot go into production after being, e.g. installed on a test environment; the change must be reverted on the environments it was already deployed. So there is no silver bullet — every strategy has advantages and disadvantages. Nevertheless, trunk-based development or short-running branches have the most benefits overall.
🚩 Development environments that aren’t updated frequently
Development tasks often require experiments. Those experiments will leave database objects of the development environment in a different state than in production. Developers might also create temporary objects or objects for testing purposes. The cleanup after implementation is burdensome, so developers often don’t do it. Without an automated mechanism that rebuilds development environments by clicking a button, database version drift of the development environment happens.
Suppose we use independent database development environments and have automated mechanisms in place. In that case, we can quickly rebuild development environments once a finished feature is installed into production and the development of the following change starts. If we have a shared development environment, rebuilding and avoiding database version drift is much more challenging because the rebuild must be coordinated with all developers.
How can you avoid database version drift?
- 1️⃣ Make sure you can restore development and test environments automatically to a particular version.
- 2️⃣ Use a branching strategy that minimises the risk of database version drift. Practice trunk-based development and, if needed, use short-running branches to develop changes.
- 3️⃣ Don’t allow manual changes on environments. All changes must be executed by an automated pipeline that takes the change set from a central version control system.
- 4️⃣ Use independent development environments that can be rebuilt by clicking a button, decoupling the work of developers.
- 5️⃣ Regularly check if database version drift happens between environments, ideally in an automated manner. Most likely, you will already have tools that allow you to check manually if database version drift happens (e.g. schema compare in your database IDE). If you want to script an automated approach, one way would be to use Oracles’ DBMS_METADATA package to compare the metadata of two databases.
Conclusion
Database version drift can have many different causes. Designing the development, integration and deployment process should be done to eliminate the risks of database version drift, or if it happens, detect it and report it to the developers.
