Database Version Control (DBVC): A Modern Foundation for Database Change Management

Example of the common DBVC tool in CI/CD approach

DBVC began gaining popularity alongside the rise of DevOps and CI/CD practices. In the past, database changes were carried out manually by DBAs and often lacked proper documentation. This created problems during rollback or audits. With the emergence of DBVC tools such as Flyway, Liquibase, etc., the process of database versioning became more automated and could be seamlessly integrated into CI/CD pipelines.

Why DBVC Tool is Important

Database Versioning Ensures Consistency Across Every Change
Source: https://medium.com/@ruxijitianu/database-version-control-liquibase-versus-flyway-9872d43ee5a4

On the other hand, here are the reasons why DBVC is essential:

• Prevents chaos during development and deployment phases
• Maintains consistency across environments
• Supports team collaboration
• Enables rollback and audit trail
• Facilitates automation processes
• Enhances security aspects

Example: Drift Checking Report Using a DBVC Tool

This report illustrates a typical drift verification scenario using the Database Version Control (DBVC) tool. It highlights changes detected between the expected database schema or data and the actual state in the target environment. In this example, the report shows:

• Deleted Objects: The table NewTable has been removed from the current database, indicating a drift from the expected schema.
• Modified Objects: Data changes are identified within the departments table. Specifically, the department name with ID 4 has changed from "marketing" (expected) to "police" (actual).

The drift verification report is essential for identifying unintended or unauthorized changes in the database, enabling teams to maintain consistency, integrity, and compliance throughout the software development lifecycle.

How Easy It Is to Manage Database Changes with DBVC:

Here is the simplified steps:

1. Add a column to your database ( e.g., in the employee table)

2. Flyway detects the object automatically in the Schema Model (via Flyway Desktop).

State-based vs Migration-based in DBVC Tool

In performing database migrations with DBVC tool, two main approaches exist, state-based and migration-based.

The state-based process. This approach works by taking a snapshot of the database structure and comparing it with changes.




Example of a state-based approach using Flyway. You can choose which changes occurred and select what to deploy to the target database.

The migration-based process. This approach stores migration scripts for each version of the database.

Example of a migration-based approach using Flyway. The versioned script files are executed and deployed to the target database.

Choosing a Right DBVC Tool for your team

A lot of DBVC tool exists in market, and one of the powerful one is Flyway. Flyway is lightweight, simple, yet powerful DBVC tool. It supports various types of databases such as PostgreSQL, MySQL, Oracle, SQL Server, and others. Flyway uses SQL or Java scripts to perform database migrations and tracks the migration status within a metadata table.

Flyway is available in both free and paid versions, offered through desktop and CLI platforms, and supports both state-based and migration-based approaches.

The trial can be activated during installation. A guide for activation and usage is available in the following article (Flyway trial installation tutorial). Below is the Flyway Desktop license management interface, which helps users activate features according to their edition:

Figure 2. License management interface in Flyway Desktop

Desktop and CLI Versions

Flyway is available in two main forms:

• Flyway CLI: A command-line tool for executing migrations. It can be integrated into a CI/CD pipeline.
• Flyway Desktop (formerly Redgate Flyway Desktop): A GUI tool for developers, making it easy to generate, preview, and validate migration scripts

Conclusion

Database Version Control (DBVC) is a crucial solution for maintaining database integrity and consistency throughout the entire software development lifecycle. With tools like Flyway, development teams can manage database changes in a controlled, automated, and secure manner. The migration-based approach is highly recommended in modern DevOps environments, as it provides greater flexibility and clear documentation for every version change.

However, implementing DBVC also presents challenges, such as potential version conflicts, the need for consistent naming conventions, and proper integration into CI/CD pipelines. Therefore, best practices should include: storing all migration scripts under version control, incorporating automated validations within pipelines, and using a staging environment to test every version before deploying to production. With a well-established approach, DBVC not only becomes a technical enabler but also a critical pillar in managing change and ensuring the continuity of digital services within organizations.