Symfony: Change Database Dynamically

Symfony: change database dynamically

Great solution but if you want get the parameter _company from the URL you can retrieve the container inside the constructor through the EventManager object passed in parameters and get the current request from it, in fact the container is injected into ContainerAwareEventManager the sub class of EventManager

class DynamicDBConnector extends Connection
{
    public function __construct($params, $driver, $config, $eventManager)
    {
        if(!$this->isConnected()){
            // Create default config and event manager if none given (case in command line)
            if (!$config) {
                $config = new Configuration();
            }
            if (!$eventManager) {
                $eventManager = new EventManager();
            }

            $refEventManager = new \ReflectionObject($eventManager);
            $refContainer = $refEventManager->getProperty('container');
            $refContainer->setAccessible('public'); //We have to change it for a moment

            /*
             * @var \Symfony\Component\DependencyInjection\ContainerInterface $container
             */
            $conrainer = $refContainer->getValue($eventManager);

            /*
             * @var Symfony\Component\HttpFoundation\Request
             */
            $request = $conrainer->get('request_stack')->getCurrentRequest();

            if ($request != null && $request->attributes->has('_company')) {
                $params['dbname'] .= $request->attributes->get('_company');
            }

            $refContainer->setAccessible('private'); //We put in private again
            parent::__construct($params, $driver, $config, $eventManager);
        }
    }
}

Dynamic doctrine database connection

I had a very similar issue recently. The solution that worked for me was a wrapper class.

<?php

declare(strict_types=1);

namespace App\DBAL;

use Doctrine\Common\EventManager;
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Driver;

final class MultiDbConnectionWrapper extends Connection
{
    public function __construct(
        array $params,
        Driver $driver,
        ?Configuration $config = null,
        ?EventManager $eventManager = null
    ) {
        parent::__construct($params, $driver, $config, $eventManager);
    }

    public function selectDatabase(string $dbName): void
    {
        if ($this->isConnected()) {
            $this->close();
        }

        $params = $this->getParams();
        $params['dbname'] = $dbName;
        parent::__construct($params, $this->_driver, $this->_config, $this->_eventManager);
    }
}

If you want to change a db host, change $params['host'] = 'XX.XX.XXX.XXX';

# config/packages/doctrine.yaml
doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        wrapper_class: App\DBAL\MultiDbConnectionWrapper

class ProductController extends AbstractController
{
    private EntityManagerInterface $em;
    public function __construct(EntityManagerInterface $em)
    {
        $this->em = $em;
    }
    public function add(Request $request): JsonResponse
    {
        $connection = $this->em->getConnection();
        if(!$connection instanceof MultiDbConnectionWrapper) {
            throw new \RuntimeException('Wrong connection');
        }

        $databaseName = 'some_db_name';
        $connection->selectDatabase($databaseName);

You can find full implementation in this repo.

Dynamic database connection in Symfony

This can be done by changing the data of the Connection class. You need to create your own Connection class and hook it up to the project.

Let's create the required class Connection

// src/Doctrine/DatabaseConnection.php

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Driver;

class DatabaseConnection extends Connection
{

    public function __construct(array $params, Driver $driver, $config, $eventManager )
    {
        // First, let symfony connect to the default database from env so that a Connection instance appears in order to execute a sql query to get the necessary data
        parent::__construct($params, $driver, $config, $eventManager);

        // Getting data and changing it
        $user_database =  $this->executeQuery('SELECT * FROM database WHERE user_id = :user_id',['user_id'=>1])->fetch();
        $params['dbname'] = $user_database['dbname'];
        $params['user'] =  $user_database['user'];
        $params['password'] = $user_database['password'];
        parent::__construct($params, $driver, $config, $eventManager);

    }
}

Note: As you can see in the example, to begin with, we connect to our main database from .env so that we can access the desired database by executing an sql query. After receiving the data, we modify it and call the parent's constructor method again to replace the Connection instance with the database we want.

Initialize DatabaseConnection

# config/packages/doctrine.yaml
doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        wrapper_class: App\Doctrine\DatabaseConnection

Note: Here we have connected our handler using the DBAL wrapperClass option

Dynamic database connection in Symfony 4

You're trying to rely on ability of Symfony services definition to use expressions for defining certain aspects of services. However you need to remember that this functionality is part of Dependency Injection component which is able (but not limited to) to use configuration files for services. To be more precise - this functionality is provided by configuration loaders, you can take a look here for example of how it is handled by Yaml configuration loader.

On the other hand configuration for Doctrine bundle, you're trying to use is provided by Config component. A fact that Dependency Injection component uses same file formats as Config component do may cause an impression that these cases are handled in the same way, but actually they're completely different.

To sum it up: expression inside Doctrine configuration does not work as you expecting because Doctrine bundle configuration processor doesn't expect to get an Expression Language expression and doesn't have support for handling them.

While explanations given above are, hopefully, answers your question - you're probably expecting to get some information about how to actually solve your problem.

There is at least 2 possible ways to do it, but choosing correct way may require some additional information which is out of scope of this question.

1. In a case if you know which connection to choose at a time of container building (your code assumes that it is a case, but you may not be aware about it) - then you should use compiler pass mechanism yo update Doctrine DBAL services definitions (which may be quite tricky). Reason for this non-trivial process is that configurations are loaded at the early stages of container building process and provides no extension points. You can take a look into sources if necessary. Anyway, while possible, I would not recommend you to go in this way and most likely you will not need it because (I suppose) you need to select connection in runtime rather then in container building time.

2. Probably more correct approach is to create own wrapper of DBAL Connection class that will maintain list of actual connections and will provide required connection depending on your application's logic. You can refer to implementation details of DBAL sharding feature as example. Wrapper class can be defined directly through Doctrine bundle configuration by using wrapper_class key for dbal configuration

Change dynamically the Data Base - Symfony

You can create and use EntityManagers dynamically:

$connection = \Doctrine\DBAL\DriverManager::getConnection([
  // connection parameters stored in config or other database
]);
$config = \Doctrine\ORM\Tools\Setup::createConfiguration($env);
// additional configuration e.g. of entities
$em = \Doctrine\ORM\EntityManager::create($connection, $config);

And your EntityManager is ready.

Symfony2 - Dynamic Doctrine Database Connections at Runtime

If $em is existing entity manager and you want to reuse it's configuration, you can use this:

$conn = array(
    'driver'   => 'pdo_mysql',
    'user'     => 'root',
    'password' => '',
    'dbname'   => 'foo'
);

$new = \Doctrine\ORM\EntityManager::create(
    $conn,
    $em->getConfiguration(),
    $em->getEventManager()
);

---

Related Topics