Raw SQL Query from Non Default Connection

Question

I need to run a raw SQL query on a non default connection.

The only way I've seen in the docs to use an alternate connection is to inject ManagerRegistry. This lets me get an ObjectManager but I'm not seeing how to execute raw sql.

EntityManager has the getConnection method which would let me run raw SQL, but I don't see how to get an entity manager for the alternate connection with DI.

Here's where I'm at based on the documentation to use multiple connections:

public function Foo(ManagerRegistry $doctrine)
{
    $om = $doctrine->getManager('foo');
    // How do I use $om to create a raw SQL query
}

For reference, here is how symfonycasts shows to do it with an EntityManager, but I'm not sure how to select the foo connection as injecting will give me the default connection.

$conn = $entityManager->getConnection();
$stmt = $conn->prepare($sql);
$res = $stmt->executeQuery();

The answer below works but sometimes I get the feeling that some developers think that you have to go through the ManagerRegistry to get to entity managers and connections. To me at least it makes more sense to just inject the database connection directly and be done with it. [Here is an example](https://stackoverflow.com/questions/51556454/dependency-inject-non-default-entity-manager-into-service-in-symfony/51558092#51558092). — Cerad, Feb 07 '22 at 14:32

score 5 · Accepted Answer · answered Feb 05 '22 at 09:31

Here is how i'm doing it.

First you need to update your config to set your different connections in config/package/doctrine.yaml :

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                url: '%env(resolve:DATABASE_URL)%'
            alt:
                url: '%env(resolve:DATABASE_URL_ALT)%'

Then, in your controller or service you can inject the ManagerRegistry and get the connection :

public function index(ManagerRegistry $managerRegistry)
{
    $connection = $managerRegistry->getConnection("alt");
    $result = $connection
        ->prepare("SELECT * FROM your_table_in_alt_db")
        ->executeQuery()
        ->fetchAllAssociative()
    ;
    dump($result);
}

Raw SQL Query from Non Default Connection

1 Answers1