Symfony: Forcing an entity to use a different database connection than the default configured one

jeudi 19 janvier 2023

It is not uncommon to have multiple databases for a single project. Normally, when two databases are used, entities can be organized into specific directories that are declared in your configuration. In this case, the system will use the connection configured for the entity directory. You can see an example of multiple connections here Symfony 4 / Sonata: managing a multi-server admin interface

Let's assume that we want to point to a read-only database (a replication) every time we use an entity. There would be several ways to proceed.

Let's start by setting up multiple configurations.

DATABASE_MASTER=mysql://login:password@db_master.host:3306/db_master?serverVersion=mariadb-10.10.2
DATABASE_SLAVE=mysql://login:password@db_slave.host:3306/db_slave?serverVersion=mariadb-10.10.2

doctrine:
    dbal:
        default_connection: db_master
        types:
            json: Sonata\Doctrine\Types\JsonType   
        connections:
            db_master:
                url: '%env(resolve:DATABASE_MASTER)%'
                driver: 'pdo_mysql'
                server_version: '5.7'
                charset: utf8
 
                default_table_options:
                    charset: utf8mb4
                    collate: utf8mb4_unicode_ci
                     
            db_slave:
                url: '%env(resolve:DATABASE_SLAVE)%'
                driver: 'pdo_mysql'
                server_version: '5.7'
                charset: utf8
 
                default_table_options:
                    charset: utf8mb4
                    collate: utf8mb4_unicode_ci
    orm:
        auto_generate_proxy_classes: true
        default_entity_manager: default
        entity_managers:
            default:
                connection: db_master
                naming_strategy: doctrine.orm.naming_strategy.underscore
                auto_mapping: true
                mappings:
                    ApplicationSonataMediaBundle: ~
                    SonataMediaBundle: ~
                    Main:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity'
                        prefix: 'App\Entity\'
                        alias: Main       
              
            slave:
                connection: db_slave
                naming_strategy: doctrine.orm.naming_strategy.underscore
                auto_mapping: false
                mappings:
                    delef:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity'
                        prefix: 'App\Entity'
                        alias: delef

To point to a specific connection, we could modify our code and point to our connection for each Doctrine request.

Here are already two methods to point to our slave server for every request:

<?php

namespace App\Repository;

use App\Entity\Whatever;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\EntityRepository;
use Psr\Container\ContainerInterface;



class WhateverRepository extends ServiceEntityRepository
{
        public function __construct(ContainerInterface $container)
        {
             $this->container = $container;
        }

        public function selectSlave()
        {
            return $this->container->get('doctrine')
            ->getManager('slave')
            ->createQueryBuilder('w')
            ->andWhere('w.exampleField = :val')
            ->setParameter('val', $value)
            ->orderBy('w.id', 'ASC')
            ->setMaxResults(10)
            ->getQuery()
            ->getResult();
        }
}

Or again:

<?php

namespace App\Repository;

use App\Entity\Whatever;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\EntityRepository;
use Doctrine\Common\Persistence\ManagerRegistry;



class WhateverRepository extends ServiceEntityRepository
{
        public function __construct(ManagerRegistry $registry)
        {
             $this->registry = $registry;
             
        }

        public function selectSlave()
        {
            return $this->registry->
            ->getManager('slave')
            ->createQueryBuilder('w')
            ->andWhere('w.exampleField = :val')
            ->setParameter('val', $value)
            ->orderBy('w.id', 'ASC')
            ->setMaxResults(10)
            ->getQuery()
            ->getResult();
        }
}

But this implies modifying all our methods one by one to point to our connection.

A more global method consists of defining our connection in our constructor for our entire repository.

<?php

namespace App\Repository;

use App\Entity\Whatever;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\EntityRepository;
use Doctrine\Common\Persistence\ManagerRegistry;
use Doctrine\ORM\EntityRepository;


class WhateverRepository extends ServiceEntityRepository
{
        public function __construct(ManagerRegistry $registry)
        {
             $this->registry = $registry;
             $manager = $registry->getManager('slave');
             parent::__construct($registry, Whatever::class);
             EntityRepository::__construct(
                          $manager, 
                          $manager->getClassMetadata(Whatever::class)
                          );
        }

