Dynamically select a database with CakePHP 1.3

phew,that bread gave me wind?

In one of my projects, I have several customers all served from the same CakePHP application. This is by no means unique, and is a very well understood pattern of maintaining co-exiting websites. In my set up, each of the customers has their own website, with their own data and design, but there is only one copy of the actual application behind all those websites. This is, of course, a fundamental attribute of selling SaaS.

From a database point of view, there are three options of managing such a set up:

  1. Add a customer identifier column to all your tables
  2. Store a separate set of tables for each customer (normally using a table prefix)
  3. Use a separate database for each customer

Each of these options require that your application can identify the relevant customer (not necessarily the user!)  on each request. This is very naturally achieved by using the domain name in the request.
So say we have the following layout, where all the following domains map to my application:

  • mygreatcustomer.com.au
  • anothergoodone.co.nz
  • someoneelse.org.ag

In PHP, I can easily identify what domain was requested, using $_SERVER['SERVER_NAME']
When I can identify the domain (that is – the relevant customer) it is very easy to use option (1) above – just use the domain as the customer identifier column.
But what if you don’t want to use this option? What if you want to use option (2)?

CakePHP v1.2 let you do that. You could set the table prefix on the fly (in database.php):

//in database.php
var $default = array(
 'driver' => 'mysql',
 'persistent' => false,
 'host' => 'kiwihost',
 'login' => 'mate',
 'password' => 'secure_as',
 'database' => 'mydatabase',
 'prefix' => '',
);

function __construct() {
   $host = $_SERVER['SERVER_NAME'];
   $host = str_replace("www.", "", $host);   //remove www part
   $host = str_replace(".", "_", $host);     //a period is illegal in mysql table names
   $this->default["prefix"] = $host."_";     //so www.anothergoodone.co.nz translates into the prefix anothergoodone_co_nz_
}

But – the ability to do this dynamically has been removed in Cake 1.3, as table prefixes are now stored in the temporary model files. To fix this, you’d need to refresh those files on each new request, which makes the whole point of using those files moot. If you used option (1) to begin with – you’re in luck. But, if not, you might want to consider moving to option (3) – separate databases for separate customers.

From a maintenance point of view, there are many reasons why this isn’t necessarily the best choice, and I had a long discussion with Scalebase‘s Liran Zelkha about it – a discussion that I’ll post here at a later stage.
Anyway, say you decided you want to split your SQL data along the customer boundary using this method – let’s see how you do it in Cake.

First, you’ll want to split your data up into multiple databases. I suggest you name your databases to the domain name, replacing ‘.’ with ‘_’. This way – the change in the __construct() functionis very elegant.

Second, take the __construct() function shown above, and make this simple change:

function __construct() {
   $host = $_SERVER['SERVER_NAME'];
   $host = str_replace("www.", "", $host);   
   $host = str_replace(".", "_", $host);     
   $this->default["database"] = $host."_";   
}

That’s it, really. Your application now selects the database on the fly, based on the domain part of the HTTP request coming in.

But what if I don’t have an HTTP request?

Sometimes, you don’t have an HTTP request. In my scenario, this happens with cron jobs.
When you have a large number of customers, each with their own domain, you don’t always want to duplicate cron jobs just because they need to use a separate database.
Using table prefix – this was easy. Your cron job (well, a shell in Cake) just needed to cycle through the prefixes one by one. However, this is not as easy when you have
to switch to a different database with every customer, as you don’t have a request (so no $__SERVER) and database.php isn’t exposed.

In the old world of Cake 1.2, you might have stored a table of sites in your database (the only one with no prefix!) and used it like so:

//This is inside a cake shell
function main() {
    $sites = $this->Site->find('all',array('conditions' => array('Site.active' => '1') ));
    foreach ($sites as $site) {
        $table_prefix = $site["Site"]["table_prefix"];
        $this->setTablePrefix($table_prefix);
        //In the loop body - perform the cron-run for each of the customer websites
    }
}

function setTablePrefix($prefix) {
    //set the prefix on each of the models you use
    $this->Country->tablePrefix = $prefix;
    $this->City->tablePrefix = $prefix;
    //etc
}

However – this doesn’t work any more. In Cake 1.3 we are not using the table prefixes but rather a different database, so we have to use a different method:

//This is inside a cake shell
function main() {
    $sites = //whichever way you want to store your sites
    foreach ($sites as $site) {
        $newDbConfig = $this->dbConnect(array('database'=>$site,'datasource'=>'default'));
        $this->setConnection($newDbConfig);
        //In the loop body - perform the cron-run for each of the customer websites
    }
}

function setConnection($newDbConfig) {
    //set the DB config on each of the models you use
    $this->Country->useDbConfig = $newConfig['name'];
    $this->Country->cacheQueries = false;
    $this->City->useDbConfig = $newConfig['name'];
    $this->City->cacheQueries = false;
    //etc
}

2 thoughts on “Dynamically select a database with CakePHP 1.3

  1. Pingback: CakePHP | ????

  2. Pingback: CakePHP Articles | ????

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>