How to establish a connection to a database in the Yii2 framework

A simple and trivial task that a beginner Yii2 developer may encounter. Let's start with the fact that depending on the project configuration, the database can be set up in different ways. Let’s consider the two options provided by the framework:

1. basic (simple setup)
2. advanced (more complex setup)

In both cases, the database configuration (setting the prefix/tablePrefix, username/username, password/password, dsn, etc.) is done the same way. It all depends on the file location and the database connection class. In this example, we’ll look at how to connect to a MySQL database, as it's one of the most popular options.

In the basic template, the database configuration file can be found at: “/config/db.php”.

In the advanced template, things are a bit more complex (hence the name — for large-scale projects). There may be more than one configuration file (for backend, frontend, etc.). It all depends on how you initially structured your application. Since both admin and frontend parts usually work with the same database, I typically place the database configuration in the “/common/config/db.php” directory. Then I reference it in the “/common/config/main.php” config. That way, both backend and frontend will automatically inherit the database settings.

In both templates, the configuration is identical and looks like this:

return [
	'class' => 'yiidbConnection',
	'dsn' => 'mysql:host=127.0.0.1;dbname=my_db_name',
	'username' => 'root',
	'password' => '',
	'charset' => 'utf8',
	'tablePrefix' => 'my_',
];

Where:
class — the connector class for database access
dsn — host and database name
username — database user
password — user password
charset — database charset
tablePrefix — table prefix (i.e., yii2 table prefix)

That's it — not so difficult after all. Good luck!