Kris Wallsmith

Managing Master and Slave Database Connections with symfony and Doctrine

This is one of those things that should be much easier than it is. Since I started using Doctrine a few months ago, I’ve been impressed with how complete it is, but I can definitely see room for improvement as the project matures. Setting up read and write connections is one of those areas.

My challenge was to get the project I’m on ready to be hosted on Amazon EC2, with the help of RightScale. If your hosting on the cloud and have the funds available, check out RightScale. They have a number of server templates with best practices already implemented, and great online tutorials.

The environment I’m setting up includes one master and (potentially) multiple slave MySQL servers (setup with EBS storage). Setting up these servers was a piece of cake, thanks to the heavy lifting RightScale had done for me with their server templates and tutorials. The challenge I met was in getting symfony (1.2) and Doctrine (1.1) setup to choose the right connection for each query.

Code Slingin’ Below

The first order of business is organizing the database connections. I decided to go with a simple naming syntax and assume the master connection is named master and the slave connection names begin with slave. I added these methods to ProjectConfiguration so these connections are accessible:

Now I can easily grab the master connection by calling ProjectConfiguration::getActive()->getMasterConnection() from anywhere in my project, and get a slave connection by calling ->getSlaveConnection(). This is cool, but it turned out to be the easiest part, by far.

Smartly Forcing a Master or Slave Connection

I started with a tutorial on master and slave connections in the Doctrine documentation repository. This was easy to implement, but it’s not a complete solution. There are write queries in the Doctrine core that don’t go though either Doctrine_Query::preQuery() or Doctrine_Record::save(), so they end up using the current connection, which is usually the slave connection.

I came up with a solution, but I’m witholding judgement on how stable it is. I’m using Doctrine events to filter all queries run through Doctrine and swap out the PDO object used inside the Doctrine_Connection object with either the master or slave PDO object:

Then simply add this listener to each of you Doctrine_Connection objects by using the (undocumented?) ProjectConfiguration::configureDoctrineConnection() method:

This seems to be working for my purposes, but I feel a bit dirty hacking into Doctrine_Connection objects like this. For a brief moment as each query is run, the Doctrine_Connection object that includes the name master may in fact include a slave PDO object, and vice versa.

One More Thing

This new environment includes multiple database servers, so it naturally includes multiple load-balanced web servers, which precludes the use of sfSessionStorage because of it’s reliance on a local filesystem. I decided to go with sfPDOSessionStorage for the time being.

I was expecting to have to extend sfPDOSessionStorage to choose between master and slave connections for each storage operation. However, upon looking at the code I realized that every operation may possibly include a write query, so I just specified the master connection in factories.yml:

NOTE This configuration assumes there is always a connection named master, whereas the method in ProjectConfiguration includes a fallback to the current Doctrine connection.