Change DB connection Dynamically

Yii 2.0 General Discussions
1

This is my scenario

I have a list of all the companies using my application in a ‘control’ schema. In the control schema, information about each company is in a table which also contains columns that for hostname, schema name, username and password for the companies DB. The schema structure is exactly the same for each company.

All these companies will use the single source code, which I am currently developling in Yii2 (current application works fine in core PHP and ZF2, but we are moving to Yii2). What I would need is to provide hostname, schema name, username and password to the db connection so that the correct schema is selected and used.

I know this is not a new problem, there could be some of us that may have solved this. I am new to Yii2

2

I use something like following code:

Where you configure the database connection change with the following:


'db' => require(__DIR__ . '/db.php'),

in db.php you can do whatever you want to configure your connection, in you case something like this should be fine:


$servername = "localhost";

$username = "username";

$password = "password";

$dbname = "company_schema";


// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}


$sql = "SELECT dsn, user, password FROM company_connection where company='".$_SERVER['SERVER_NAME']."'";

$result = $conn->query($sql);


if ($result->num_rows == 1) { // more than one connection should never happen!

    $row = $result->fetch_assoc();

    $connection = [

        'class' => 'yii\db\Connection',

        'dsn' => $row["dsn"],

        'username' => $row["user"],

        'password' => $row["password"],

        'charset' => 'utf8'

    ];  

} else {

    die("Error more than one connection found");

}

$conn->close();

return $connection;

Hope this help.

3

Thank you Roberto

This works fine.

I forgot to mention that it is one URL for all companies; the login screen has three fields -username, password, and company code, thus, each company has more than one user, so they log in in with a username, password and code (the company code is used to get the schema that belongs to the company)

In most cases, the logged in session has to continue with the company schema unless another company is selected in the case of parent company calling an subsidiary independent company

However, I will customize your suggestion to meet this requirement. I thought there was a Yii2 dynamic configuration for db

million thanks

4

This should fit much better to your situation, my previous example was from my code in which I don’t need to get connection info from another database.

modify the index.php (the one that is in web for basic template or frontent/backend for advanced)

Comment the application run:


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

and add at the end of the file the following:




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


// we only load the configuration so everything goes up

// but the application is not run yet so no db connection.

(new yii\web\Application($config));

// is there a user logged?

if (!Yii::$app->user->isGuest) :

    //if yes we setup a different db connection

    Yii::$app->db= \yii\db\Connection([

        // dsn user and password are from session, set these value during login procedure

        'dsn' => Yii::$app->session->get('custorem_connection.dns'),

        'username' => Yii::$app->session->get('custorem_connection.username'),

        'password' => Yii::$app->session->get('custorem_connection.password'),

    ]);

endif;


Yii::$app->run(); // this will run the application

In user model (and also on the model that get the connection info) use a secondary connection for the authentication, if not present add the following:




    /**

     * @return \yii\db\Connection the database connection used by this AR class.

     */

    public static function getDb() {

        return Yii::$app->get('dbUser');

    }

Where dbUser is the connection that holds your user database which also has the connection info for the application database.

To have a dbUser connection just configure it in main config in component section:




'dbUser' => [

            'class' => 'yii\db\Connection',

            'dsn' => 'mysql:host=localhost;dbname=company_schema',

            'username' => 'dbUser',

            'password' => 'dbPwd',

            'charset' => 'utf8',

        ]

I suggest you to use a secondary connection for login stuff so your application model will use always the default one "db" and just 2 or 3 model which handles user infos need to be configure with a different connection.

So in login action do something like the following (this is a stub, much depends if you have your custom code or use a module to handle auth)




public function actionLogin() {


.......

    if (<authentication is valid>)) {

        //code after authentication like user session start

        Yii::$app->user->login($user);

        // retrive connection information

        $appConnection= <here follow the query to your model connection>;

        //register connection info in session, these info are retrived before application run

        Yii::$app->session->set('custorem_connection.dns', $appConnection->dns);

        Yii::$app->session->set('custorem_connection.username', $appConnection->user);

        Yii::$app->session->set('custorem_connection.password',  $appConnection->password);

        ......

    } else {

        .........

Didn’t fully test it, to much to configure … database, tables and so on and I’m lazy :P

Hope this help.

5

Buonasera Roberto.

Ho seguito passo passo il tuo esempio, ma ottengo sempre il seguente messaggio:

Setting read-only property: yii\web\Application::db

Cosa sbaglio ?

Grazie in anticipo.

Luca

6

I would use a method that’s quite a bit “shorter” to handle:

in your web.php, add in components:




'dbDynamic' => [

            'class' => 'yii\db\Connection',

        ],

in the main index.php:




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


(new yii\web\Application($config));


// if user is logged in, load additional DB connection parameters


if (!Yii::$app->user->isGuest) :

    $appConnection = \app\models\UsersDatabases::find()->where(['user_id' => Yii::$app->user->id])->one();

    \Yii::$app->dbUser->dsn = $appConnection->dsn;

    \Yii::$app->dbUser->username = $appConnection->username;

    \Yii::$app->dbUser->password = $appConnection->password;

    \Yii::$app->dbUser->charset = $appConnection->charset;

endif;

and then as already mentioned in the model that needs access to the "external" database:




    public static function getDb()

    {

        return Yii::$app->get('dbDynamic');

    }
7

Now you can change database dynamically with in Yii2 application
Using

Yii::$app->set(‘db’, [
‘class’ => ‘yii\db\Connection’,
‘dsn’ => ‘mysql:host=localhost;dbname=DBName’,
‘username’ => ‘DBUser’,
‘password’ => ‘DBPassword’,
‘charset’ => ‘utf8’,
‘enableSchemaCache’ => !YII_DEBUG,
]);

Or you can it beforeRequest in application conficuration.

...
'components' => [
    ...
],
'on beforeRequest' => function() {
    if(<auth true>){
        Yii::$app->set('db', [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=DBName',
            'username' => 'DBUser',
            'password' => 'DBPassword',
            'charset' => 'utf8',
            'enableSchemaCache' => !YII_DEBUG,
        ]);
    }
},