Buy

Database Migrations

The Article entity is ready, and Doctrine already knows to save its data to an article table in the database. But... that table doesn't exist yet! So... how can we create it?

Generating a Migration

Ah, this is one of Doctrine's superpowers. Go back to your terminal. At the bottom of the make:entity command, it has a suggestion: run the make:migration command.

I love this! Try it:

php bin/console make:migration

The output says that it created a new src/Migrations/Version* class that we should review. Ok, find your code, open the Migrations directory and, there it is! One migration file:

29 lines src/Migrations/Version20180413174059.php
... lines 1 - 2
namespace DoctrineMigrations;
use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
/**
* Auto-generated Migration: Please modify to your needs!
*/
class Version20180413174059 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 article (id INT AUTO_INCREMENT NOT NULL, title VARCHAR(255) NOT NULL, slug VARCHAR(100) NOT NULL, content LONGTEXT DEFAULT NULL, published_at DATETIME DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
}
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('DROP TABLE article');
}
}

Inside, cool! It holds the MySQL code that we need!

CREATE TABLE article...

This is amazing. No, seriously - it's way more awesome than you might think. The make:migration command actually looked at our database, looked at all of our entity classes - which is just one entity right now - and generated the SQL needed to update the database to match our entities. I'll show you an even better example in a few minutes.

Executing the Migration

This looks good to me, so close it and then go back to your terminal. To execute the migration, run:

php bin/console doctrine:migrations:migrate

This command was also suggested above. Answer yes to run the migrations and... done!

But now, run that same command again:

php bin/console doctrine:migrations:migrate

How Migrations Work

It does nothing! Interesting. Run:

php bin/console doctrine:migrations:status

Ok, this tells us a bit more about how the migration system works. Inside the database, the migration system automatically creates a new table called migration_versions. Then, the first time we ran doctrine:migrations:migrate, it executed the migration, and inserted a new row in that table with that migration's version number, which is the date in the class name. When we ran doctrine:migrations:migrate a second time, it opened the migration class, then looked up that version in the migration_versions table. Because it was already there, it knew that this migration had already been executed and did not try to run it again.

This is brilliant! Whenever we need to make a database change, we follow this simple two-step process: (1) Generate the migration with make:migration and (2) run that migration with doctrine:migrations:migrate. We will commit the migrations to our git repository. Then, on deploy, just make sure to run doctrine:migrations:migrate. The production database will have its own migration_versions table, so this will automatically run all migrations that have not been run yet on production. It's perfect.

Migration a Second Change

To see how nice this is, let's make one more change. Open the Article class. See the slug field?

93 lines src/Entity/Article.php
... lines 1 - 6
/**
* @ORM\Entity(repositoryClass="App\Repository\ArticleRepository")
*/
class Article
{
... lines 12 - 23
/**
* @ORM\Column(type="string", length=100)
*/
private $slug;
... lines 28 - 91
}

This will eventually be used to identify the article in the URL. And so, this must be unique across every article in the table.

To guarantee that this is unique in the database, add unique=true:

93 lines src/Entity/Article.php
... lines 1 - 6
/**
* @ORM\Entity(repositoryClass="App\Repository\ArticleRepository")
*/
class Article
{
... lines 12 - 23
/**
* @ORM\Column(type="string", length=100, unique=true)
*/
private $slug;
... lines 28 - 91
}

This option does only one thing: it tells Doctrine that it should create a unique index in the database for this column.

But of course, the database didn't just magically update to have this index. We need a migration. No problem! Find your terminal and do step 1: run:

php bin/console make:migration

Ha! I even misspelled the command: Symfony figured out what I meant. This created a second migration class: the first creates the table and the second... awesome! It creates the unique index:

29 lines src/Migrations/Version20180413174154.php
... lines 1 - 2
namespace DoctrineMigrations;
use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
/**
* Auto-generated Migration: Please modify to your needs!
*/
class Version20180413174154 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 UNIQUE INDEX UNIQ_23A0E66989D9B62 ON article (slug)');
}
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('DROP INDEX UNIQ_23A0E66989D9B62 ON article');
}
}

This is the Doctrine magic I mentioned earlier: the make:migration command looked at the entity, looked at the database, determined the difference between the two, then generated the SQL necessary to update the database.

Now, for step (2), run:

php bin/console doctrine:migrations:migrate

It sees the two migration classes, notices that the first has already been executed, and only runs the second.

Ok! Our database is setup, our Article entity is ready, and we already have a killer migration system. So let's talk about how to save articles to the table.

Leave a comment!

  • 2018-04-23 Victor Bocharsky

    Hey Dmirtiy,

    As I see you use migrations which is super cool! And despite the fact that Doctrine generate a migration for you to sync your DB schemas, you are totally able to write/add custom queries inside that migration. So, after "ALTER TABLE" query that adds a column you can add one more SQL query manually that will populate the new column with some data. It's a totally valid way if you have some lightweight changes, and this migration will took a few seconds. But you need more time, like about an hour to populate that new column with some data, like you need to perform API calls for to get proper data for this column - you probably better do not do it in migration. Use one-time migration commands - it's a regular Symfony console command, that you will run on your production only once to populate some columns with some data when this operation takes a long time like about an hour. So after its successful execution you can just removed it at all from your code.

    Cheers!

  • 2018-04-21 Дмитрий Ченгаев

    Please, help.

    I add a new property (column) to the entity, with the command

    bin/console doctrine:migrations:migrate

    My database table already contains some data and a new column is added with empty value for existing data in the database.

    What should I do if I want to add a new column for existing fields, not with an empty value, but with a certain default value.