13.3. Creating Migrations

A migration is a change set upgrading or downgrading the schema to its next or previous state, respectively. You generate a new empty migration with the help of the following commands:

cd APP_DIR
./vendor/bin/doctrine-module migrations:generate

The commands above make the application directory the current working directory and then run the migrations:generate console command.

DoctrineModule and DoctrineORMModule provide several console commands that you can use for various database maintenance tasks (like generating or executing migrations). For the list of available commands, you can use the list command:

./vendor/bin/doctrine-module list

Once you run the migrations:generate command, you will be able to find the newly created migration under the APP_DIR/data/Migrations directory. The file has a name like VersionYYYYMMDDHHIISS.php, where YYYY is current year, MM is current month, DD is current day, HH, II and SS represent current hour, minute and second, respectively.

If you look into the newly created file, you will find the following content:

<?php

namespace Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20160901114333 extends AbstractMigration
{
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs

    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs

    }
}

If you do not see the newly created migration in NetBeans IDE, you need to open the menu Source and select the Scan for external changes menu item.

As you can see from the code above, a migration is a usual PHP class inherited from Doctrine\DBAL\Migrations\AbstractMigration base class. Every migration should have at least two methods: up() and down(). The up() method upgrades the schema to a newer state, the down() method downgrades the schema from its newer state to the previous state. Both up() and down() methods have a single argument of type Doctrine\DBAL\Schema\Schema, which can be used for actual database schema modifications.

The Schema class is a part of Doctrine\DBAL component. For more information about the methods it provides, please refer to Doctrine DBAL documentation. Another, even better way is to look at the code inside your vendor/doctrine/dbal directory.

A migration class may optionally have the following (overridden) methods (table 13.1):

The AbstractMigration base class also provides the following useful methods (table 13.2):

As you can see from table 13.2, you can also modify the schema by calling addSql() method. This method can be used to create a table, to update a table or to remove a table. It can also be used, for example, to insert some data to a table (however, inserting data is not a schema change).

Doctrine migrations are designed for schema changes, not for inserting data to the database. Although, inserting some initial data to database is useful in some cases.

Now that you know how to create a migration, let's create a couple of migrations for our Blog sample.

13.3.1. Creating the Initial Migration

The first migration we will create is the initial migration. This migration will be applied to empty database schema and will create four tables: post, comment, tag and post_tag.

Modify the migration class we have created in the previous section to look like below:

<?php

namespace Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

/**
 * A migration class. It either upgrades the databases schema (moves it to new state)
 * or downgrades it to the previous state.
 */
class Version20160901114333 extends AbstractMigration
{
    /**
     * Returns the description of this migration.
     */
    public function getDescription()
    {
        $description = 'This is the initial migration which creates blog tables.';
        return $description;
    }
    
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        // Create 'post' table
        $table = $schema->createTable('post');
        $table->addColumn('id', 'integer', ['autoincrement'=>true]);        
        $table->addColumn('title', 'text', ['notnull'=>true]);
        $table->addColumn('content', 'text', ['notnull'=>true]);
        $table->addColumn('status', 'integer', ['notnull'=>true]);
        $table->addColumn('date_created', 'datetime', ['notnull'=>true]);
        $table->setPrimaryKey(['id']);
        $table->addOption('engine' , 'InnoDB');
        
        // Create 'comment' table
        $table = $schema->createTable('comment');
        $table->addColumn('id', 'integer', ['autoincrement'=>true]); 
        $table->addColumn('post_id', 'integer', ['notnull'=>true]);
        $table->addColumn('content', 'text', ['notnull'=>true]);
        $table->addColumn('author', 'string', ['notnull'=>true, 'lenght'=>128]);
        $table->addColumn('date_created', 'datetime', ['notnull'=>true]);
        $table->setPrimaryKey(['id']);
        $table->addOption('engine' , 'InnoDB');
        
        // Create 'tag' table
        $table = $schema->createTable('tag');
        $table->addColumn('id', 'integer', ['autoincrement'=>true]); 
        $table->addColumn('name', 'string', ['notnull'=>true, 'lenght'=>128]);
        $table->setPrimaryKey(['id']);
        $table->addOption('engine' , 'InnoDB');
        
        // Create 'post_tag' table
        $table = $schema->createTable('post_tag');
        $table->addColumn('id', 'integer', ['autoincrement'=>true]); 
        $table->addColumn('post_id', 'integer', ['notnull'=>true]);
        $table->addColumn('tag_id', 'integer', ['notnull'=>true]);
        $table->setPrimaryKey(['id']);
        $table->addOption('engine' , 'InnoDB');       
    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        $schema->dropTable('post_tag');
        $schema->dropTable('tag');
        $schema->dropTable('comment');
        $schema->dropTable('post');
    }
}

In the code above we have three methods:

• the getDescription() method provides the description of the migration.
• the up() method upgrades the schema to its newer state by adding new tables.
• the down() method drops tables thus downgrading the schema to its previous state.

13.3.2. Adding Another Migration

Now assume we decided to improve the performance of our database by adding indexes to our tables.

If you want to learn about database indexes in more details and why indexes are so helpful, you can refer to an excellent tutorial called Use the Index, Luke.

We can also improve data integrity by adding foreign keys. To do this, we have to add another migration. Generate another empty migration with the migrations:generate console command. Modify the code to look like below:

<?php

namespace Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

/**
 * A migration class. It either upgrades the databases schema (moves it to new state)
 * or downgrades it to the previous state.
 */
class Version20160901114938 extends AbstractMigration
{
    /**
     * Returns the description of this migration.
     */
    public function getDescription()
    {
        $description = 'This migration adds indexes and foreign key constraints.';
        return $description;
    }
    
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        // Add index to post table
        $table = $schema->getTable('post');
        $table->addIndex(['date_created'], 'date_created_index');
        
        // Add index and foreign key to comment table
        $table = $schema->getTable('comment');
        $table->addIndex(['post_id'], 'post_id_index');
        $table->addForeignKeyConstraint('post', ['post_id'], ['id'], [], 'comment_post_id_fk');
        
        // Add indexes and foreign keys to post_tag table
        $table = $schema->getTable('post_tag');
        $table->addIndex(['post_id'], 'post_id_index');
        $table->addIndex(['tag_id'], 'tag_id_index');
        $table->addForeignKeyConstraint('post', ['post_id'], ['id'], [], 'post_tag_post_id_fk');
        $table->addForeignKeyConstraint('tag', ['tag_id'], ['id'], [], 'post_tag_tag_id_fk');
    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        $table = $schema->getTable('post_tag');
        $table->removeForeignKey('post_tag_post_id_fk');
        $table->removeForeignKey('post_tag_tag_id_fk');
        $table->dropIndex('post_id_index');
        $table->dropIndex('tag_id_index'); 
        
        $table = $schema->getTable('comment');
        $table->dropIndex('post_id_index'); 
        $table->removeForeignKey('comment_post_id_fk');
        
        $table = $schema->getTable('post');
        $table->dropIndex('date_created_index');       
    }
}

You can find the migrations we have just created inside of the Blog sample bundled with this book.