Trouble-Free Database Migration: Idempotence and Convergence for DDL Scripts

The manual practical resolution of this task without any additional tools entails the creation of a folder with numbered DDL scripts in the project’s code base, their aggregation in this folder, and the introduction of the strictest discipline among the developers. The process of recording changes in every script file and the process of applying changes to the database should be formalized.

A more advanced and convenient solution is the utilization of specialized tools that memorize the last script executed and ensure that structure modification scripts are executed in a linear order once and only once. Liquibase, Flyway, Goose, and dbdeploy are some examples.

These tools are of a great help to developers, but still do not make the migration task as convenient as modifying your application’s source code.

The chief disadvantages of a change log-based approach are as follows:

The reason for these limitations is the fact that there is data in the database, and this data needs to be dealt with if the structure is altered. We’ll discuss this problem later on, but let’s take a look at another viable approach to resolving this problem.

Problems similar to those that emerge during database schema migration have long been around in server configuration. How can we automate the installation of the appropriate software on the server and ensure configuration upgrades? Is it possible to alter the infrastructure as easily as we alter the source code — namely, by introducing direct changes to the description of the desired state rather than by writing an additional changeset?

Configuration management systems, such as Ansible, Chef, Puppet, etc. deal with these issues more than successfully. Idempotence and convergence are the two key principles at the core of all systems of this type. Both of these terms were borrowed from mathematics and have the following meaning:

I believe that all of the above is already sufficient to understand how these principles can assist in controlling database schema. Let’s assume that our database supports the CONVERGE keyword, and we have the following script:

The CONVERGE keyword should be interpreted by the database as “render the table into the desired structure.” The meaning is: if there isn’t a table, create one; if there is one, see which fields are present, what types, indexes, foreign keys, default values, etc. are there and whether any alterations of the table are required in order to render it into the desired state.

If databases could support such a keyword, i.e. if there was a possibility of writing idempotent and convergent DDL-scripts for databases, there would be no need for this article. We’d all simply have CONVERGE TABLE scripts in the system’s code base at hand, scripts that describe the required data schema. We’d deal with it just as with usual source code: add new fields to the table as needed and edit the index field set as required. Branching and merging these scripts in VCS would not cause any troubles.

I can sense that you’re wondering about actual data migration, but please sit tight, and I’ll get to that soon.

Unfortunately, the progress towards real DDL idempotence support in the world of relational databases is rather slow. For instance, in the recent years, the CREATE IF NOT EXISTS statement emerged, but to be entirely honest, it’s a rather weak statement. The CREATE IF NOT EXISTS script does, of course, act as an idempotent one (won’t throw an error if a table already exists) but not as a convergent one (won’t modify the structure of an already existing table).

That’s why we have to rely on external tools.

The implementation of such tools is perfectly possible. For example, the MS Dynamics NAV ERP system has for many years been using the tactics of rendering the actual state of the database into the desired state based on the specifications.

Moreover, such tools are surely demanded by developers. I’ve met with expert approval at numerous conferences and meetups where I shared the idea of an idempotent DDL.

However, as of now, I am unaware of general-purpose tools (that aren’t built into the development platform) that implement idempotent DDL. That’s what inspired our team to launch 2bass, a new open-source project.

In order to resemble a regular DDL script to developing tools (i.e. syntax highlighting and the ER-diagram visual editors), 2bass uses the CREATE keyword, although it has the meaning of a hypothetical CONVERGE. At every launch of the bass apply command, the system compares the actual structure of the connected database with the desired structure, described in an idempotent DDL script. If required, it executes the minimum requiredseries of CREATE/ALTER/DROP commands. As of today, four database types are supported: PostgreSQL, Oracle, MS SQL Server, and H2.

One does not simply execute a set of idempotent statements in random order. As we know, certain database objects depend on others, i.e. tables refer to each other via foreign keys, views and indexes depend on tables, and so on. That’s why prior to executing a series of object creation/alteration, they need to be arranged according to mutual dependence. To put it more precisely, the dependence graph has to be sorted topologically, and then we can start with objects that nothing depends on. A temporary reset of foreign keys is often required. These foreign keys are recovered after the modification of the tables that they link takes place. This feature is implemented in 2bass. It allows to securely run upgrades in the vast majority of cases.

So, what should be done about data migration since a simple ALTER is not always sufficient? What can be done if we, for instance, want to add a NOT NULL field to a non-empty table and do not want to supply it with a DEFAULT value? If such a field is not filled with data somehow, the database will not allow executing an ALTER TABLE ADD script. And what happens if we decide to add a foreign key, but data in the table doesn’t satisfy this constraint? What if, for example, the application logic has changed and data needs to be moved from one column to another?

All these questions are entirely valid, but for starters, let’s note that the majority of alterations introduced to the database in the process of software development do not require migration, and a simple ALTER script is sufficient. You don’t need to migrate data if you are merely adding a new table or a new column to the table (NULLABLE or with a DEFAULT value). You don’t need to migrate data if you’re adding or rebuilding an index. Nothing needs to be migrated if a view query is changed. My experience confirms that the vast majorityof alterations introduced by developers are precisely of this type.

If data migration is really required, then yes, you’ll need to write and execute a single-use migration script. You do not, however, need to save the script for the future, and the whole story with its implementation and fine-tuning is much simpler than in the changelog-based systems.

Let’s examine the case of a foreign key being added to a non-empty table, where some records do not satisfy this key. During the update, 2bass will attempt to create such a key using the ALTER TABLE…​ ADD CONSTRAINT …​ FOREIGN KEY command. If it works, perfect! If not, 2bass exits and reports that it is unable to conduct a full update of a certain object for a specified reason, with a specific message from the database.

There’s nothing worse for a changelog system than a half-executed changeset that stalled. That’s when the system is stuck between two revisions and the situation can only be straightened out manually. The lack of rollback support for DDL transactions in some database management systems presents additional problems.

Incomplete updates are not an issue for convergent systems, unlike changelog systems, since for the purpose of ALTER command generation, the system compares the current actual state of the database with the desired state and tries to complete the alterations that have not been executed during the first attempt.

When facing a situation where an update cannot be automatically executed (I’ll repeat myself and remind you of how rarely this happens), you may write an ad hoc single-use script. For instance, it may be one that fills a reference table with required data, thus creating the conditions for 2bass to conduct an automatic update. You can fine-tune this script on a fresh copy of the production base, then execute it on the production base, and then execute a bass apply command.

After all of this is done, you can simply throw outyour script because you’ll never need it again! Your production database structure is already in the desired state, and if you decide to create a new database from scratch, then you won’t need the database to follow the entire path that you’ve gone through in the course of its development.

Perhaps this approach seems less reliable than using changelog systems, which force you to deliberate on the steps required for data migration at each structure alteration and about incorporating such steps in a changeset. But come to think of it, the professed reliability of changelog systems is mostly fictitious. Software without bugs does not exist, and this is quite true of data modification scripts. The fact that the changeset modification script was fine-tuned and performed correctly for your dataset does not actually provide a 100% guarantee of its error-free execution on any database. At least, in the case of idempotent DDL execution, we do not consider the data modification script unalterable, a revision attribute protected by the hash sum. In case of an error, we can always repeat the update attempts until we bring the system to the desired structure. Your data won’t be lost since 2bass never automatically drops columns or tables with data, leaving this operation for manual execution.

The given approach certainly has its limitations. For instance, it won’t work when your application is installed on dozens of databases, and you need to synchronize the structure of all the databases simultaneously. It won’t work when you need to execute data conversion scripts frequently due to the application’s peculiarities. At the same time, for most business applications that use a single production database, this approach works perfectly.