Doctrine migrations: How to avoid SQL errors in the postUp step?

How do you deal with migrations when deploying Symfony2 apps that use Doctrine ORM?

I have been using DoctrineMigrationsBundle. It works well if you avoid trying to use entities in the postUp() part of the migration. However, if you do, you get into trouble.

Here is some pseudocode to explain the problem:

/* Migration1.php */
class Migration1 extends... implements ContainerAwareInterface {
    public function up(...) {
        query("CREATE TABLE entities WITH COLUMNS col1, col2;");
    }

    public function postUp(...) {
        $e = new Entity();
        $e->setCol1("value1");
        $e->setCol2("value2");
        $entityManager->persist($e);
    }
}


/* Migration2.php */
class Migration2 extends... implements ContainerAwareInterface {
    public function up(...) {
        query("ALTER TABLE entities ADD col3");
    }
}

Practical case 1:

  • Developers change application code and prepare Migration1
  • Code is deployed on the server, including running the migrations
  • Developers change application code and prepare Migration2
  • Code is deployed on the server, including running the migrations

It all works well.

Practical case 2:

  • Developers change application code and prepare Migration1
  • Developers change application code and prepare Migration2
  • Code is deployed on the server, including running the migrations

This does not work. Why?

Code in postUp() part of Migration1 will run with the newest application code. This means that Doctrine ORM will expect that col3 is already present in the entities table. However, since Migration2 did not run yet up to that point, there is not field col3. Trying to persist new entity in Migration1 results in an SQL error.

I’ve come up with two ideas for solution of this problem:

  • When deploying new code, use version control system. Check out each commit separately, run migrations in each commit until you get to the newest commit OR
  • Don’t use postUp() for changing entities. Deal with entities in separate scripts after all structural changes to the database have been done.

Please comment with your experience on the issue and explain how you deal with it. I’m sure some of you have encountered this in practice.

  • How to clone a specific version of a git repository?
  • What's the proper way to use SATIS with shared internal library
  • Git don't push a symfony bundle into Github
  • Untrack git file
  • How to update add/replace an entire database using Doctrine or Mysql
  • Corrupted Symfony 2 vendors install
  • Git crashes while installing libraries
  • Sylius install: get Loggable annotation
  • One Solution collect form web for “Doctrine migrations: How to avoid SQL errors in the postUp step?”

    The scenario you describe perfectly shows that it is not possible (in general) to use entities in Doctrine Migrations.

    So I’m afraid the only way to make this work in a robust fashion is to descend to the DBAL level:

    public function postUp(...) {
        $this->connection->insert('entities', [
            'col1' => 'value1',
            'col2' => 'value2'
        ]);
    }
    

    By using the DBAL query the columns to be used are supplied directly instead of indirectly via the entity that is defined by the application code, which does not need to be in sync with the current table structure.

    See also section 4.2 of the “How to work with Doctrine migrations in Symfony” post, which also describes this issue with using entities in Doctrine Migrations, especially the following quote:

    don’t use entities within migrations, you must not rely on the entity (PHP) code, but on the database only!

    Git Baby is a git and github fan, let's start git clone.