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:
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:
# 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.