Yii2 Models, Active Record & Database Connections

In Yii2, models represent data structures and are used for database interactions. Yii2 provides the Active Record (AR) pattern to simplify working with databases by mapping database tables to PHP classes.

In this tutorial, we will cover:

• Creating and using Active Record models
• Performing CRUD operations
• Setting up database connections
• Using multiple databases in Yii2
• Using different databases for modules

1. Database Connection in Yii2

Yii2 uses the db component for database connections. By default, Yii2 stores database configuration in the config/db.php file (for the basic template) or common/config/main.php (for the advanced template).

1.1 Configuring the Database

Modify config/db.php to set up the database connection:

return [
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=localhost;dbname=mydatabase',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8',
];

For the advanced template, modify common/config/main.php:

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

2. Creating an Active Record Model

Active Record (AR) allows you to interact with database tables as PHP objects.

2.1 Generating a Model Using Gii

Yii2 provides Gii, a web-based tool to generate models.

Enable Gii in config/web.php:

'modules' => [
    'gii' => [
        'class' => 'yii\gii\Module',
        'allowedIPs' => ['127.0.0.1', '::1'], // Adjust for remote access
    ],
],

Open Gii in your browser:

http://localhost/yii2-app/index.php?r=gii

Click on Model Generator.

Enter the table name (e.g., users).

Click Preview, then Generate.

2.2 Manually Creating a Model

Alternatively, create a model manually inside the models directory.

For example, create models/User.php:

namespace app\models;

use yii\db\ActiveRecord;

class User extends ActiveRecord
{
    public static function tableName()
    {
        return 'users'; // Table name in the database
    }
}

3. CRUD Operations with Active Record

3.1 Retrieving Data

Fetch all users:

$users = User::find()->all();

Fetch a single user by ID:

$user = User::findOne(1);

Find a user by condition:

$user = User::find()->where(['username' => 'admin'])->one();

3.2 Inserting Data

$user = new User();
$user->username = 'Gourav';
$user->email = 'gourav@example.com';
$user->save();

3.3 Updating Data

$user = User::findOne(1);
$user->email = 'new_email@example.com';
$user->save();

3.4 Deleting Data

$user = User::findOne(1);
$user->delete();

4. Using Multiple Databases in Yii2

Sometimes, you need to connect to multiple databases in Yii2, for example, when dealing with multiple applications.

4.1 Configuring Multiple Databases

Modify config/db.php (or common/config/main.php for advanced template):

return [
    'components' => [
        'db' => require(__DIR__ . '/db-main.php'),  // Default database
        'db2' => require(__DIR__ . '/db-second.php'), // Second database
    ],
];

Then create separate database configuration files:

config/db-main.php (First Database - Main DB)

return [
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=localhost;dbname=main_db',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8',
];

config/db-second.php (Second Database - Orders DB)

return [
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=localhost;dbname=orders_db',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8',
];

4.2 Assigning Models to Different Databases

By default, all models use Yii::$app->db. To make a model use another database, override the getDb() method.

For the User model (Main Database):

namespace app\models;

use yii\db\ActiveRecord;

class User extends ActiveRecord
{
    public static function getDb()
    {
        return \Yii::$app->db; // Connects to the main database
    }

    public static function tableName()
    {
        return 'users';
    }
}

For the Orders model (Second Database):

namespace app\models;

use yii\db\ActiveRecord;

class Orders extends ActiveRecord
{
    public static function getDb()
    {
        return \Yii::$app->db2; // Connects to the second database
    }

    public static function tableName()
    {
        return 'orders';
    }
}

5. Using Different Databases for Modules

Sometimes, you want each module to use a different database. You can do this by configuring the module’s init() function.

5.1 Setting Up Module-Specific Database

Inside the module’s init() method, configure it to load a separate database connection:

Example: modules/dashboard/Module.php

namespace app\modules\dashboard;

use Yii;
use yii\base\Module as BaseModule;

class Module extends BaseModule
{
    public function init()
    {
        parent::init();
        Yii::configure($this, require(__DIR__ . '/config/main-local.php'));
    }
}

Now, create the config/main-local.php file inside the module:

<?php

return [
    'components' => [
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=dashboard_database',
            'username' => 'root',
            'password' => 'Password#3',
            'charset' => 'utf8',
        ],
    ],
];

5.2 Using the Module-Specific Database in Models

Now, inside the Dashboard module models, override getDb() to use the new database:

namespace app\modules\dashboard\models;

use Yii;
use yii\db\ActiveRecord;

class DashboardUser extends ActiveRecord
{
    public static function getDb()
    {
        return Yii::$app->getModule('dashboard')->db;
    }

    public static function tableName()
    {
        return 'users';
    }
}

Now, all models inside the Dashboard module will use the dashboard_database.

Conclusion

• Yii2 supports multiple databases.
• You can override getDb() in models to assign different databases.
• Modules can have their own database configuration using Yii::configure().