Using Doctrine Migrations Part 2: Adding Data to a Migration

[jcfiala]

This is a blog post copied from John's Website - please feel free to join him there and post comments. He has set up openid, so you can post there with your livejournal account using your openid, which is the same as your journal url minus the http://. You can find this entry at http://www.jcfiala.net/blog/2014/08/29/using-doctrine-migrations-part-2-adding-data-migration.

(If you missed it, my previous post on this topic was Using Doctrine Migrations Without the ORM.)

So my previous blog post laid down a good start on how to use Doctrine to move changes to the database, but soon I ran into a new problem - what about when you have data to change in addition to changing the structure of the database? For instance, my first try at this was putting a default row of data into a new table, or maybe putting initial data into a new column?

My first try was simple, but unfortunately not quite right, I used the addSql() command:

<?php
class Version20140828145153 extends AbstractMigration
{
    public function up(Schema $schema) {
    <code that creates a new table>
    $this->addSql("INSERT INTO <table> (this, that, theother) VALUES (...)");
    }
...
?>

This didn't work because the INSERT INTO somehow got called before the table was created. Oops. My first solution to this worked - I created another migration which just inserted the data I wanted. But this isn't perfect - it would be cleaner if one migration both created the new table and inserted the data.

Happily a few days later I found a solution: hidden inside of AbstractMigration are preUp, postUp, preDown and postDown functions. As you probably can guess, these functions allow you to prepare for a migration (in the pre functions) and perform changes after the migration (in the post functions). So, with my next migration, which was adding a new field that copied an existing field (but which was varchar instead of a text field, allowing better use of an index), I tried to use postUp() to fill the new field:

<?php
    public function postUp(Schema $schema) {
        $this->addSql("UPDATE data SET newfield = oldfield");
    }
?>

But... no error, no action - nothing happened. Huh? I quickly resorted to my usual fallback with open source software that isn't doing what I think it should do - I started reading code. After a bit of digging I discovered that the sql queue that addSql was adding to was something only used during the up and down functions - not in the postUp function.

So, after a bit more digging, I ended up with this:

<?php
    public function postUp(Schema $schema) {
        $this->connection->executeQuery("UPDATE data SET newfield = oldfield");
    }
?>

By grabbing the connection object, I'm able to run queries against the database directly. Since I'm in the postUp() function, I know the table changes are done. And now my data changes are packaged with my structure changes, which makes the code easier to understand.