Two databases with Symfony 4/5 & Doctrine 2? Not as straightforward as it might look.
--
Since I started working on my big new project I knew one thing. This is gonna be multi-database by design. I already knew that both Symfony & Doctrine are ready to handle this problem, question was: how well? Turns out that every single bit of this problem is covered by documentation… but not as complete solution. At time of writing this, I went trough all the hustle to make it finally work as intended without any help, because… well, there was none to be found. There are some bits and pieces in some articles but all too general. Most of the issues I ran into were covered by StackOverflow answers. So I decided to share my experience and maybe help others trying to achieve same thing.
Using: Symfony 4.3, Doctrine 2, PHP 7.3 and PostgreSQL 9.6
Designing databases
First, it is worth to mention that there is limitation by Doctrine:
Entities cannot define associations across different entity managers.
If your entities are about to interact with each other anyhow, you are left with three options:
- Map relations using ids. Although official Symfony docs refer to this StackOverflow answer, which mentions ids as “old-style”, there was a quite interesting reply to my article on reddit by u/LogicUpgrade about why it might be still the leading way of relations on Doctrine.
- Prepare eventListeners to do the joining “magic”: https://stackoverflow.com/a/42434119,https://stackoverflow.com/a/17226035
- If you use database that supports cross-database (PostgreSQL for example) on same host, you can actually use
@ORM\Table(name=”schemaname.tablename”)or since Doctrine 2.5@ORM\Table(schema=”schemaname”)https://stackoverflow.com/a/11494543
I wanted clear and simple approach. My entities are truly separated by databases for purpose, so joining a few of them by id is definitely not a big deal. Pick whatever suits your needs, but you might as well find why third option goes against my design:
Variable database name
If you are okay with having database name written inside your annotations, you can skip down to Entity managers and connections configuration
Otherwise, another issue I ran into thinking about third solution was that I wanted to have my database defined in .env file. This project is about to get deployed in multiple instances, some will share same remote database, some will share different remote one and each single instance is going to have its own local database. So any hard-coded solution inside @ORM\Table annotations is a no-go.
The only option left here is to define two connections and two entity managers.
.env Configuration
We can head first to prepare our variables that are project-independent and each instance, be it dev/test/production, has to set its own values. Inside the .env file located in project root I added those:
APP_LOCAL_DATABASE_URL=pgsql://docker:docker@postgres:5432/app-local
APP_REMOTE_DATABASE_URL=pgsql://docker:docker@postgres:5432/app-remoteapp-local and app-remote are names of respective databases. I’m using docker, thus you sure might want to adjust your URL to match your database login, IP and port.
Entity managers and connections configuration
We then proceed to add each connection and entity managers to config/packages/doctrine.yaml:
doctrine:
dbal:
default_connection: app-local-connection
connections:
app-local-connection:
url: '%env(resolve:APP_LOCAL_DATABASE_URL)%'
driver: 'pdo_pgsql'
server_version: '9.6.11'
charset: utf8
default_table_options:
charset: utf8mb4
collate: utf8mb4_unicode_ci
app-remote-connection:
url: '%env(APP_REMOTE_DATABASE_URL)%'
driver: 'pdo_pgsql'
server_version: '9.6.11'
charset: utf8
default_table_options:
charset: utf8mb4
collate: utf8mb4_unicode_ci
orm:
default_entity_manager: app-local
entity_managers:
app-local:
mappings:
Local:
is_bundle: false
type: annotation
dir: '%kernel.project_dir%/src/Entity/Local'
prefix: 'App\Entity\Local'
alias: AppLocal
app-server:
connection: app-server-connection
mappings:
Remote:
is_bundle: false
type: annotation
dir: '%kernel.project_dir%/src/Entity/Remote'
prefix: 'App\Entity\Remote'
alias: AppRemoteThere is not much to explain here, this is basically what Symfony docs tell you https://symfony.com/doc/current/doctrine/multiple_entity_managers.html. Worth to mention about entityManagers is that you define which entity belongs to which manager by this mapping configuration, see dir — actual directory where entities are located and prefix — namespace of such entities.
Repositories
Your entity managers should be now working fine. So you probably head to write bunch of entities and what should follow are the repositories. Using repository as a place to control loading & persisting entity is a good practice. Good news is that you don’t need to specify or register anyhow your repositories relation to entity managers. All you need to do is extend ServiceEntityRepository and trough constructor let framework to set the correct registry in relation to entity:
<?php declare(strict_types=1);
namespace App\Repository\Remote\User;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use App\Entity\Remote\User\Device;
use Symfony\Bridge\Doctrine\RegistryInterface;
/**
* Class DeviceRepository
* @package App\Repository\Remote\User
*/
class DeviceRepository extends ServiceEntityRepository implements DeviceRepositoryInterface
{
/**
* DeviceRepository constructor.
* @param RegistryInterface $registry
*/
public function __construct(RegistryInterface $registry)
{
parent::__construct($registry, Device::class);
}
}It is also good practice to keep your repositories organized same way as entities, thus in this case, in App\Repository\Remote\User namespace.
When you now call $this->_em or $this->createQueryBuilder() you will be given the correct entity manager for the repository’s entity.
Migrations
Here is where the real problems started. If you go ahead and try to generate migrations for your databases you are going to bump into one obvious problem. Command:
$ php bin/console doctrine:migrations:diffwill generate diff containing ALL entities from both of your databases. That is done without any note about which data belongs to which database. So once you run
$ php bin/console doctrine:migrations:migrateyou are going to end with default database filled with ALL data from both databases and second one empty.
“Luckily” both commands offer options to specify which EM should be used. This is OK but this will not move you anywhere since you cannot use it together commands--em and--db as noted here: https://github.com/doctrine/DoctrineMigrationsBundle/issues/38
There are also suggestions to be found. One is to use ContainerAwareInterface migrations to ask if current migration processed is for actually being used entity manager. Albeit being a working solution, it is ugly and results in no SQL statements being run warnings.
I picked another working approach to solve this issue, which is to create two separate directories and configurations of migrations, each for its own database. Beware, if you place these configuration files inside config directory you will get errors about unknown sections by symfony config loader. One way to exclude them is to place them inside different directory. I choose the bin directory because those are tied only with scripts I run there.
First file for the local database migrations, the migrations-app-local.yaml:
migrations_directory: '../src/Migrations/app-local'
migrations_namespace: App\Migrations\Local
table_name: migration_versions
name: Application_Migrations_Localand same with few changes for migrations-app-remote.yaml:
migrations_directory: '../src/Migrations/app-remote'
migrations_namespace: App\Migrations\Remote
table_name: migration_versions
name: Application_Migrations_RemoteYou can now delete the doctrine_migrations.yaml from your config directory.
From now on, if you are going to run migration commands, you will have to specify two options:
$ php bin/console doctrine:migrations:diff --em=app-local --configuration=./bin/migrations-app-local.yamlIt is quite lengthy and boring to write all that every time, so you can either write your own command https://symfony.com/doc/current/console.html
or, you can take the path of creating simple .sh script which calls given command on both databases:
#!/bin/bash
php bin/console doctrine:migrations:migrate --em=app-local --configuration=./bin/migrations-app-local.yaml
php bin/console doctrine:migrations:migrate --em=app-remote --configuration=./bin/migrations-app-remote.yamCalling migrations via command
If there is anything that is definitely broken by design, it is the option to call your migrations from another command. I will try to xdebug this but for now, there is for sure something being “cached” if you try to call migrations twice in a row, as described here: https://stackoverflow.com/questions/22508058/unable-to-run-two-migration-execute-commands-together-within-a-single-console-co.
The solution is, as proposed in one of the answers, not too ugly in my opinion so I stick with it:
$arguments = [
[
"command" => "doctrine:migrations:migrate",
"--quiet" => true,
"--no-interaction" => true,
"--em" => "app-local",
"--configuration" => "./bin/migrations-app-local.yaml"
],
[
"command" => "doctrine:migrations:migrate",
"--quiet" => true,
"--no-interaction" => true,
"--em" => "app-remote",
"--configuration" => "./bin/migrations-app.remote.yaml"
]
];
foreach ($arguments as $args) {
$command = new MigrationsMigrateDoctrineCommand();
$command->setApplication($this->getApplication());
$arguments = new ArrayInput($args);
$arguments->setInteractive(false);
$command->run($arguments, new NullOutput());
}As you can see, the whole trick lies in:
$command = new MigrationsMigrateDoctrineCommand();instead of (as recommended by Symfony docs):
$command = $this->getApplication()
->find(‘doctrine:migrations:migrate’);Using the second option, you will end up in arguments being stored somewhere internally from first command call and then re-used, so only migrations for app-local will migrate. I tried to consult this behavior on Slack but nobody responded.
Injecting correct entity manager
Now that migrations are working, you might ran into different issue. First, reminder: you should use repositories, which know which entity manager pick in order to work with given entity. But in case you don’t want to deal with repository for some reason and you want to shoot directly for entity managers — how to obtain them?
Symfony docs once again to the rescue:
// These methods also return the default entity manager, but it's preferred
// to get it by injecting EntityManagerInterface in the action method
$entityManager = $this->getDoctrine()->getManager();
$entityManager = $this->getDoctrine()->getManager('default');
$entityManager = $this->get('doctrine.orm.default_entity_manager');
// Both of these return the "customer" entity manager
$customerEntityManager = $this->getDoctrine()->getManager('customer');
$customerEntityManager = $this->get('doctrine.orm.customer_entity_manager');But, what if you don’t want to rely on $this->get() and you want the entity manager injected instead?
There are basically once again three approaches:
- Extending EntityManagerDecorator: https://stackoverflow.com/questions/43041832/symfony-3-entitymanager-dependency-injection-with-multiple-db-connections
- Defining your interface for each entity manager and make it as alias to actual entity managers: https://symfony.com/doc/current/service_container/autowiring.html#working-with-interfaces
- Obtaining it trough
ManagerRegistryinterface and then create trait which will set property for each entity manager whenever you need it: https://stackoverflow.com/questions/51396449/how-to-use-mulitple-entity-manager-in-services-symfony-4
Traits: https://gist.github.com/raitocz/faec272acd3868c7a3d342645e0e408d
Horayy, it should be working!
At this point your two database app should be fully working. If you landed here because googling for one of the issues mentioned I hope it was helpful to you. I’m open to any discussion about this approach and don’t mind changing anything mentioned. Let me know if there is anything else that I might forget which you ran into when working with this type of configuration.
I’m still actively working on this project and so far so good, everything works as expected.
