Can one use different database credentials for Doctrine migrations in Symfony2?

Question

How can one configure Symfony's DoctrineMigrationsBundle to use different database authentication credentials to its DoctrineBundle—or at very least, a different DoctrineBundle connection to that used elsewhere in the app?

We would like the app to connect to the database with only limited permissions, e.g. no ability to issue DDL commands such as CREATE, ALTER or DROP. However, migrations will need to execute such DDL commands and so should connect as a user with elevated permissions. Is this possible?

Baugher

Answer 1

I know it's a very old post, but as it is the one which shows on a Google search on the subject, I add my solution, working with Symfony 4.

First, you just have to define a new database connection in config/doctrine.yml (a new entity manager is NOT needed):

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                # This will be the connection used by the default entity manager
                url: '%env(resolve:DATABASE_URL)%'
                driver: 'pdo_pgsql'
                server_version: '11.1'
                charset: UTF8
            migrations:
                # This will be the connection used for playing the migrations
                url: '%env(resolve:DATABASE_MIGRATIONS_URL)%'
                driver: 'pdo_pgsql'
                server_version: '11.1'
                charset: UTF8

    orm:
        # As usual... 

You also have to define the DATABASE_MIGRATIONS_URL with the admin credentials in the .env file or in environnement variables:

###> doctrine/doctrine-bundle ###
# Format described at http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/configuration.html#connecting-using-a-url
DATABASE_URL=postgresql://app_user:app_user_pass@localhost:5432/db
# Database url used for migrations (elevated rights)
DATABASE_MIGRATIONS_URL=postgresql://admin_user:admin_user_pass@localhost:5432/db
###< doctrine/doctrine-bundle ###

Then, just execute your migrations with the --db option, passing the name of your new connection:

php bin/console doctrine:migrations:migrate --db=migrations

Reames

Answer 2

Yes. Just define a new entity manager with the correct connection details and then use that entity manager when running migration commands

$ php app/console doctrine:migrations:version --em=new_entity_manager

Turoff

Actually, I'm not convinced that this is what I want... the --db-configuration parameter looks more correct. Could you help me understand how one should choose between the two? – Baugher

I can't - I don't have direct experience with what you're trying to do - I just poked into the commands in the bundle and noticed they all accept an --em parameter – Turoff