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) {
			$client_id = Configure::read('Client.id');
				$cookie = ClassRegistry::init('CookieComponent');
				$cookie->key = Configure::read('Security.salt');
				$client_id = $cookie->read('client_id');
			$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);
				$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;
		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.