Confusion with parameters.yml and config_(dev|stage|prod).yml for database connections

Question

I understand why I should keep my database connections in the parameters.yml file but I also wanted to setup additional database connections as well as different hosts for dev, stage and prod environments. I thought I could do this with the config_dev.yml, config_test.yml and config_prod.yml in conjunction with config.yml, am I wrong?

In parameters.yml I hold the main database connection but can I move this to the config(s) instead?

Example: config.yml

# Doctrine Configuration - notice there is no host defined here
doctrine:
    dbal:
      default_connection: blah
      connections:
        blah:
          driver:   pdo_pgsql
          port:     5432
          dbname:   blah
          user:     blah_user
          password: blah_pass
          charset:  UTF8
        foo:
          driver:   pdo_pgsql
          port:     5432
          dbname:   foo
          user:     foo_user
          password: foo_pass
          charset:  UTF8

Example: config_dev.yml

# Doctrine Configuration - Here I define the dev hosts
doctrine:
    dbal:
      connections:
        blah:
          host: blah_dev
        foo: 
          host: foo_dev

Example: config_test.yml

# Doctrine Configuration - Here I define the stage or QA hosts
doctrine:
    dbal:
      connections:
        blah:
          host: blah_stage
        foo: 
          host: foo_stage

Example: config_prod.yml

# Doctrine Configuration - Here I define the prod hosts
doctrine:
    dbal:
      connections:
        blah:
          host: blah_prod
        foo: 
          host: foo_prod

Now I have also removed the settings in parameters.yml but Symfony / Doctrine doesn't like this. Am I missing something? How can I setup something like I have?

Now if I define the default database connection in parameters.yml I can connect to it and then

parameters:
    database_driver: pdo_pgsql
    database_host: blah_prod
    database_port: 5432
    database_name: blah
    database_user: blah_user
    database_password: blah_pass

and in config.yml

doctrine:
    dbal:
        driver:   "%database_driver%"
        host:     "%database_host%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"

But now I loose the multiple databases I need and the ability to switch from dev to stage to prod database servers.

I must be missing some additional documentation where this is being addressed, any help would be great.

I've seen the documentation for Multiple Database connections with Doctrine

• http://symfony.com/doc/current/cookbook/doctrine/multiple_entity_managers.html

But I would like to avoid having to add three database options like below for each dev, stage and prod server(s) in parameters.yml

parameters:
    database_driver: pdo_pgsql
    database_host: blah_dev
    database_port: 5432
    database_name: blah
    database_user: blah_user
    database_password: blah_pass
    #database_driver2: pdo_pgsql
    database_host2: blah_stage
    #database_port2: 5432
    #database_name2: blah
    #database_user2: blah_user
    #database_password2: blah_pass
    #database_driver3: pdo_pgsql
    database_host3: blah_prod
    #database_port3: 5432
    #database_name3: blah
    #database_user3: blah_user
    #database_password3: blah_pass

( Maybe just configure the different hosts? )

This looks ugly and a PITA, and this is just for blah database, I would have to do this for foo and any other databases I need to configure.

Answer 1

If your connections are the same in each environment but only the configuration values are different, you should use the parameters-configuration-file. Define the environment variables, you need, in the parameters.yml.dist file like this:

# parameters.yml.dist
parameters:
    database_host_1: blah
    database_host_2: blub
    ...

Your config.ml may look like this:

# config.yml
imports:
    - { resource: parameters.yml }
...
doctrine:
    dbal:
  default_connection: blah
  connections:
    blah:
      driver:   pdo_pgsql
      port:     5432
      host:     "%database_host_1%"
      dbname:   blah
      user:     blah_user
      password: blah_pass
      charset:  UTF8
    foo:
      driver:   pdo_pgsql
      port:     5432
      host:     "%database_host_2%"
      dbname:   foo
      user:     foo_user
      password: foo_pass
      charset:  UTF8

If your parameters.yml is missing, you'll be ask for the database hostnames next time you call composer update for example.

If you would like to save all your configurations in separate files, you should add this at the beginning of your config_XXX.yml files:

# config_XXX.yml
imports:
    - { resource: config.yml }
    - { resource: parameters_XXX.yml }

Replace XXX with your environment. Create a parameters_XXX.yml for each environment and set the configuration parameters for your hosts in there like it is in the default parameters.yml file.

# parameters_XXX.yml
parameters:
    database_host_1: blahInXXXEnv
    database_host_2: blubInXXXEnv
    ...