yii2 change database dynamically

Question

I'm trying to move to another database dynamically. I've seen several questions that showed change db files from one to another and they just getting some information from next database. But what I need is completely moving to second database. How should I do this? I've seen that in order to achieve this dsn (in db.php file) should be altered. But I changed it and it's still not changed?? I should have full access to second database closing first one. Give me advice please

Answer 1

Configs like db.php are not intended to be changed in process (while PHP is processing). They are loaded once in the initialization, when request is entered the framework. As an alternative, you can configure second DB beforehand in db.php, and change between them dynamically like:

Yii::$app->db // your default Database

and

Yii::$app->db2 // Second configured Database, to which you can switch dynamically later

You can learn about multiple database connections here

So, if you want ActiveRecord(for instance User) to be able to access two databases, you can define some static variable, which specifies from which DB to read/write. For example:

class User extends \yii\db\ActiveRecord
{
    const DB_DATABASE1 = 'db1';
    const DB_DATABASE2 = 'db2';

    private static $db = self::DB_DATABASE1;

    public static function setDb($db)
    {
        self::$db = $db;
    }

    public static function getDb()
    {
        switch (self::$db) {
            case self::DB_DATABASE1:
                return Yii::$app->db;
            case self::DB_DATABASE2:
                return Yii::$app->db2;
            default:
                throw new \Exception("Database is not selected");
        }
    }

    //...

And then use it in Controller like:

User::setDb(User::DB_DATABASE1);
$usersDB1 = User::find()->all();

User::setDb(User::DB_DATABASE2);
$usersDB2 = User::find()->all();

Answer 2

Set up multiple database connections in your main.php or main-local.php configuration

Yii::$app->db1;
Yii::$app->db2;
Yii::$app->db3;

when making inquiries

$usersDB1=User::find()->all(Yii::$app->db1);
$usersDB2=User::find()->all(Yii::$app->db2);
$usersDB3=User::find()->all(Yii::$app->db3);

Answer 3

Globally switch to a different database dynamically

I have combined Yerke's answer above and Write & use a custom Component in Yii2.0 here.

First, create a component class to do the DB switching. Here, I call it DbSelector and put it in app\components\DbSelector.php.

namespace app\components;

use Yii;
use yii\base\Component;
use yii\base\InvalidConfigException;

class DbSelector extends Component {

    const DB_MAIN = 'db';
    const DB_SUB1 = 'db1';
    const DB_SUB2 = 'db2';

    private static $db = self::DB_MAIN;

    public static function setDb($db)
    {
        self::$db = $db;
    }

    public static function getDb()
    {
        return \Yii::$app->get(self::$db);
    }
}

Second, modify the config/web.php file or whichever your config file is to have multiple databases and add DbSelector as a Yii::$app component.

$config = [
    'components' => [
        //...

        'db' => $db,
        'db1' => $db1,
        'db2' => $db2,

        'dbSelector' => [
            'class' => 'app\components\DbSelector',
        ],

        //...
    ],

    //...
];

Third, in each model class, add the following static function getDb() to call the DbSelector getDb():

public static function getDb()
{
    return \Yii::$app->dbSelector->getDb();
}

For example,

class DiningTable extends \yii\db\ActiveRecord
{
    public static function tableName()
    {
        return '{{%dining_table}}';
    }

    public static function getDb()
    {
        return \Yii::$app->dbSelector->getDb();
    }

    //...
}
    
class Customer extends \yii\db\ActiveRecord
{
    public static function tableName()
    {
        return '{{%customer}}';
    }

    public static function getDb()
    {
        return \Yii::$app->dbSelector->getDb();
    }

    //...
}

Lastly, to globally switch to a different database, use \Yii::$app->dbSelector->setDb(YOUR_PREFERRED_DB),

use app\components\DbSelector;

//...

\Yii::$app->dbSelector->setDb(DbSelector::DB_SUB1);
$tables_1 = DiningTable::findAll();
$customers_1 = Customer::find()->where(['<', 'dob', '2000-01-01'])->all();

\Yii::$app->dbSelector->setDb(DbSelector::DB_MAIN);
$tables_main = DiningTable::findAll();
$customers_main = Customer::find()->where(['<', 'dob', '2000-01-01'])->all();