Read replica database connection never used, goes back to default

Question

I'm following the documentation here https://symfony.com/doc/current/reference/configuration/doctrine.html about setting up alternative database connections for the purpose of read replication. For reference I'm using AWS Aurora PostgreSQL with end points for primary and read only.

I've set the this for my doctrine.yaml

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                url: '%env(resolve:DATABASE_URL)%'
            read:
                url: '%env(resolve:READ_URL)%'
    orm:
        default_entity_manager: default
        entity_managers:
            default:
                connection: default
                naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
                auto_mapping: true
                dql:
                    datetime_functions:
                        date_trunc: App\DoctrineExtensions\DateTrunc
                mappings:
                    App:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity'
                        prefix: 'App\Entity'
                        alias: App
            read:
                connection: read
                naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
                auto_mapping: false
                dql:
                    datetime_functions:
                        date_trunc: App\DoctrineExtensions\DateTrunc
                mappings:
                    App:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity'
                        prefix: 'App\Entity'
                        alias: App
        auto_generate_proxy_classes: true

And calling the read only orm as:

$user = $this->getDoctrine()->getRepository(User::class, 'read')->findOneBy(['email' => $payload['email']]);

as an example.

If I call this it still goes to the default database. If I misname it Symfony throws an exception as expected so I can confirm the config is being picked up and loading correctly.

How is my config or call incorrect that it's still requesting from the default database connection or is there any better way to define read replicas for the data connection? I haven't found any current documentation for it.

I might be missing something, but this looks good to me. Can you confirm that `%env(resolve:READ_URL)%` actually resolves the right URL? — Julien B., Dec 02 '20 at 04:17
Yes, it's just in test but I basically just change the password for the different database URLs. Changing the default one causes the call to fail, changing the read one doesn't cause the error so I can assume it's using the default one. I wouldn't be surprised if it tested the default one but it should fail trying to call the read only db as thats what should be doing the call. — Rudiger, Dec 02 '20 at 04:31
I think the warning at the very end of this page might be the reason why you are having that problem. https://symfony.com/doc/current/doctrine/multiple_entity_managers.html — Julien B., Dec 02 '20 at 04:57
@JulienB. I suspect you're right. Is there any other way to query the read replica instead of the primary? This seems overly difficult for a common use case. — Rudiger, Dec 02 '20 at 23:59

Julien B. · Answer 1 · 2020-12-03T12:35:40.863

According to this:

One entity can be managed by more than one entity manager. This however results in unexpected behavior when extending from ServiceEntityRepository in your custom repository. The ServiceEntityRepository always uses the configured entity manager for that entity. In order to fix this situation, extend EntityRepository instead and no longer rely on autowiring:

So you should make sure your repository extends EntityRepository instead of ServiceEntityRepository.

Then you should now always fetch this repository using ManagerRegistry::getRepository().

So basically this would work since $this->getDoctrine() return an instance of ManagerRegistry.

$user = $this->getDoctrine()->getRepository(User::class, 'read')->findOneBy(['email' => $payload['email']]);

The downside is you will not be able to use autowiring for the repository itself.

So this would not work unless you pass that repository manually.

public function __construct(UserRepository $userRepository)
{
    $this->userRepository = $userRepository;
}

Thanks, I got to this point over the last 24 hours and basically ended up using prepared sql query for the 2 queries I want to offload to the read replica. — Rudiger, Dec 03 '20 at 07:17

Read replica database connection never used, goes back to default

1 Answers1