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!

Back home

Comments (3)

gravatar By Tomasz Ducin, the 09/03/2010 at 8:55 AM
You can read about Doctrine bug when using multiple db connections at
http://symfony-world.blogspot.com/2010/05/multiple-database-symfony-configuration.html.
gravatar By Luke, the 02/21/2011 at 1:05 AM
Is there any way of specifing database while creating query?
Such as Doctrine_Query::create(‘system’)->from… etc. Where system is connection name in schema?
I have two sets of connections with exactly the same tables. Different content though, therefore I have to change connection when pulling data. Any ideas?
gravatar By Grégoire Marchal, the 02/21/2011 at 8:24 PM
I think you should declare 2 tables in your schema for each table. I mean:

MyTable1:
connection: connection1
tableName: my_table
columns:

MyTable2:
connection: connection2
tableName: my_table
columns:


And then you just have to use the right table class.

Comment