Symfony Doctrine multiple DB to get connection for the 2nd database

Question

I am using Symfony 7.1 to develop my API backend.

In my .env:

DATABASE_URL="mysql://root:[email protected]:3306/rsywx?serverVersion=8"
BLOG_URL="mysql://root:xxxxxxxx$@xxxx:3306/wordpress?serverVersion=8"

(One local, one remote.)

In my doctrine.yaml:

octrine:
    dbal:
        connections:
            default:
                url: '%env(resolve:DATABASE_URL)%'
            blog:
                url: '%env(resolve:BLOG_URL)%'
        

        # IMPORTANT: You MUST configure your server version,
        # either here or in the DATABASE_URL env var (see .env file)
        #server_version: '16'

            profiling_collect_backtrace: '%kernel.debug%'
            use_savepoints: true
        
        default_connection: default
    orm:
        default_entity_manager: default
        entity_managers:
            default:
                connection: default
            blog:
                connection: blog
        #auto_generate_proxy_classes: true
        #enable_lazy_ghost_objects: true
        #report_fields_where_declared: true
        #validate_xml_mapping: true
        #naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
        #auto_mapping: true
        #mappings:
        #    App:
        #        type: attribute

I have commented out many other configurations to keep it bare minimum.

In my controllers, I like to use raw sql to fetch records.

public function summary(Connection $connection):JsonResponse
    {
        $sql_book='select count(id) bc, sum(page) pc, sum(kword) wc from book_book';
        $sql_visit='select count(vid) vc from book_visit';
        $summary=$connection->fetchAssociative($sql_book);
        $visit=$connection->fetchAssociative($sql_visit);
        
        $res=[
            'bc'=>$summary['bc'],
            'pc'=>$summary['pc'],
            'wc'=>$summary['wc'],
            'vc'=>$visit['vc'],
        ];
        
        return new JsonResponse($res);
    }

This works fine and Symfony uses the default database.

In another controller, I have to fetch records from another db:

public function summary(Connection $connection):JsonResponse
    {
        $sql='select ....';
        ...


    }

How do I get connection to my 2nd db?

Thanks!

Answer 1

There is an article from the symfony documentation on how to use multiple connection/entity managers.

You should not execute sql query directly from your controller but in a service/repository instead and retrieve one with the proper connection.

From the doc:

// src/Controller/UserController.php
namespace App\Controller;

use AcmeStoreBundle\Entity\Customer;
use AcmeStoreBundle\Entity\Product;
use Doctrine\Persistence\ManagerRegistry;
// ...

class UserController extends AbstractController
{
    public function index(ManagerRegistry $doctrine): Response
    {
        // Retrieves a repository managed by the "default" entity manager
        $products = $doctrine->getRepository(Product::class)->findAll();

        // Explicit way to deal with the "default" entity manager
        $products = $doctrine->getRepository(Product::class, 'default')->findAll();

        // Retrieves a repository managed by the "blog" entity manager
        $customers = $doctrine->getRepository(Customer::class, 'blog')->findAll();

        // ...
    }
}

You can also get a specific entity manager directly:

public function index(ManagerRegistry $doctrine): Response
    {
        // Both methods return the default entity manager
        $entityManager = $doctrine->getManager();
        $entityManager = $doctrine->getManager('default');

        // This method returns instead the "blog" entity manager
        $customerEntityManager = $doctrine->getManager('blog');
    }