Symfony: change database dynamically

Question

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!

@goto each database has it's own users so I need to know which one before connecting to it — SlimenTN
– SlimenTN, Commented Nov 5, 2018 at 9:50
What does your database have in common? you can create 3 separate instance of your app, one of each database instead. (Sry I can't help you for the prefix problem) — goto
– goto, Commented Nov 5, 2018 at 9:54
they are all the same, in fact in the future will be more than 3 database they will be created dynamically using doctrine commands doctrine:database:create doctrine:schema:update --force — SlimenTN
– SlimenTN, Commented Nov 5, 2018 at 9:59
Possible duplicate of Symfony2, Dynamic DB Connection/Early override of Doctrine Service — famas23
– famas23, Commented Nov 6, 2018 at 14:17

Sajad Torkamani · Accepted Answer · 2024-10-06 16:06:34Z

13

I found a solution. The idea is to change 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 ensure that Symfony will always use this class to create the connection and we no longer need to fire listeners with http requests.

edited Oct 6, 2024 at 16:06

Sajad Torkamani

7461 gold badge10 silver badges20 bronze badges

answered Nov 5, 2018 at 15:56

SlimenTN

3,6248 gold badges39 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Giovani Over a year ago

How do you get parameters from the constructor method? I've tried to use RequestStack but gives me null.

SlimenTN Over a year ago

@Giovani please check the marked answer maybe it will help because I'm following it and it's working fine for me :)

isom · Accepted Answer · 2018-11-14 13:33:33Z

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

Cinava · Accepted Answer · 2023-12-21 19:49:08Z

Another way to do it is using doctrine.dbal.connection_factory as proposed by TvC in Symfony2, Dynamic DB Connection/Early override of Doctrine Service. I was able to it in Symfony 6:

#services.yaml
App\Routing\DBAL\DatabaseConnectionFactory:
        decorates: doctrine.dbal.connection_factory
        arguments:       
            $requestStack: '@request_stack'
            

#DatabaseConnectionFactory.php
namespace App\Routing\DBAL;

use Doctrine\Common\EventManager;
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Driver;
use Doctrine\Bundle\DoctrineBundle\ConnectionFactory;

class DatabaseConnectionFactory extends ConnectionFactory
{
    private $requestStack;

    public function __construct( $requestStack )
    {
        $this->requestStack = $requestStack;    
    }

    public function createConnection(
        array $params,
        Configuration $config = null,
        EventManager $eventManager = null,
        array $mappingTypes = []
    )
    {
       $uri = null;
       $request = $this->requestStack->getCurrentRequest();

       if( $request ) {
            $uri = $request->getUri();
       }

       if( $uri && str_contains($uri, 'c/lmh/pathology') ) {
            //here logic to get dbname
            $dbName = 'Tenant2';
            $params['dbname'] = $dbName;
        } else {
            //don't change default dbname
        }
        return parent::createConnection($params, $config, $eventManager, $mappingTypes);
    }

}

Haythem Mrad · Accepted Answer · 2018-11-05 10:04:11Z

-2

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

answered Nov 5, 2018 at 10:04

Haythem Mrad

377 bronze badges

2 Comments

SlimenTN Over a year ago

No I can't because the databases will be created dynamically from another form, they are not predefined :)

walidtlili Over a year ago

i dont think that @SlimenTN didn't know this mr haythem

Collectives™ on Stack Overflow

Symfony: change database dynamically

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related