[FIXED] Dynamic doctrine database connection

Issue

I have a project of an monitoring application under symfony 5.

Currently I've done most of the work working with a single database containing logs from another application. What I'm looking to do now is to be able to connect dynamically to a database (MSSQL). For the moment the connection to the database is done via the file "doctrine.yaml".

Doctrine.yaml

 doctrine:
  dbal:
   default_connection: default
    connections:
     default:
       driver: pdo_sqlsrv
       host: 192.168.1.33
       port: null
       dbname: 'job'
       user: 'sa'
       password: 'Lasernet@2020'
       charset: utf8mb4

   orm:
    default_entity_manager: default
    entity_managers:
     default:
       connection: default
       mappings:
        Main:
         is_bundle: false
         type: annotation
         dir: "%kernel.project_dir%/src/Entity/Main"
         prefix: 'App\Entity\Main'
         alias: default

I did this for the dynamic connection

DynamicConnection.php

<?php

namespace App\Doctrine;


class DynamicConnection {


    public function __construct($dbname,$user,$password,$host,$driver,$port)
    {
        $this->dbname = $dbname;
        $this->user = $user;
        $this->password = $password;
        $this->host = $host;
        $this->driver = $driver;
        $this->port = $port;

    }

    public function changeDatabase(){



        $connectionParams = array(
            'dbname' => $this->dbname,
            'user' => $this->user,
            'password' => $this->password,
            'host' => $this->host,
            'driver' => $this->driver,
            'port' => $this->port
        );

        $conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams);
    
        if($conn){
            return $conn;
        }else{
            return "no";
        }

    }


    public function getParams()
    {
        $connectionParams = array(
            'driver' => $this->driver,
            'host' => $this->host,
            'port' => $this->port,
            'dbname' => $this->dbname,
            'user' => $this->user,
            'password' => $this->password,
            'charset' => "utf8mb4",
            'driverOptions' => [],
            'defaultTableOptions' => []
        );

        return $connectionParams;
    }

}

And in my Controller

/**
 * @Route("/testconnection", name="test_connect")
 */
public function testConnection(){


    $dbname = "job";
    $user = "sa";
    $password = "Lasernet@2020";
    $host = "192.168.1.34";
    $driver = "pdo_sqlsrv";
    $port = null;

    $connection = new DynamicConnection($dbname,$user,$password,$host,$driver,$port);
    $params = $connection->getParams();


    $newEm = EntityManager::create($params,$this->em->getConfiguration(), $this->em->getEventManager());


    $job = $newEm->getRepository(Job::class)->findAll();
    dd($job);
        
}

The problem is that the findAll() returns all the records of the database "192.168.1.33" not the one of "192.168.1.34" which behaves a different number of records.

Is there another way to connect dynamically to the database or to modify the "doctrine.yaml" file directly using JS for example, but I don't think this is the best solution.

If someone has a solution to my problem to make my findAll() return the info from the 192 database .168.1.34

Small precision the two databases have the same structure of tables, fields, etc.

Solution

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.

Answered By - Karol Dabrowski