Yii - Database Migration

---

---

During the developing of a database-driven application, the database structure evolves with the source code. Yii provides the database migration feature that allows you to keep track of database changes.

Yii provides the following migration command line tools −

• Create new migrations
• Revert migrations
• Apply migrations
• Re-apply migrations
• Show migration status and history

Creating a Migration

Let us create a new database migration.

Step 1 − Inside the project root of the basic application template open the console window and run.

./yii migrate/create add_news_table

The above command will create a new migration file (m160113_102634_add_news_table.php in this case) in the migrations folder.

The file contains the following code −

<?php
   use yii\db\Schema;
   use yii\db\Migration;
   class m160113_102634_add_news_table extends Migration {
      public function up() {
   
      }
      public function down() {
         echo "m160113_102634_add_news_table cannot be reverted.\n";
         return false;
      }
      /*
      // Use safeUp/safeDown to run migration code within a transaction
      public function safeUp() {
 
      }
      public function safeDown() {
   
      }
      */
   }
?>

Each DB migrations is a PHP class extending the yii\db\Migration class. The class name is generated in the following format −

m<YYMMDD_HHMMSS>_<Name>

where <YYMMDD_HMMSS> is the UTC datetime at which the migration command was executed and <Name> is the argument you provided in the console command.

The up() method is invoked when you upgrade your database, while the down() method is called when you downgrade it.

Step 2 − To add a new table to the database, modify the migration file this way.

<?php
   use yii\db\Schema;
   use yii\db\Migration;
   class m160113_102634_add_news_table extends Migration {
      public function up() {
         $this->createTable("news", [
            "id" => Schema::TYPE_PK,
            "title" => Schema::TYPE_STRING,
            "content" => Schema::TYPE_TEXT,
         ]);
      }
      public function down() {
         $this->dropTable('news');
      }
      /*
      // Use safeUp/safeDown to run migration code within a transaction
      public function safeUp() {
	
      }
      public function safeDown() {

      }
      */
   }
?>

In the above code we created a new table called news in the up() method and dropped this table in the down() method.

The news table consists of three fields: id, title, and content. When creating a table or a column we should use abstract types so that migrations are independent of a database type. For example, in the case of MySQL, TYPE_PK will be converted into int(11) NOT NUL AUTO_INCREMETN PRIMARY KEY.

Step 3 − To upgrade a database, run this command.

./yii migrate

The above command will list all available migrations that have not been applied yet. Then, if you confirm to apply migrations, it will run safeUp() or up() in all new migration classes.

Step 4 − To apply only three available migrations, you may run.

./yii migrate 3

Step 5 − You can also define a particular migration the database should be migrated to.

# using timestamp to specify the migration

yii migrate/to 160202_195501

# using a string that can be parsed by strtotime()

yii migrate/to "2016-01-01 19:55:01"

# using full name

yii migrate/to m160202_195501_create_news_table

# using UNIX timestamp

yii migrate/to 1393964718

Step 6 − To revert a migration(execute down() or safeDown() methods), run.

./yii migrate/down

Step 7 − To revert the most five recently applied migrations, you may run.

./yii migrate/down 5

Step 8 − To redo(revert and then apply again) migrations, run.

./yii migrate/redo

To list the migrations already applied, use these commands −

• yii migrate/new # shows the first 10 new migrations

• yii migrate/new 3 # shows the first 3 new migrations

• yii migrate/new all # shows all new migrations

• yii migrate/history # shows the last 10 applied migrations

• yii migrate/history 20 # shows the last 20 applied migrations

• yii migrate/history all # shows all applied migrations

Sometimes you need to add or drop a column from a specific table. You can use addColumn() and dropColumn() methods.

Step 1 − Create a new migration.

./yii migrate/create add_category_to_news

Step 2 − Modify the newly created migration file this way.

<?php
   use yii\db\Schema;
   use yii\db\Migration;
   class m160113_110909_add_category_to_news extends Migration {
      public function up() {
         $this->addColumn('news', 'category', $this->integer());
      }
      public function down() {
         $this->dropColumn('news', 'category');
      }
   }
?>

Now, if you run ./yii migrate, the category column should be added to the news table. On the contrary, if you run ./yii migrate/down 1, the category column should be dropped.

When performing DB migrations, it is important to ensure each migration has succeded or failed. It is recommended to enclose DB operations in a transaction. To implement transactional migrations, you should just put the migration code in the safeUp() and safeDown() methods. If any operation in these methods fails, all previous operations will be rolled back.

The previous example in the “transactional way” will be −

<?php
   use yii\db\Schema;
   use yii\db\Migration;
   class m160113_110909_add_category_to_news extends Migration {
      public function safeUp() {
         $this->addColumn('news', 'category', $this->integer());
      }
      public function safeDown() {
         $this->dropColumn('news', 'category');
      }
   }
?>

The yii\db\Migration class provides the following methods for manipulating databases −

• execute() − Executes a raw SQL statement

• createTable() − Creates a table

• renameTable() − Renames a table

• insert() − Inserts a single row

• batchInsert() − Inserts multiple rows

• update() − Updates rows

• delete() − Deletes rows

• addColumn() − Adds a column

• renameColumn() − Renames a column

• dropColumn() − Removes a column

• alterColumn() − Alters a column

• dropTable() − Removes a table

• truncateTable() − Removes all rows in a table

• createIndex() − Creates an index

• dropIndex() − Removes an index

• addPrimaryKey() − Adds a primary key

• dropPrimaryKey() − Removes a primary key

• addForeignKey() − Adds a foreign key

• dropForeignKey() − Removes a foreign key