Managing Migrations

Managing migrations with Doctrine is easy. You can execute migrations from the console and easily revert them. You also have the option to write the SQL for a migration to a file instead of executing it from PHP.

Status

Now that we have a new migration created, run the status command with the --show-versions option to see that the new migration is registered and ready to be executed:

$ ./vendor/bin/doctrine-migrations status --show-versions

 == Configuration

    >> Name:                                               My Project Migrations
    >> Database Driver:                                    pdo_mysql
    >> Database Host:                                      localhost
    >> Database Name:                                      migrations_docs_example
    >> Configuration Source:                               /data/doctrine/migrations-docs-example/migrations.php
    >> Version Table Name:                                 doctrine_migration_versions
    >> Version Column Name:                                version
    >> Migrations Namespace:                               MyProject\Migrations
    >> Migrations Directory:                               /data/doctrine/migrations-docs-example/lib/MyProject/Migrations
    >> Previous Version:                                   Already at first version
    >> Current Version:                                    0
    >> Next Version:                                       2018-06-01 19:30:57 (MyProject\Migrations\Version20180601193057)
    >> Latest Version:                                     2018-06-01 19:30:57 (MyProject\Migrations\Version20180601193057)
    >> Executed Migrations:                                0
    >> Executed Unavailable Migrations:                    0
    >> Available Migrations:                               1
    >> New Migrations:                                     1

 == Available Migration Versions

    >> 2018-06-01 19:30:57 (MyProject\Migrations\Version20180601193057)                not migrated     This is my example migration.

As you can see we have a new migration version available and it is ready to be executed. The problem is, it does not have anything in it so nothing would be executed! Let's add some code to it and add a new table:

1<?php declare(strict_types=1); namespace MyProject\Migrations; use Doctrine\DBAL\Schema\Schema; use Doctrine\Migrations\AbstractMigration; /** * Auto-generated Migration: Please modify to your needs! */ final class Version20180601193057 extends AbstractMigration { public function getDescription(): string { return 'This is my example migration.'; } public function up(Schema $schema): void { $this->addSql('CREATE TABLE example_table (id INT AUTO_INCREMENT NOT NULL, title VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id))'); } public function down(Schema $schema): void { $this->addSql('DROP TABLE example_table'); } }
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

Dry Run

Now we are ready to give it a test! First lets just do a dry-run to make sure it produces the SQL we expect:

$ ./vendor/bin/doctrine-migrations migrate --dry-run

                    My Project Migrations


Executing dry run of migration up to MyProject\Migrations\Version20180601193057 from 0

  ++ migrating MyProject\Migrations\Version20180601193057

     -> CREATE TABLE example_table (id INT AUTO_INCREMENT NOT NULL, title VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id))

  ++ migrated (took 60.9ms, used 8M memory)

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

  ++ finished in 69.4ms
  ++ used 8M memory
  ++ 1 migrations executed
  ++ 1 sql queries

Executing Multiple Migrations

Everything looks good so we can remove the --dry-run option and actually execute the migration.

The migrate command will execute multiple migrations if there are multiple new unexecuted migration versions available. It will attempt to go from the current version to the latest version available.

$ ./vendor/bin/doctrine-migrations migrate

                    My Project Migrations


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

  ++ migrating MyProject\Migrations\Version20180601193057

     -> CREATE TABLE example_table (id INT AUTO_INCREMENT NOT NULL, title VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id))

  ++ migrated (took 47.7ms, used 8M memory)

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

  ++ finished in 49.1ms
  ++ used 8M memory
  ++ 1 migrations executed
  ++ 1 sql queries

Executing Single Migrations

You may want to just execute a single migration up or down. You can do this with the execute command:

$ ./vendor/bin/doctrine-migrations execute MyProject\Migrations\Version20180601193057 --down
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 MyProject\Migrations\Version20180601193057

     -> DROP TABLE example_table

  ++ migrated (took 42.6ms, used 8M memory)

No Interaction

Alternately, if you wish to run the migrations in an unattended mode, we can add the --no-interaction option and then execute the migrations without any extra prompting from Doctrine.

$ ./vendor/bin/doctrine-migrations migrate --no-interaction

                    My Project Migrations


Migrating up to MyProject\Migrations\Version20180601193057 from 0

  ++ migrating MyProject\Migrations\Version20180601193057

     -> CREATE TABLE example_table (id INT AUTO_INCREMENT NOT NULL, title VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id))

  ++ migrated (took 46.5ms, used 8M memory)

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

  ++ finished in 47.3ms
  ++ used 8M memory
  ++ 1 migrations executed
  ++ 1 sql queries

By checking the status again after using either method you will see everything is updated:

$ ./vendor/bin/doctrine-migrations status --show-versions

 == Configuration

    >> Name:                                               My Project Migrations
    >> Database Driver:                                    pdo_mysql
    >> Database Host:                                      localhost
    >> Database Name:                                      migrations_docs_example
    >> Configuration Source:                               /data/doctrine/migrations-docs-example/migrations.php
    >> Version Table Name:                                 doctrine_migration_versions
    >> Version Column Name:                                version
    >> Migrations Namespace:                               MyProject\Migrations
    >> Migrations Directory:                               /data/doctrine/migrations-docs-example/lib/MyProject/Migrations
    >> Previous Version:                                   0
    >> Current Version:                                    2018-06-01 19:30:57 (MyProject\Migrations\Version20180601193057)
    >> Next Version:                                       Already at latest version
    >> Latest Version:                                     2018-06-01 19:30:57 (MyProject\Migrations\Version20180601193057)
    >> Executed Migrations:                                1
    >> Executed Unavailable Migrations:                    0
    >> Available Migrations:                               1
    >> New Migrations:                                     0

 == Available Migration Versions

    >> 2018-06-01 19:30:57 (MyProject\Migrations\Version20180601193057)                migrated (executed at 2018-06-01 17:08:44)     This is my example migration.

