Using Multiple Databases in Symfony with Propel

By marc • Apr 22nd, 2008 • Category: Symfony

There comes a time in your symfony development career when you will need to connect to multiple databases from within one application. At first it may not be obvious how to configure symfony correctly to handle a couple different databases or database servers. Let me show you just how simple it is with Propel!

Keep in mind that the examples below are just a simplified overview to show the basic concepts. There are numerous ways that this can be expanded upon to work with specific server configurations.

Configuration

The first thing that we need to do is properly configure the database configuration files (schema.yml & databases.yml). So here is an example of how these two files can be configured for a basic application:

schema.yml

propel:
  1.  
  2.   articles:
  3.     _attributes: { phpName: Article }
  4.       idMethod: native
  5.     id:
  6.       type: INTEGER
  7.     title:
  8.       type: VARCHAR
  9.     created_at:
  10.       type: TIMESTAMP
  11.     updated_at:
  12.       type: TIMESTAMP
  13.  
  14.   categories:
  15.     _attributes: { phpName: Category }
  16.       idMethod: native
  17.     id:
  18.       type: INTEGER
  19.     category:
  20.       type: VARCHAR
  21.     parent_id:
  22.       type: VARCHAR
  23.     created_at:
  24.       type: TIMESTAMP
  25.     updated_at:
  26.       type: TIMESTAMP
  27.  
  28. # etc…..

As you can see, this schema.yml file looks exactly the same as one for a single database application. In most of my applications I usually have many of the same tables in various databases, so in this case I can just put all the schema definitions in one schema file and use the “propel” connection name.

databases.yml

  1. all:
  2.   propel:
  3.     class:          sfPropelDatabase
  4.     param:
  5.       dsn:          mysql://USERNAME:PASSWORD@DB_HOST_1/DB_NAME
  6.  
  7.   slave:
  8.     class:          sfPropelDatabase
  9.     param:
  10.       dsn:          mysql://USERNAME:PASSWORD@DB_HOST_2/DB_NAME

The main thing to consider is that you probably always want a connection named “propel”. This is necessary for certain things to work correctly such as “symfony propel-build-model”, etc especially since the schema.yml uses that connection name.

Switching Connections

Now when you want to use your different connections, you just have to let Propel know which connection you want to use. By default, Propel uses the “propel” connection if no other connection is explicitly set on a query. Here is how you would go about using your two connections defined in the databases.yml file described above:

  1. // get all articles from the slave database
  2. $c = new Criteria();
  3. $articles = ArticlePeer::doSelect($c, Propel::getConnection('slave'));
  4.  
  5. // save an article in the master database (explicitly setting the connection name)
  6. $article = new Article();
  7. $article->setTitle('Symfony Rocks!!!');
  8.  
  9. $article->save(Propel::getConnection('propel');

This is just a very basic demonstration of the concepts used to switch the database connections. There are many ways that this would be expanded to handle various environments. For example, if you had a classic set up of a master database with N number of slaves, it would probably be best to create a behaviour or something to override the update/insert/delete methods of the models so that they are only performed on the master database and the selects would be performed only on the slaves. Users with this type of set up may want to take a look at sfPropelLoadbalancerPlugin which seems to accomplish this goal.

marc is a Senior Software Engineer in Los Angeles, CA. He likes to receive comments :)
All posts by marc

9 Responses »

  1. ken on May 9th, 2008 at 7:27 am:

    This is at least easier for me.

    Create an application for every database connection, then merge all of their models into your application. After that delete all the temporary applications. And that’s it. Just make sure their connection name in database.yml are unique from each other.

  2. Basic Mysql Commands Databases on June 22nd, 2008 at 8:47 pm:

    I found your site on faves.com bookmarking site.. I like it ..gave it a fave for you..ill be checking back later

  3. Scott Matthews on August 19th, 2008 at 7:53 am:

    Thanks for the posting, I have a comment though. I notice with your statement here and others I’ve found about the need to name a connection ‘propel:’ for things to work such as propel-build-model. This doesn’t seem right and the application I’m building does not have this designation and everything is fine. My schema.yml, databases.yml and propel.ini are basically in synch with the name I used and it is not ‘propel’.

    From what I can see, propel.ini is what drives the connection for things such as ‘propel-build-model’ and ‘propel-insert-sql’ giving you the need to have one connection defined in that file for the action you are taking. The only thing that I can see that is confusing this issue is in propel.ini we have to set things such as ‘propel.project’ and ‘propel.database’ but I do not believe that directly means a connection in databases.yml or schema.yml called ‘propel’ it’s just that it’s settings for propel to use.
    Example:

    ## from Schema.yml
    data_connection_master:
    status:
    _attributes: { phpName: my_status, package: lib.model.my_administrator }
    code: { type: integer, required: true, primaryKey: true }
    type: { type: varchar, size: 50, required: true, primaryKey: true }
    created: { type: timestamp }
    modified: { type: timestamp }
    …….
    ##from databases.yml
    all:
    data_connection_master:
    class: sfPropelDatabase
    param:
    phptype: mysql # Database vendor
    hostspec: localhost
    database: data_connection_master
    username: symfonyuser
    password: symfonyuser
    port: 3306
    encoding: utf8 # Default charset for table creation
    persistent: true # Use persistent connections
    …….
    #from propel.ini
    propel.database = mysql
    propel.database.createUrl = mysql://symfonyuser:symfonyuser@localhost:3306/
    propel.database.url = mysql://symfonyuser:symfonyuser@localhost:3306/data_connection_master

    Since things work fine for me without having a connection called ‘propel’ I’m pretty sure I’m right. I’d appreciate hearing the perspective and experience of someone else on this.

  4. Junni on October 1st, 2008 at 6:16 am:

    Thanks for this nice article. I needed this information because I’m developing a new version of a community project. Of course I need al existing data transferred to the new tables, for which this solution is made!

  5. Daily Digest for 2008-10-06 | Pedro Trindade on October 7th, 2008 at 12:06 am:

    […] » Using Multiple Databases in Symfony with Propel : LampJunkie.com - Everything related to Linux, P… […]

  6. andrew on October 24th, 2008 at 10:39 pm:

    hi there sir,

    is there a way to use this process without referring to databases.yml for the slaves?

    i want my databases.yml file to contain only 1 database connection params which is for the master database. as for the slaves, i will keep the record in a database table.

    please help. thanks.

  7. triggertoo on November 7th, 2008 at 3:46 am:

    is there a way to set a database default in an action, so that i don’t need to call Propel::getConnection(’slave’) as the second parameter in a doSelect or as the first in a save() method every time?

  8. Julian on December 24th, 2008 at 8:19 am:

    Nice article. But i’m looking for someway to do WRITE related actions with an user and READ with other. Where i work the db is replicated (mysql) and we have a single user which performs all the INSERT/DELETE/UPDATE on the master db while all the SELECT are done on the slave servers with another user. To do this we use our own insert/update/delete/select functions.

    Now we’re thinking about moving to symfony but we wouldn’t like to lose this capability as this allows us to remove lot’s a heavy queries on the master db. Do you know of a plugin or something which allows us to do this ?

  9. marco simão on February 12th, 2009 at 4:50 am:

    Hey man, seems obvious but when you dont know the solution… LOL
    I’ve figured that i would need to pass the parameter $con, just didnt know how to get the connection. Thanks a lot!

Leave a Reply