How to Use Multiple Databases Dynamically for One Model in Cakephp

How to use multiple databases dynamically for one model in CakePHP

This gentleman (Olivier) had the same problem! (A year ago) He wrote a small adaptation for the Controllers! It's pretty small and it turns out, it works in 1.3 and 2.x.

Anyhow, this is my final solution, that I put in the app/Model/AppModel.php:

class AppModel extends Model
{
  /**
   * Connects to specified database
   *
   * @param String name of different database to connect with.
   * @param String name of existing datasource
   * @return boolean true on success, false on failure
   * @access public
   */
    public function setDatabase($database, $datasource = 'default')
    {
      $nds = $datasource . '_' . $database;      
      $db  = &ConnectionManager::getDataSource($datasource);

      $db->setConfig(array(
        'name'       => $nds,
        'database'   => $database,
        'persistent' => false
      ));

      if ( $ds = ConnectionManager::create($nds, $db->config) ) {
        $this->useDbConfig  = $nds;
        $this->cacheQueries = false;
        return true;
      }

      return false;
    }
}

And here is how I used it in my app/Controller/CarsController.php:

class CarsController extends AppController
{
  public function index()
  {
    $this->Car->setDatabase('cake_sandbox_client3');

    $cars = $this->Car->find('all');

    $this->set('cars', $cars);
  }

}

I'm betting, I'm not the first or last one with this problem. So I really hope this information will find people & the CakePHP community.

CakePHP using multiple databases for models

This site has a way to do it, but it's a little hacky.

CakePHP 3 Multiple Databases

Follow the Below Instructions

Step 1 : Open config/app.php Find Datasources array and add Remote Database configuration like as -

'Datasources' => [
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'localhost',
        'port' => '3306',
        'username' => 'YOUR_DB_USER',
        'password' => 'YOUR_DB_PASS',
        'database' => 'YOUR_DB_NAME',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'flags' => [],
        'cacheMetadata' => false,
        'log' => false,
        'quoteIdentifiers' => false,
        'url' => env('DATABASE_URL', null),
    ],
    'remote_db_1' => [ /*Remote Database 1*/
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => '192.168.1.47', /*YOUR_REMOTE_SERVER_IP*/
        'port' => '3306',
        'username' => 'REMOTE_DB_USER',
        'password' => 'REMOTE_DB_PASS',
        'database' => 'REMOTE_DB_NAME',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'flags' => [],
        'cacheMetadata' => false,
        'log' => false,
        'quoteIdentifiers' => false,
        'url' => env('DATABASE_URL', null),
    ],
   'remote_db_2' => [ /*Remote Database 2*/
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => '192.168.1.47', /*YOUR_REMOTE_SERVER_IP*/
        'port' => '3306',
        'username' => 'REMOTE_DB_USER',
        'password' => 'REMOTE_DB_PASS',
        'database' => 'REMOTE_DB_NAME',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'flags' => [],
        'cacheMetadata' => false,
        'log' => false,
        'quoteIdentifiers' => false,
        'url' => env('DATABASE_URL', null),
    ],

Step 2 : Now you can access Remote database in your controller like as-

use Cake\Datasource\ConnectionManager;
use \PDO;

class YourController extends AppController{
    public function getRemoteData(){
      $conn1 = ConnectionManager::get('remote_db_1'); #Remote Database 1
      $conn2 = ConnectionManager::get('remote_db_2'); #Remote Database 2
    }
}

Note: Now you can use PDO methods to Insert,Retrieve,Update

Example :

class YourController extends AppController{
    public function getRemoteData(){
      $conn1 = ConnectionManager::get('remote_db_1'); #Remote Database 1
      $sql   = "SELECT * FROM  users";
      $query = $conn1->prepare($sql);
      $query->execute();
      $result = $query->fetchAll(); #Here is the result
    }
}

Dynamically change database connection in cakephp 3

Use the ConnectionManager::config() function to create connections on the fly and the ConnnectionManager::alias() method to make all your Table classes use it by default.

There is a very good article describing the process here:

http://mark-story.com/posts/view/using-cakephp-and-a-horizontally-sharded-database

The only difference is that you can create the connection config on the fly instead of declaring the shards manually as it was shown in that article.

Retrieve associated model from different database using cakephp 3

You could try setting the strategy in the association to select. This might help. There's no full support for cross schema associations yet. I've put some work into it but it won't solve all cases.

I'm not sure in which CakePHP 3 version it got merged but I do recommend staying up-to-date with the release cycles.

Multiple database connection in cakephp 3

You can declare the defaultConnectionName() method on the tables that will use by default another connection. In any of your Table classes:

public static function defaultConnectionName()  
{
    return 'another_config_name';
}

---

Related Topics