Reverting Migrations

The migrate command optionally accepts a version or version alias to migrate to. By default it will try to migrate up from the current version to the latest version. If you pass a version that is older than the current version, it will migrate down. To rollback to the the first version you can use the first version alias:

$ ./vendor/bin/doctrine-migrations migrate first

                    My Project Migrations


WARNING! You are about to execute a database migration that could result in schema changes and data loss. Are you sure you wish to continue? (y/n)y
Migrating down to 0 from MyProject\Migrations\Version20180601193057

  -- reverting MyProject\Migrations\Version20180601193057

     -> DROP TABLE example_table

  -- reverted (took 38.4ms, used 8M memory)

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

  ++ finished in 39.5ms
  ++ used 8M memory
  ++ 1 migrations executed
  ++ 1 sql queries

Now if you run the status command again, you will see that the database is back to the way it was before:

$ ./vendor/bin/doctrine-migrations status --show-versions

 == Configuration

    >> Name:                                               My Project Migrations
    >> Database Driver:                                    pdo_mysql
    >> Database Host:                                      localhost
    >> Database Name:                                      migrations_docs_example
    >> Configuration Source:                               /data/doctrine/migrations-docs-example/migrations.php
    >> Version Table Name:                                 doctrine_migration_versions
    >> Version Column Name:                                version
    >> Migrations Namespace:                               MyProject\Migrations
    >> Migrations Directory:                               /data/doctrine/migrations-docs-example/lib/MyProject/Migrations
    >> Previous Version:                                   Already at first version
    >> Current Version:                                    0
    >> Next Version:                                       2018-06-01 19:30:57 (MyProject\Migrations\Version20180601193057)
    >> Latest Version:                                     2018-06-01 19:30:57 (MyProject\Migrations\Version20180601193057)
    >> Executed Migrations:                                0
    >> Executed Unavailable Migrations:                    0
    >> Available Migrations:                               1
    >> New Migrations:                                     1

 == Available Migration Versions

    >> 2018-06-01 19:30:57 (MyProject\Migrations\Version20180601193057)                not migrated     This is my example migration.

Version Aliases

You can use version aliases when executing migrations. This is for your convenience so you don't have to always know the version number. The following aliases are available:

  • first - Migrate down to before the first version.
  • prev - Migrate down to before the previous version.
  • next - Migrate up to the next version.
  • latest - Migrate up to the latest version.

Here is an example where we migrate to the latest version and then revert back to the first:

$ ./vendor/bin/doctrine-migrations migrate latest
$ ./vendor/bin/doctrine-migrations migrate first

Writing Migration SQL Files

You can optionally choose to not execute a migration directly on a database from PHP and instead output all the SQL statement to a file. This is possible by using the --write-sql option:

$ ./vendor/bin/doctrine-migrations migrate --write-sql

                    My Project Migrations


Executing dry run of migration up to MyProject\Migrations\Version20180601193057 from 0

  ++ migrating MyProject\Migrations\Version20180601193057

     -> CREATE TABLE example_table (id INT AUTO_INCREMENT NOT NULL, title VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id))

  ++ migrated (took 55ms, used 8M memory)

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

  ++ finished in 60.7ms
  ++ used 8M memory
  ++ 1 migrations executed
  ++ 1 sql queries
-- Migrating from 0 to MyProject\Migrations\Version20180601193057


Writing migration file to "/data/doctrine/migrations-docs-example/doctrine_migration_20180601172528.sql"

Now if you have a look at the doctrine_migration_20180601172528.sql file you will see the would be executed SQL outputted in a nice format:

$ cat doctrine_migration_20180601172528.sql
-- Doctrine Migration File Generated on 2018-06-01 17:25:28

-- Version MyProject\Migrations\Version20180601193057
CREATE TABLE example_table (id INT AUTO_INCREMENT NOT NULL, title VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id));
INSERT INTO doctrine_migration_versions (version, executed_at) VALUES ('MyProject\Migrations\Version20180601193057', CURRENT_TIMESTAMP);

The --write-sql option also accepts an optional value for where to write the sql file. It can be a relative path to a file that will write to the current working directory:

$ ./vendor/bin/doctrine-migrations migrate --write-sql=migration.sql

Or it can be an absolute path to the file:

$ ./vendor/bin/doctrine-migrations migrate --write-sql=/path/to/migration.sql

Or it can be a directory and it will write the default filename to it:

$ ./vendor/bin/doctrine-migrations migrate --write-sql=/path/to/directory

Managing the Version Table

Sometimes you may need to manually mark a migration as migrated or not. You can use the version command for this.

Use caution when using the version command. If you delete a version from the table and then run the migrate command, that migration version will be executed again.

$ ./vendor/bin/doctrine-migrations version 'MyProject\Migrations\Version20180601193057' --add

Or you can delete that version:

$ ./vendor/bin/doctrine-migrations version 'MyProject\Migrations\Version20180601193057' --delete

This command does not actually execute any migrations, it just adds or deletes the version from the version table where we track whether or not a migration version has been executed or not.

Next Chapter: Generating Migrations