Using dynamic database names using config.php and doctrine.yaml in Symfony

Question

I had setup a symfony project. All my database connections are in app/.env.

This is the way I had configured in .env file:

DATABASE_URL=mysql://root@127.1.0.1:3306/abcdefg

Now I want to use a .php file like config.php where I can store the values of database configurations in it and the application should also use the same instead of taking values from .env file.

This is to connect with different database's based on the application URL. So the database name depends on the URL.

To make it dynamic, I want to use a PHP file instead of .env file.

Answer 1

(im assuming you are using Symfony version 4 or higher - but should also work in earlier versions with slight modifications)

Part 1 - loading container parameters from php

1. Create file "config/my_config.php" like this:

<?php

$container->setParameter('my_param', 'something1');

$elements = [];
$elements[] = 'yolo1';
$elements[] = 'yolo2';

$container->setParameter('my_param_which_is_array', $elements);

2. In your services.yaml file import "my_config.php" like this:

imports:
    - { resource: my_config.php }

3. Clear cache.
4. Check if those parameters are loaded in container - for example by running following commands:

php bin/console debug:container --parameter=my_param
 ----------- ------------
  Parameter   Value
 ----------- ------------
  my_param    something1
 ----------- ------------

php bin/console debug:container --parameter=my_param_which_is_array
 ------------------------- -------------------
  Parameter                 Value
 ------------------------- -------------------
  my_param_which_is_array   ["yolo1","yolo2"]
 ------------------------- -------------------

If above steps work then you can use your parameters from container in your application.

Important warning: If you will store security credentials in such php file (db user and password etc) then make sure you are not adding it to repository along with code of rest of your application - so add it to ".gitignore" similarily as ".env" is added there.

For more info about handling of symfony parameters see https://symfony.com/doc/current/service_container/parameters.html (on the code snippets click the "PHP" tab instead of "YAML" to see PHP examples)

Part 2 - using different database depending on url(host) or CLI parameter

To dynamically choose database connection credentials we can use a doctrine connection factory. We will decorate default service 'doctrine.dbal.connection_factory' with our modified version:

Create new file "src/Doctrine/MyConnectionFactory.php":

<?php

namespace App\Doctrine;

use Doctrine\Bundle\DoctrineBundle\ConnectionFactory;
use Doctrine\Common\EventManager;
use Doctrine\DBAL\Configuration;
use Symfony\Component\Console\Input\ArgvInput;
use Symfony\Component\HttpFoundation\Request;

class MyConnectionFactory
{
    /**
     * @var array
     */
    private $db_credentials_per_site;

    /**
     * @var ConnectionFactory
     */
    private $originalConnectionFactory;

    public function __construct($db_credentials_per_site, ConnectionFactory $originalConnectionFactory)
    {
        $this->db_credentials_per_site = $db_credentials_per_site;
        $this->originalConnectionFactory = $originalConnectionFactory;
    }

    /**
     * Decorates following method:
     * @see \Doctrine\Bundle\DoctrineBundle\ConnectionFactory::createConnection
     */
    public function createConnection(array $params, Configuration $config = null, EventManager $eventManager = null, array $mappingTypes = [])
    {
        $siteName = $this->getSiteNameFromRequestOrCommand();

        if (!isset($this->db_credentials_per_site[$siteName])) {
            throw new \RuntimeException("MyConnectionFactory::createConnection - Unknown site name: {$siteName}");
        }

        return $this->originalConnectionFactory->createConnection(
            [
                'url' => $this->db_credentials_per_site[$siteName]['url'],
            ],
            $config,
            $eventManager,
            $mappingTypes
        );
    }

    /**
     * @return string
     */
    private function getSiteNameFromRequestOrCommand()
    {
        // If we are inside CLI command then take site name from '--site' command option:
        if (isset($_SERVER['argv'])) {
            $input = new ArgvInput();
            $siteName = $input->getParameterOption(['--site']);

            if (!$siteName) {
                throw new \RuntimeException("MyConnectionFactory::getSiteNameFromRequestOrCommand - You must provide option '--site=...'");
            }

            return (string) $siteName;
        }

        // Otherwise determine site name by request host (domain):
        $request = Request::createFromGlobals();
        $host = $request->getHost();
        switch ($host) {
            case 'my-blue-site.local.dev2':
                return 'blue_site';
            case 'redsite.local.com':
                return 'red_site';
        }

        throw new \RuntimeException("MyConnectionFactory::getSiteNameFromRequestOrCommand - Unknown host: {$host}");
    }
}

