Database Migration

Note: The database migration feature has been available since version 1.1.6.

Like source code, the structure of a database is evolving as we develop and maintain a database-driven application. For example, during development, we may want to add a new table; or after the application is put into production, we may realize the need of adding an index on a column. It is important to keep track of these structural database changes (called migration) like we do with our source code. If the source code and the database are out of sync, it is very likely the whole system may break. For this reason, Yii provides a database migration tool that can keep track of database migration history, apply new migrations, or revert existing ones.

The following steps show how we can use database migration during development:

  1. Tim creates a new migration (e.g. create a new table)
  2. Tim commits the new migration into source control system (e.g. SVN, GIT)
  3. Doug updates from source control system and receives the new migration
  4. Doug applies the migration to his local development database

Yii supports database migration via the yiic migrate command line tool. This tool supports creating new migrations, applying/reverting/redoing migrations, and showing migration history and new migrations.

In the following, we will describe how to use this tool.

Note: It's better to use application-specific yiic (e.g. cd path/to/protected) when working with migrate command instead of one from framework directory. Make sure you have protected\migrations directory and it is writable. Also check if you've configured database connection in your protected/config/console.php.

1. Creating Migrations

To create a new migration (e.g. create a news table), we run the following command:

yiic migrate create <name>

The required name parameter specifies a very brief description of the migration (e.g. create_news_table). As we will show in the following, the name parameter is used as part of a PHP class name. Therefore, it should only contain letters, digits and/or underscore characters.

yiic migrate create create_news_table

The above command will create under the protected/migrations directory a new file named m101129_185401_create_news_table.php which contains the following initial code:

class m101129_185401_create_news_table extends CDbMigration
{
    public function up()
    {
    }
 
    public function down()
    {
        echo "m101129_185401_create_news_table does not support migration down.\n";
        return false;
    }
 
    /*
    // implement safeUp/safeDown instead if transaction is needed
    public function safeUp()
    {
    }
 
    public function safeDown()
    {
    }
    */
}

Notice that the class name is the same as the file name which is of the pattern m<timestamp>_<name>, where <timestamp> refers to the UTC timestamp (in the format of yymmdd_hhmmss) when the migration is created, and <name> is taken from the command's name parameter.

The up() method should contain the code implementing the actual database migration, while the down() method may contain the code reverting what is done in up().

Sometimes, it is impossible to implement down(). For example, if we delete table rows in up(), we will not be able to recover them in down(). In this case, the migration is called irreversible, meaning we cannot roll back to a previous state of the database. In the above generated code, the down() method returns false to indicate that the migration cannot be reverted.

Info: Starting from version 1.1.7, if the up() or down() method returns false, all the following migrations will be canceled. Previously in version 1.1.6, one has to throw exceptions to cancel the following migrations.

As an example, let's show the migration about creating a news table.

class m101129_185401_create_news_table extends CDbMigration
{
    public function up()
    {
        $this->createTable('tbl_news', array(
            'id' => 'pk',
            'title' => 'string NOT NULL',
            'content' => 'text',
        ));
    }
 
    public function down()
    {
        $this->dropTable('tbl_news');
    }
}

The base class CDbMigration provides a set of methods for manipulating data and schema of a database. For example, CDbMigration::createTable will create a database table, while CDbMigration::insert will insert a row of data. These methods all use the database connection returned by CDbMigration::getDbConnection(), which by default returns Yii::app()->db.

Info: You may notice that the database methods provided by CDbMigration are very similar to those in CDbCommand. Indeed they are nearly the same except that CDbMigration methods will measure the time used by their methods and print some messages about the method parameters.

2. Transactional Migrations

Info: The feature of transactional migrations has been supported since version 1.1.7.

While performing complex DB migrations, we usually want to make sure that each migration succeed or fail as a whole so that the database maintains the consistency and integrity. In order to achieve this goal, we can exploit DB transactions.

We could explicitly start a DB transaction and enclose the rest of the DB-related code within the transaction, like the following:

class m101129_185401_create_news_table extends CDbMigration
{
    public function up()
    {
        $transaction=$this->getDbConnection()->beginTransaction();
        try
        {
            $this->createTable('tbl_news', array(
                'id' => 'pk',
                'title' => 'string NOT NULL',
                'content' => 'text',
            ));
            $transaction->commit();
        }
        catch(Exception $e)
        {
            echo "Exception: ".$e->getMessage()."\n";
            $transaction->rollback();
            return false;
        }
    }
 
    // ...similar code for down()
}

However, an easier way to get transaction support is to implement the safeUp() method instead of up(), and safeDown() instead of down(). For example,

class m101129_185401_create_news_table extends CDbMigration
{
    public function safeUp()
    {
        $this->createTable('tbl_news', array(
            'id' => 'pk',
            'title' => 'string NOT NULL',
            'content' => 'text',
        ));
    }
 
    public function safeDown()
    {
        $this->dropTable('tbl_news');
    }
}

When Yii performs the migration, it will start a DB transaction and then call safeUp() or safeDown(). If any DB error occurs in safeUp() or safeDown(), the transaction will be rolled back, thus ensuring the database remain in a good shape.

Note: Not all DBMS support transactions. And some DB queries cannot be put into a transaction. In this case, you will have to implement up() and down(), instead. And for MySQL, some SQL statements may cause implicit commit.

3. Applying Migrations

To apply all available new migrations (i.e., make the local database up-to-date), run the following command:

yiic migrate

The command will show the list of all new migrations. If you confirm to apply the migrations, it will run the up() method in every new migration class, one after another, in the order of the timestamp value in the class name.

After applying a migration, the migration tool will keep a record in a database table named tbl_migration. This allows the tool to identify which migrations have been applied and which are not. If the tbl_migration table does not exist, the tool will automatically create it in the database specified by the db application component.

Sometimes, we may only want to apply one or a few new migrations. We can use the following command:

yiic migrate up 3

This command will apply the 3 new migrations. Changing the value 3 will allow us to change the number of migrations to be applied.

We can also migrate the database to a specific version with the following command:

yiic migrate to 101129_185401

That is, we use the timestamp part of a migration name to specify the version that we want to migrate the database to. If there are multiple migrations between the last applied migration and the specified migration, all these migrations will be applied. If the specified migration has been applied before, then all migrations applied after it will be reverted (to be described in the next section).

4. Reverting Migrations

To revert the last one or several applied migrations, we can use the following command:

yiic migrate down [step]

where the optional step parameter specifies how many migrations to be reverted back. It defaults to 1, meaning reverting back the last applied migration.

As we described before, not all migrations can be reverted. Trying to revert such migrations will throw an exception and stop the whole reverting process.

5. Redoing Migrations

Redoing migrations means first reverting and then applying the specified migrations. This can be done with the following command:

yiic migrate redo [step]

where the optional step parameter specifies how many migrations to be redone. It defaults to 1, meaning redoing the last migration.

6. Showing Migration Information

Besides applying and reverting migrations, the migration tool can also display the migration history and the new migrations to be applied.

yiic migrate history [limit]
yiic migrate new [limit]

where the optional parameter limit specifies the number of migrations to be displayed. If limit is not specified, all available migrations will be displayed.

The first command shows the migrations that have been applied, while the second command shows the migrations that have not been applied.

7. Modifying Migration History

Sometimes, we may want to modify the migration history to a specific migration version without actually applying or reverting the relevant migrations. This often happens when developing a new migration. We can use the following command to achieve this goal.

yiic migrate mark 101129_185401

This command is very similar to yiic migrate to command, except that it only modifies the migration history table to the specified version without applying or reverting the migrations.

8. Customizing Migration Command

There are several ways to customize the migration command.

Use Command Line Options

