Expert Topics

How to work with Doctrine migrations in Symfony

7 Comments // Reading Time: 13 min.

This article is part of the series. The first article discussed the creation of an initial Symfony 2 project, the second is about Symfony2 forms, and the third article describes a nice and easy way to implement a REST API with the Symfony framework.

Introduction

Let's say that you have made some changes in the model of your Symfony project, you have committed the code, and it will be applied on the production. But, you also need to update the production database. You might run the command: php app/console doctrine:schema:update, but then you might lose your data (it depends on the model changes you made). Right and data safe way is to use Doctrine migrations. Doctrine can automatically generate migration for you, but that migration will contain the same SQL code like doctrine:schema:update command, and it doesn't care about the existing data. To migrate the data, you will have to modify migration file, and we will talk about that later in the text. Let's get started.

1. Install Doctrine migrations bundle

If Doctrine migrations bundle is not already included in your Symfony project, add this line in "require" section of your composer.json file:

"doctrine/migrations": "1.0.*@dev"

Adjust the version if there is a newer one and run composer update (or run composer install if you don't wish to update your other dependencies).

Also, don't forget to add the bundle in your AppKernel file:

2. Generating migration file

After you've made changes on the model (you added/deleted some fields of some entities, or you added/deleted whole entities), you can create the migration file by running the command:

php app/console doctrine:migrations:diff 

You will notice that a new file is added to your project in directory:

app/DoctrineMigrations

The file will be named something like this:

Version20151118233337.php

It contains timestamp of creation moment: VersionYYYYMMDDhhmmss.php. In that way, migration names are unique and properly ordered, and order of migrations execution is important. But, doctrine takes care of all of that for you.

Before executing the migration, you might wish to peek in the file just to check if the generated SQL is OK. If everything is fine, and there is no need to migrate the data (we will come to it later), you can run the migration.

Before going on, I would like to comment on migration class methods. By default, auto generated migration class (which is contained in the migration file, and has same name as the file) contains two methods: up and down. Up method is executed if we are migrating to a newer version, and down migrates back to the older version of project. You can see it in the example below. I have added the title field to the person table. If migrating up, the field is added, and if migrating down, it is dropped.


Does your company need a web specialist to talk to on an equal footing?

 


3. Execute the migration

php app/console doctrine:migrations:migrate

This is the main command you will use to execute the migrations. It runs all the new (unexecuted) migrations. Before running it, you can check what migrations are new, and see a lot of more info by executing this command:

php app/console doctrine:migrations:status

There are more migration commands:

php app/console doctrine:migrations:generate
php app/console doctrine:migrations:execute
php app/console doctrine:migrations:version
php app/console doctrine:migrations:latest

but they are not so much interesting for us now. To see more info about any command, and how to use it, run the command with --help argument.

4. Migrate the data

The example shows a migration which moves the address fields from the person table to a separate table - address and references it in the person table. The migration, as you can see it above, cares only for database structure, and doesn't care about the data. But if the application is already running on the production and it contains a data, we need to migrate the data too. In this example we will need to copy address data (street, street_number, city, ...). So, we will do it in these three steps:

  1. Create the address table and create the foreign key address_id in the person table.
  2. Copy all the address data from the person table to the address table.
  3. Drop the address fields from person table.

Steps 1 and 3 already exist, and we just need to separate and order them properly. Step 2 is the tricky part, and we need to implement it.

What exactly do we need to do in the step 2?  We need to take the address data of each person and insert it in the address table. Then we need to connect each person with it's address by inserting right address_id in the person table. It would be easy if there is a unique address field (we can't use address_id because it didn't exist in the person table - we need to insert it now), or if there is unique combination of the address fields, then we might copy the data by executing these SQL queries:

But the problem is that there can be 2 or more persons with the same address. And each of them should have his copy of the address in the address table (they would have only different id-s). So, the SQL above can't be used in this example. 

We can copy the data by iterating the person table, and in each iteration we read/select the address data from the person, insert the data in the address table, fetch address.id and update it in the person table to the same row from which we have read the data. How to implement that?

We can make iterations in SQL, but we need to make a stored procedure to do the job, and we can also make the iterations in PHP. The problem with PHP approach is that we can't work with our data from within the up function because all the SQL code added by the $this->addSql method is executed at the end of the up function. Solution is simple:

We are going to use postUp function which is executed after up function, in that way securing that all the tables are already created when we handle the data migration. So, even SQL-procedure-approach should be implemented within the postUp function. Here is how it should look like:

4.1 Migrating the data by use of mySQL stored procedure

We did these 3 sub steps:

  1. Create the stored procedure.
  2. Execute (call) it
  3. Delete (drop) it

As you can see, in the procedure, we are using a CURSOR for selecting  address fields only and then we are iterating the selection (in a loop) while there is the data available. within each iteration, we are inserting the data in the address table, then fetching the address.id by LAST_INSERT_ID() function and updating it to the person table.

Read here more on mySQL cursors.

4.2 Use of PHP for handling the data in the postUp method

In the postUp method we can even use entitymanager, but first we need to fetch it from the context. So we need to have the context aware migration:

This is a different example than before. We are making the company table out of the string field work which is defined in the customer table. And those customers are actually employees in the company (this doesn't make much sense now because you don't see complete model in these examples, but just accept it as it is).

I must say right away that this example has an error! The work field doesn't exist anymore in the Customer class, and we are trying to get it. So we can't rely on entities - objects, because their code can be inconsistent with current database state. Another example of inconsistency between the code and the migration is when the migration is executed in the future. Let's say that a user is creating a new database, so when he runs the migrate command, all the migrations are executed, and the code - entities are of the newest version and some of the entities/fields which exist in the migration, might be deleted in the code. So there might be some migrations which won't work.

Solution to this problem is simple: don't use entities within migrations, you must not relay on the entity (PHP) code, but on the database only!

So, don't use entitymanager, nor DQL, just access the database via Doctrine\DBAL\Connection, like in the next part of the code:

The code above creates companies (inserts company names in the company table) from the customer.work string field.

But the best solution is to use simple SQL queries if it is possible. Example of creating companies based on the customer.work field and connecting the customers with their company:

5. All in all, on the whole

Let's sum the important stuff:

  • Use the console command to generate the migration (php app/console doctrine:migrations:diff)
  • Modify the migration to preserve/migrate the data.
  • Use postUp method for migrating the data
  • Do all relevant droppings on the end of the postUp method
  • If it is possible execute simple SQL queries to migrate the data
    • If simple SQL can't do the job, use SQL stored procedure with loops
    • Or use PHP within postUp method but be sure to rely on database only
      • don't use entities, use Doctrine\DBAL\Connection ($this->connection)
  • Execute the migrations (php app/console doctrine:migrations:migrate)

Enjoy the migrations. Questions, discussion and comments are welcome. :)

