Standalone Doctrine Migrations redux

Since, I last wrote about using the Doctrine project’s Migrations tool independently of Doctrine’s ORM, it’s now stable and much easier to get going with.

Installation and configuration

As with all good PHP tools, we simply use Composer:

$ composer require --dev doctrine/migrations

This will install the tool and a script is placed into vendor/bin/doctrine-migrations.

To configure, we need to add two files to the root of our project:

migrations.yml:

name: Migrations
migrations_namespace: Migrations
table_name: migrations
migrations_directory: migrations

This file sets up the default configuration for Migrations. The most important two settings are table_name which is the name of the database table holding the migration information and migrations_directory which is where the migration files will be stored. This directory name may be a relative path (relative to the directory where migrations.yml is stored, that is).

migrations-db.php:

<?php
return [
    'driver' => 'pdo_sqlite',
    'path' => __DIR__ . '/db/bookshelf.db',
];

This file is simply the standard DBAL configuration for a database connection. In this case, I’m using SQLite. Again, the path is relative.

Using Migrations

Migrations is a command line tool called doctrine-migrations that has been installed into the vendor/bin/ directory. Running the tool without arguments will give you helpful information:

$ vendor/bin/doctrine-migrations
Doctrine Migrations version v1.4.0

Usage:
  command [options] [arguments]

Options:
  -h, --help            Display this help message
  -q, --quiet           Do not output any message
  -V, --version         Display this application version
      --ansi            Force ANSI output
      --no-ansi         Disable ANSI output
  -n, --no-interaction  Do not ask any interactive question
  -v|vv|vvv, --verbose  Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug

Available commands:
  help                 Displays help for a command
  list                 Lists commands
 migrations
  migrations:execute   Execute a single migration version up or down manually.
  migrations:generate  Generate a blank migration class.
  migrations:latest    Outputs the latest version number
  migrations:migrate   Execute a migration to a specified version or the latest available version.
  migrations:status    View the status of a set of migrations.
  migrations:version   Manually add and delete migration versions from the version table.

The two important command are: migrations:generate and migrations:migrate.

Creating a migration

To generate a new migration, we run:

$ vendor/bin/doctrine-migrations migrations:generate
Loading configuration from file: migrations.yml
Generated new migration class to "/www/dev/bookshelf-api/migrations/Version20160701185240.php"

This will create a new file for us in our migrations directory which we then need to fill in. There are two methods class: up() and down() and the rule is that whatever we do in up() must be reversed in down().

For example to create a new table, we code it like this:

    public function up(Schema $schema)
    {
        $table = $schema->createTable("author");
        $table->addColumn("author_id", "guid");
        $table->addColumn("name", "string", ["length" => 100]);
        $table->addColumn("biography", "text", ["notnull" => false]);
        $table->addColumn("date_of_birth", "date", ["notnull" => false]);

        $table->setPrimaryKey(["author_id"]);
    }

    public function down(Schema $schema)
    {
        $schema->dropTable('author');
    }

If you want to write your schema changes as SQL, then use the $this->addSql() method.

We can also populate the newly created table adding the postUp() method to the class. As its name suggests, the code in the method is execute after the code in up(). In postUp() we access the connection property and then call the appropriate methods. For example:

    public function postUp(Schema $schema)
    {
        $item = [
            'author_id' => '77707f1b-400c-3fe0-b656-c0b14499a71d',
            'name' => 'Suzanne Collins',
            'biography' => null,
            'date_of_birth' => '1962-08-10',
        ],

        $this->connection->insert('author', $item);
    }

Alternatively, we could have used executeQuery() and written it as SQL:

    public function postUp(Schema $schema)
    {
        $sql = "INSERT INTO author (author_id, name, date_of_birth) VALUES
            ('77707f1b-400c-3fe0-b656-c0b14499a71d', 'Suzanne Collins', '1962-08-10')";

        $this->connection->executeQuery($sql);
    }

Running the migrations

To update the database to the latest migration we run the migrations:migrate command. This will run the up() method on all migration files that haven’t already been run. For example:

$ vendor/bin/doctrine-migrations migrations:migrate
Loading configuration from file: migrations.yml

                    Migrations


WARNING! You are about to execute a database migration that could result in schema changes and data lost. Are you sure you wish to continue? (y/n) y
Migrating up to 20160701193352 from 0

  ++ migrating 20160701185240

     -> CREATE TABLE author (author_id CHAR(36) NOT NULL, name VARCHAR(100) NOT NULL, biography CLOB DEFAULT NULL, date_of_birth DATE DEFAULT NULL, PRIMARY KEY(author_id))

  ++ migrated (0.06s)

  ++ migrating 20160701193352

     -> CREATE TABLE book (book_id CHAR(36) NOT NULL, author_id CHAR(36) NOT NULL, title VARCHAR(100) NOT NULL, isbn VARCHAR(13) NOT NULL, PRIMARY KEY(book_id))
     -> CREATE INDEX IDX_CBE5A331F675F31B ON book (author_id)

  ++ migrated (0.01s)

  ------------------------

  ++ finished in 0.07s
  ++ 2 migrations executed
  ++ 3 sql queries

In this case, I have two migration files in my migrations directory and started with an empty database.

That’s it

That’s all there is to it. Migrations is very easy to use as a standalone tool and worth considering for your database migrations.