Migrations

A migration is a class that describes one change to the database structure and the way to undo it. Migrations are stored together with the code, so every developer's database and the server's are brought to the same state with one command.

Creating a migration

php yii migrate/create create_post_table

The command asks for confirmation and creates a file in the migrations folder. Its name contains the date and time, which set the execution order. From the words create_..._table Yii prepares a table stub by itself. Add the columns to it:

// migrations/m260302_101500_create_post_table.php
use yii\db\Migration;
 
class m260302_101500_create_post_table extends Migration
{
    public function safeUp()
    {
        $this->createTable('post', [
            'id' => $this->primaryKey(),
            'user_id' => $this->integer()->notNull(),
            'title' => $this->string(255)->notNull(),
            'body' => $this->text()->notNull(),
            'status' => $this->smallInteger()->notNull()->defaultValue(0),
            'created_at' => $this->integer()->notNull(),
            'updated_at' => $this->integer()->notNull(),
        ]);
    }
 
    public function safeDown()
    {
        $this->dropTable('post');
    }
}

safeUp() makes the change, safeDown() undoes it. The created_at and updated_at columns will store time as a number of seconds, which is the convention in Yii.

Running

php yii migrate

The command shows the list of new migrations, asks for confirmation and creates the post table. Applied migrations are remembered in the migration table, so a second run does nothing.

Column types

Modifiers are added to the type: notNull() forbids an empty value, defaultValue(0) sets a default, unique() forbids duplicates.

Rolling back

php yii migrate/down
php yii migrate/down 3
php yii migrate/history

The first command undoes the last migration, the second the last three, the third shows the list of applied ones.

Changing a table

To add a column, create a new migration:

php yii migrate/create add_slug_to_post

public function safeUp()
{
    $this->addColumn('post', 'slug', $this->string(255)->notNull()->defaultValue(''));
    $this->createIndex('idx-post-slug', 'post', 'slug');
}
 
public function safeDown()
{
    $this->dropIndex('idx-post-slug', 'post');
    $this->dropColumn('post', 'slug');
}

After php yii migrate the table gets a slug column with an index, which speeds up searching by this column.

A foreign key

A foreign key prevents saving a post with a non-existent author:

$this->addForeignKey('fk-post-user_id', 'post', 'user_id', 'user', 'id', 'CASCADE');

The arguments: the key name, the table and its column, the related table and its column, the action on delete. CASCADE means that a user's posts are deleted together with the user.

Common mistake

Editing a migration that has already run is useless: Yii considers it applied and will not run it again, and the databases of your teammates and the server stay as they were. Create a new migration for every change.