Using more than one database in the application

Yii 2.0 General Discussions
1

Hi,

we have more than one database, which we want to use in our application. So far I’ve seen only examples and google search results which are showing, how to work with one database. There are a few appropriate examples for Yii 1 (f.e. http://www.yiiframework.com/forum/index.php/topic/10649-how-to-change-db-name-on-the-fly/), but not for Yii 2. I was starting ahead with the Yii2 basic example.

I’ve tried by myself to instantiate a new instance of yii\web\Application(); but it seems, that there’s a singleton pattern contained. Also I’ve tried to find a method, which allows me to switch the configuration, but I couldn’t succeed.

That’s the way I’m instantiating my Yii2 application:


        defined('YII_DEBUG') or define('YII_DEBUG', true);

        defined('YII_ENV') or define('YII_ENV', 'test');


        require_once(__DIR__ . '/../vendor/autoload.php');

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


        // create application instance and run

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

        new yii\web\Application($config);

Could you please provide me hint for using multiple databases (configs) in my Yii 2 application?

Thanks

2

You have to configure another db connection in your main/config.php (or other places where you have config file)




        'secondDb'=>[

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

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

            'username' => 'root',

            'password' => '',

            'charset' => 'utf8'            

        ],

and then you call secondDb component.

Read this:

http://www.bsourcecode.com/yiiframework2/yii2-0-database-connection/

3

in addition you can override the ActiveRecord class, once for each database, and then have your models extend from the correct ActiveRecord class.

here’s some Yii1 code, which you can adapt:




class CActiveRecord_SecondDB extends CActiveRecord {


    public function init() {

        self::$db = Yii::app()->secondDB;

    }
4

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#connecting-to-database

5

Hi,

thank you for your fast answers.

If I insert


    public static function getDb()

    {

        return \Yii::$app->db;  // use different db

    }

into my model and change the db configuration from


<?php


return [

        'db' => [

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

            'dsn' => 'mysql:host=localhost;dbname=db-test',

            'username' => 'xxx',

            'password' => 'xxx',

            'charset' => 'utf8',

        ],

];

to


<?php


return [

    'components' => [

        'db' => [

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

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

            'username' => 'xxx',

            'password' => 'xxx',

            'charset' => 'utf8',

        ],

        'cron-db' => [

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

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

            'username' => 'xxx',

            'password' => 'xxx',

            'charset' => 'utf8',

        ]

    ],

];

I get the following exception after running my controller:

This exception points to this part of the DB-configuration array in db.php:


return [

    'components' => [

        'db' => [

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

How can I advice my controller or model to use the new configuration properly?

Thanks

6

Ok, I’ve solved it. :)

My mistake was, that I’ve inserted another array dimension into the db.php file. The second db must be defined in the web.php file, inside the components array-dimension.

See


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

        'db2' => require(__DIR__ . '/db_cron.php'),

in the following entire config array.




$config = [

    'id' => 'basic',

    'basePath' => dirname(__DIR__),

    'bootstrap' => ['log'],

    'components' => [

        'urlManager' => [

            'enablePrettyUrl' => true,

            'showScriptName' => false,

        ],

        'request' => [

            // !!! insert a secret key in the following (if it is empty) - this is required by cookie validation

            'cookieValidationKey' => 'Fkq6Tq3h7YzUsLuiWAcZrbL4dMGlhMlV',

        ],

        'cache' => [

            'class' => 'yii\caching\FileCache',

        ],

        'user' => [

            'identityClass' => 'app\models\User',

            'enableAutoLogin' => true,

        ],

        'errorHandler' => [

            'errorAction' => 'site/error',

        ],

        'log' => [

            'traceLevel' => YII_DEBUG ? 3 : 0,

            'targets' => [

                [

                    'class' => 'yii\log\FileTarget',

                    'levels' => ['error', 'warning'],

                ],

            ],

        ],

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

        'db2' => require(__DIR__ . '/db_cron.php'),

    ],

    'params' => $params,

];

Thanks for your hints guys.