Now lets setup the decoration in services.yaml:

(you can read more about decorating of services here: https://symfony.com/doc/current/service_container/service_decoration.html)

    App\Doctrine\MyConnectionFactory:
        decorates: doctrine.dbal.connection_factory
        arguments:
            $db_credentials_per_site: '%db_credentials_per_site%'

and add 'db_credentials_per_site' parameter in "config/my_config.php" - as you see it is injected to MyConnectionFactory above:

$container->setParameter('db_credentials_per_site', [
    'blue_site' => [
        'url' => 'mysql://user1:pass1@127.0.0.1:3306/dbname-blue',
    ],
    'red_site' => [
        'url' => 'mysql://user2:pass2@127.0.0.1:3306/dbname-red',
    ],
]);

We need one more thing to support this feature in CLI commands - we need to add '--site' option to each command. As you see it is being read in \App\Doctrine\MyConnectionFactory::getSiteNameFromRequestOrCommand. It will be mandatory for all commands that will use database connection:

in services.yaml:

    App\EventListener\SiteConsoleCommandListener:
        tags:
            - { name: kernel.event_listener, event: console.command, method: onKernelCommand, priority: 4096 }

create new file "src/EventListener/SiteConsoleCommandListener.php":

<?php

namespace App\EventListener;

use Symfony\Component\Console\Event\ConsoleCommandEvent;
use Symfony\Component\Console\Input\InputOption;

class SiteConsoleCommandListener
{
    public function onKernelCommand(ConsoleCommandEvent $event)
    {
        // Add '--site' option to every command:
        $command = $event->getCommand();
        $command->addOption('site', null, InputOption::VALUE_OPTIONAL);
    }
}

Now we are ready to test if it works:

• when you call http://my-blue-site.local.dev2/something then the 'blue_site' database credenatials will be used.
• when you call http://something.blabla.com/something then the 'red_site' database credenatials will be used.
• when you run following command then 'blue_site' database credenatials will be used:

php bin/console app:my-command --site=blue_site

• when you run following command then 'red_site' database credenatials will be used:

php bin/console app:my-command --site=red_site

Answer 2

Finally what i did to resolve this issue is, made few changes to @domis86 answer.

1. Created a file as "my_config.php" inside "app/config/packages/".
2. add code to it as follows

$container->setParameter('my_param', 'mysql://root@xxx.x.x.x:0000/'.$_SERVER['HTTP_HOST']);

The credentials from .env file are being used in "app/config/packages/doctrine.yaml"

Previously the "doctrine.yaml" looked like this:

doctrine:
    dbal:
        # configure these for your database server
        driver: 'pdo_mysql'
        server_version: '5.7'
        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collate: utf8mb4_unicode_ci

        url: '%env(resolve:DATABASE_URL)%'
    orm:
        auto_generate_proxy_classes: true
        naming_strategy: doctrine.orm.naming_strategy.underscore
        auto_mapping: true
        mappings:
            App:
                is_bundle: false
                type: annotation
                dir: '%kernel.project_dir%/src/Entity'
                prefix: 'App\Entity'
                alias: App

Now, i added the below line to import my newly created "my_config.php" file to "doctrine.yaml" like this:

imports:
    - { resource: my_config.php }

then made few changes to code like this: (only added "'%my_param%'" to url line)

imports:
    - { resource: my_config.php }

doctrine:
    dbal:
        # configure these for your database server
        driver: 'pdo_mysql'
        server_version: '5.7'
        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collate: utf8mb4_unicode_ci

        url: '%my_param%'
    orm:
        auto_generate_proxy_classes: true
        naming_strategy: doctrine.orm.naming_strategy.underscore
        auto_mapping: true
        mappings:
            App:
                is_bundle: false
                type: annotation
                dir: '%kernel.project_dir%/src/Entity'
                prefix: 'App\Entity'
                alias: App

This solved my issue.

Thank you all @Nico Haase @dbrumann @domis86 @Cerad for your support.