Yii Framework 2 : Database Connection

Yii2.0 Database Connection
To use the database we have to configure the database connection component by adding ‘db’ information to application config/main.php file.

Yii 2.0 DBMS Supports

Yii 2.0 supports the following DBMS:

• MySQL
• MariaDB
• SQLite
• PostgreSQL
• CUBRID: version 9.1.0 or higher.
• Oracle
• MSSQL

DBMS Configuration

Create a ‘db.php’ file inside the config folder and add the database credentials like below code.

<?php
return [
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=localhost;dbname=reportsa_macromoney', // MySQL, MariaDB
    //'dsn' => 'sqlite:/path/to/database/file', // SQLite
    //'dsn' => 'pgsql:host=localhost;port=5432;dbname=mydatabase', // PostgreSQL
    //'dsn' => 'cubrid:dbname=demodb;host=localhost;port=33000', // CUBRID
    //'dsn' => 'sqlsrv:Server=localhost;Database=mydatabase', // MS SQL Server, sqlsrv driver
    //'dsn' => 'dblib:host=localhost;dbname=mydatabase', // MS SQL Server, dblib driver
    //'dsn' => 'mssql:host=localhost;dbname=mydatabase', // MS SQL Server, mssql driver
    //'dsn' => 'oci:dbname=//localhost:1521/mydatabase', // Oracle
    'username' => 'root',
    'password' => '$123',
    'charset' => 'utf8',
];
?>		

We have to add this file into $config[‘components’][‘db’] params to make a DB connection. For example, Add the following line into ‘config/main.php’ file.

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

Now the database connection will created.
After the connection was created, we can access the ‘db’ connection like

$connection = \Yii::$app->db;

Yii 2.0 Multiple Database Connection

Like Yii 1.0, You can configure multiple databases in your application component. While configuring a database connection, you can use a different type of Data Source Name(DSN). It will help us to connect different type of DBs in the single application.

For example, You can use below code in your config/web.php file.

<?php
return [
'components' => [
    'db' => [
        'class' => 'yii\db\Connection',
        'dsn' => 'mysql:host=localhost;dbname=db1name',
        'username' => 'db1username',
        'password' => 'db1password',
    ],
    'db2' => [
        'class' => 'yii\db\Connection',
        'dsn' => 'mysql:host=localhost;dbname=db2name',//pgsql, sqlsrv, etc
        'username' => 'db2username',
        'password' => 'db2password',
    ],
],
];

Note:
Your database connection will establish When you run the first execution query using DB instance. Before first execution, You DB connection will not be established.

Yii 2.0 How to use Multiple Database?

By default, Yii 2.0 model is extended by ActiveRecord.

<?php
class User extends \yii\db\ActiveRecord implements IdentityInterface {

}

ActiveRecord class is having one function for getting database connection Which is configured in components.

<?php
class ActiveRecord extends BaseActiveRecord
{
	.........
    public static function getDb()
    {
        return Yii::$app->getDb();
    }
	.........
}

getDb() function is requesting to the application settings for getting database connection.

<?php
abstract class Application extends Module
{
	.........
	public function getDb()
	{
        return $this->get('db');
	} 
	.........
}

Now you will get some idea about DB connection.

To use another DB connection, We have to override ActiveRecord getDb() function from model.

<?php
class User extends \yii\db\ActiveRecord implements IdentityInterface {
	.........
	public static function getDb() {
		return Yii::$app->get('db2');
	}
	.........
	(or) 
	.........
	public static function getDb() {
		return Yii::$app->db2;
    }
	.........
}

Yii 2.0 Query with different DB connection

<?php
Yii::$app->db1->createCommand((new \yii\db\Query)->select('*')->from('table_name'))->queryAll()

Yii::$app->db2->createCommand((new \yii\db\Query)->select('*')->from('table_name'))->queryAll()

$row = Yii::$app->db1->createCommand("SELECT * FROM table_name")->queryOne();