In my application build with cakephp 4.4 I have to have two databases one database for regular options and one database for the user which connection details stored in the first database (regular options) My problem is, when user logged in I have to load database credentials for the regular db an build a connection for the customer. Is there a specific way how to solve the? What is the best one, does anybody got a similar problem and maybe a hint or solution??
You can define a different connection for each table overriding defaultConnectionName() Table Objects - 4.x
If you share the credentials between databases you can use the method $this->setTable(‘database.table’) inside a Table object Class Table | CakePHP 4.4
PD: does every user has its own database? do all those databases share the same schema?
That’s the point each User (or User group) have his own db. With there own credentials which nobody knows (for security reason) So I can not have defaultConnectionName which I have setup in app.php But I can do it with the ConnectionManager but for this I have to dive into the vendor code in table.php and tableLocator etc… Which I did in previous versions but now I’m sick do it this way. Also know I have changed some code in cake so it works for me but for all version upgrades and updates I have to change this code. Which I don’t want.
There should be a way to register somehow this connection instance for a user in all models I can define the defaultConnectionName so each query knows the connection instance for which it has to call the correct database. That’s something I miss in cake…
Haven’t needed to do this, so I don’t know if it would work, but maybe some bit of middleware could set that up for you? Take a look at what the normal startup process does with connection details from the config, and mimic it using details from your credentials database?
public function initialize(): void
{
parent::initialize();
$this->loadComponent('Authentication.Authentication');
$this->loadComponent('CrossDatabase');
}
CrossDatabaseComponent.php
<?php
declare(strict_types=1);
namespace App\Controller\Component;
use Cake\Controller\Component;
use Cake\Database\Connection;
use Cake\Datasource\ConnectionManager;
use Cake\Database\Driver\Mysql;
/**
* CrossDatabase component
*/
class CrossDatabaseComponent extends Component
{
/**
* Other Components this component uses.
*
* @var array
*/
protected $components = ['Authentication.Authentication'];
/**
* Default configuration.
*
* @var array<string, mixed>
*/
protected $_defaultConfig = [];
/**
* Constructor hook method.
*
* Implement this method to avoid having to overwrite
* the constructor and call parent.
*
* @param array<string, mixed> $config The configuration settings provided to this component.
* @return void
*/
public function initialize(array $config): void
{
$identity = $this->Authentication->getIdentity();
if (is_null($identity)) {
return;
}
ConnectionManager::setConfig('custom', [
'className' => Connection::class,
'driver' => Mysql::class,
'persistent' => false,
'host' => 'localhost',
'username' => $identity->get('db_username'),
'password' => $identity->get('db_password'),
'database' => $identity->get('db_name'),
'encoding' => 'utf8mb4',
'timezone' => 'UTC',
'cacheMetadata' => true,
]);
}
}
UsersTable.php
public static function defaultConnectionName(): string
{
return 'default';
}
CustomTable.php
public static function defaultConnectionName(): string
{
return 'custom';
}
Again, I haven’t done this, but I don’t see why you can’t do it with middleware. The user is “known” well before the end of the middleware stack is reached. You just have to make sure your new middleware is run after the authentication one.
use Cake\Datasource\ConnectionManager;
private $db;
public function initialize()
{
parent::initialize();
$this->db = ConnectionManager::get('db_connection_name');
}
In Model
public static function defaultConnectionName()
{
return 'db_connection_name';
}
I’ve checked it out. And the problem is, that cake/ConnectionManager will be started first before CrossDatabaseComponent can be setup setConfig… so on some runtime values it works but not all the time. On fast systems I can’t get setup Config before cake/Connectionmanager::get(‘custom’)
Thank you daroath. I use this in models but this doesn’t work! I use cakephp 4.4.11. In this version I use to have use $this->fetchTable($alias, $options) (from LocatorAwareTrait.php) to get a Model.
But for $options I have to set [‘connection’ => ConnectionManager::get(‘custom’)] without this $alias can’t be find in the correct database. I thought this can be find be using Model::defaultConnectionName(). But this is wrong. defaultConnectionName() will not been used at all!
public static function defaultConnectionName()
{
return 'db_connection_name';
}
I used above function in model. It works on both Cakephp 3 and 4. Make sure you use the correction function name and change the database name to match with your database name. Check this Configuring Connections. You can also mixed the two databases using RAW SQL. Hope this help.
