Symfony 2 : multiple and dynamic database connection

If you use ConnectionFactory, your event subscribers attached to the connection will stop working, for example stofDoctrineExtensions.

Here is my method. I have as with ConnectionFactory have empty connection and EntityManager. While working I just replace connection configuration by Reflections. Works on SF 2.0.10 ;)

class YourService extends ContainerAware
{ 

  public function switchDatabase($dbName, $dbUser, $dbPass) 
  {
    $connection = $this->container->get(sprintf('doctrine.dbal.%s_connection', 'dynamic_conn'));
    $connection->close();

    $refConn = new \ReflectionObject($connection);
    $refParams = $refConn->getProperty('_params');
    $refParams->setAccessible('public'); //we have to change it for a moment

    $params = $refParams->getValue($connection);
    $params['dbname'] = $dbName;
    $params['user'] = $dbUser;
    $params['password'] = $dbPass;

    $refParams->setAccessible('private');
    $refParams->setValue($connection, $params);
    $this->container->get('doctrine')->resetEntityManager('dynamic_manager'); // for sure (unless you like broken transactions)
  }
}

UPDATE:

More elegant solution for doctrine 2.2 / sf 2.3 (without relection), created for php5.4 (I love new array initializer :D) We can use doctrine feature called connection wrapper, see http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/portability.html

This example use session service for temporary storing connection details.

At first we have to create special connection wrapper:

namespace w3des\DoctrineBundle\Connection;

use Doctrine\DBAL\Connection;
use Symfony\Component\HttpFoundation\Session\Session;
use Doctrine\Common\EventManager;
use Doctrine\DBAL\Events;
use Doctrine\DBAL\Event\ConnectionEventArgs;

/*
 * @author Dawid zulus Pakula [[email protected]]
 */
class ConnectionWrapper extends Connection
{

const SESSION_ACTIVE_DYNAMIC_CONN = 'active_dynamic_conn';

/**
 * @var Session
 */
private $session;

/**
 * @var bool
 */
private $_isConnected = false;

/**
 * @param Session $sess
 */
public function setSession(Session $sess)
{
    $this->session = $sess;
}

public function forceSwitch($dbName, $dbUser, $dbPassword)
{
    if ($this->session->has(self::SESSION_ACTIVE_DYNAMIC_CONN)) {
        $current = $this->session->get(self::SESSION_ACTIVE_DYNAMIC_CONN);
        if ($current[0] === $dbName) {
            return;
        }
    }

    $this->session->set(self::SESSION_ACTIVE_DYNAMIC_CONN, [
        $dbName,
        $dbUser,
        $dbPass
    ]);

    if ($this->isConnected()) {
        $this->close();
    }
}

/**
 * {@inheritDoc}
 */
public function connect()
{
    if (! $this->session->has(self::SESSION_ACTIVE_DYNAMIC_CONN)) {
        throw new \InvalidArgumentException('You have to inject into valid context first');
    }
    if ($this->isConnected()) {
        return true;
    }

    $driverOptions = isset($params['driverOptions']) ? $params['driverOptions'] : array();

    $params = $this->getParams();
    $realParams = $this->session->get(self::SESSION_ACTIVE_DYNAMIC_CONN);
    $params['dbname'] = $realParams[0];
    $params['user'] = $realParams[1];
    $params['password'] = $realParams[2];

    $this->_conn = $this->_driver->connect($params, $params['user'], $params['password'], $driverOptions);

    if ($this->_eventManager->hasListeners(Events::postConnect)) {
        $eventArgs = new ConnectionEventArgs($this);
        $this->_eventManager->dispatchEvent(Events::postConnect, $eventArgs);
    }

    $this->_isConnected = true;

    return true;
}

/**
 * {@inheritDoc}
 */
public function isConnected()
{
    return $this->_isConnected;
}

/**
 * {@inheritDoc}
 */
public function close()
{
    if ($this->isConnected()) {
        parent::close();
        $this->_isConnected = false;
    }
}
}

Next register it in your doctrine configuration:

…

connections:
  dynamic:
    driver:   %database_driver%
    host:     %database_host%
    port:     %database_port%
    dbname:   'empty_database'
    charset:  UTF8
    wrapper_class: 'w3des\DoctrineBundle\Connection\ConnectionWrapper'

And our ConnectionWrapper is properly registered. Now session injection.

First create special CompilerPass class:

namespace w3des\DoctrineBundle\DependencyInjection\CompilerPass;

use Symfony\Component\DependencyInjection\Compiler\CompilerPassInterface;
use Symfony\Component\DependencyInjection\ContainerBuilder;
use Symfony\Component\DependencyInjection\Definition;
use Symfony\Component\DependencyInjection\Reference;

class ConnectionCompilerPass implements CompilerPassInterface
{

/**
 * {@inheritDoc}
 */
public function process(ContainerBuilder $container)
{
    $connection = $container
    ->getDefinition('doctrine.dbal.dynamic_connection')
    ->addMethodCall('setSession', [
        new Reference('session')
    ]);
}
}

And we record our new compiler class in *Bundle class:

public function build(ContainerBuilder $container)
{
    parent::build($container);
    $container->addCompilerPass(new ConnectionCompilerPass());
}

And that its all!

Connection will be created on demand, based on session properties.

To switch database, just use:

$this->get('doctrine.dbal.dynamic_connection')->forceSwitch($dbname, $dbuser, $dbpass);

Advantages

1. No more reflection
2. Creation on demand
3. Elegant and powerfull

Disadvantages

1. You have to manualy cleanup your entity manager, or create special doctrine event for this
2. Much more code

You can look into Symfony\Bundle\DoctrineBundle\ConnectionFactory, using the container service doctrine.dbal.connection_factory:

$connectionFactory = $this->container->get('doctrine.dbal.connection_factory');
$connection = $connectionFactory->createConnection(array(
    'driver' => 'pdo_mysql',
    'user' => 'root',
    'password' => '',
    'host' => 'localhost',
    'dbname' => 'foo_database',
));

That's just a quick example, but it should get you started.

I run into the same needing to have different databases with the same schema for each client. Since symfony 2.3, after the deprecation of the method resetEntityManager, i noticed that the code run well without closing the connection and without resetting the (old Entity) Manager.

this is my current working code:

public function switchDatabase($dbName, $dbUser, $dbPass) {
    $connection = $this->container->get(sprintf('doctrine.dbal.%s_connection', 'dynamic_conn'));

    $refConn = new \ReflectionObject($connection);
    $refParams = $refConn->getProperty('_params');
    $refParams->setAccessible('public'); //we have to change it for a moment

    $params = $refParams->getValue($connection);
    $params['dbname'] = $dbName;
    $params['user'] = $dbUser;
    $params['password'] = $dbPass;

    $refParams->setAccessible('private');
    $refParams->setValue($connection, $params);
}