Buy
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:

... 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?

... 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:

... 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:

... 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-10-02 Victor Bocharsky

    Hey Maxim,

    I suppose you're talking about this problem: https://github.com/doctrine... - and looks like it's not fixed yet, at least I see the issue is still opened, so probably no other solutions except some workarounds.

    P.S. Thanks for sharing the package that helps you, probably may be useful for other users.

    Cheers!

  • 2018-10-01 Victor Bocharsky

    Hey Timothy,

    Thanks for sharing your solution with others! I think it's good when you have the same environment locally that you have on production, but it's not required, especially if you have a few projects that have different environment because it could be tricky unless you're using some virtualization tools such as Docker, etc. But, what I'd recommend is to use a continuous integration (CI) set up for your projects where having the same environment as you have on production. So, basically, it would be cool to test your project on the same environment as you have on production. Then you can do not worry about having the same environment locally.

    And yeah, I'd recommend to upgrade your local web server if it's possible and not hard to do to have MySQL 5.6 at least.

    Cheers!

  • 2018-09-29 Timothy Jupe

    Hi, I have run into an error with the second migration in this lesson:

    An exception occurred while executing 'CREATE UNIQUE INDEX UNIQ_23A0E66989D9B62 ON article (slug)':
    SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

    Reading around it seems this is due to using the utf8mb4 database type and MySQL <=5.6. I fixed the problem as advised here: https://github.com/symfony/... - by reducing to length=190 in the same annotation in Article.php.

    Ill be hosting on Google App Engine so should have 5.7 MySQL in production. But my local XAMPP MySQL is probably 5.5ish. Am I ok to carry on with this db, or should I change my dev db type or indeed upgrade my dev MySQL to 5.7?

  • 2018-09-28 Maxim Mandrik

    I spent a lot of time dealing with migrations for PostgreSQL.

    In the migration, $this->addSql('CREATE SCHEMA public') always gets into the down method);
    This is a problem for many people based on Google search results.

    Eventually stopped on package "ruvents/doctrine-fixes-bundle", which fixes it.

    But I think it's a dirty hack. If you know, maybe there's another solution?

  • 2018-09-25 weaverryan

    Hey Krzysztof Krakowiak!

    Wow, that's a very complex & interesting setup! So, as I understand it, you basically have 2 connections: 1 login connection that *everyone* uses, and another connection that is *different* for every user - like each user has its *own* database (or, at least, can choose some dynamic database). That's pretty cool :).

    I at least mostly like your solution. Btw, you could also listen on KernelEvents::REQUEST - you should also have access to the session there. It doesn't matter too much - but in the future, it's possible that something that listens on KernelEvents::REQUEST will try to make a query, and it won't be ready yet (because REQUEST comes before CONTROLLER). But, that's a small detail.

    About using Reflection to access & change the private properties, this is for sure a hack. However, I'm not sure if there is a better solution - it's pretty advanced to want to change your database parameters at runtime. But, I *do* have one idea. In Symfony 4, you typically configure you database credentials using environment variables. For example, the default doctrine.yaml configuration contains this:


    doctrine:
    dbal:
    url: '%env(resolve:DATABASE_URL)%'

    When you use this config, it means that, at runtime, Symfony actually reads the DATABASE_URL environment variable. And *that* means that (in theory) in your listener, you could SET this environment variable, and Doctrine would simply start using it. You could literally do: putenv('DATABASE_URL=mysql://.....');. It's worth a try - it may *still* require the "USE $database_name" hack, but we'll see :). Also, moving our listener earlier (to REQUEST) may also help with this.

    Cheers!

  • 2018-09-21 Krzysztof Krakowiak

    Thanks again Ryan.

    I need to clarify something with you.

    What I have at the moment is an old application which will be replaced by symfony. In order to do that I have embedded symfony inside that old one. At the moment I was able to resolve all the problems apart from the problem described above.

    The truth is that I have resolved above problem too but I need to confirm my solution with you, I am not sure if my solution is an optimal one.

    So in my application I have several database connections, user can select which one he wants to use.

    One connection for login details is constant, but other one is dynamic.

    In doctrine.yaml I have defined both connections, but dynamic one without required details to connect. Then I have created subscriber which on

    KernelEvents::CONTROLLER

    will modify connection details for that dynamic connection, and for that I am using Reflection to access there private properties.

    I have decided to use above event because $_SESSION is available at that stage and in session I have the details what connection was selected by user.

    This solution had one issue, I have to make an extra query with: "USE $database_name", otherwise I have following error:

    SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected

    I couldn't figure out why this is happening.

  • 2018-09-18 weaverryan

    Hey Krzysztof Krakowiak!

    Ahhhh :). So yes, try the simplest solution first. But, indeed.... there is dark magic you can use to override *anything* you want in the service container. Without going into too many details, this is done with a "compiler pass", which you can actually implement directly in your Kernel class: https://symfony.com/blog/ne...

    This "process" function is passed a ContainerBuilder, which is an object that knows everything about the container and all of its services. From here, you can change anything you want - completely remove services, replace services, change the class of a service, etc, etc. So, yes, you could (in theory) remove the doctrine.default_entity_manager entirely. However, it's very possible that there are places in the system that rely on this, and so everything will fail. If that's the case, you could also replace this service with some class that simply throws an exception when it's used. As I mentioned, this is dark magic - so... be careful ;).

    Cheers!

  • 2018-09-18 Krzysztof Krakowiak

    Thanks Ryan, yes I have seen this class yesterday when I was investigating this, and I was wondering if it is possible to overwrite this method, I would like to extend this class and only overwrite this one method, mainly I am wondering how I can overwrite any of the symfony/bundle class. I know that this might not resolve the issue, and app will crash because there is no default value for default connection.

    Is it possible to reset: doctrine.default_entity_manager after it is set?

    I will test your solution, sometimes the simplest ones are the best.

  • 2018-09-17 weaverryan

    Hey Krzysztof Krakowiak!

    Hmm, I understand what you're saying - it makes perfect sense. Unfortunately, I don't think it's possible. The DoctrineBundle is configured at a low level to always have a default em and default connection. Here is some deep logic that proves this: https://github.com/doctrine...

    So, the best option I can think of to "enforce" being explicit about which em you want is to make an "extra" em that you set as default, and which has bad connection settings. It's a lame solution, but then you would get an immediate error when using it, instead of unexpected results.

    I know that's not the answer you want - but I hope it helps!

    Cheers!

  • 2018-09-17 Krzysztof Krakowiak

    How I can overwrite default command for migrations? I have different database connections and I want to get rid of default one, user should always specify against which database migration is run.

    I would like to overwrite default one and make below options mandatory:
    --em
    --db-configuration
    So it should be not possible to run migrations without above.

    Is there a way that in Symfony itself that I could get rid of default connection to db? At the moment in doctrine.yaml I have specified:
    default_connection: ~

    and the same I did for default_entity_manager but Symfony is ignores that and uses first defined one as a default one.

  • 2018-07-11 Amy

    Thank you. That was very helpful :).

  • 2018-07-11 weaverryan

    Hey Amy!

    Ah, very interesting situation & question! And... yes... more or less :). The bad news is that the migration library doesn't make it *super* easy to configure & setup the services you need to do this. If you look in your project for a class called MigrationsMigrateDoctrineCommand, you'll see the logic needed to do it (well, most of the logic is in other classes it calls and its parent class). So yes, it's possible to create the Configuration object you need, then the Migration and migrate, but, it's not as easy as it ideally would be.

    But, another option is to run the command directly from inside Symfony. This is a fine solution - here are some details https://symfony.com/doc/cur...

    I hope this helps!

    Cheers!

  • 2018-07-11 Amy

    Currently, I have an app that uses a different framework. I am hoping to port it to symfony. Our app uses a different database for each customer, and has a home-built migrations system. If somebody logs in to the customer database, it checks to see if it needs to run any of the migrations. (If we know we have a big migration to run for a large customer, we make sure to have support do that at an off-peak time). So, my question is - is it possible to run doctrine migrations from within the application and not via the terminal? (If not, I'll just put together a script that cycles through all the databases and runs the migrations command)

  • 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.