Creating multiple databases with doctrine based on multiple connections

Question

I am facing an issue when creating multiple databases based on multiple connections for functional testing purposes.

I created on a Symfony 3.3 application due to some restrictions and an update to a maintained version of Symfony will be scheduled. The thing to keep in mind is that I had to deal with an existing PostgreSQL database, especially with 3 databases:

database A
database B
database C

I dislike designing my application from an existing database. Usually when I create an application from scratch, I design my application and all business rules first but I had no choice here. Sounds bad but this part of my application is designed from the existing database. Any suggestion is most welcome anyway. So, in App\Entity folder I configured the mapping for the 3 connections:

entities related to database A are in App\Entity\Dba folder
entities related to database B are in App\Entity\Dbb folder
entities related to database C are in App\Entity\Dbc folder

Using flex, the doctrine configuration in the doctrine.yaml file looks like this:

doctrine:
    dbal:
        default_connection: a
        connections:
            a:
                driver:   '%database_a_driver%'
                url: '%env(DATABASE_A_URL)%'
                charset:  UTF8
                server_version: '%server_version%'
            b:
                driver:   '%database_b_driver%'
                url: '%env(DATABASE_B_URL)%'
                charset:  UTF8
                server_version: '%server_version%'
            c:
                driver:   '%database_c_driver%'
                url: '%env(DATABASE_C_URL)%'
                charset:  UTF8
                server_version: '%server_version%'
    orm:
        auto_generate_proxy_classes: '%kernel.debug%'
        default_entity_manager: em_a

        entity_managers:
            em_a:
                connection: a
                mappings:
                    AppDba:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity/Dba'
                        prefix: 'App\Entity'
                        alias: AppDba
            em_b:
                connection: b
                    AppDbb:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity/Dbb'
                        prefix: 'App\Entity'
                        alias: AppDbb
            em_c:
                connection: c
                    AppDbc:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity/Dbc'
                        prefix: 'App\Entity'
                        alias: AppDbc

Now comes the issue.

When I run this command:

bin/console doctrine:database:create --connection=a

The corresponding database is created with the correct name and the default public schema which is empty is also created. It is normal because I did not execute the doctrine:schema:create --em=em_a command yet.

But there is also a schema with some tables inside which are also created. And this schema does not belong to the a database but it belongs to the b database. And the strange thing is that those tables inside this unexpected schema are not configured in any mapping in my application. They are only in the existing database.

Does anyone know the origin of this issue and how to solve it?

Additional informations:

Symfony 3.3.18
PostgreSQL 9.4.17
doctrine orm 2.5.9-stable
doctrine dbal v2.6.3
doctrine bundle 1.9.1

You're setting all the mappings under connection a so what would you expected that this? — DonCallisto
– DonCallisto, Commented Sep 19, 2018 at 9:31
@DonCallisto I edited the question, I had forgot some configuration keys — fgamess
– fgamess, Commented Sep 19, 2018 at 9:37
Are the prefix written here as you have written them in the application or do you have "pasting" errors there? Because I suppose them should be App\Entity\Dba, App\Entity\Dbb, App\Entity\Dbc — DonCallisto
– DonCallisto, Commented Sep 19, 2018 at 9:49
I suppose is due to mappings section. I'm trying to guessing of course as I'm not really aware of this problem, otherwise I would have answered to the question. BTW change the prefix and try again: what do you obtain? — DonCallisto
– DonCallisto, Commented Sep 19, 2018 at 9:54
You should definitely change prefix otherwise it won't work. You should also clear doctrine metadata (php bin/console doctrine:cache:clear-metadata) in case you use caching of doctrine annotations. — iiirxs
– iiirxs, Commented Sep 19, 2018 at 11:41

fgamess · Accepted Answer · 2018-09-20 09:21:50Z

So thanks to DonCallisto and iiirxs I fixed the issue. I put correct prefix and also I am using url setting key for dbal connection. I don't if it was helpful for my issue.

So here the configuration:

doctrine:
dbal:
    default_connection: a
    connections:
        a:
            driver:   '%database_a_driver%'
            url: '%env(DATABASE_A_URL)%'
            charset:  UTF8
            server_version: '%server_version%'
        b:
            driver:   '%database_b_driver%'
            url: '%env(DATABASE_B_URL)%'
            charset:  UTF8
            server_version: '%server_version%'
        c:
            driver:   '%database_c_driver%'
            url: '%env(DATABASE_C_URL)%'
            charset:  UTF8
            server_version: '%server_version%'
orm:
    auto_generate_proxy_classes: '%kernel.debug%'
    default_entity_manager: em_a

    entity_managers:
        em_a:
            connection: a
            mappings:
                AppDba:
                    is_bundle: false
                    type: annotation
                    dir: '%kernel.project_dir%/src/Entity/Dba'
                    prefix: 'App\Entity\Dba'
                    alias: AppDba
        em_b:
            connection: b
                AppDbb:
                    is_bundle: false
                    type: annotation
                    dir: '%kernel.project_dir%/src/Entity/Dbb'
                    prefix: 'App\Entity\Dbb'
                    alias: AppDbb
        em_c:
            connection: c
                AppDbc:
                    is_bundle: false
                    type: annotation
                    dir: '%kernel.project_dir%/src/Entity/Dbc'
                    prefix: 'App\Entity\Dbc'
                    alias: AppDbc

Also, maybe related to some synchronization issue with the docker container hosting the Postgres database, I removed the container and then I created it again. So now I don't have any unexpected schema and table which are not configured in my mapping configuration.

One last thing, when using bin/console doctrine:database:create --env=test --connection=a the expected result is that the database should be created without any schemas and tables. And then when running bin/console doctrine:create:schema --em=em_a --env=test it should create all the schemas and tables from your mapping and your configuration.

But Postgres database must always have a default public. So when creating the database with doctrine, this schema will be created. It will be empty without any table but it will be there. I guess this is a specific behavior related to the postgres driver. So before using doctrine to create the schema and tables, it is necessary to manually remove this public schema, otherwise, it will trigger this error:

Schema-Tool failed with Error 'An exception occurred while executing 'CREATE SCHEMA public':
SQLSTATE[42P06]: Duplicate schema: 7 ERROR: schema "public" already exists' while executing DDL: CREATE SCHEMA public

Maybe there is a setting which permits to do this automatically but I don't know it. Any suggestion is welcome.

Collectives™ on Stack Overflow

Creating multiple databases with doctrine based on multiple connections

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related