The migration command comes with four options that can be specified in command line:

  • interactive: boolean, specifies whether to perform migrations in an interactive mode. Defaults to true, meaning the user will be prompted when performing a specific migration. You may set this to false should the migrations be done in a background process.

  • migrationPath: string, specifies the directory storing all migration class files. This must be specified in terms of a path alias, and the corresponding directory must exist. If not specified, it will use the migrations sub-directory under the application base path.

  • migrationTable: string, specifies the name of the database table for storing migration history information. It defaults to tbl_migration. The table structure is version varchar(255) primary key, apply_time integer.

  • connectionID: string, specifies the ID of the database application component. Defaults to 'db'.

  • templateFile: string, specifies the path of the file to be served as the code template for generating the migration classes. This must be specified in terms of a path alias (e.g. application.migrations.template). If not set, an internal template will be used. Inside the template, the token {ClassName} will be replaced with the actual migration class name.

To specify these options, execute the migrate command using the following format

yiic migrate up --option1=value1 --option2=value2 ...

For example, if we want to migrate for a forum module whose migration files are located within the module's migrations directory, we can use the following command:

yiic migrate up --migrationPath=ext.forum.migrations

Note that in case you're setting boolean options such as interactive using command line you should pass either 1 or 0 like the following:

yiic migrate --interactive=0

Configure Command Globally

While command line options allow us to configure the migration command on-the-fly, sometimes we may want to configure the command once for all. For example, we may want to use a different table to store the migration history, or we may want to use a customized migration template. We can do so by modifying the console application's configuration file like the following,

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

Now if we run the migrate command, the above configurations will take effect without requiring us to enter the command line options every time.

$Id$

Total 12 comments

#16574 report it
realtebo at 2014/03/07 03:21am
How to use alternative CDbConnection

Referring to comment 14039

It's very easy to change db connection. you can use command line option to change it

or even simpler do this:

$this->dbConnection = Yii::app()->other_db;

Be aware to use it for both up and down where other_db must bedefined in config/console.php

'components'=>array( 
    ....
    "other_db" => array (
        'class' => "CDbConnection",
        'connectionString' => 'mysql:host=localhost;dbname=other_db_name'
        'username' => 'the_username',
        'password' => 'the_password',
        'charset' => 'utf8',
        'tablePrefix' => 'tab_',
    ), 
    ....
), // end of components

There is also another trick: you can specify in every CActiveRecord derivated model this function:

/**
* @return CDbConnection the database connection used for this class
*/
public function getDbConnection()
{
    return Yii::app()->other_db;
}
#14579 report it
whatif at 2013/08/24 06:21am
Important things to note
  1. One thing to note is that while running yiic migrate command, you need to be in the application/protected folder.

  2. The other thing to remember is that you need to edit the db configurations in the protected/main/console.php file otherwise you are going to bump into the following error exception 'CDbException' with message 'CDbConnection failed to open the DB connection: could not find driver' in /path/to/your/yii/lib/framework/db/CDbConnection.php:287

  3. You need to check if the Yii framework path is correct in application/protected/yiic.php. In my case, it was $yiic=dirname(FILE).'/../../framework-1.1.14/yiic.php';

#14039 report it
InsaneWookie at 2013/07/12 09:20pm
Don't use models in migrations

Important note: Do not use Active Record models in a migration as they always connect to the default database. It is also possible the models will get out of sync with the migrations. Instead use the provided database functions to perform modifications.

See: Using active records in migrations

#12188 report it
Jmper at 2013/03/05 12:53am
Transactions CAN have DDL if you use proper DB server

@Evgen:

I understand you think MySQL = SQL. Many people do so. But it's not true.

In fact, MySQL was not designed to support transactions at all at the beginning. Transactions have been supported since version 4 so no wonder MySQL is quite limited in it.

Other DBMS, designed to be transactional from the scratch, fully support transactions, including DDL statements.

For example, I'm using Postgres and have no problems in transactional migrations containing extensive DDL. Transactions are really handy here - if anything goes wrong, you don't have to worry about your db becoming inconsistent.

#6486 report it
Evgen at 2012/01/13 03:38am
Transactions and DDL

Example with transaction is not correct. DDL constructions (create, alter tables) can not be rolled back http://dev.mysql.com/doc/refman/5.0/en/cannot-roll-back.html and when a DDL statement is issued MySQL will commit the pending transaction.

#4737 report it
aztech at 2011/08/10 05:08am
Changing column to be primary key

