How to use more than one database using doctrine orm in Symfony 3

How to use more than one database using doctrine orm in Symfony 3

This task is more easy to achieve than you think and theoretically you'll be able to use multiple databases in a couple of minutes (if you have already built the entities). In this article you'll learn to :

  • Prepare your databases and create the entities by your own in the bundles that you want (this step will be skipped in this article as you can implement this by your own).
  • Add the required configuration to the config.yml file in your project
  • Learn how to update and create schema with the symfony console

Connecting to the databases

In this case, we use 2 databases, the default (ourcodeworld) and analytics (analytics), therefore we need to register those changes to our project. Go to the config.yml file and change the following configuration in the doctrine property :

doctrine:
    # Configure the abstraction layer
    dbal:
        # Set the default connection to default
        default_connection: default
        connections:
            default:
                driver:   '%default_driver%'
                host:     '%default_host%'
                port:     '%default_port%'
                dbname:   '%default_name%'
                user:     '%default_user%'
                password: '%default_password%'
                charset:  UTF8
            analytics:
                driver:   '%analytics_driver%'
                host:     '%analytics_host%'
                port:     '%analytics_port%'
                dbname:   '%analytics_name%'
                user:     '%analytics_user%'
                password: '%analytics_password%'
                charset:  UTF8
    # Configure the ORM
    orm:
        default_entity_manager: default
        entity_managers:
            # Register which bundle should use which connection
            default:
                connection: default
                mappings:
                    AppBundle:  ~
                    AnalyticsBundle: ~
            analytics:
                connection: analytics
                mappings:
                    AnalyticsBundle: ~

Be sure that if your database use credentials, these matches with the given credentials in the previous configuration to prevent any weird error. Till this point the configuration is enough.

Be careful too with any existing configuration, for example if your project already use i.e dql extensions (custom functions, for example match_against), these settings needs to be change from the dbal global settings (orm block) and append them to every custom entity manager. Otherwise you'll get errors like Unrecognized options "naming_strategy, auto_mapping, dql" under "doctrine.orm".

See the following example only for an already existing project :

# Doctrine Configuration
doctrine:
    dbal:
         # Set the default connection to default
        default_connection: default
        connections:
            default:
                driver:   'pdo_mysql'
                host:     '127.0.0.1'
                port:     null
                dbname:   'ourcodeworld'
                user:     'root'
                password: null
                charset:  UTF8
            analytics:
                driver:   'pdo_mysql'
                host:     '127.0.0.1'
                port:     null
                dbname:   'analytics'
                user:     'root'
                password: null
                charset:  UTF8

    orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        default_entity_manager: default
        entity_managers:
            # Register which bundle should use which connection
            default:
                naming_strategy: doctrine.orm.naming_strategy.underscore
                connection: default
                auto_mapping: true
                mappings:
                    AppBundle:  ~
                dql:
                    string_functions:
                        MATCH_AGAINST: ourcodeworld\Extensions\Doctrine\MatchAgainst
            analytics:
                naming_strategy: doctrine.orm.naming_strategy.underscore
                connection: analytics
                mappings:
                    AnalyticsBundle: ~
                dql:
                    string_functions:
                        MATCH_AGAINST: ourcodeworld\Extensions\Doctrine\MatchAgainst

Now that our basic configuration is ready, you only need to learn how to use the entity manager with multiple connections on your controllers. You'll be able to manipulate everything as you did always, only you need to specify which connection you want to use giving a string as first paramater with the name of the connection on the getManager function.

class AnyofMyController extends Controller
{
   public function anyAction()
   {
       // All three return the "default" entity manager
       $em = $this->get('doctrine')->getManager();
       $em = $this->get('doctrine')->getManager('default');
       $em = $this->get('doctrine.orm.default_entity_manager');

       // Both of these return the "analytics" entity manager
       $analyticsEm = $this->get('doctrine')->getManager('analytics');
       $analyticsEm = $this->get('doctrine.orm.analytics_entity_manager');

       $analyticItem = $analyticsEm->getRepository("analyticsBundle:Analytics")->find(12);// Find an analytics item
   }
}

Note that this article assumes that you will handle all the setup of your database (database desing and entity mapping).

Doctrine schema update and create

As you're working with multiple databases, when working with commands like doctrine:database:create and doctrine:schema:update you need to provide at every execution, with which connection you want to execute this task, for example :

# Play only with "default" connection
$ php bin/console doctrine:database:create

# Play only with "analytics" connection
$ php bin/console doctrine:database:create --connection=analytics

And to update any schema, use :

# Play only with "default" mappings
$ php bin/console doctrine:schema:update --force

# Play only with "analytics" mappings
$ php bin/console doctrine:schema:update --force --em=analytics

Final recommendations

  • This task is a little more advanced than create a normal connection with a default database in symfony and not usually required. Be sure you actually need multiple entity managers before adding in this layer of complexity (foreign keys that target to an existing database etc.).
  • If you do omit the entity manager's name when asking for it, the default entity manager (e.g default) is returned.
  • Remember that auto_mapping option can be only enabled for 1 entity manager, otherwise the runtime will throw error.

Have fun

Become a more social person