Version migration of the database structure: from theory to practice

In the topic is considered another simple system of versioning the database structure, as well as IMHO, why we are not suitable for others.


Accidentally stumbled upon the Shedal habrauser topic. Versional migration of database structure: basic approaches . I wanted to write a detailed comment, but I looked at the date of the creation of the latter and decided to issue a separate topic.

The purpose of this topic is not a review of approaches to versioning the database - it is remarkably made in the article at the link above - there will only be a brief IMHO on approaches that for some reason did not suit us.
')
This topic will be primarily of interest to developers who are only looking at database migrations and learning tools. If you have been using any systems / equipment for a long time and are satisfied with them, then you probably won't learn anything new here.

Formulation of the problem

We develop in PHP + MySQL, use the Kohana framework. It was necessary to organize somehow the process of migration of the database structure, in order to somehow automate the deployment to the test server and production. We do not write simple websites, business cards, but also super-complex projects, too. The main projects are mostly not very large (15-30 tables in the database; 50-200 man-hours). Everything written below is true for us, but it is quite possible that it will not work for more complex projects or for other technologies / frameworks (many frameworks have their own implementation of database migrations, therefore there is no sense to use other bicycles)

Method of simulating database structure to source code

When the entire database schema is stored in the version control system, and for the migration of the database to the latest version, a diff script is generated that converts the original database to the last one. In theory, everything is fine, in practice there are problems:

• existing tools cannot always (even theoretically) solve this problem automatically. The simplest example is renaming a column or table.
• I personally never trust the program to generate ALTER scripts for production, which means that before using them you need to view / edit your hands

We tried several tools and abandoned this option.

Incremental change method

There are several options here: either we store ALTER scripts written by hand, or we write them in PHP (or any other one on which we write our application). An example of such a spherical migration in a vacuum:

class Migration_0001 extends Migration_Abstract { public function up() { $this->createTable("users") ->column("id", self::PK) ->column("username", self::VARCHAR); } public function down() { $this->dropTable("users"); } } 

In general, a pretty good option. As far as I know, this is implemented in Doctrine and other frameworks. A few words against it: after all, this is not always a justified extra complication of our system. Why we didn’t use it - Kohana doesn’t support these migrations out of the box, I don’t want to use Doctrine for this either - it’s too monstrous IMHO. This is true for our projects. You can be different.

For us, the most convenient method seemed to be when ALTER scripts are manually written in SQL. Tuls and a technician for this mass, links to them in the topic on the link at the top. All of them are good, but we decided to simplify the system to the impossibility so that the full functionality of rolling changes fits into several lines and it was not necessary to use ready-made tools for this.

Our solution

First of all, we refuse the possibility of a rollback. I personally think that in most cases this option is not needed at all, and there are a lot of questions with it. Further, we refuse the rules of naming migration scripts, their numbers and a separate version number for the database.

All database changes are stored in separate .sql files (we, like many, call them deltas) in one folder. Changes are rolled in alphabetical order. The applied deltas are saved in the changelog table (which is loaded from a separate “initial” sql-dump). All non-applied deltas are used during migration. Everything. An example of a folder with deltas:
deltas
|- 0001-users.sql
|- 0002-users-add-username.sql
'- 0003-users-drop-last-login.sql

The code that handles this migration system is very simple and can be written and embedded anywhere. We, for example, use phing. Someone enough simple php-file.

The absence of the rule of end-to-end numbering of delta scripts miraculously solves the main problem of this approach - parallel development in different branches of the repository. I will explain.

Suppose we have three deltas in a trunk, as described above. Vasya makes a brunch, for example, to add a “remember me” checkbox, Petya at this time continues to rule minor bugs in the trunk. Vasya adds the delta 0004-user-tokens.sql , Peter adds the delta 0004-users-change-username-length.sql and 0005-users-add-email.sql . Okay. After the merge, there will be two deltas under the “number” 0004 in the trunk - but the number is not important to us, we apply all those not applied, so Vasya’s changes will be applied to the base in Petit’s trunk without any problems.

Of course, there may be conflicts when simultaneously changing the same columns of the table, but this situation is extremely rare.