Mixed DDL and DML SQL Commands in Doctrine Migrations

Doctrine is a useful library when working on a heavy DB dependent application in PHP/HackLang. It provides migrations as a tool to ease the process of applying changes to the database.

Each migration version is a class (often extends an abstract migration class provided by Doctrine), which specifies the steps required to migrate the DB from previous version to this one (up method), and what should be done to revert the changes (down method). Hook methods preUp/preDown and postUp/postDown can be defined in the version class and are called before and after the respected methods.

The name of the class (and so the name of the file) should start with Version, and the rest would be the version. If doctrine command line tool is used to generate the migration file, it would be named in YYYYMMDDHHmmSS format, but there are no requirements and it can be named anything. So a human readable description of the version can be used for the name as well, or a mix of both.

<?php // file: Version20151015114023_CreateGatewayTable.php

namespace DoctrineMigrations;

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

class Version20151015114023_CreateGatewayTable extends AbstractMigration
{
    const TABLE_NAME = 'gateway';

    public function up(Schema $schema)
    {
        $table = $schema->createTable(self::TABLE_NAME);
        $table->addOption("charset", "utf8");
        $table->addOption("collate", "utf8_bin");
        $table->addColumn(
            'id',
            Type::INTEGER,
            [
                'unsigned' => true,
                'notnull' => true,
                'autoincrement' => true
            ]
        );
        $table->setPrimaryKey(['id']);
        $table->addColumn('name', Type::STRING, ['length' => 64]);
        $table->addColumn('region', Type::STRING, ['length' => 2]);
        $table->addUniqueIndex(['name', 'region']);
    }

    public function down(Schema $schema)
    {
        $schema->dropTable(self::TABLE_NAME);
    }
}

The migration tool passes an instance of Doctrine\DBAL\Schema\Schema to both up and down methods of the version class, so these methods can modify the schema as required.

The migration tool calls the up/down methods and compares the (possibly updated) schema with the schema before running the migration, creates a list of SQL commands to migrate from the older schema to the new one, and then runs those SQL commands.

The AbstractMigration class provides addSql method which accepts raw SQL. However these SQL commands are not executed right away when addSql is called, but stored in a queue (an array) to be executed later. This method is specially useful when there is no (easy) way to modify the database using the schema instance, or a database specific command is required, or the migration requires to modify the data and not just the schema itself.

What if the migration requires to change the schema, and migrate the data afterwards? Although it seems that post action hook methods could be where we would migrate the data, current version of Doctrine migrations (1.1), does not run SQL commands registered in post migration methods automatically. This means that calling addSql in post hooks would have no effect.

There is a catch in using up and down methods themselves to migrate the data. The migration tool creates the list of SQL commands required to migrate the schema, and appends them to the SQL commands queue, after the up and down methods are called.

This means that all the calls to addSql will be in the SQL commands queue before the ones that change the schema. So those SQL commands can not rely on the new schema, since they will execute while the database is still in previous version.

One solution is not using the Schema instance at all and just use addSql first to change the schema, and then to migrate the data.

Another solution is to use the connection property of the Version class in postUp/postDown methods. Each migration version class is provided by an instance of Doctrine\DBAL\Connection which can be used to execute any query directly.

<?php // file: Version20151015114023_CreateGatewayTable.php

namespace DoctrineMigrations;

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

class Version20151015114023_CreateGatewayTable extends AbstractMigration
{
    const TABLE_NAME = 'gateway';

    public function up(Schema $schema)
    {
        $table = $schema->createTable(self::TABLE_NAME);
        $table->addOption("charset", "utf8");
        $table->addOption("collate", "utf8_bin");
        $table->addColumn(
            'id',
            Type::INTEGER,
            [
                'unsigned' => true,
                'notnull' => true,
                'autoincrement' => true
            ]
        );
        $table->setPrimaryKey(['id']);
        $table->addColumn('name', Type::STRING, ['length' => 64]);
        $table->addColumn('region', Type::STRING, ['length' => 2]);
        $table->addUniqueIndex(['name', 'region']);
    }

    public function down(Schema $schema)
    {
        $schema->dropTable(self::TABLE_NAME);
    }

    public function postUp(Schema $schema)
    {
        $this->connection->insert(
            self::TABLE_NAME,
            ['name' => 'first', 'region' => 'MY']
        );
    }
}