To create primary key, when table is already existing and don't have already PK defined, call following statemet:

$this->alterColumn('{{table_name}}','column_name','type PRIMARY KEY');

where "type" is a column type, e.g. string, int, etc.

#4724 report it
CeBe at 2011/08/08 12:52pm
most execute commands are available on the migration object itself

@thyseus:

public function up() {
    $this->execute('alter table example add column email varchar(255);');
}

is a much nicer way ;-) Your approach is neccessary for select commands or more complicated things that are not wrapped in CDbMigration.

And you should use $this->getDbConnection instead of Yii::app()->db since the migration connectionId to use is configurable in MigrateCommand and hence can be different from db.

#3879 report it
MadSkillsTisdale at 2011/05/17 12:03pm
Generic Column Types

Here are the generic column types you can use to keep migrations DB independent, and what they mean (as of Yii 1.1.7)

  • pk: an auto-incremental primary key type, will be converted into "int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY"
  • string: string type, will be converted into "varchar(255)"
  • text: a long string type, will be converted into "text"
  • integer: integer type, will be converted into "int(11)"
  • boolean: boolean type, will be converted into "tinyint(1)"
  • float: float number type, will be converted into "float"
  • decimal: decimal number type, will be converted into "decimal"
  • datetime: datetime type, will be converted into "datetime"
  • timestamp: timestamp type, will be converted into "timestamp"
  • time: time type, will be converted into "time"
  • date: date type, will be converted into "date"
  • binary: binary data type, will be converted into "blob"

The implementation uses CDbSchema->getColumnType(), and you can find more information here:

http://www.yiiframework.com/doc/api/1.1/CDbSchema#getColumnType-detail

#3870 report it
thyseus at 2011/05/16 07:54am
Using plain SQL in migrations

To use plan SQL in migrations, you can do something like this:

public function up()
    {
    Yii::app()->db->createCommand('alter table example add column email varchar(255);')->execute();
}

please note that, if using plain SQL queries, the migration will (most probably) only work on the same DBMS system. It is better to use the methos of CDbCommand to stay dbms-independent.

#2789 report it
Dana at 2011/02/10 12:18pm
Interactive Mode

It should be noted that for the interactive option to properly recognize a false setting, it requires int 0, not 'false'

so --interactive=0 rather than --interactive=false

#2589 report it
Kevin Guto at 2011/01/20 04:10am
Few things to note
  1. One thing to note is that while running yiic migrate create command, you need to be in the application/protected folder.
  2. The other thing to remember is for yiic migrate, yicc migrate up **or **yiic migrate down to work then you need to edit the db configurations in the protected/main/console.php file otherwise you are going to bump into the following error exception 'CDbException' with message 'CDbConnection failed to open the DB connection: could not find driver' in /path/to/your/yii/lib/framework/db/CDbConnection.php:287
#2550 report it
Leric at 2011/01/17 03:19am
Great feature

We don't need to envy rails any more, now we can put db schema under version control, but for old project, that already have a big schema picture, writing the migration script can be pretty boring. I wrote a command that can take care of most repeatable jobs:

class DumpSchemaCommand extends CConsoleCommand
{
 
    public function run($args) {
        $schema = $args[0];
        $tables = Yii::app()->db->schema->getTables($schema);
        $result = '';
        foreach ($tables as $def) {
            $result .= '$this->createTable("' . $def->name . '", array(' . "\n";
            foreach ($def->columns as $col) {
                $result .= '    "' . $col->name . '"=>"' . $this->getColType($col) . '",' . "\n";
            }
            $result .= '), "");' . "\n\n";
        }
        echo $result;
    }
 
    public function getColType($col) {
        if ($col->isPrimaryKey) {
            return "pk";
        }
        $result = $col->dbType;
        if (!$col->allowNull) {
            $result .= ' NOT NULL';
        }
        if ($col->defaultValue != null) {
            $result .= " DEFAULT '{$col->defaultValue}'";
        }
        return $result;
    }
}

Remember it's just a 10 minutes scrawl, not to do everything, you still need to add index defination, table options, constraints by hand.

Leave a comment

Please to leave your comment.