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.
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.
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.
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.
Migrations are semi-automatic and fully interactive.
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.
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.
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
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.