Database connection

Blog posts have to be stored somewhere, and that is what a database is for. Yii works with MySQL, MariaDB, PostgreSQL, SQLite and other systems. The connection settings go into one file, and then the database is available anywhere in the code as the Yii::$app->db component.

Creating the database

Yii does not create the database itself. Create it in any convenient way, for example in the MySQL console:

CREATE DATABASE blog CHARACTER SET utf8mb4;

An empty blog database appears. Tables will be added by the migrations from the next lesson.

The connection file

The settings are in the file config/db.php, which already exists in the project. Replace the values with yours:

// config/db.php
return [
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=localhost;dbname=blog',
    'username' => 'root',
    'password' => 'secret',
    'charset' => 'utf8mb4',
];

The dsn string says which database to connect to: the system type, the server address and the database name. This file is included in config/web.php and config/console.php with the line 'db' => $db, so the setting works both on the site and in the console.

The dsn string for different systems

System	Example `dsn`
MySQL, MariaDB	`mysql:host=localhost;dbname=blog`
PostgreSQL	`pgsql:host=localhost;port=5432;dbname=blog`
SQLite	`sqlite:/path/to/blog.db`

Each system needs its own PHP extension enabled: pdo_mysql, pdo_pgsql or pdo_sqlite.

Checking

The simplest way to check the connection is the migration console command:

php yii migrate

If the settings are right, the command answers No new migrations found. Your system is up-to-date. and creates the service table migration in the database. With a wrong password or database name you will see the error text from the database server.

The first query

A query in SQL is run through the db component:

// controllers/PostController.php
public function actionDbTest(): string
{
    $version = Yii::$app->db->createCommand('SELECT VERSION()')->queryScalar();
 
    return "Server version: $version";
}

The page /post/db-test shows the MySQL server version. The queryScalar() method returns one value, queryOne() one row, queryAll() all rows as an array. In the next lessons you will hardly need manual SQL: tables are handled through models.

Common mistake

The file config/db.php with a real password must not go into a shared repository: everyone with access to the code would see the server password. Keep a variant with development data in the repository, and on the server read the real values from environment variables, for example 'password' => getenv('DB_PASSWORD').

Thanks for reading!