Update: Check out our new Yii2 Developer Exchange site at http://yii2x.com. It’s devoted exclusively for Yii2 developers to make it easier to find news, tips, tutorials and extensions related to Yii2.

Database Migrations

Yii’s Database Migrations allow for programmatic creation and incremental changes to your application’s database schema.

I wrote a bit of extra code to support Yii’s table prefixes in my migrations – this is shown below.

To create your migration file, use yiic migrate:

cd ~/sites/yourapp
./app/protected/yiic migrate create create_folder_table

Look at the before() function to see how to set a prefix for my tables. You can also look at createIndex and addForeignKey to see how I create and drop indices and foreign keys.

class m131222_185835_create_folder_table extends CDbMigration
{
     protected $MySqlOptions = 'ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci';
     public $tablePrefix;
     public $tableName;

     public function before() {
       $this->tablePrefix = Yii::app()->getDb()->tablePrefix;
       if ($this->tablePrefix <> '')
         $this->tableName = $this->tablePrefix.'folder';
     }

   	public function safeUp()
   	{
   	  $this->before();
    $this->createTable($this->tableName, array(
               'id' => 'pk',
               'account_id' => 'integer default 0',
               'user_id' => 'integer default 0',               
               'name' => 'string NOT NULL' ,              
               'train' => 'TINYINT default 0',
               'digest' => 'TINYINT default 0',
               'created_at' => 'DATETIME NOT NULL DEFAULT 0',
               'modified_at' => 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',

                 ), $this->MySqlOptions);
               $this->createIndex('folder_user_id', $this->tableName , 'user_id', true);
                 $this->addForeignKey('fk_folder_user', $this->tableName, 'user_id', $this->tablePrefix.'users', 'id', 'CASCADE', 'CASCADE');

   	}

   	public function safeDown()
   	{
   	  $this->before();
   	  $this->dropForeignKey('fk_folder_account', $this->tableName);
          $this->dropIndex('user_setting_user', $this->tableName);
   	  $this->dropTable($this->tableName);
   	}
}

When your ready to add the table, use the migrate up command:

./app/protected/yiic migrate up

Here’s an example of a migration adding columns to an existing table. This allows you to programmatically extend database tables as your application matures:

class m140110_222719_add_to_user_settings extends CDbMigration
{
	protected $MySqlOptions = 'ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci';
    public $tablePrefix;
    public $tableName;

    public function before() {
      $this->tablePrefix = Yii::app()->getDb()->tablePrefix;
      if ($this->tablePrefix <> '')
        $this->tableName = $this->tablePrefix.'user_setting';
    }

  	public function safeUp()
  	{
  	  $this->before();   	  
     $this->addColumn($this->tableName,'do_not_disturb','TINYINT DEFAULT 0');
  	}

  	public function safeDown()
  	{
  	  	$this->before();
       $this->dropColumn($this->tableName,'do_not_disturb');        
  	}
}

Altering Indexes and Foreign Keys Manually

Sometimes during migration testing, I find that it’s helpful to delete indexes and foreign keys manually using SQL. If migrations break in the middle during testing, partial indexes will block further testing. Here’s some sample SQL code to do this:

Drop Foreign Key Relationship

alter table fi_sender_status drop foreign key fk_sender_status_sender;

Drop Index

drop index sender_status_sender_id on 'fi_sender_status';

Create Index with MySQL

create index sender_status_sender_id on fi_sender_status (sender_id);

Create Foreign Key Relationship

ALTER TABLE fi_sender_status ADD CONSTRAINT fk_sender_status_sender FOREIGN KEY ('sender_id') REFERENCES 'fi_sender' ('id') ON DELETE CASCADE ON UPDATE CASCADE;

Show Result

SHOW CREATE TABLE fi_sender_status;