Dynamic Database Selection in CakePHP – Approach to SAAS

Separate database for individual client is a common design approach for SAAS (Software as a service) model implementation. And while its about CakePHP, it makes life difficult as it reads the database connection from ‘database.php’ script. Its not possible, or rather not viable, to put a new database connection in the ‘database.php’ file each time a client registers.

Here is what I have done to accomplish or overcome the problem, inspired from this article. They used to get the client information from the url, but in my case I had to get the info from Cookie or Configure::read() method.

The first step : Specify a new variable in each of the models which are specific to client.

class YOUR_CLASS extends AppModel {
	var $specific = true;
}

Now we check for this variable while loading the model. If its true then we have to make the database connection dynamically which is achieved in ‘app_model.php’ under ‘app’ directory.

class AppModel extends Model {
	var $specific = false;
	
	function __construct($id = false, $table = null, $ds = null) {
		
		if($this->specific){
			
			$client_id = Configure::read('Client.id');
			if(!$client_id){
				$cookie = ClassRegistry::init('CookieComponent');
				$cookie->key = Configure::read('Security.salt');
				$client_id = $cookie->read('client_id');
				if(!$client_id){
					return;
				}
			}
			$product_id = Configure::read('Product.id');
			$db_config = ConnectionManager::getDataSource('default')->config;
			
			$link = mysql_connect($db_config['host'], $db_config['login'], $db_config['password']);
			mysql_select_db($db_config['database'], $link);
			$res = mysql_query('SELECT * FROM client_database WHERE client_id = '.$client_id.' AND product_id = '.$product_id);
			$row = mysql_fetch_assoc($res);
			
			if(isset($row['host'])){
				$dbName = $row['db_name'];
				$config = array();
				// Set correct database name
				$config['driver'] = 'mysql';
				$config['persistent'] = false;
				$config['host'] = $row['host'];
				$config['login'] = $row['username'];
				$config['password'] = $row['password'];
				$config['database'] = $dbName;
				$config['prefix'] = '';
				
				// Add new config to registry
				ConnectionManager::create($dbName, $config);
				// Point model to new config
				$this->useDbConfig = $dbName;
			}else{
				return;
			}
		}
		parent::__construct($id, $table, $ds);
	}
}

Its a full hack, which totally breaks the MVC (Model View Controller) structure of CakePHP, using Cookie in Model. And also using Mysql functions prevents use of any other data source. So I recommend to get the idea from given code and implement something more structured and flexible way to do this. And please share if you can implement a better way such that.

Advertisements

5 Responses to Dynamic Database Selection in CakePHP – Approach to SAAS

  1. Arijit says:

    Thanks Dyutiman for this excellent article.But can u plz help me for a situation where I need to access and insert data in multiple database(20+) on the fly,ie. how to create multiple connection dynamically through controller.

    • dyutiman says:

      while looping through the clients we were calling this function
      function addModels($client_id = null, $models = null) {
      if(!is_null($client_id) && is_numeric($client_id) && !is_null($models) && is_array($models)) {
      Configure::write(‘Client.id’,$client_id);
      foreach($models as $model) {
      ClassRegistry::removeObject($model);
      $this->loadModel($model);
      }
      return true;
      }
      return false;
      }

      After calling this the database get selected for the client whose id was passed. Now just call the save method.
      Let me know if that works for u.

  2. saved my life , great post !

  3. adam says:

    Very nice. I’ve looking for a way to load SQLite databases dynamically given a specific user. Does this implementation work for models in cake 1.3?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: