Why to use Yii migrations instead of plain SQL files?

If you have never been using Yii 1.x database migrations before, you may be sceptic (as I was before starting to using them), if they’re so good? And whether it is worth to waste time on dumping database scheme or rewriting .sql files into Yii migrations.

This document outlines most important pros of using Yii migration system.

Live code

The most important advantages of using migrations that I know is the fact that migrations allows you to use Yii and PHP commands. Possibilities are endless. Starting from variables (feeding database with fields and values generated based on some factors, like current time etc.).

Through using classes; for example, for generating user password:

$user == new User(); $user->hashPassword('somepassword')

You can hash password with current hash used in hashPassword method or even set by user. While in .sql iles you have to always place plain, non-dynamic, non-changeable hash.

To, finally, complex data importing scripts that can i.e. automatically pull data from other systems, applications or databases and feed your dataset with them.

Network

Because migrations uses PHP code, you can for example:

• include data from some external files or even fetched over network,
• poll certain servers to obtain extra data or an information, if migration should continue
• log each fired migrations any way you’d like.

Only your imagination can set border here.

All of these isn’t possible, when operating on plain .sql files.

User interaction

Since migrations are fired from the console, you can interact with user firing it, through that console.

You can read user input and generate data based on it.

You don’t need absolutely nothing else to write complex application installation scripts. That will gather all required information from user (like database login and password) and feed database with data generated accordingly to that user entry.

Full interaction

Migrations are semi-automatic and fully interactive.

You fire yiic migrate and Yii itself takes care about checking integrity between your current database state and selected migrations. It will apply only those migrations, that are required.

When using .sql file you have to manually check, which files has already been imported to database and which should be imported right now. Human factor here makes it easier to make some stupid mistakes.

Console approach

Migrations are (in most cases) easier to apply.

They do not require access to phpMyAdmin or similar tool. You don’t have to provide person responsible for updating a database (i.e. user in some public scenarios) with URLs or logins and password required by these tools. You don’t have to expose these tools to the public.

On the other hand, on shared hosting you have to have shell access to run them. This may be a huge problem, if not hitting the wall, on cheapest or free hostings. There are certain solutions to resolve this is issue (shell-in-the-box-like solutions that exposes console through web).

Because you run migration from command-line, you can create a Git (or any other VCS) hook that will automatically fire migrations after each git pull. While you have to manually include each update .sql file or play a lot to made this part automatic.

Two-way highway

Migrations are two-ways organisms. Most of migration-related classes and commands provides down methods. You can use them to “reset” database state to a particular moment.

This would be very hard (if not impossible) to achieve using plain .sql files.

All aces in your hand

When using CDbMigration commands you create database change scheme that can be applied virtually over any RDBMS. Yii will try to detect it using connectionString and adapt generated commands to match RDBMS-specific requirements.

When dealing with pure SQL instead, you have to always write or generate separate .sql file for each RDBMS.

Last, but not least…

There is, of course, also one big disadvantage. Writing migrations.

While SQL files can be generated by many tools (like phpMyAdmin), migrations must be written by hand.

You have certain tools and tricks to make this process easier, but be prepared that at least some migrations you’ll have to write by hand.