Symfony 2 : multiple and dynamic database connection

Question

I am quite new to SF2 and I was wondering how I could manage connections to severals databases into ONE bundle. For the moment I have this solution - which works fine - but I don't know if it is the right way to do it....

in myBundle\Ressource\config\config.yml :

doctrine:
dbal:
    default_connection:       default
    connections:
        default:
            dbname:           SERVER
            user:             root
            password:         null
            host:             localhost
        client:
            dbname:           CLIENT_134
            user:             root
            password:         null
            host:             localhost
orm:
    default_entity_manager:   default
    entity_managers:
        default:
            connection:       default
            mappings:
                MyBundle: ~
        client:
            connection:       client
            mappings:
                MyBundle: ~

And then, in order to switch to one of the BD or the other, I do :

$O_ressource=  $this->get('doctrine')->getEntityManager('client');
$O_ressource=  $this->get('doctrine')->getEntityManager('default');

So guys, do you think it is a good way to manage this?

And my second question is :

how to set up dynamic database connection? I mean I have 100 databases in my system and I can't set all them in my config.yml file. So I would like to be able to change database on the fly.

Thanks for the help!

Answer 1

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 [zulus@w3des.net]
 */
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

Answer 2

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.

Answer 3

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);
}