Contact us!

We are a digital agency, which is specialized in the development of digital products. Our core topics are websites and portals with TYPO3, eCommerce with Shopware and Android and iOS-Apps. In addition, we deal with many other topics in the field of web development. Feel free to contact us with your concerns!

Comments

  • Stefan Galinski

    Stefan Galinski

    at 23.11.2015

    Very helpful, Damjan! Very helpful, Damjan!

  • Martins

    Martins

    at 20.12.2015

    Thanks! Thanks!

  • Olivier

    Olivier

    at 28.04.2016

    Hi Damjan !

    Very nice and helpful introduction to Doctrine Migrations.

    I am currently working on a SaaS web app. It means all our customers share the same backend Symfony application but each [...] Hi Damjan !

    Very nice and helpful introduction to Doctrine Migrations.

    I am currently working on a SaaS web app. It means all our customers share the same backend Symfony application but each one as its own database. Is it helpful to use Migrations in this case and why ?

    Thank you

  • Saint-Cyr

    Saint-Cyr

    at 28.05.2016

    Nice post !
    Olivier at your place I would do separate the whole project like one Symfony application for only one Data Base because normally designing a Data base is a standalone project (or [...] Nice post !
    Olivier at your place I would do separate the whole project like one Symfony application for only one Data Base because normally designing a Data base is a standalone project (or sub-project) and designing an application to communicate to is another one and most of the time the application structure (data structure), logic (algorithms), ... all rely on the DB but the reverse is not. But according to your case if more than one customer have the same business logic ( they can share one DB) then you can share the DB among them in this case but steel there is a probability that one day one of them ask for a feature that require change of DB schema and this might not be the desire of the other....

  • tomasz

    tomasz

    at 27.03.2017

    Any clues on debugging except var_dumping? Any clues on debugging except var_dumping?

  • Christian Elowsky

    Christian Elowsky

    at 23.10.2017

    One quick note. For this section where you execute the query, the results should actually be retrieved via a call to fetchAll() like so:
    $queryBuilder = [...] One quick note. For this section where you execute the query, the results should actually be retrieved via a call to fetchAll() like so:
    $queryBuilder = $this->connection->createQueryBuilder();
    $queryBuilder
    ->select('customer.work')
    ->from('customer', 'customer')
    ->groupBy('customer.work')
    ->orderBy('customer.work');
    $stmt = $queryBuilder->execute();
    $customersGroupedByWork = $stmt->fetchAll();

    foreach ($customersGroupedByWork as $customerGroupedByWork) {
    // Insert statement by DoctrineDBALConnection
    $this->connection->insert('company', ['name' => $customerGroupedByWork['work']]);

  • Liam

    Liam

    at 26.11.2019

    Nice guide! Nice guide!