Yii Dynamic DB Connection according to user?

Question

My project is on the basis of multi-tenant.

I have multiple clients (companies) and each client has multiple users.

Each client has their own database, so during user authentication, I discover the name of associated database for that user.

The structure of each database is identical... only the data is different.

So that we can keep the different database for the different company, that will not going to mix in data in database.

The number of clients (and therefor the number of databases) is unknown when the application is written, so it is not possible to include all the connections in the bootstrap script.

Now, what I want to do is, dynamically alter the DB connection that is in the bootstrap or have the ability to dynamically create a new connection for the user signing in. Is there a simple solution for this in Yii and still use AR , query builder ?

The same question was asked on yii forum that still not answered clearly,.... you can find this question here Yii dynamic dabatabase connection

Answer 1

Got it

First of all I have main Db called companyMainDb it have all user information along with thier company id

Each company have different db named as company_company_id

step 1:

      Go to web/index.php

Step 2:

Change it like this

require(__DIR__ . '/../vendor/autoload.php');
Dotenv::load(__DIR__ . '/../');

// comment out the following two lines when deployed to production
defined('YII_DEBUG') or define('YII_DEBUG', true);
defined('YII_ENV') or define('YII_ENV', getenv('YII_ENV'));

require(__DIR__ . '/../vendor/yiisoft/yii2/Yii.php');

$config = require(__DIR__ . '/../config/web.php');

new yii\web\Application($config);

Yii::$app->user->isGuest?$dbName='companyMainDb':$dbName='company_'.Yii::$app->user->identity->company_id;

$config['components'] ['db'] =[ 'class' => 'yii\db\Connection', 'dsn' => 'mysql:host=' . getenv('DB_HOST'). ';dbname='.$dbName, 'username' => getenv('DB_USER'), 'password' => getenv('DB_PASSWORD'), 'charset' => 'utf8', ];

(new yii\web\Application($config))->run();

Answer 2

Yii::app()->db->setActive(false);

Yii::app()->db->connectionString ='mysql:host=localhost;dbname='.trim($databasename);

Yii::app()->db->setActive(true);

Answer 3

Not sure that my answer came in time for @Gaurish, but I had exactly the same problem a few days ago and here is how I solved it.

Briefly, you should extend CActiveRecord class and override not only the getDbConnection(), but also a class constructor and model() function.

class SuperRecord extends CActiveRecord {

public static $host;
public static $port;
public static $user;
public static $pass;

public static $connection = null;

function __construct($dbName = null, $scenario='insert') {
    if ($tenant != null) {
         Yii::app()->params['activeDb'] = $dbName;
    }
    self::$host = Yii::app()->params['defaultDbHost'];
    self::$port = Yii::app()->params['defaultDbPort'];
    self::$user = Yii::app()->params['superDbUser'];
    self::$pass = Yii::app()->params['suPassword'];
    parent::__construct($scenario);
    Yii::app()->params['activeDb'] = ''; //destruct sensitive data after using
}

public function getDbConnection() {
    if(self::$connection!==null)
        return self::$connection;
    else
    {
        $connectionString = 'mysql:host='.self::$host.';port='.self::$port.';dbname='.Yii::app()->params['activeDb'];
        self::$connection=new CDbConnection($connectionString, self::$user, self::$pass);
        self::$connection->emulatePrepare = true;
        self::$connection->charset = 'utf8';
        self::$connection->active=true;
        if(self::$connection instanceof CDbConnection)
            return self::$connection;
        else
            throw new CDbException(Yii::t('yii','Active Record requires a "db" CDbConnection application component.'));
    }
}

public static function superModel($dbName, $className=__CLASS__) {
    Yii::app()->params['activeDb'] = $dbName;
    return self::model($className);
}

public static function model($className=__CLASS__)
{
    return parent::model($className);
}
}

After that you will be able to create any model connected to database, which name you'll send to constructor, like that:

$messagesModel = new UserMessages($databaseName);

or that:

$messages = UserMessages::superModel($databaseName)->findAll();

Check out the article, I've tried to explain it as clearly as possible.

Answer 4

Thanks for your valuable help ,

For my application I need multiple database,

a) master database which contains all the information about company say it as 'projectmaster'

b) database for company sa 'company1'

So,I have implimented code in yii\framework\db\ar\CActiveRecord.php as below,

self::$db=Yii::app()->getDb();
if(self::$db instanceof CDbConnection)
{
    $db=Yii::app()->db;
    $constring=array();
    $constring=$db->createCommand()
        ->select('dbname,host,dbusername,dbpassword')
        ->from('projectmaster')
        ->where('company_name =company1')
        ->queryRow();
    self::$db=new CDbConnection('mysql:host='.$constring['host'].';dbname='.$constring['dbname'],$constring['dbusername'],$constring['dbpassword']);

self::$db->active=true;
return self::$db;

}

Here I am creating the new connection object 'self::$db' using existing connection, is this override the old connection or it creates the new connection

and when I have to use 'createCommand()' for some data fetching I am not using the active record class,

so that time I am connection object as,

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

here I am geting the old connection '$connection' which is created by yii.... But for application it should use new connection so it will get data from 'company1' database.

So I want create connection which should reusable. is it possible to change the yiii connection or we have to cerate new connection every time when we want use custom query.

Answer 5

I'd do the same as qiang posted on the forum. You need a list of db connections and a property of the logged in user at Yii::app()->user that tells you which connection to use (I name it connectionId for my example). You then overide getDbConnection() in a ActiveRecord base class:

public function getDbConnection()
{
    if(self::$db!==null)
        return self::$db;
    else
    {
        // list of connections is an array of CDbConnection configurations indexed by connectionId
        $listOfConnections=/* to be loaded somehow */;
        // create DB connection based on your spec here:
        self::$db=new CDbConnection($listOfConnections[Yii::app()->user->connectionId]);
        self::$db->active=true;
        return self::$db;
    }
}