Yii 1.1: Multiple-database support in Yii

40 followers

The customary configuration of a Yii application includes just a single database section in the protected/config/main.php file, but it's easy to extend this to support more than one, tying each Model to one of the databases.

We'll extend the standard blog example to tie into a separate Advertising database: though it's related to the blog, it's still an independent system.

Config Setup

The first step configures the second database into the configuration next to the first DB, and though you can call it db2 if you want, it it's perhaps helpful to name it more usefully: we're calling it dbadvert:

// protected/main/config.php
 
return array(
    ...
    'components' => array(
        'db' => array(
            'connectionString' => 'mysql:host=dbserver1;dbname=my1db',
            ...
        ),
        'dbadvert' => array(
            'connectionString' => 'mysql:host=adserver2;dbname=advertisingDB',
            'username'         => 'advertuser',
            'password'         => '***********',
            ...
            'class'            => 'CDbConnection'          // DO NOT FORGET THIS!
        ),
        ...

The parameters should generally follow the pattern of the first entry, but you must include the class parameter in the second so that Yii knows you're defining a DB Connection object. It will fail without this.

Once this is defined, the second database is referred to as Yii::app()->dbadvert rather than Yii::app()->db (of course, the first is still available).

But we can do much better integration than this, starting with Gii and ending with AR support.

Using Gii

Gii can use multiple database connections in Yii > 1.1.11.

If you are using a previous version, Gii only knows how to use the primary database connection, so for a brief time while creating models/controllers/crud, you'll have to edit your protected/config/main.php file to temporarily make the advertising database the primary db connection:

// protected/config/main.php
    'components' => array(
#       'db' => array(
#           'connectionString' => 'mysql:host=dbserver1;dbname=my1db',
#           ...
#        ),
        // TEMPORARY (put back to 'dbadvert' when done)
        'db' => array(
            'connectionString' => 'mysql:host=adserver2;dbname=advertisingDB',

Once this is done, use the Gii code generator to create what you need, then edit your config file back to make both database connections live.

GetDbConnection() override

Every model defined in protected/models/*.php includes GetDbConnection() in the base class, and it returns a handle to the DB connection object for the primary database. We need to override this method in the models representing the advertising database to return the second DB connection.

Though it's possible to do this in the model definition file itself, this doesn't scale well as it would duplicate a lot of code if more than one model lives in the Advertising database. Better is to use a custom wrapper class to CActiveRecord where this can be centralized.

The notion of custom wrapper classes is described in this wiki article, and we'll assume that you've created a protected/components/MyActiveRecord.php file, and taught all of your model files to extend MyActiveRecord rather than CActiveRecord.

// protected/components/MyActiveRecord.php
 
class MyActiveRecord extends CActiveRecord {
    ...
    private static $dbadvert = null;
 
    protected static function getAdvertDbConnection()
    {
        if (self::$dbadvert !== null)
            return self::$dbadvert;
        else
        {
            self::$dbadvert = Yii::app()->dbadvert;
            if (self::$dbadvert instanceof CDbConnection)
            {
                self::$dbadvert->setActive(true);
                return self::$dbadvert;
            }
            else
                throw new CDbException(Yii::t('yii','Active Record requires a "db" CDbConnection application component.'));
        }
    }
    ...

This method is purposely static: the underlying cached $dbadvert value is, so the function may as well be be too. Now, with this helper prepared, we can edit the model itself:

// protected/models/Ad.php
 
class Ad extends MyActiveRecord {
    ...
    public function getDbConnection()
    {
        return self::getAdvertDbConnection();
    }
    ...

Now this model will properly fetch from the Advertising database instead of the blog database, and this can be extended to as many models as you like.

Limitations on Multi-DB support

  • Tables in one database cannot directly reference tables in another database, and this means that relations don't cross DB boundaries.

Total 14 comments

#16274 report it
Elecen at 2014/02/05 04:51am
Switching databases in realtime

@Boaz Actually what I did in the end was that I deactivated the connection, changed the connection string, and reactivated the connection. That's all it takes pretty much. However, keep in mind that if the databases have differences in structure (i.e. applying migrations) you need to clear the cache or use different cache prefix for the connection (I find the different prefix to be the best solution in my project).

#16273 report it
Paul_Kish at 2014/02/05 04:42am
Using databases of different servers

@Rienier Patron Did you forget to add this property?

'class'            => 'CDbConnection'          // DO NOT FORGET THIS!
#16272 report it
Rienier Patron at 2014/02/05 04:27am
Using databases of different servers

I have a question, i used this multiple db support and is working when im using databases which are both in the localhost, but when i tried setting db as database on localhost and db2 for the database of another host, i got this error below

Object configuration must be an array containing a "class" element.

/var/www/tcash/tcash_api/protected/components/TagbondActiveRecord.php(11)

01 <?php 02 class TagbondActiveRecord extends CActiveRecord { 03 private static $dbTagbond = null; 04 05 protected static function getTagbondDbConnection() 06 { 07 if (self::$dbTagbond !== null) 08 return self::$dbTagbond; 09 else 10 { 11 self::$dbTagbond = Yii::app()->dbTagbond; 12 if (self::$dbTagbond instanceof CDbConnection) 13 { 14 self::$dbTagbond->setActive(true); 15 return self::$dbTagbond; 16 } 17 else 18 throw new CDbException(Yii::t('yii','Active Record requires a "db" CDbConnection application component.')); 19 } 20 } 21 } 22 ?>

What should i do for this error? thank you :)

#15062 report it
Boaz at 2013/10/03 08:32am
@Elecen

Extend CApplication and override getDb() ? Should work. Maybe there's a cleaner way, but I'm not sure.

#14450 report it
Elecen at 2013/08/12 06:10pm
Connecting to multiple databases on the fly

My infrastructure consists of several different databases all operated from the same source code. I am trying to modify the cli commands to allow me to apply a cli command automatically on all the databases.

So, I extend the CConsoleApplication (and create my console application instead of the CConsoleApplication in YiiBase) and have it execute the command once for every database from scratch.

By default I read the config and tried to Yii::app()->db = Yii::createComponent(...) which of course doesn't work hence Yii::app()->db is read-only. The question is, how do I get to change the db at runtime so that all my models and functionality keeps working out-of-the-box?

[still looking into it, any ideas?]

#13746 report it
Zugluk at 2013/06/21 09:09am
'DBNAME.TBLNAME' is mysql specific ?

I work with postgresql and reading other comments I found interesting to make queries between multiple databases linking tables and informations. However postgresql using 'schemeNAME.tblNAME' I wonder if it was possible to use that is it described below to link different tables in different databases ?

Because I don't see very well how to do it with postgresql.

#10873 report it
realtebo at 2012/11/29 03:47am
getDbConnection must be public function and not protected static function to use with Gii

In CActiveRecord.getDbConnection source

If you world like to use Gii to create models extended from MyActiveRecord, you must declare getDbConnection like public function, otherwise Gii will output a blank page, and will not preview/generate any code

#10711 report it
Gerhard Liebenberg at 2012/11/18 08:03am
Fixing a problem with Relational Query across databases

I did a relational query that tunneled across two databases using a couple of 'with'=>array(...) statements.

The query did not work because tables in both databases were prefixed with the same database-name.

I got it working after I repeated everything in this wiki (including what Psih did in comment #2770) for all models in both databases. Now all my models are always prefixed with the correct database-name.

#10139 report it
ItsYii at 2012/10/07 03:02am
Brilliant

Thanks for the well written instructions and code. Does exactly what it says, Fantastic.

#4816 report it
itmagetan at 2011/08/18 05:39pm
little hack

@Psih: Your little hack about cross db is very good one. I found that this shortcut is work either :

/**
 * @return string the associated database table name
 */
public function tableName()
{
    return 'myAnotherDBName.table_name';
}
#2770 report it
Psih at 2011/02/08 09:29am
Psih

Actually you can do cross-db queries via ActiveRecord with no modifications to the framework at all.

All you need, is that your DB user has SELECT access to the other databases. Then just use this little hack in the models, witch need to be joined to the main database (in my case I link data from dbadmin connection to my main db connection):

/**
 * @return CDbConnection
 */
public function getDbConnection(){
    return Yii::app()->dbadmin;
}
 
/**
 * @return string the associated database table name
 */
public function tableName()
{
    preg_match("/dbname=([^;]+)/i", $this->dbConnection->connectionString, $matches);
    return $matches[1].'.table_name';
};

Now thanks to the form "dbname.tablename" SQL is perfectly valid and cross-database joins work. Runs perfect in production on MySQL 5.1

#2626 report it
Gustavo at 2011/01/24 02:37am
multi active record extension

If you are here this might interest you

I create an extension to do what this post is explaning with a couple extra things, like gii support and more you can check this out here http://www.yiiframework.com/extension/multiactiverecord/

#2483 report it
intel352 at 2011/01/08 03:32pm
Cross Joins across connections

Cross joins across connections should be do-able, only not in the same query. AR supports querying relations/joins as separate queries for each join, which is how this would be possible.

#2391 report it
Volli at 2010/12/22 04:55am
Cross Joins

I think you can make relations across multi dbs.

See following forum entry http://www.yiiframework.com/forum/index.php?/topic/9723-cross-database-relations-join-table-cannot-be-found/

Leave a comment

Please to leave your comment.

Write new article
  • Written by: Steve Friedl
  • Updated by: phazei
  • Category: How-tos
  • Yii Version: 1.1
  • Votes: +28
  • Viewed: 65,233 times
  • Created on: Dec 20, 2010
  • Last updated: Aug 2, 2012
  • Tags: Database