Symfony: change database dynamically

let’s say I have 3 databases:

  • prefix_db1
  • prefix_db2
  • prefix_db3

And I want to connect to them dynamically from the url like this http://localhost/my-project/web/app_dev.php/db1/books so I know which database to conenct to from the url (in this case prefix_db1)

And basically the idea was to prepare a listener that will be fired with each http request, get the database name from the url and then override doctrin’s params, something like this:
Within services.yml:

dynamic_connection:
        class: AppBundle\service\DynamicDBConnector
        arguments:  ['@request_stack']
        calls:
            - [ setDoctrineConnection, ['@doctrine.dbal.default_connection'] ]
        tags:
            - { name: kernel.event_listener, event: kernel.request, method: onKernelRequest }

My listener:

<?php    
namespace AppBundle\service;

use Doctrine\DBAL\Connection;
use Symfony\Component\HttpFoundation\RequestStack;
use Exception;

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

    /*
     * @var Request
     */
    private $request;


    public function __construct(RequestStack $requestStack)
    {
        $this->request = $requestStack->getCurrentRequest();

    }

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

    public function onKernelRequest()
    {
        if ($this->request->attributes->has('_company')) {

            $connection = $this->connection;
            $params     = $this->connection->getParams();

            $companyName = $this->request->get('_company');
            // I did the concatenation here because in paramaters.yml I just put the prefix (database_name: prefix_) so after the concatenation I get the whole database name "prefix_db1"
            $params['dbname'] = $params['dbname'] . $companyName;

            // 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;
    }
}

Now this worked very well I have tested it using a simple list of books and each time I change the url I get the list related to each database:

http://localhost/my-project/web/app_dev.php/db1/books // I get books of database prefix_db1

http://localhost/my-project/web/app_dev.php/db2/books // I get books of database prefix_db2

Now let’s get to the problem shall we 🙂:
The problem now is that when I secure my project with authentication system and try to login (of course each database has user table) using this url http://localhost/my-project/web/app_dev.php/db1/login
I get this exception :

An exception occured in driver: SQLSTATE[HY000] [1049] Base 'prefix_' unknown

As you can see symfony tried to login the user using the database_name declared in parameters.yml which means that the security_checker of symfony has been fired before my listener and before overriding Doctrine’s params.

My question:
Is there any way to fire my listener before any other http request listener ? or maybe an alternative solution to make sure that any request to database must be with the right database name.
Sorry for the long post.

EDIT:
From the official documentation of symfony:
https://symfony.com/doc/2.3/cookbook/event_dispatcher/event_listener.html

The other optional tag attribute is called priority, which defaults
to 0 and it controls the order in which listeners are executed (the
highest the priority, the earlier a listener is executed
). This is
useful when you need to guarantee that one listener is executed before
another. The priorities of the internal Symfony listeners usually
range from -255 to 255 but your own listeners can use any positive or
negative integer.

I set the priority of my listener to 10000:

tags:
    - { name: kernel.event_listener, event: kernel.request, method: onKernelRequest, priority: 10000 }

But the problem persist, still can’t fire my listener before symfony!

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

I found a solution
The idea is tochange the default Connection class that symfony uses to create a database connection:

doctrine:
    dbal:
        connections:
            default:
                wrapper_class: AppBundle\Doctrine\DynamicConnection
                driver: pdo_mysql
                host: '%database_host%'
                port: '%database_port%'
                dbname: '%database_name%'
                user: '%database_user%'
                password: '%database_password%'
                charset: UTF8

After that we can change the given params in the constructor:

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

class DynamicConnection extends Connection
{
    public function __construct(array $params, Driver $driver, $config, $eventManager)
    {
        $params['dbname'] = 'teqsdqsdqst';
        parent::__construct($params, $driver, $config, $eventManager);
    }
}

Now we just need to get the parameter from the url and set inside $params['dbname'].
In this way we make sure that symfony will always use this class to create the connection and we no longer need to fire listeners with http requestes

Solution 2

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

Solution 3

you should add the database name in your config.yml like this :

 orm:
        auto_generate_proxy_classes: '%kernel.debug%'
#        naming_strategy: doctrine.orm.naming_strategy.underscore
#        auto_mapping: true
        default_entity_manager: default
        entity_managers:
            default:
                connection: default
                mappings:
                    DataMiningBundle: ~
                    AppBundle: ~
                    UserBundle: ~

            your_second_db:
                connection: your_second_db (decalared in parameters.yml)
                mappings:
                    yourBundle: ~

and call it from your controller :

 $em = $doctrine->getConnection('your_second_db');

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply