Using multiple databases with Symfony2 and Doctrine2

I used to believe in vampires, vampires were kick ass when I was young! Similarly in my recent years I used to believe that it wasn't possible to use multiple databases with relationships with Symfony2 and Doctrine2, however the modern, wiser me no longer believes in the existence of blood sucking pale corpses, and similarly now knows that one can have multiple databases within a Symfony2/Doctrine2 app. Here's how to do it.

One connection, multiple databases

You will need to use only connection that spans all of your databases, if you want to build a relationship between entities then they must use the same connection. Do to this you will need a user that has access to all of the databases the you wish to access. Setup this user as per usual within your Symfony2 application, for the database name just select one of the databases, it doesn't matter which one.

No extra Doctrine config is needed to get this working, though as a side note you may want to use Gedmo's Doctrine Extensions, they aren't relevant to this topic but they are awesome.

Configuring entities

The key to getting multiple databases to work is within your entity classes, you need to specify the table name of the entity with a prefix of the name of the database to which the table belongs. Here is an example using annotations:

<?php
namespace Demo\UserBundle\Entity;

use DoctrineORMMapping as ORM;

/**
 * Demo\UserBundle\Entity\User
 *
 * @ORMTable(name="users.User")
 */
class User implements
{
  /* ... */
}

As you can see the table name has been prefixed by the database name, in this case 'admin'.

Now let's add in another entity in a different bundle:

<?php
namespace Demo\PostBundle\Entity;

use DoctrineORMMapping as ORM;

/**
 * Demo\PostBundle\Entity\Post
 *
 * @ORMTable(name="posts.Post")
 */
class Post implements
{
  /* ... */
}

As long as the database user disignated in your app as access to both databases Doctrine will shift seemlessly between the two.

Adding in relations

Doctrine2's relations will work as per usual, with no extra configuration at all, which is something that I didn't expect at all when I started looking into this. There are some articles and stackoverflow answers kicking around that seem to suggest that a load of extra is required to get multiple db relations working, but they worked straight away when I tried them. I can only surmise that this functionality has been added in newer versions of Doctrine2.

Anyway, let's add in a relation between posts and users like so:

<?php
namespace Demo\PostBundle\Entity;

use DoctrineORMMapping as ORM;

/**
 * Demo\PostBundle\Entity\Post
 *
 * @ORMTable(name="posts.Post")
 */
class Post implements
{
    /**
     * @ORM\ManyToOne(targetEntity="\Demo\UserBundle\Entity\User")
     **/
    private $user;

    /* ... */

    /**
     * Set user
     *
     * @param \Demo\UserBundle\Entity\Site $site
     * @return Post
     */
    public function setUser($user)
    {
        $this->user = $user;
    
        return $this;
    }

    /**
     * Get user
     *
     * @return \Demo\UserBundle\Entity\Site
     */
    public function getUser()
    {
        return $this->user;
    }
}

This relation will function as per usual, with no extra work, Symfony's forms will be able to update the user field as per usual and called $post->getUser() will return a User entity as expected and you will also be able to use the relations in Doctrine queries just as any other relation.

Here's an example project that demonstrates the technique laid out above symfony2-multiple-db-example.