Dynamic database connection based on request – Symfony and Doctrine

Symfony and Doctrine give us an easy way to deal with several databases if they are explicitly specified in a configuration file. However, there are some cases where we need to select a database dynamically. SAAS applications are a great example. They often use separate databases for every company or organization. The right database is chosen on the fly based on data contained in the request (headers or body). In this post, I will show you how to implement such behavior.

Project setup

Let’s create a simple Symfony 5 project, install Doctrine, and run the development server. I use Symfony CLI, but you can also do it only with composer and any HTTP server.

symfony new symfony_dynamic_db
composer require symfony/orm-pack
symfony server:start

We can leave a default Doctrine configuration as is. All we have to do is modify DATABASE_URL environment variable in the .env file.

# config/packages/doctrine.yaml

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'

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

# .env

# Enter your db connection data.
# Skip database name. We are going to add it programmatically.
DATABASE_URL="mysql://root:root@127.0.0.1:3306?serverVersion=5.7"

Now we have to create a simple entity class. Repository class is optional for this example, so I skipped it.

// src/Entity/Product.php

<?php

namespace App\Entity;

use App\Repository\ProductRepository;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass=ProductRepository::class)
 * @ORM\Table(name="products")
 */
class Product
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private ?int $id;

    /**
     * @ORM\Column(type="string", length=100)
     */
    private string $name;

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

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getName(): string
    {
        return $this->name;
    }
}

When our project is set up, we can finally focus on solving the problem.

Connection wrapper

Connection wrapper extends a default connection class with custom features. That wrapper is returned instead of a default connection anytime you ask for a connection object. We want to add a method that modifies our credentials to a database.

// src/DBAL/MultiDbConnectionWrapper.php

<?php

declare(strict_types=1);

namespace App\DBAL;

use Doctrine\Common\EventManager;
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Driver;

final class MultiDbConnectionWrapper extends Connection
{
    public function __construct(
        array $params,
        Driver $driver,
        ?Configuration $config = null,
        ?EventManager $eventManager = null
    ) {
        parent::__construct($params, $driver, $config, $eventManager);
    }

    public function selectDatabase(string $dbName): void
    {
        if ($this->isConnected()) {
            $this->close();
        }

        $params = $this->getParams();
        $params['dbname'] = $dbName;
        parent::__construct($params, $this->_driver, $this->_config, $this->_eventManager);
    }
}

Then, we have to define our new class as a connection wrapper in Doctrine configuration.

# config/packages/doctrine.yaml

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        wrapper_class: App\DBAL\MultiDbConnectionWrapper

From now, every time you ask for a connection object, the service container will return an instance of MultiDbConnectionWrapper instead of a default Doctrine\DBAL\Connection.

The last thing to do is to create a controller and configure a route.

// src/Controller/ProductController.php

<?php

declare(strict_types=1);

namespace App\Controller;

use App\DBAL\MultiDbConnectionWrapper;
use App\Entity\Product;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;

class ProductController extends AbstractController
{
    private EntityManagerInterface $em;

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

    public function add(Request $request): JsonResponse
    {
        $connection = $this->em->getConnection();
        if(!$connection instanceof MultiDbConnectionWrapper) {
            throw new \RuntimeException('Wrong connection');
        }

        $data = json_decode($request->getContent(), true);
        $databaseName = $data['databaseName'];
        $productName = $data['productName'];

        $connection->selectDatabase($databaseName);

        $product = new Product($productName);
        $this->em->persist($product);
        $this->em->flush();

        return new JsonResponse();
    }
}

# config/routes.yaml

add_product:
  path: /product/add
  controller: App\Controller\ProductController::add
  methods: POST

And that’s all. From now on, which database the application will use will depend on the parameters in the request. If you need you can change not only the database name but also the username, password, and even host.

// src/DBAL/MultiDbConnectionWrapper.php
//...
$params = $this->getParams();
$params['dbname'] = $dbName;
$params['user'] = $user;
$params['password'] = $pass;
$params['host'] = $host;
$params['port'] = $port;

Conclusion

A custom wrapper class is a powerful yet little known feature of Doctrine. In the official documentation, the description of this feature contains exactly two sentences! The source code of the example I showed in this article can be found in this repository.