Codeigniter – Connect to two different (multiple) databases

Created at: September 26, 2013; Last update: January 15, 2015

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:

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:

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:

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.

10 thoughts on “Codeigniter – Connect to two different (multiple) databases

  1. Pingback: Vài đường link có ích | HocLapTrinhOnline

  2. Jason Locashio

    Adrian,

    While your post shows how to connect to 2 separate databases, this isn’t really an accurate example of database “scalability”, since each database would house separate tables, and the data between them would not be replicated. If server requests went up, this solution wouldn’t, in and of itself, handle the increased load. It will, obviously, reduce a single server’s overhead, which would help, but the issues created (mostly in regards to joining data from tables in different DB’s) aren’t worth the small gain.

    If clustering is outside of your ability or budget, there is a possible solution that this could provide, assuming you have the server overhead to support it in a timely manner. You could create duplicate databases on both servers and do inserts/updates to both so the data stays synchronized. Then you can build out a custom model class that will failover to the secondary DB if the first is unresponsive or overloaded. You would need to keep a list of the failed insert/update queries to perform on the downed server once it’s back up (through a cron job or something). That way, you always have a current DB to work with, with all tables and data available to query against.

    Obviously, this method is likely to be significantly slower than clustering, but where budget or ability are the chief concerns, this is a viable option.

    Reply
    1. avenirer Post author

      Thank you very much for your comment. It’s been inspiring. I actually never encountered the need for scalability (imagine that the biggest table I’ve met was one with aproximately 10k records). Indeed, this post is not really about scalability – subject that would require an entire book (or more for that matter), but is a good starting point to talk about it before talking about connecting to two different databases.

      Reply
  3. Jason Locashio

    Oh, and your model code sample failed to declare the another class property, so it would likely fail as defined.

    That is, after your class declaration:

    You need to have something like this

    so that calls to $this->another know what you are referring to.

    Reply
  4. Ashok Yadav

    Really you have touched a good topic. I am working on CRM(Customer Relationship Management) in Code Igniter & I was looking for how to connect to database on another server.

    Reply
  5. chris

    is possible add query $query = $this->db->get(‘user.table@other_schema’); in codeigniter, if answer is true, please comment it’s question. Thank so much!!

    Reply
  6. Federico

    Hi Adrian, I have a question about configuration.
    In my project public user get access to a specific database ( for inquiry or registering new infos ), Admin user (using his own Admin panel) has access to the samne database for full administration activities.
    I’m wondering, for security purpose, if I can set, in the configuration file and for the same db, two diferent user/password data since public user should get limited access while admin user should get full access.
    Thanks a lot for any feedback

    Reply
    1. avenirer Post author

      You can do whatever passes your mind. You are not restricted to use two connections only for two different databases. You can use the connections for same database. What I don’t understand is why do you think that a user, using his own admin panel (hence only the controllers and methods and models you provide him) could access something else from your database? 🙂

      Reply

Leave a Reply

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

No spam? * Time limit is exhausted. Please reload CAPTCHA.