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"

Remember that a default migrations mechanism won’t work when the database name is not explicitly specified. You will need to create database schemes in a different way. You can also create your own console method to run migrations in a loop for all databases.

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;

In the example, the database is chosen at the beginning of a controller action. However, you probably don’t want to repeat that piece of code in every action. A good approach is to implement that behavior in the event listener run before a controller action. If you use LexikJWTAuthenticationBundle, you can also create a listener for the JWT_DECODED event and call selectDatabase() method there. Remember to inject the wrapper as a service to the listener.

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.

You May Also Like

Leave a Reply

Your email address will not be published. Required fields are marked *