        public function selectSlave()
        {
            return $this->createQueryBuilder('w')
            ->andWhere('w.exampleField = :val')
            ->setParameter('val', $value)
            ->orderBy('w.id', 'ASC')
            ->setMaxResults(10)
            ->getQuery()
            ->getResult();
        }
}

In the end, you can choose from the different methods available according to the needs of your application.

``` Please note that the placeholders `

DATABASE_MASTER=mysql://login:password@db_master.host:3306/db_master?serverVersion=mariadb-10.10.2
DATABASE_SLAVE=mysql://login:password@db_slave.host:3306/db_slave?serverVersion=mariadb-10.10.2

`, `

doctrine:
    dbal:
        default_connection: db_master
        types:
            json: Sonata\Doctrine\Types\JsonType   
        connections:
            db_master:
                url: '%env(resolve:DATABASE_MASTER)%'
                driver: 'pdo_mysql'
                server_version: '5.7'
                charset: utf8
 
                default_table_options:
                    charset: utf8mb4
                    collate: utf8mb4_unicode_ci
                     
            db_slave:
                url: '%env(resolve:DATABASE_SLAVE)%'
                driver: 'pdo_mysql'
                server_version: '5.7'
                charset: utf8
 
                default_table_options:
                    charset: utf8mb4
                    collate: utf8mb4_unicode_ci
    orm:
        auto_generate_proxy_classes: true
        default_entity_manager: default
        entity_managers:
            default:
                connection: db_master
                naming_strategy: doctrine.orm.naming_strategy.underscore
                auto_mapping: true
                mappings:
                    ApplicationSonataMediaBundle: ~
                    SonataMediaBundle: ~
                    Main:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity'
                        prefix: 'App\Entity\'
                        alias: Main       
              
            slave:
                connection: db_slave
                naming_strategy: doctrine.orm.naming_strategy.underscore
                auto_mapping: false
                mappings:
                    delef:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity'
                        prefix: 'App\Entity'
                        alias: delef

`, `

<?php

namespace App\Repository;

use App\Entity\Whatever;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\EntityRepository;
use Psr\Container\ContainerInterface;



class WhateverRepository extends ServiceEntityRepository
{
        public function __construct(ContainerInterface $container)
        {
             $this->container = $container;
        }

        public function selectSlave()
        {
            return $this->container->get('doctrine')
            ->getManager('slave')
            ->createQueryBuilder('w')
            ->andWhere('w.exampleField = :val')
            ->setParameter('val', $value)
            ->orderBy('w.id', 'ASC')
            ->setMaxResults(10)
            ->getQuery()
            ->getResult();
        }
}

`, `

<?php

namespace App\Repository;

use App\Entity\Whatever;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\EntityRepository;
use Doctrine\Common\Persistence\ManagerRegistry;



class WhateverRepository extends ServiceEntityRepository
{
        public function __construct(ManagerRegistry $registry)
        {
             $this->registry = $registry;
             
        }

        public function selectSlave()
        {
            return $this->registry->
            ->getManager('slave')
            ->createQueryBuilder('w')
            ->andWhere('w.exampleField = :val')
            ->setParameter('val', $value)
            ->orderBy('w.id', 'ASC')
            ->setMaxResults(10)
            ->getQuery()
            ->getResult();
        }
}

`, and `

<?php

namespace App\Repository;

use App\Entity\Whatever;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\EntityRepository;
use Doctrine\Common\Persistence\ManagerRegistry;
use Doctrine\ORM\EntityRepository;


class WhateverRepository extends ServiceEntityRepository
{
        public function __construct(ManagerRegistry $registry)
        {
             $this->registry = $registry;
             $manager = $registry->getManager('slave');
             parent::__construct($registry, Whatever::class);
             EntityRepository::__construct(
                          $manager, 
                          $manager->getClassMetadata(Whatever::class)
                          );
        }

        public function selectSlave()
        {
            return $this->createQueryBuilder('w')
            ->andWhere('w.exampleField = :val')
            ->setParameter('val', $value)
            ->orderBy('w.id', 'ASC')
            ->setMaxResults(10)
            ->getQuery()
            ->getResult();
        }
}

` denote sections of the original text where there may be code or other specific content that should not be translated but instead directly inserted back into the translation in the appropriate place.

Share this article