Symfony 5 dynamic database connection

The other day I was facing a problem to connect Symfony to multiple databases, there is a way to have multiple database connection in the official documentation but in that method you should define all the databases from the start, which means you can only pick a database that already has defined in your controller, you can't just connect to a database in real time.

Sometimes, you may need to specify the connection parameters inside the controller itself, for example, you may want to connect to a database where the database name was specified in the URL or you want to insert data into a different databases where the database connection parameters are inside an other database... It's getting complicated, I know! Let's just say in this blog I will show how to connect dynamically to a database inside the controller.

Let's start by creating a new PHP class called DynamicConnection.php and add it inside src folder, we can even create a new folder called Doctrine inside src and put the file over there so things could be more organized.

/src/Doctrine/DynamicConnection.php:

<?php
	namespace App\Doctrine;
	use Doctrine\DBAL\Connection;
	use Doctrine\DBAL\Driver;
	class DynamicConnection extends Connection
	{
	public function __construct(array $params, Driver $driver, $config, $eventManager)
	{
	parent::__construct($params, $driver, $config, $eventManager);
	}
	public function changeDatabase(string $host,string $port,string $user,string $password,string $dbName)
	{
	$params = $this->getParams();
	if ($this->isConnected())
	$this->close();
	$params['url'] = "mysql://".$user.":".$password."@".$host.":".$port."/".$dbName;
	$params['host'] = $host;
	$params['port'] = $port;
	$params['dbname'] = $dbName;
	$params['user'] = $user;
	$params['password'] = $password;
	parent::__construct(
	$params,
	$this->_driver,
	$this->_config,
	$this->_eventManager
	);
	}
	}

Now let's go to /config/packages/doctrine.yaml and make some changes:

doctrine:
	dbal:
	default_connection: dbname
	connections:
	dbname:
	# configure these for your database server
	url: '%env(DATABASE_URL)%'
	driver: 'pdo_mysql'
	server_version: '5.7'
	charset: utf8mb4
	mapping_types:
	enum: string
	wrapper_class: App\Doctrine\DynamicConnection
	orm:
	default_entity_manager: dbname
	entity_managers:
	dbname:
	connection: dbname
	mappings:
	Name:
	is_bundle: false
	type: annotation
	dir: '%kernel.project_dir%/src/Entity'
	prefix: 'App\Entity'
	alias: dbname

You can refer to How to Work with multiple Entity Managers and Connections for more information but what's important is adding the wrapper_class.

Now inside the controller it's a little bit messy, this is how you change the database:

// Change to user DB
$conn = $this->getDoctrine()->getConnection();
$conn->changeDatabase($this->TARGET_HOST,$this->TARGET_PORT,$this->TARGET_USER,$this->TARGET_PASSWORD,$user);

And this is how you create a new entity manager:

$em = $this->getDoctrine()->getManager();
$params = $this->getDoctrine()->getConnection()->getParams();
$params['dbname'] = $user;
$newEm = EntityManager::create($params,$em->getConfiguration(), $em->getEventManager());

It's a a little bit annoying as each time you have to do all that to change the database, it gets more complicated when you are working with two or more databases at the same time, like for example getting data from an exciting database and insert them into an other base.
Also I don't things this code is safe as you are changing the database in real time! so yeah it does the trick but be careful.   

1/02/2020