Changing Postgresql Schemas For Unit Testing

It’s not easy in yii to work in different schemas than default (public), in an enterprise wide db, like postgresql. If you don’t want or cannot have n different databases for developing and testing, you may try using different schemas, but there is no direct way in yii to specify or dynamically change the schema where CDbConnection gets linked. By other hand, your model’s tableName() must return the string “schemaname.tablename”.

I’ve read a closed topic: Postgresql schemas not possible, and I wanted to search an option to CDbConnection subclassing. In a learning proccess, I tried behaviors, and used a global constant.

We need to follow a convention for naming the schemas, or specify the names in some way. Having a developing schema called schemaname, the testing schema will be called schemaname_test, and I create a behavior, protected/components/XXXSetSchema.php:

class XXXSetSchema extends CBehavior


    private $schemaName = "";


    function setSchemaName($name)


        $this->schemaName = $name;


    function getSchemaName()


        $suffix = defined('APP_TEST') ? '_test' : '';

        return $this->schemaName.$suffix;


    function setSchema()


        $sql = "set search_path = $this->schemaName, public;";

        $stmt = Yii::app()->db->createCommand($sql);




Then, I attached this behavior to the application connection. I choose onBeginRequest app event to do this. First, create a class and a callback to manage the event:


class XXXBeginRequestHandler {

    public static function setBeginRequestHandler()


        $comp = Yii::app()->db; 

        $comp->attachbehavior('XXXsetschema', new XXXSetSchema());

        $comp->schemaName = 'MySchema';





Then, must specify this app event handler, in protected/config/main.php:


// uncomment the following to define a path alias

// Yii::setPathOfAlias('local','path/to/local-folder');

// This is the main Web application configuration. Any writable

// CWebApplication properties can be configured here.

return array(


	'name'=>'Another app',



Specifyng the event handler in main.php is sufficient for testing purposes, because the tests configuration file (protected/config/test.php) includes main.php when runs.

Modify every model class definition to change tableName():


 * @return string the associated database table name


public function tableName()


	return Yii::app()->db->schemaName.'.MyTableName';  // change MyTableName to the model table real name


Finally, to have the test units running correctly, you must modify the bootstrap (protected/tests/bootstrap.php):


// change the following paths if necessary




require_once( Yii::getPathOfAlias('system.test.CTestCase').'.php' );




Yii::app()->raiseEvent('onBeginRequest', new CEvent());

In the bootstrap, we can define the global constant APP_TEST and raise the onBeginRequest to force attach the behavior.

This code can be mofified to support different DBMSs, maybe defining some global constants, and managing every case in the behavior class.

I hope this code can help you.