Symfony and Doctrine: multiple connections

3 gravatar By Grégoire Marchal - 09/02/2010

Well, it's my first post, I don't go into a very complicated and revolutionary concept. Let's regard that as being a breaking in! So I'll try to write a small post about my recent first experience with multiple database connections, with Symfony 1.4 and Doctrine 1.2.

At the beginning, my project was connected to a unique database, a typical configuration, the routine. Then one day, I needed to update the value of a field in an other database. I had two possibilities:

  • use the odious mysql_*() function, like during the 80's (who says I exaggerate?!)
  • discover the marvellous world of multiple Doctrine connections

As I had never taken a look at this point, I worried that the second option was to heavy. But, with the advices of my SQL Technologies colleagues, it turned out that it was quite easy.

First of all, I had to edit the config/databases.yml file to handle the second connection:

# databases.yml
all:
  connection1:
    class:        sfDoctrineDatabase
    param:
      ...
  connection2:
    class:        sfDoctrineDatabase
    param:
      ...

Then I introspected this second database in order to generate its schema (you can do it by hand if you're courageous!):

symfony doctrine:build-schema

From that moment, in the config/doctrine/schema.yml file, you can see that each table is linked to a connection thanks to the parameter... "connection"!

MyTable1:
  connection: connection1
  columns:
    ...
MyTable2:
  connection: connection2
  columns:
    ...

And when you re-build the model, the magic happens: in the Base*.class.php files of the model, a new line has appeared. It allows the model class to be linked to its connection:

// BaseMyTable1.class.php
// Connection Component Binding
Doctrine_Manager::getInstance()->bindComponent('MyTable1', 'connection1');

That's it! Our objects can be used without caring of where the data are stored.

// stupid actions
$oObject1 = Doctrine::getTable('MyTable1')->find(7);
$oObject2 = Doctrine::getTable('MyTable2')->find(42);
$oObject2->setName($oObject1->getTitle())->save();

Long live Doctrine, long live Symfony!