Symfony2, Dynamic Db Connection/Early Override of Doctrine Service

Symfony2, Dynamic DB Connection/Early override of Doctrine Service

Here is the new and improved non-reflection version

#services.yml
acme_app.dynamic_connection:
    class: %acme.dynamic_doctrine_connection.class%
    calls:
        - [setDoctrineConnection, [@doctrine.dbal.default_connection]]

<?php

namespace Acme\Bundle\AppBundle;

use Doctrine\DBAL\Connection;
use Symfony\Component\HttpKernel\Exception\ServiceUnavailableHttpException;
use Exception;

class DynamicDoctrineConnection
{
    /**
     * @var Connection
     */
    private $connection;

    /**
     * Sets the DB Name prefix to use when selecting the database to connect to
     *
     * @param  Connection       $connection
     * @return SiteDbConnection $this
     */
    public function setDoctrineConnection(Connection $connection)
    {
        $this->connection = $connection;

        return $this;
    }

    public function setUpAppConnection()
    {
        if ($this->request->attributes->has('appId')) {
            $connection = $this->connection;
            $params     = $this->connection->getParams();

            // we also check if the current connection needs to be closed based on various things
            // have left that part in for information here
            // $appId changed from that in the connection?
            // if ($connection->isConnected()) {
            //     $connection->close();
            // }

            // Set default DB connection using appId
            //$params['host']   = $someHost;
            $params['dbname'] = 'Acme_App'.$this->request->attributes->get('appId');

            // Set up the parameters for the parent
            $connection->__construct(
                $params, $connection->getDriver(), $connection->getConfiguration(),
                $connection->getEventManager()
            );

            try {
                $connection->connect();
            } catch (Exception $e) {
                // log and handle exception
            }
        }

        return $this;
    }
}

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

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 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

symfony2 dynamic database connection using doctrine

It seems to me that using Doctrines ODM is not the right way to approach this. You can still use Doctrine to connect to databases and query them. But if you have no entity classes the use of an entity manager seems to be inappropriate.

Use Doctrine for Connection handling

Here is how you create a connection to a Database with the doctrine Connection class:

/** @var \Doctrine\Bundle\DoctrineBundle\ConnectionFactory $connectionFactory */
$connectionFactory = $this->getContainer()->get('doctrine.dbal.connection_factory');
$connection = $connectionFactory->createConnection(
    array('pdo' => new \PDO("mysql:host=$hostname;dbname=$dbname", $username, $password))
);

Now you can use $connection as a simple PDO object:

$connection->executeQuery('SELECT * FROM your_table');

You could add this code as a service to make it accessible everywhere.

If you want to connect to a different database for a different domain you can use this code to identify the domain:

$this->getRequest()->getHost();

To access the domain in an action do this:

public function yourAction(Request $request, /* ... */)
{
    // the Controller extends the Container. So need to get it here:
    $connectionFactory = $this->get('doctrine.dbal.connection_factory');

    // also access the domain like this:
    $domain = $request->getHost();
}

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.

---

Related Topics