Use symfony2 application with mysql & postgresql

Question

I am developing an application using the symfony2 framework. The application should run on different servers, one with mysql and one with postgresql.

For postgresql, I need to use schema = "admin" in several tables. So, I did for entities:

@ORM \ Table (scheme = "admin", name = "si_user")

It works fine on postgresql.

When I try to update or create a sql schema, the doctrine does not create or find a table. And this works when I delete schema = "admin".

@ORM \ Table (name = "si_user")

Do you have any solutions related to schema attribute and mysql not to use schema attribute?

Thank you for your help.

Answer 1

Solution 1: the same entity class, multiple mappings

First, as @Jekis pointed out, you will need different connections and entity managers.

# Doctrine Configuration
doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                driver:   pdo_mysql
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8
            postgres:
                driver:   pdo_pgsql
                host:     "%pg_host%"
                port:     "%pg_port%"
                dbname:   "%pg_name%"
                user:     "%pg_user%"
                password: "%pg_password%"
                charset:  UTF8
    orm:
        default_entity_manager: default
        auto_generate_proxy_classes: "%kernel.debug%"
        entity_managers:
            default:
                connection: default
                mappings:
                    AppBundle:
                        type: yml
                        dir: Resources/config/doctrine/default
            postgres:
                connection: postgres
                mappings:
                    AppBundle:
                        type: yml
                        dir: Resources/config/doctrine/postgres

Secondly, since you need different mapping information for each connection on the same objects, I assume that you will need something other than annotations for your metadata. For example, yml. This way you can define a specific mapping for each entity manager.

MySQL:

# Resources/config/doctrine/default/User.orm.yml
AppBundle\Entity\User:
    type: entity
    table: user
    repositoryClass: AppBundle\Repository\UserRepository
    id:
        id:
            type: integer
            id: true
            generator:
                strategy: AUTO
    fields:
        name:
            type: string
            length: 255
    lifecycleCallbacks: {  }

Postgres:

# Resources/config/doctrine/postgres/User.orm.yml
AppBundle\Entity\User:
    type: entity
    schema: admin
    table: user
    repositoryClass: AppBundle\Repository\UserRepository
    id:
        id:
            type: integer
            id: true
            generator:
                strategy: AUTO
    fields:
        name:
            type: string
            length: 255
    lifecycleCallbacks: {  }

Then, by invoking the command doctrine:schema:update, you can also pass the entity manager for the database that you want to update.

php bin/console doctrine:schema:create
php bin/console doctrine:schema:create -em postgres

, : https://github.com/vtoulouse/multiple-mapping-test

2:

, , , .