Codeigniter – Connect to two different (multiple) databases

In a job application interview, the employer asked me if I made some “database scalability” in the past. To my shame, I didn’t even understand what that is, but now I can confidently say that I know the basics.

So, according to Wikipedia, “database scalability” consists of one or more different approaches to enable databases to grow to very large sizes while supporting an ever-increasing rate of transactions per second.

One of these approaches is by scaling out a database “across a cluster of separate database servers” (mind-blowing for noobs like me, but quite simple once you understand the basics).

Taking in consideration that we have the CodeIgniter framework installed, and we also have two databases located one on localhost (or wherever is located) and the other on another server (let’s say having the IP: XXX.XXX.X.XXX), the process would go as follows:

Step 1 – Establish the connections’ parameters for both databases

We do this by changing the database.php which is located inside application/config folder. Inside the file we will find something similar to:

$active_group = 'default';
$active_record = TRUE;

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'user';
$db['default']['password'] = 'pass';
$db['default']['database'] = 'your_db';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

We need to change the array element $db[‘default’][‘pconnect’] (permanent persistent connection) to FALSE, because, if set to TRUE, sometimes the application gets stuck between databases (didn’t happen to me, I just read that on stackoverflow).

After that, in order to add another database to the application, we add the following lines:

$db['anotherdb']['hostname'] = 'XXX.XXX.X.XXX'; // here you must change XXX with the IP where the second database is located
$db['anotherdb']['username'] = 'another_user';
$db['anotherdb']['password'] = 'another_pass';
$db['anotherdb']['database'] = 'another_db';
$db['anotherdb']['dbdriver'] = 'mysql';
$db['anotherdb']['dbprefix'] = '';
$db['anotherdb']['pconnect'] = FALSE;
$db['anotherdb']['db_debug'] = TRUE;
$db['anotherdb']['cache_on'] = FALSE;
$db['anotherdb']['cachedir'] = '';
$db['anotherdb']['char_set'] = 'utf8';
$db['anotherdb']['dbcollat'] = 'utf8_general_ci';

Of course, you can change ‘anotherdb‘ to whatever term you deem appropiate (‘forumdb’,’admindb’, etc.).

Now we can say that we have two databases at our disposal, from which one is set as default (the ‘default’ one).

Step 2 – Using the new database

In my opinion, it’s better to keep the two databases’ queries in separate models. It would be a big mess if you would have a model that is connecting to both databases, don’t you agree?

Having this in mind, you can work as usual with models when you need to query the “default” database.

If you need to query the other database, you can make a model as follows:

The model (anotherdb_model.php) would look as below:

<?php

if(!defined('BASEPATH')) exit('No direct script access allowed');

class Anotherdb_model extends CI_Model
{
  private $another;
  function __construct()
  {
    parent::__construct();
    $this->another = $this->load->database('anotherdb',TRUE);
  }

  public function getSomething()
  {
    $this->another->select('somecol');
    $q = $this->another->get('sometable');
    if($q->num_rows()>0)
    {
      foreach($q->result() as $row)
      {
        $data[] = $row;
      }
    }
    else
    {
      return FALSE;
    }
  }
}

Although in the Codeigniter manual says that the database connections are automatically closed, someone (John Anderson) advised me to put a $this->anotherdb->close() at the end of the function. That is because, “Even though the connection isn’t persistent, it won’t close automatically. (Correspondingly, if you have left the pconnect setting as TRUE, calling the close method shouldn’t work.)”.

That’s it… Questions? Did I do something wrong? Please, leave a comment.