Yii2: Connection string for different Database Server

Published on November 27, 2019 3:54:43AM by Administrator

Connection string configuration for Yii2 Framework


Yii2: Connection string for different Database Server

Connection represents a connection to a database via PDO.

Connection works together with yii\db\Command, yii\db\DataReader and yii\db\Transaction to provide data access to various DBMS in a common set of APIs. They are a thin wrapper of the PDO PHP extension.

Connection supports database replication and read-write splitting. In particular, a Connection component can be configured with multiple $masters and $slaves. It will do load balancing and fail-over by choosing appropriate servers. It will also automatically direct read operations to the slaves and write operations to the masters.

To establish a DB connection, set $dsn, $username and $password, and then call open() to connect to the database server. The current state of the connection can be checked using $isActive.

Below is an example to create a connection instance and establish the DB connection:

$connection = new \yii\db\Connection([
    'dsn' => $dsn,
    'username' => $username,
    'password' => $password,
]);
$connection->open();


Connection is often used as an application component and configured in the application configuration like the following:

'components' => [
    'db' => [
        'class' => '\yii\db\Connection',
        'dsn' => 'mysql:host=127.0.0.1;dbname=demo',
        'username' => 'root',
        'password' => '',
        'charset' => 'utf8',
    ],
],


Yii2 supports different database server types and each one have its own way to connect to database. To connect with any database we need to prepare DSN for connection.

In this quick tip we are going to see how DSN should be created for different Database servers.

If you are running basic app of Yii2 then you need to make this changes in db.php file under config directory and in case of advance application it should be done in main.php under config directory.

'components' => [
     'db' => [
       'class' => 'yii\db\Connection',
       'dsn' => 'mysql:host=localhost;dbname=my_awesome_db', // 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' => 'DB User Name',
       'password' => 'Password for selected user',
       'charset' => 'utf8',
    ]
 ];