Multiple-database support in Yii

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 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.