php - Symfony 3 connection to multiple databases

Question

dynamic:
driver:   %database_driver%
host:     %database_host%
port:     %database_port%
dbname:   %database name%
user:     %database_user%
password: %database_password%
charset:  UTF8

Hi everybody

I would like to connect to multiple databases dynamically. I read this tutorial http://symfony.com/doc/current/cookbook/doctrine/multiple_entity_managers.html and it works as it was explained.

But my only problem is to change the value of dbname directly from my Controller to connect to all my databases.

It will be 24 hours that I am on this problem. if you have ideas I really need it

Answer 1

Your code example shows only one connection. You've to define to database connection, like the example from the documentation:

Easy hack: don't use an entity manager, but just connection.

If you want an easy way out, don't use an EntityManager at all. It might be overkill if you only want to get some information about the database and don't want to store information or use Entities. Then, just create a custom connection and use 'vanilla' SQL:

$this->get('doctrine.dbal.connection_factory')->createConnection($params);

Your question does not say how many database you expect. So let's describe two possibilities: a small number of (fixed) database or a dynamic connection.

Multiple connections, the simple way

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                driver:   pdo_mysql
                host:     '%database_host%'
                port:     '%database_port%'
                dbname:   '%database_name%'
                user:     '%database_user%'
                password: '%database_password%'
                charset:  UTF8
            model_a:
                driver:   pdo_mysql
                host:     '%database_hosta%'
                port:     '%database_porta%'
                dbname:   '%database_namea%'
                user:     '%database_usera%'
                password: '%database_passworda%'
                charset:  UTF8
            model_b:
                driver:   pdo_mysql
                host:     '%database_hostb%'
                port:     '%database_portb%'
                dbname:   '%database_nameb%'
                user:     '%database_userb%'
                password: '%database_passwordb%'
                charset:  UTF8
    orm:
        default_entity_manager: default
        entity_managers:
            default:
                connection: default
            model_a:
                connection: model_a
            model_b:
                connection: model_b

By default, connection default will be used. In your controller, you can switch to another connection:

$customerEm = $this->getDoctrine()->getManager('model_a');

And of course, you can dynamically choose your Manager:

//just an example, I don't know how you choose which database to use.
$managerName = $company->getModelName();
$em = $this->getDoctrine()->getManager($managerName);

Dynamically create EntityManager

The DoctrineBundle has no support for dynamic values (other than configured values like the first example). You can create your own EntityManager, if you don't want (or can't) define multiple connections because the dynamic table name.

Create a factory and define it a service:

class EntityManagerFactory
{
    public function createManager($dbname)
    {
        $isDevMode = false;
        $config = Setup::createAnnotationMetadataConfiguration(array(__DIR__."/src"), $isDevMode);
        // or if you prefer yaml or XML
        //$config = Setup::createXMLMetadataConfiguration(array(__DIR__."/config/xml"), $isDevMode);
        //$config = Setup::createYAMLMetadataConfiguration(array(__DIR__."/config/yaml"), $isDevMode);

        $conn = [
            'driver' => 'pdo_mysql',
            'username' => 'foo',
            'password' => 'foo',
            'dbname' => $dbname
        ];

        // obtaining the entity manager
        return EntityManager::create($conn, $config);
    }
}

And use it in your controller:

public function controllerAction()
{
    $em = $this->get(EntityManagerFactory::class)->getManager('model-name');
}