An introduction to database migrations in Yii

adminYii0 Comment
5 min. reading

If you’re new to Yii migrations, these sources might be a help for you:

  1. General information: Database Migration topic in The Definitive Guide to Yii.

  2. How to create initial migration basing on current database state, explains this Stack Overflow question and GitHub Gist (with InitialDbMigrationCommand class’ code) to support it.

To use migrations, you have to properly configure console version of your Yii application. Details follows.

Configuring and tweaking up migrations system

For using migrations, you need to setup correctly console configuration. In particular, you need do define correct database connection — that is the db element of components group in your console application configuration file (usually console.php). In most cases, your db component will be exactly the same as in case of regular application configuration, unless you need to use different database connection or configuration for executing console commands.

By default, all your migrations will be stored in tbl_migration table. If you want to change this (for example to just migrations, to match names of your other tables) you need to override migrate command in commandMap group, in console application configuration, like this:

'commandMap'=>array
(
    'migrate'=>array
    (
        'class'=>'system.cli.commands.MigrateCommand',
        'migrationPath'=>'application.migrations',
        'migrationTable'=>'migrations',
        'connectionID'=>'db'
    )
)

Put this into main array of your console application configuration and migrations in your application should now start using non-standard table name.

Using migrations

The very basic idea, is to do following after each git pull:

cd protected
yiic migrate

And then hit Y to run all migrates from current point to the newest migration.

Above is an equivalent of yiic migrate up. So, you can expect, that executing yiic migrate down will do something exactly opposite, that is — run all migrations from current point to the very bottom (to the initial migration), executing their down() or safeDown() (see below) methods to revert all the changes done by each of these migrations.

Running yiic migrate redo will do a redo operation, which is an execution of down() or safeDown() method followed by execution of up() or safeUp() method.

You can also execute yiic migrate up N, yiic migrate down N or yiic migrate redo N to force yiic to run up, down or redo N migrations from current point.

Some resources:

  1. If you need to run single migration, that is not the current one, here is a solution on how to do this.
  2. You may consider linking yiic migrate command as post-pull hook to be executed automatically, after each git pull. Or running it periodically via CRON. Interested? Then this link and that one maybe useful for you.
  3. If you need to run migrations stored inside your modules you can use this method or install migrate-command extension, which offers this (and many more) functionalities.

Note, however, that even though storing migration commands in modules is an interesting idea to provide full module separation, you should consider it twice, as it is not suggested one method (you should keep all your migrations in protected/migrations folder).

Creating new migration

Execute yiic migrate create NAME to create new migration with a given NAME. A file named DATE_TIME_NAME.php will be created, containing DATE_TIME_NAME class inside, a descent (extension) of CDbMigration class and prefilled with empty up() and down() methods.

Write your code, add some coments, test and deploy. Voila!

Note, that Yii migrations system (CDbMigration) offers two types of methods — up() plus down() and safeUp() plus safeDown(). Second set differs from first one only in that it is run as transaction and rolled back entirely, if any part of migration fails. Keep in mind, that CDbMigration always runs ONLY ONE sets of commands, either standard or “safe” one; never both of them. You have to decide, if you want particular migration to be run directly or enclosed entirely inside a transaction. To workaround this problem, you can also, split particular operation into two migrations, one run directly and one enclosed in a transaction.

In a very rare situation, where you would really have to combine transaction-based and transaction-less command in one migration, you can use dbConnection property, which returns the currently active database connection (a CDbConnection object), and start a transaction through it. An example of this is given in an introduction to CDbConnection and includes something like this:

$transaction = $connection->beginTransaction();

try
{
   $connection->createCommand($sql1)->execute();
   $connection->createCommand($sql2)->execute();

   $transaction->commit();
}
catch(Exception $e)
{
   $transaction->rollback();
}

Put this into your up() or down() method to separate part of SQL statements into a transaction.

Leave a Reply