How to use multiple databases dynamically for one model in CakePHP

Question

Ok, my first question was modified so many times that I chose to delete it and reformulate my question. I've made a small test-case project with different model-names to find a proper solution for my problem.

Warning: Don't mix the databases up with tables

Motivation: I separated the user-data into multiple databases for legal & performance issues.

Currently I'm working on a CakePHP project that has multiple User's and each User has it's own database with multiple tables (cars is one of the tables). Now, I need to explain something first:

Every User has his own database (not a table, a database), so the database names are as follows.

• [DATABASE] app (This is the app's main database)
  • [TABLE] users
  • [TABLE] permissions (Not relevant for this question)
• [DATABASE] app_user1 (User.id 1 owns this entire database)
  • [TABLE] cars (a table entirely owned by User.id 1)
• [DATABASE] app_user2 (User.id 2 owns this entire database)
  • [TABLE] cars (a table entirely owned by User.id 2)
• etc...

I made a small drawing which might clarify the database / table -definitions and their relations to the models:

The problem!!!

I don't know which database to connect to until the User logs in. User's and their databases are created dynamically, so I cant use app/Config/database.php.

So I'm currently writing an extension on the Model and ConnectionManager classes to bypass CakePHP's basic database behaviors. So the Car model knows which database to use. But I just have a feeling this could be done easier!

So I guess it all boils down to one question:

Is there an easier way of doing this?!

Thanks to anyone who will take the time and effort of reading and understanding my problem!

Cuticle

Answer 1

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.

Cuticle

Made a few changes, so you can switch any model to a different database on the fly, as long as the database-user has the same permissions. You only need to put 1 extra line in you Controller – Cuticle

+1 @ "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." – Mccluskey

Thank you very much... Your code helped me.. I got Strict (2048): Only variables should be assigned by reference [APP\Model\AppModel.php, line 46] but after removing & from $db = &ConnectionManager::getDataSource($datasource); it's working nice – Macroscopic

@ShaunakShukla: It could be that the getDataSource method doesn't retrieve a reference anymore, but a copy of the value instead. In that case you're totally right, it wouldn't work in strict-mode. – Cuticle

@Cuticle The Error will not come. if you edit above code. You wirte $db = ConnectionManager::getDataSource($datasource); instead of $db = &ConnectionManager::getDataSource($datasource); . Actually i removed only "&" sign. – Lipocaic

Hello - I am facing the same issue and I used your solution. It works well but the problem I am facing is that when I set the database name in Cars - it does not propagate to other related models. I have to set the database name individually in every model from my controller. Did you solve this scenario by any chance? – Oquendo

Are you sure you extended your AppModel with the setDatabase function? This handle the switch. I'm not very up to date on CakePhp's current code impl. so if Cake's implementation changed, it might not work anymore. I'll look into it this evening. – Cuticle

@Cuticle The above is not working for hasOne tables for any model, even if we setDatabase in controller for hasOne table. Any thing i need to do? – Fraud

@Anupal, the code should work for any type of table-relation. It mainly switches the database connection. It simply does not interfere with that part of CakePHP. You should probably check your implementation of your solution for 1 database first (without my implementation). It could also be possible that Cake changed their framework to such an extend, it doesn't work anymore. Which version are you using? – Cuticle

@Cuticle It is v2.5.7 I have implemented the same model which was working with 1 datasource. Its working for 1 level 1 model but not for related tables like hasOne table. – Fraud

And you're sure all the tables necessary are accessible in both/all other databases? – Cuticle

@Cuticle It is connecting to "default" dataSource which i've set when no setDatabase required where no table like this is there. – Fraud

@Anupal, So it's not properly connecting to your Alternative DB's. In that case it might be, because Cake's internals has changed, which would mean you need to look for a different implementation. You should be able to see if Cake still implemements it's own ConnectionManager in the same way. I'm afraid I can't help you any further if that's the case. – Cuticle

Let us continue this discussion in chat. – Fraud

I figured it out using the Gentleman solution. – Fraud

I am having issue while connecting each db's in foreach loop one by one. It select 1st database in each loop. Is it cache issue ? – Fraud

What is the issue? How many databases are talking about here? – Cuticle

Urgent: I am having issue while connecting each db's in foreach loop one by one. It selects 1st database in each loop. Is it the cache issue? Please help! – Fraud

@Fraud stating the exact same text does not get me closer to understanding. – Cuticle

@Cuticle There are about 10 databases which i want to connect in loop one by one and then fetch their inner table data. – Fraud

@Fraud I never needed more then 5 (because of limited dealers) but I've never run into any physical limits before, what is CakePHP debug telling you about memory usage and script execution time? – Cuticle

Let us continue this discussion in chat. – Fraud

Answer 2

I don't like the idea of writing down the actual name of the database in the code. For multiple databases, you have the database.php file where you can set as many databases as you need.

If you want to "switch" a database for a specific model on the fly, use the setDataSource method. (see here)

For example, if you have two databases, you can define them in the database.php file as "default" and "sandbox", as an example.

Then, in your code:

$this->Car->setDataSource('sandbox');

The sandbox is the name of the configuration, and the actual name of the database is written only once in the database.php file.

Always

Answer 3

You can always query any databases using full notation in mysql. F.ex.:

SELECT * FROM my_schema_name.table_name;

Cake way:

$db = $this->getDataSource();
$query = array(
    'fields' => array('*'),
    'table' => 'my_schema_name.table_name'
);
$stmt = $db->buildStatement($query, $this);
$result = $db->execute($stmt);

Bacchus

Yes, you are correct. However, you are breaking/bypassing the CakePHP conventions from that point forward, within that model.I don't mean to insult but your solution is suitable as a quickfix for models that don't have any deeper relations to other models. – Cuticle

This solution is for cases where you have hundreds of customer databases and you do not want to create hundreds of database connections to generate, let's say, a summary of data from all customer databases due to performance issues. – Bacchus

Yes, indeed it does. Though it's not what was needed in my case/scenario. Because once a user is logged in, it doesn't require to keep switching databases until a new user logs in. – Cuticle

Answer 4

In your database.php

public $mongo = array(
    'datasource' => 'Mongodb.MongodbSource',
    'database' => 'database_name_mongo',
    'host' => 'localhost',
    'port' => 27017,
);

In your controller you can use

$this->Organisation->setDataSource('mongo');

then apply query like

this->Organisation->find('all');

Klute

Answer 5

This is a folk of the accepted answer. This will prevent the error if the new datasource has already existed:

public function setDatabase($database, $datasource = 'default')
{
    $newDatasource = $datasource . '_' . $database;
    try {
        // return the new datasource if it's already existed
        $db = ConnectionManager::getDataSource($newDatasource);
        $this->useDbConfig  = $newDatasource;
        $this->cacheQueries = false;
        return true;
    } catch (Exception $e) {
        // debug($e->getMessage());
    }

    $db  = ConnectionManager::getDataSource($datasource);
    $db->setConfig(array(
        'name'       => $newDatasource,
        'database'   => $database,
        'persistent' => false
    ));

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

    return false;
}

Capitalization

Very elegant, thank you for maintaining the solution :) – Cuticle