Yii 1.1.6 and the beauty of yiic migrate

The new Migration feature is amazing. A-mazing.

In addition to migrating production servers from one version of your software to another, it's excellent for coordinating among a group of developers *AND* for coordinating between primary and test databases. Farewell SQL alter script coordination, it's been real ;)

If you haven't read it yet, I highly recommend reading this guide to migrating:

http://www.yiiframework.com/doc/guide/1.1/en/database.migration#creating-migrations

(Also, I recommend getting the bug-fix from the current yii trunk here: http://code.google.com/p/yii/source/detail?r=2907 which fixes the issue with the migrate not using the connectionId option properly.)

So, how do we actually apply this new wonderful feature? It's quite simple!

Configure your application for migrations

Migrate is a console command. So, to use it properly, you need to configure your config/console.php file to know what your database connection is.

If you have a test database that you wish to keep up to date via migration command, add a connection component for the test database as well and ensure that you've explicitly set its 'class'=>'CDbConnection' property, or it won't recognize it as a database connection. Also ensure that you've applied the bug-fix listed above.

You will need to create the default migrations folder under the webapp's /protected/ directory, or create an alternate location for it. If you're using an alternate, be sure to set the config for the migration properly as shown at the bottom of the guide to migrations.

Now, you're ready to begin!
If you're working with a team, lets assume you're all starting with the same database structure. If not, do everyone a favor and start with a mysql dump file to get everyone at the same starting point. Simply add the 'base' .sql file to your shared source control repository.

Lets say you have an existing web application that has a User object.  You decide that you want to add a flag on the model for accountStatus and have it track whether the account is current or not. (This is a very simple addition, but makes a good example I think).

Go to the shell, and from your webapp/protected/ folder, run the following command:

./yiic migrate create update_user_accountStatus 

This will generate a new CDbMigration object in your protected/migrations/ folder.  The file will have a timesamp prepended to the name of the object to make it unique and so that the migrate command knows which order to apply changes in.

Open up your newly created  m101129_185401_update_user_accountStatus
object and note that it has both an up and a down method.  You WILL want to enable the down method as well.

In the up method, tell it to add your table column for accountStatus, as follows:

public function up()
{
  // Add the column `accountStatus` to the `tbl_user` with specified type
  // properties
  $this->addColumn('tbl_user', 'accountStatus', 'INT(0) DEFAULT 1');
}

And reverse the change in the down method:

public function down()
{
  // Undo add the column `accountStatus` to the `tbl_user` with specified type
  // properties
  $this->dropColumn('tbl_user', 'accountStatus');
}

Now, you can run your change by going to your shell and running the migrate command from your webapp/protected/ directory:

./yiic migrate up

You will be asked to confirm that you wish to apply the migration, simply answer yes.
Look at your database and you should see the change.

To reverse the change, simply run

./yiic migrate down

If everything is good, you can commit your change and tell your development team to pull the changes and run ./yiic migrate on their local development environments.

When applying multiple patches, or after syncing with a repository update, you should run the migration script for all entries, not just up 1, by using simply

./yiic migrate

What about the test database??
No worries!!
Ensure that you've fixed the current bug listed at the top (or have the newest trunk version of yii), and simply run your migration specifying your test database connection defined in the config/console.php

./yiic migrate --connectionID=testdb

You can now update your fixture data and unit tests to include functionality for your new accountStatus field without worrying that someone's tests will fail. Simply ensure that your build processes run the yiic migrate commands against both databases.