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:
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:
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:
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.