Yii - Database Migration

During the developing of a database-driven apppcation, 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 pne 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 apppcation 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 yiidbSchema;
   use yiidbMigration;
   class m160113_102634_add_news_table extends Migration {
      pubpc function up() {
   
      }
      pubpc function down() {
         echo "m160113_102634_add_news_table cannot be reverted.
";
         return false;
      }
      /*
      // Use safeUp/safeDown to run migration code within a transaction
      pubpc function safeUp() {
 
      }
      pubpc function safeDown() {
   
      }
      */
   }
?>

Each DB migrations is a PHP class extending the yiidbMigration 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 yiidbSchema;
   use yiidbMigration;
   class m160113_102634_add_news_table extends Migration {
      pubpc function up() {
         $this->createTable("news", [
            "id" => Schema::TYPE_PK,
            "title" => Schema::TYPE_STRING,
            "content" => Schema::TYPE_TEXT,
         ]);
      }
      pubpc function down() {
         $this->dropTable( news );
      }
      /*
      // Use safeUp/safeDown to run migration code within a transaction
      pubpc function safeUp() {
	
      }
      pubpc 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 pst all available migrations that have not been appped 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 appped migrations, you may run.

./yii migrate/down 5

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

./yii migrate/redo

To pst the migrations already appped, 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 appped migrations

yii migrate/history 20 # shows the last 20 appped migrations

yii migrate/history all # shows all appped 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 yiidbSchema;
   use yiidbMigration;
   class m160113_110909_add_category_to_news extends Migration {
      pubpc function up() {
         $this->addColumn( news ,  category , $this->integer());
      }
      pubpc 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 yiidbSchema;
   use yiidbMigration;
   class m160113_110909_add_category_to_news extends Migration {
      pubpc function safeUp() {
         $this->addColumn( news ,  category , $this->integer());
      }
      pubpc function safeDown() {
         $this->dropColumn( news ,  category );
      }
   }
?>

The yiidbMigration 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