Data Migrations Shouldn’t Be Cliffhangers

A Cliffhanger

Let’s set the scene. Your organization operates a custom database-driven PHP application. The development team has implemented and tested an exciting new feature that requires structural modifications to the database. The operations team is in the process of releasing the new version into production. They start to manually apply the modifications to the live production database, and …

For many software applications, this moment is truly a cliffhanger. Has the development database accurately captured all of the relevant nuances of the production database? Did the development team remember to document each and every one of the migration steps and in the correct order? How easily could a syntactically valid typo in one of these steps result in massive data loss or corruption? If the deployment fails, how much time and effort would be required to restore the production database to its previous state?

Data migrations shouldn’t be cliffhangers. While there are multiple tools available to put this generalization into practice, in this article I will discuss one in particular: Yii.

What is Yii?

In short, Yii is a high-performance PHP framework that implements the MVC (Model-View-Controller) architectural pattern and promotes simple, elegant code based on that pattern. Among many other features, it provides an object-oriented API for accessing relational databases, including MySQL, MariaDB, SQLite, PostgreSQL, and more. This API is purposefully designed to boost performance, to elevate usability, and also to tighten security by assuming much of the burden of preventing SQL injection attacks.

How does Yii help with Data Migrations?

Yii incorporates a suite of tools dedicated entirely to streamlining and fortifying data migrations. My discussion here will remain fairly high-level, so I recommend that you also visit the official Definitive Guide to Yii: Database Migration, which is comprehensive yet accessible and includes plenty of concrete scenarios with supporting code examples.

The Yii migration tools all revolve around complete, precise, and automatable instructions in the form of PHP classes, which are intended to be version-controlled together with the application source code. Each one of these classes is known as “a migration” and consists primarily of two methods: one named up or safeUp, which makes changes to the database, and another named down or safeDown, which reverts those changes. (Sometimes it may not be possible to revert certain changes, and down can be structured to indicate as such.) Migrations can be easily created, applied, reverted, re-applied, and listed through straightforward command-line actions. After every application and reversion, Yii will add an entry to a special managed table that pinpoints the database’s current state in relation to the collection of defined migrations.

Let’s consider a specific scenario. Assume that there exists a table named customers with the following structure and data:

Suppose that one or more users decide to distinguish identical names by appending a middle initial/name to the first name:

Now imagine that, in order to preserve the original purpose of the name_first column, the development team has been instructed to move the middle initial/name to its own dedicated column.

In this case, the assigned developer first runs a Yii command to generate a new migration scaffold. Then, the up method is edited to (1) create a new column name_middle in table customers, (2) update name_middle with the appropriate trailing substring of name_first, and (3) replace name_first with its appropriate leading substring. The down method is edited to revert these changes in reverse order: (1) append name_middle to the end of name_first and (2) drop column name_middle. The developer finishes by running a Yii command to apply this migration to the development database, which executes all of the actions in up. At this point, the customers table should look like:

Once the result is validated, the migration is committed to the version control repository for dissemination to other team members. When it finally becomes time to apply it to the production database, all the assigned deployer has to do is run the same Yii command on the same explicitly defined migration that the development team has already tested and verified. If for some reason the migration aspect of the deployment fails, and if the migration actions have been duly encapsulated within a transaction as officially recommended, the database will automatically revert. If instead, a completely separate aspect of the deployment fails, the deployer could simply run a Yii command to manually revert the (successful) migration, which executes all of the predefined actions in down.

Thus the data migrations cliffhanger has been skirted in favor of a firm, steady slope that supports both incremental advances and incremental retreats.

Who uses Yii?

Currently, as of October 2018, Yii is not a particularly widely used framework. In fact, as far as I know, not one major website or web application relies on it. It’s far overshadowed by its most direct competitor, Laravel, which ranks #8 in popularity on HotFrameworks and has been neck-and-neck with Angular (not AngularJS) for the past year. In contrast, Yii languishes at #25. Even among PHP frameworks only, HotFrameworks ranks Yii as a mere #6 in popularity. If the ranking is instead based on subjective “best” PHP frameworks, as opposed to popularity alone, Yii manages to claw upward to #4, according to a Hongkiat.com article from earlier in the year.

Although this relative lack of standing should rightly give pause to anyone considering new development with it, Yii does boast a number of distinct advantages over its competitors. Informative comparisons of Yii versus other PHP frameworks can be found in How to choose a PHP framework, PHP Frameworks Comparison: Yii vs Laravel, and Yii vs Laravel 5. These sources generally agree that:

• Yii is blazing fast.
• Yii’s out-of-the-box security is excellent.
• Yii’s native integration of jQuery is nice.
• Yii is simple to install and get working.
• Yii is highly and easily extensible.
• Yii has a strong, mature support community.

In other words, if you like what you’ve seen so far, Yii might prove to be your own “best” choice of framework for your database-driven PHP application. To speak about Yii or other data migration tools with some of our very own industry professionals, reach out to our team.