728x90 AdSpace

Thursday, January 2, 2020

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.

Symfony 5 dynamic database connection

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.   

0 comments:

Post a Comment

Item Reviewed: Symfony 5 dynamic database connection Rating: 5 Reviewed By: Hosni
Scroll to Top