Multiple database symfony configuration (doctrine)

Scene from ingenious "Platoon" by Oliver Stone (1986)

It may happen that we'll need to connect to many databases inside one project. As this problem is not very common, I'll try to present the easiest way to manage multiple database connections.


First of all, edit the databases.yml file and set all database connections with appropriate labels. This means, the doctrine connection label means nothing and therefore it should be changed. Usually, in such case, apart from all external databases, the project itself has its own database which connection to is called master. Below is an example of multiple connection project databases.yml file:

  master:
    class: sfDoctrineDatabase
    param:
      dsn:      mysql:host=localhost;dbname=admin
      username: admin
      password: Mt5iA0Lcm0

  production:
    class: sfDoctrineDatabase
    param:
      dsn:      mysql:host=localhost;dbname=old_shop
      username: shop
      password: Mt5iA0Lcm0

  shop_one:
    class: sfDoctrineDatabase
    param:
      dsn:      mysql:host=localhost;dbname=shop_one
      username: shop
      password: Mt5iA0Lcm0

  shop_two:
    class: sfDoctrineDatabase
    param:
      dsn:      mysql:host=localhost;dbname=shop_two
      username: shop
      password: Mt5iA0Lcm0

This is an example database list of a project that migrates all data from one shop (which is, for example, too old and too bad to be mantained anymore) into two new distinct shops (each shop is deployed using open source software, like magento or prestashop) and one admin database (which is created for the project itself).

Now we need to apply a small change to the schema.yml files:

connection: master

This should be added in the beginning of each schema yml file, defining the connection name. It means, that all models defined in such schema file will be bound to chosen database. Remember to execute the clear cache command, because otherwise you may force difficulties with class files autoload (also when you're using plugins).

The main feature in connecting different classes with distinct databases is to have class-database connection binding set properly. In each base class, you will see

// Connection Component Binding
Doctrine_Manager::getInstance()->bindComponent('Product', 'master');

This line of code states, that all Product objects will be fetched from master database connection.

the structure to be chosen

From this moment, the model classes generated by the framework will include classes from all databases. And now here comes the main question to answer - how complex is my project and do I have to follow all symfony default mechanisms? Answer to this question is essential, because many implementation solutions will depend on the decision made. I'll briefly describe two alternative approaches:

• Stick to the defaults. Suppose, you'll have about one hundred tables in each database. If we have 4 connections set, it gives 400 distinct model classes (to be precise: 400 base model classes, 400 model classes and 400 table classes, which is 1200).They all should have connection bindings set correctly, anyway, searching through 400 classes in a tree-view may be frustrating - this is the main disadvantage. Another one is that you'll have to generate all classes even if you won't need them (e.g. if you use open source software, you may need only some of them for your project). And one more disdvantage - you'll also have to keep all form and filter classes for all tables even if they're not needed. But the advantage is that if you modify anything in schema.yml file, base classes will be updated automatically and you don't have to care about that.
• Choose your own lib directory structure. The task now is to separate all generate classes into different directories so that everything will be in order. The basic solution is to organise one directory for each database connection. The good things about this solution are: everything is more structured, easier to find; you don't have to keep all classes (if you need only few). And you don't have to keep all model/form/filter classes that doctrine generates - you choose them manually. The disadvantage is that all modifications made in schema files have to be updated manually (replace old version of your base model class in a special directory with a just generated new base model class). Subversion (or other versioning systems) helps greatly to keep everything up to date and user-friendly. After running symfony doctrine:generate-model you update the files you want (which means, destination base class files are replaced with their new versions) - then you commit your changes and afterwards clean it (remove model directory and update it from repository, so that all files you changed are saved in the repository and all the rest - which you don't want - is deleted).
  

Although the second option may seem too complicated or weird (or even both at the same time), I definitely prefer it. One of its great advantages I still haven't to mentioned is that it if you use your own lib classes structure, it allows you to do advanced and highly complex solutions (example: you've got few shops powered by the same OS software. And you need one tool to manage them all - an administration application. If you want to follow symfony default mechanisms, you have a very big problem, since table names will surely be the same in different shops. And connection binding is really basic in doctrine, since such problem is really rare. Even if you use distinct prefixes to set table names for distinct shop databases, you'll still force a big problem, since you'll need to copy-paste all methods between analogical classes, let's say, you define getFullName method in Product class - you'll have to copy this method into XxxProduct, YyyProduct, ZzzProduct and so on, if you want to stick to symfony defaults. In my opinion it's enough to prove, that symfony defaults are good as long as the project is easy, but if it's really advanced, non-standard solutions are usually the only way). Sticking to symfony defaults and being ready to regenerate model at the same time disables some higly complex features.

look out for the bug!

Unfortunately, there's a bug in symfony core, dealing with multiple database connections. It has been reported some time ago on trac, however, the bug is still there. In short, in some cases DoctrineManager takes the last connection from the databases.yml list, instead of the one you choose (you can read the details on the ticket page). To avoid problems with it, I pay attention to have all base model classes defined with the correct connection binding - and this is enough to get all project working.


As you can see, you're already ready to go - your site can handle multiple database connections!