4 months ago, you published a website on a production server. Ok, no a big deal. You work on next iterations and today you must update the website.

Some git push here, git pull there (or whatever you use). Still ok. But what about the database ? You did upgade you schema, still you cannot delete all existing datas in the production database, right ?

How should you manage that ? Check every field you changed, go to phpMyAdmin and manually build each field and table, launch functional tests to check no error appears ? Come on, let's not get silly ! You'll use Doctrine migration task !

Starting up the project

One upon a time your schema was straight and clear. You basically has a list of members with a fistname and a lastname

Schema at its first version

Member:
  columns:
    id:         { type: integer(4), primary: true, autoincrement: true }
    firstname:  string(50)
    lastname:   string(50)

Of course you had some basic fixtures :

Member:
  pointbar:
    firstname:  Stéphane
    lastname:   Langlois
  ioo:
    firstname:  Lionel
    lastname:   Chanson
  vinyll:
    firstname:  Vincent
    lastname:   Agnano

Then you did something like :

symfony doctrine:build --all --and-load

Made your modules, wrote your tests, refactored, enjoyed, had lunch and fun, pushed your site on the staging, got it tested and validated and finally run it in production ! Phew, what a journey you had.

Some time later, you reached today. And today is a great new day because you must update your website. And its database. Push all that in production. Oh, and preserving the datas from that database. Ok ok, you got the point and its issue. Let's go then !

Updating the project

Members are required to get username. A new functionality also comes up as they may have tasks. Easy...

First thing first, you update your schema as necessary :

Task:
  columns:
    id:         { type: integer(4), primary: true, autoincrement: true }
    title:      string(100)
    body:       clob
    member_id:  integer(4)
  relations:
    Member:
      class:        Member
      local:        member_id
      foreign:      id
      foreignAlias: Tasks

Member:
  columns:
    id:         { type: integer(4), primary: true, autoincrement: true }
    firstname:  string(50)
    lastname:   string(50)
    username:   string(20)

And update your fixtures :

Member:
  pointbar:
    username:   pointbar
    firstname:  Stéphane
    lastname:   Langlois
  ioo:
    username:   ioo
    firstname:  Lionel
    lastname:   Chanson
  vinyll:
    username:   vinyll
    firstname:  Vincent
    lastname:   Agnano
    
Task: 
  migrations:
    title:    Finish blog article
    body:     Will I ever make it ?
    Member:   vinyll
  mediabrowser:
    title:    Fix sfMediaBrowser tickets
    body:     "Ask some Windows to solve http://github.com/vinyll/sfMediaBrowserPlugin/issues/issue/5"
    Member:   vinyll

Update your model

Here is the point. Don't update your schema as you're used to as it would fully recreate your model.

Instead, you'll use the migration generation from difference.

Generating a migration

As of Doctrine 1.1, you can generate migrations classes automatically from schema differences and not type the whole thing. Warning : Do NOT run some "symfony doctrine:build --model" if you don't want to run through some error.

So you may safely hit this line :

symfony doctrine:generate-migrations-diff

Magic comes out and as the tasks says, it generates migration classes from the differences.

Check out /lib/migration/doctrine/ and you'll see 2 new files (2 in our example case). Those are the classes that will updagrade-enable and downgrade-enable our application without reconstructing the whole structure. The first generated file is the basic columns modification while the second is for indexes and relations (as it must be done after columns creation/deletion, database-wise).

Applying a migration

Simply run !

symfony doctrine:migrate

What happened ?

Check out your Base model classes and your database. They have been updated, just as if you had run a "doctrine:build --all" but with the awareness of versionning. We could compare it to svn or git.

Doctrine's versionning system

It actually happens in 2 places :

• One that holds the current version : that's your database ! If you have a look at it, you'll see a table called "migration_version" that has 1 column named "version" and 1 record that holds "2" ("2" in our specific case). That "2" is as you understood the current version you are at !

• The file system : these classes you opened earlier (/lib/migration/doctrine/*) are named by their version number.

Therefore it will be very easy to know at what version you currently are, how far your are from head version and what happens in next/previous version just reading the class files.

Update in staging/prod environment

You'll be required to migrate your prod version as well. First update your files going for a "git pull" or whatever. That brings in your migration classes. Next you just need to run your migration task :

symfony doctrine:migrate

Now you may run your tests again. All datas are preserved. You can go back for lunch and fun.

You may freely comment or tweet me.