How to use multiple database server with CodeIgniter ?

Use multiple database server with CodeIgniter 3  or CodeIgniter 2

Before some time i got a chance to work on an interesting project. That was developed using Codeigniter as framework. Now, the challenge is that we need to use two differ database server as well as different RDBMS server. One is Mysql and other one is Postgresql. It was really simple to use two database. This is the way I did this for two database same can be used for multiple databases.
So here i am going to show you how you can achieve this. First set your db configuration for both db into config/Database.php. First define your default db settings. In my case default is MySql.

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'codeigniter';//';
$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;

Now define your second db settings. In my case this one is PgSql.

$db['postgredb']['hostname'] = 'localhost';
$db['postgredb']['username'] = 'root';
$db['postgredb']['password'] = '123456789';
$db['postgredb']['database'] = 'codeigniter';//';
$db['postgredb']['dbdriver'] = 'postgre';
$db['postgredb']['port'] = 5432;

Database configuration is done. Now you can can connect to your database by using.

$this->load->database();

This will connect to your default DB. You need to manually connect to postgredb. Using this.

$this->load->database('postgredb');

Hence we want to connect to multiple database simultaneously. We need to do in following way

$DEFAULT_DB = $this->load->database('default', TRUE);
$POSTGRES_DB = $this->load->database('postgredb', TRUE);

And instead of using

$this->db->query();

We need to use in this manner

$DEFAULT_DB->query('QUERY');
$POSTGRES_DB->query('QUERY');

Also you can create your on Model Class by extending CI_Model and handle this connection related stuff into that. To do this create a file named application/code/MY_Model.php
Code of this file will be look like

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

/**
 * Description of MY_Model
 *
 * @author OsinLab
 */
class MY_Model extends CI_Model 
{
    public $DEFAULT_DB;
    public $POSTGRES_DB;
    public function __construct() {
        parent::__construct();
        $this->DEFAULT_DB = $this->load->database('default', TRUE); //Load default DB
        $this->POSTGRES_DB = $this->load->database('postgredb', TRUE); //Load PG DB
    }
}

/* End of file MY_Model.php */
/* Location: ./application/core/MY_Model.php */
Now you can use this class like

<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
/**
 * Description of test_model
 *
 * @author OsinLab
 */
class Test_model extends MY_Model
{
	public function test ()
	{
		//Run query on mysql DB
		$this->DEFAULT_DB->query('QUERY');
		//Run query on PGDB
		$this->POSTGRES_DB->query('QUERY');
	}
}
/* End of file test_model.php */
/* Location: ./application/model/test_model .php */

For more detail see here

Leave a Reply

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