X
    Categories: All Symfony Tutorials, Tips And Guides you needLearn PHP Tutorials, Tips And Guides

Use multiple databases in Symfony Projects on Cloudways

At the time of writing this article, the most recent version of Symfony is 3.3.6. The community is abuzz with the news of imminent release of Symfony 4.

In many of my previous articles, I have covered Entity Managers and Doctrine in Symfony. The tutorials have covered the process of generating and configuring entities in Symfony. Now a common scenario in real world Symfony projects is the requirement of working with multiple databases. In many cases, the project requires access to two or more databases simultaneously for Doctrine related actions.  I will cover this topic in this tutorial.

For the purpose of this tutorial, I am assuming that you have already installed Symfony on Cloudways and that it’s running properly in the browser.

The Traditional DB Config

While installing Symfony, the Symfony Installer asks questions about the database credentials:

Note:  You can get your Cloudways DB credentials in Application access area of the Cloudways Platform.

The following credentials are available in the parameters.yml file:

parameters:

  database_host: 127.0.0.1

  database_port: null

  database_name: qmsfumgabd

  database_user: qmsfumgabd

  database_password: xxx9bxxMxx

  mailer_transport: smtp

  mailer_host: 127.0.0.1

  mailer_user: null

Here are the details about the default connection and credentials in the config.yml file:

# Doctrine Configuration

doctrine:

  dbal:

      driver: pdo_mysql

      host: '%database_host%'

      port: '%database_port%'

      dbname: '%database_name%'

      user: '%database_user%'

      password: '%database_password%'

      charset: UTF8

  orm:

      auto_generate_proxy_classes: '%kernel.debug%'

      naming_strategy: doctrine.orm.naming_strategy.underscore

      auto_mapping: true

When you create entities and call the getManager() function, you will get the default connection (that you used while installing Symfony). If you wish to bypass this traditional database setup and wish to use a different or multiple databases in your Symfony projects, you have two options. You could define it in the parameters.yml and then configure it in the config.yml. The second option is to directly define it in config.yml.

Configure Multiple Databases

I will start with the adding parameters to parameters.yml file. The second database connection could be added using the following parameters:

Parameters:

# First database

  database_host: 127.0.0.1

  database_port: null

  database_name: qmsfumgabd

  database_user: qmsfumgabd

  database_password: xxx9bxxMxx



# Second database

  database2_host: 127.0.0.1

  database2_port: null

  database2_name: huscqxzwaw

  database2_user: huscqxzwaw

  database2_password: dxxxFXxxxB

The next step is to get these credentials in the config.yml:

doctrine:

  # Configure the abstraction layer

  dbal:

      # Set the default connection to default

      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

         database2:

               driver:    pdo_mysql

               host:      '%database2_host%'

               port:      '%database2_port%'

               dbname:   '%database2_name%'

               user:            '%database2_user%'

               password:   '%database2_password%'

               charset:  UTF8

I have added a default connection default_connection: default with no connection specified in the entity manager method. Next, I distributed the configuration of the connections into ‘default’ and ‘database2’.

Finally, I will specify the mapping of each bundle in the project:

# Configure the ORM

  orm:

      default_entity_manager: default

      entity_managers:

          # Register which bundle should use which connection

          default:

              connection: default

              mappings:

                  AppBundle:  ~

                  DemoBundle: ~

          database2:

              connection: database2

              mappings:

                  CloudwaysBundle: ~

At this point, the first option is complete. I will now describe the second method in which I could directly add the database credentials in the config.yml:

doctrine:

  # Configure the abstraction layer

  dbal:

      # Set the default connection to default

      default_connection: default

      connections:

          default:

                driver:  pdo_mysql

                host:    localhost

                port:    null

                dbname: qmsfumgabd

                user:   qmsfumgabd

                password:  xxx9bxxMxx

                charset: UTF8

         database2:

               driver:   pdo_mysql

               host:     localhost

               port:     null

               dbname:   huscqxzwaw

               user:     huscqxzwaw

               password: dxxxFXxxxB

               charset:  UTF8

The rest of the ORM settings will remain the same.

Now,  I can call the entity manager in the controller by just calling it by name:

class UserController extends Controller

{

   public function indexAction()

   {

       // All 3 return the "default" entity manager

       $em = $this->getDoctrine()->getManager();

       $em = $this->getDoctrine()->getManager('default');

       $em = $this->get('doctrine.orm.default_entity_manager');



       // Both of these return the "database2" entity manager

       $anotherEm = $this->getDoctrine()->getManager('database2');

       $anotherEm = $this->get('doctrine.orm.database2_entity_manager');

   }

}

The following is the recommended syntax for repository calls, as mentioned in the Symfony docs.Similarly call the name of EM here also.

// Explicit way to deal with the "default" em

       $products = $this->getDoctrine()

           ->getRepository(Product::class, 'default')

           ->findAll() ;


// Retrieves a repository managed by the "database2" em

       $customers = $this->getDoctrine()

           ->getRepository(Customer::class, 'database2')

           ->findAll();

If a specific entity manager is not called, `default` will be returned to the controller. To avoid errors, I will add `auto_mapping: true` to any one of the connection.

Once all this is done, when I am working with the schema and performing Doctrine actions, I will specify the connection name like this:

# For "default" mappings

$ php bin/console doctrine:schema:update --force


# For “database2” mappings

$ php bin/console doctrine:schema:update --force --em=database2

Final Words

The best thing about Symfony is its very flexible structure that ensures that developers could easily mold the features of the framework to project specifications.

Remember that Cloudways offer one database per Symfony application. If you wish to use the second database, you have to launch the second application and use its DB’s credentials. In this scenario, remember that other configurations in the config.yml should not be affected.

If you have any question or queries, feel free to comment below.

Shahroze Nawaz: Shahroze is a PHP Community Manager at Cloudways - A Managed PHP Hosting Platform. He’s always in search of new frameworks and methods to implement them. Besides his coding life, he loves movies and playing soccer with friends. You can email him at shahroze.nawaz@cloudways.com