Over a million developers have joined DZone.

The Definitive Guide to Database Version Control – Part 2

DZone's Guide to

The Definitive Guide to Database Version Control – Part 2

In part one we reviewed how the methods for creating software evolved into Agile and DevOps and how automation is based on version control.

· Database Zone ·
Free Resource

MariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

This is part two of a seven-part series on database version control. In part one we reviewed how the methods for creating software evolved into Agile and DevOps and how automation is based on version control. We will now discuss why version control for the database is a key component of DevOps. We will go 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.

Most IT applications today have many components using different technologies, such as mobile, ASP, PHP, application servers, Citrix, databases, etc. These must all be in sync for the application to work. If, for example, a new column was added to a table, or a new parameter was added to a stored procedure, all other application components must be synchronized to the structure change in order to function correctly. If this synchronization breaks, then the application can fail by calling the wrong parameters to the stored procedure, or by trying to insert data without the new column.

The unique properties of the database component differentiate it from other components:

  • A database is more than just SQL scripts. It has a table structure, code written in the database language within stored procedures, content that is saved in reference tables or configuration tables, and dependencies between objects.
  • A database is a central resource. Several developers can work on the same object, and their work must be synchronized to prevent code overrides.
  • Deploying database changes is not as simple as copying and replacing old binaries. Database deployment is the transformation from version A to version B, while keeping the business data and transforming it to the new structure.
  • Database code exists in any database, and can be modified directly in any environment. This is unlike other components, where everything starts from a clean workspace in the build server.
  • database changes

    Must-Have Requirements

    There are several challenges that must be addressed when managing database changes. You must:

    • Ensure all database code is covered (structure, code, reference content, grants)
    • Ensure the version control repository can act as the single source of truth
    • Ensure the deployment script being executed is aware of the environment status when the script is executing
    • Ensure the deployment script handles conflicts and merges them
    • Generate a deployment script for only relevant changes
    • Ensure the deployment script is aware of the database dependencies

    In part three of the series, we will examine the most basic method for managing database changes, which is to save the alter command in a script or set of scripts, and manage them in the existing file-based version control. We will see if this method overcomes the challenges for the database.

    MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.

    database version control ,database ,devops

    Published at DZone with permission of

    Opinions expressed by DZone contributors are their own.

    {{ parent.title || parent.header.title}}

    {{ parent.tldr }}

    {{ parent.urlSource.name }}