CODEWITHSUNDEEP
cakephpcakephp-4.x
ToX 82
ToX 82

Reputation: 1074

Using multiple databases for the same model

I am working on some APIs that needs to connect to two separate database, containing similar tables but different data.

I have configurated the two datasources in my app.php and app_local.php, and both of them are reachable.

With a bit of research I have found that ConnectionManager::alias should be what I need, so I came up with this:

// Fetching `energy` DB data
ConnectionManager::alias('energy', 'default');
$energy = $this->Users->find()->first();
debug($energy);
ConnectionManager::dropAlias('default');

// Fetching `default` (gas) DB data
$gas = $this->Users->find()->first();
debug($gas);

The problem is that if I run those those queries like this the debug gives the same data, meaning that the data from the energy database is printed two times.

But:

It looks like only the first datasource is actually used.

What I'm doing wrong?

Upvotes: 0

Views: 665

Answers (1)

ndm
ndm

Reputation: 60463

Once a table class has been instantiated, it will hold the connection instance that was assigned to it when it was instantiated (connection option for \Cake\ORM\Table::__construct()), respectively the one that was created when the table first tried to obtain the connection (\Cake\ORM\Table::getConnection()).

So dropping the alias alone won't do anything, the next call on the table will use the connection instance that the table already holds, it will not attempt to obtain a connection again on its own.

Since you want the connection change to affect everything, you will have to clear the table registry after dropping the connection alias, and load the tables again, so that that they will be created again with the actual default connection (if you wanted to affect only specific instance, you could've assigned a new connection instance via setConnection()). To avoid running into the same problem with already created table instances when creating the alias, you should most probably also clear the registry after that.

Basic example:

ConnectionManager::alias('energy', 'default');
$this->getTableLocator()->clear();
// table instances created after this point will respect the alias

$this->Users = null;
$this->loadModel('Users');
$energy = $this->Users->find()->first();

ConnectionManager::dropAlias('default');
$this->getTableLocator()->clear();
// table instances created after this point will use the non-aliased connection

$this->Users = null;
$this->loadModel('Users');
$gas = $this->Users->find()->first();

As you can see, this not only requires loading the model/table again, but also to unset the instance on the property, as otherwise loadModel() will not actually load the table again, but instead return the instance that is already set on the $this->Users property.

I would suggest that you switch to obtaining your models via the table locator, even in your controller, that way you can skip this awkward unset stuff, and you reduce the risk of accidentally accessing old instances stored on properties like $this->Users:

ConnectionManager::alias('energy', 'default');
$this->getTableLocator()->clear();

$energy = $this->getTableLocator()-get('Users')->find()->first();

ConnectionManager::dropAlias('default');
$this->getTableLocator()->clear();

$gas = $this->getTableLocator()-get('Users')->find()->first();

You should probably also consider centralizing this connection switching somewhere in a service.

Upvotes: 1

Related Questions