Handling database migrations with DoctrineMigrationsBundle in symfony

08/12/2017 - DOCTRINE, SYMFONY

DoctrineMigrationsBundle helps us to reflect changes in our entities to database so that we don't do anything manually. It compares your entities to your database so any updates in your entities will appear in your migration file after running diff command. It works in given way below:



The critical point here is, before you run diff command to create migration file, make sure the changes are not in database yet otherwise diff command won't be able see the changes and you will end up with an empty migration file.


Not: If you are running migration process first time, the migration command will create migration_versions table in database. After that, it will insert migration versions into it.


Our example


I have a app_user table in database but removed AppUser entity in my application so the migration should remove app_user table in database as well. I then added a new entity called Customer in my application but the table doesn't exist in database yet so the migration should create customer table in database as well.


Composer


Run composer require doctrine/doctrine-migrations-bundle "^1.3" to install DoctrineMigrationsBundle and add new Doctrine\Bundle\MigrationsBundle\DoctrineMigrationsBundle(), to AppKernel.php file.


Configuration


#config.yml
doctrine_migrations:
    dir_name: "%kernel.root_dir%/DoctrineMigrations"
    namespace: CustomerBundle
    table_name: migration_versions
    name: Application Migrations
    organize_migrations: false

Database


Currently we have only app_user table in database.


mysql> SHOW TABLES;
+------------------+
| Tables_in_common |
+------------------+
| api_user         |
+------------------+
1 row in set (0.00 sec)

Customer entity


Create entity below. I removed annotations, setters and getter methods just to keep the code short but obviously you have to have them.


namespace CustomerBundle\Entity;

class Customer
{
    private $id;
    private $name;
    private $dob;
}

Create migrations file


$ php bin/console doctrine:migrations:diff
Generated new migration class to "/var/www/html/symfony/common/app/DoctrineMigrations/Version20171208210144.php" from schema differences.

$ cat app/DoctrineMigrations/Version20171208210144.php

namespace CustomerBundle;

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

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20171208210144 extends AbstractMigration
{
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        $this->addSql('CREATE TABLE customer (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(50) NOT NULL, dob DATETIME NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('DROP TABLE api_user');
    }

    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        $this->addSql('CREATE TABLE api_user (id INT AUTO_INCREMENT NOT NULL, api_key VARCHAR(36) NOT NULL COLLATE utf8_unicode_ci, roles LONGTEXT NOT NULL COLLATE utf8_unicode_ci COMMENT \'(DC2Type:array)\', UNIQUE INDEX unq_api_key (api_key), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('DROP TABLE customer');
    }
}

Execute migration


$ php bin/console doctrine:migrations:migrate --no-interaction
                                                              
                    Application Migrations                    
                                                              

Migrating up to 20171208210144 from 0

  ++ migrating 20171208210144

     -> CREATE TABLE customer (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(50) NOT NULL, dob DATETIME NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB
     -> DROP TABLE api_user

  ++ migrated (0.41s)

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

  ++ finished in 0.41s
  ++ 1 migrations executed
  ++ 2 sql queries

Database


mysql> SHOW TABLES;
+--------------------+
| Tables_in_common   |
+--------------------+
| customer           |
| migration_versions |
+--------------------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM migration_versions;
+----------------+
| version        |
+----------------+
| 20171208210144 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM customer;
Empty set (0.01 sec)