Database Integration in Yii2: Active Record, Query Builder & Migrations

Phase 2 gave you Controllers, Views, and Models working with in-memory arrays. That's great for understanding the pattern, but real apps talk to databases.

Phase 3 completes the picture: configure a real MySQL connection, write migrations to version your schema, use Active Record to turn database rows into PHP objects, define relations between tables, and reach for the Query Builder when you need more than simple CRUD.

By the end of this post you'll have a task manager backed by a real database — same URLs as Phase 2, same controller actions, but now everything persists.

What You'll Build

A database-backed task manager:

tasks table
  id          INT AUTO_INCREMENT
  title       VARCHAR(255)
  description TEXT
  status      ENUM('pending','in_progress','done')
  user_id     INT (FK → users.id)
  created_at  DATETIME
 
users table
  id          INT AUTO_INCREMENT
  username    VARCHAR(50)
  email       VARCHAR(255)

You'll be able to list, view, create, update, and delete tasks — and load each task's user with a single extra query through Active Record relations.

Configuring the `db` Component

Yii2's database connection lives in config/db.php (basic app) or common/config/main-local.php (advanced app).

Basic app:

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

This file is referenced from config/web.php:

// config/web.php
$db = require __DIR__ . '/db.php';
 
return [
    // ...
    'components' => [
        'db' => $db,
        // ...
    ],
];

Supported drivers:

Driver	DSN prefix	Extension
MySQL / MariaDB	`mysql:`	`pdo_mysql`
PostgreSQL	`pgsql:`	`pdo_pgsql`
SQLite	`sqlite:`	`pdo_sqlite`
MS SQL Server	`sqlsrv:`	`pdo_sqlsrv`

You access the connection anywhere via Yii::$app->db.

Test the connection from terminal:

php yii migrate/create test_connection
# If it runs without error, db config is correct

Migrations — Versioning Your Schema

Migrations are PHP classes that describe schema changes. They're committed to git, run on every environment, and can be rolled back.

Creating a Migration

php yii migrate/create create_users_table
php yii migrate/create create_tasks_table

Yii generates files in migrations/ (or console/migrations/ in advanced app):

// migrations/m260312_000001_create_users_table.php
use yii\db\Migration;
 
class m260312_000001_create_users_table extends Migration
{
    public function up(): void
    {
        $this->createTable('users', [
            'id'         => $this->primaryKey(),
            'username'   => $this->string(50)->notNull()->unique(),
            'email'      => $this->string(255)->notNull()->unique(),
            'created_at' => $this->dateTime()->notNull()->defaultExpression('CURRENT_TIMESTAMP'),
        ]);
    }
 
    public function down(): void
    {
        $this->dropTable('users');
    }
}

// migrations/m260312_000002_create_tasks_table.php
use yii\db\Migration;
 
class m260312_000002_create_tasks_table extends Migration
{
    public function up(): void
    {
        $this->createTable('tasks', [
            'id'          => $this->primaryKey(),
            'title'       => $this->string(255)->notNull(),
            'description' => $this->text()->null(),
            'status'      => "ENUM('pending','in_progress','done') NOT NULL DEFAULT 'pending'",
            'user_id'     => $this->integer()->notNull(),
            'created_at'  => $this->dateTime()->notNull()->defaultExpression('CURRENT_TIMESTAMP'),
        ]);
 
        $this->addForeignKey(
            'fk_tasks_user_id',
            'tasks', 'user_id',
            'users', 'id',
            'CASCADE', 'CASCADE'
        );
 
        $this->createIndex('idx_tasks_user_id', 'tasks', 'user_id');
        $this->createIndex('idx_tasks_status',  'tasks', 'status');
    }
 
    public function down(): void
    {
        $this->dropForeignKey('fk_tasks_user_id', 'tasks');
        $this->dropTable('tasks');
    }
}

Running Migrations

# Apply all pending migrations
php yii migrate
 
# Roll back the last migration
php yii migrate/down
 
# Roll back the last 3 migrations
php yii migrate/down 3
 
# Check migration history
php yii migrate/history
 
# Check pending migrations
php yii migrate/new

Migration Builder Methods

// Column types
$this->primaryKey()        // INT AUTO_INCREMENT PRIMARY KEY
$this->bigPrimaryKey()     // BIGINT AUTO_INCREMENT PRIMARY KEY
$this->string(255)         // VARCHAR(255)
$this->text()              // TEXT
$this->integer()           // INT
$this->bigInteger()        // BIGINT
$this->boolean()           // TINYINT(1)
$this->decimal(10, 2)      // DECIMAL(10,2)
$this->dateTime()          // DATETIME
$this->timestamp()         // TIMESTAMP
$this->json()              // JSON (MySQL 5.7+)
 
// Column modifiers (chainable)
->notNull()
->null()
->unique()
->defaultValue('pending')
->defaultExpression('CURRENT_TIMESTAMP')
->unsigned()
->comment('User who owns this task')
 
// Schema operations
$this->addColumn('tasks', 'priority', $this->integer()->defaultValue(0));
$this->dropColumn('tasks', 'priority');
$this->renameColumn('tasks', 'description', 'body');
$this->alterColumn('tasks', 'title', $this->string(500)->notNull());
$this->addForeignKey(...);
$this->dropForeignKey(...);
$this->createIndex(...);
$this->dropIndex(...);

Active Record — Database Rows as PHP Objects

Active Record is Yii2's ORM. Each model class maps to one database table. Each model instance maps to one row.

Creating Model Classes

# You can use Gii (covered in YII-9) or write by hand
php yii gii/model --tableName=users --modelClass=User

Manual approach:

// models/User.php
namespace app\models;
 
use yii\db\ActiveRecord;
 
class User extends ActiveRecord
{
    // Table name (optional — defaults to snake_case of class name)
    public static function tableName(): string
    {
        return 'users';
    }
 
    // Validation rules (same as yii\base\Model from Phase 2)
    public function rules(): array
    {
        return [
            [['username', 'email'], 'required'],
            ['email', 'email'],
            ['username', 'string', 'min' => 3, 'max' => 50],
            ['username', 'unique'],
            ['email', 'unique'],
        ];
    }
 
    // Attribute labels for forms/errors
    public function attributeLabels(): array
    {
        return [
            'id'         => 'ID',
            'username'   => 'Username',
            'email'      => 'Email',
            'created_at' => 'Joined',
        ];
    }
}

// models/Task.php
namespace app\models;
 
use yii\db\ActiveRecord;
 
class Task extends ActiveRecord
{
    const STATUS_PENDING     = 'pending';
    const STATUS_IN_PROGRESS = 'in_progress';
    const STATUS_DONE        = 'done';
 
    public static function tableName(): string
    {
        return 'tasks';
    }
 
    public function rules(): array
    {
        return [
            [['title', 'user_id'], 'required'],
            ['title', 'string', 'max' => 255],
            ['description', 'string'],
            ['status', 'in', 'range' => [self::STATUS_PENDING, self::STATUS_IN_PROGRESS, self::STATUS_DONE]],
            ['user_id', 'integer'],
            ['user_id', 'exist', 'targetClass' => User::class, 'targetAttribute' => 'id'],
        ];
    }
 
    public function attributeLabels(): array
    {
        return [
            'id'          => 'ID',
            'title'       => 'Task Title',
            'description' => 'Description',
            'status'      => 'Status',
            'user_id'     => 'Assigned To',
            'created_at'  => 'Created',
        ];
    }
 
    // Status label helper
    public function getStatusLabel(): string
    {
        return [
            self::STATUS_PENDING     => 'Pending',
            self::STATUS_IN_PROGRESS => 'In Progress',
            self::STATUS_DONE        => 'Done',
        ][$this->status] ?? $this->status;
    }
}

Active Record Architecture

Active Record CRUD

Create — Insert a New Row

// Method 1: new + save()
$task = new Task();
$task->title       = 'Write unit tests';
$task->description = 'Cover all controller actions';
$task->status      = Task::STATUS_PENDING;
$task->user_id     = 1;
 
if ($task->save()) {
    // INSERT succeeded — $task->id is now populated
    echo $task->id; // 1
} else {
    print_r($task->errors); // validation errors
}
 
// Method 2: load from POST + save()
$task = new Task();
if ($task->load(Yii::$app->request->post()) && $task->save()) {
    Yii::$app->session->setFlash('success', 'Task created!');
    return $this->redirect(['task/view', 'id' => $task->id]);
}
 
// Method 3: batch insert (no model validation — raw SQL)
Yii::$app->db->createCommand()->batchInsert(
    'tasks',
    ['title', 'status', 'user_id'],
    [
        ['Task A', 'pending', 1],
        ['Task B', 'pending', 2],
    ]
)->execute();

Read — Find Rows

// Find by primary key
$task = Task::findOne(42);          // returns Task|null
$task = Task::findOne(['id' => 42]); // same
 
// Find multiple by primary keys
$tasks = Task::findAll([1, 2, 3]);  // returns Task[]
 
// Find with conditions
$tasks = Task::find()
    ->where(['status' => 'pending'])
    ->all();                         // returns Task[]
 
$task = Task::find()
    ->where(['id' => 42])
    ->one();                         // returns Task|null
 
// Count
$count = Task::find()
    ->where(['user_id' => 1])
    ->count();
 
// Check existence
$exists = Task::find()
    ->where(['email' => 'admin@example.com'])
    ->exists();
 
// Scalar value
$maxId = Task::find()->max('id');
$minId = Task::find()->min('id');
 
// All tasks for a user, newest first
$tasks = Task::find()
    ->where(['user_id' => 1])
    ->orderBy(['created_at' => SORT_DESC])
    ->limit(10)
    ->offset(20)
    ->all();

Update — Modify a Row

// Find then modify
$task = Task::findOne(42);
if ($task !== null) {
    $task->status = Task::STATUS_DONE;
    $task->save(); // runs validation + UPDATE
}
 
// Load from POST + save
$task = Task::findOne(42);
if ($task !== null && $task->load(Yii::$app->request->post()) && $task->save()) {
    $this->redirect(['task/view', 'id' => $task->id]);
}
 
// Mass update (no model validation — direct SQL)
Task::updateAll(
    ['status' => 'done'],           // SET
    ['user_id' => 1, 'status' => 'pending'] // WHERE
);
 
// Update specific attributes only (skip other validation)
$task->updateAttributes(['status' => Task::STATUS_DONE]);

Delete — Remove a Row

// Find then delete
$task = Task::findOne(42);
if ($task !== null) {
    $task->delete(); // triggers beforeDelete / afterDelete events
}
 
// Mass delete (no events fired)
Task::deleteAll(['status' => 'done', 'user_id' => 5]);

save() vs. insert() vs. update()

Method	When new record	When existing record	Validation
`save()`	INSERT	UPDATE	Yes (by default)
`save(false)`	INSERT	UPDATE	No (skip)
`insert()`	INSERT	N/A	Yes
`update()`	N/A	UPDATE	Yes
`updateAll()`	N/A	Batch UPDATE	No
`deleteAll()`	N/A	Batch DELETE	No

Relations — Connecting Models

Relations describe how tables relate. Yii2 handles the SQL JOIN (or second query) for you.

Defining Relations

// models/Task.php
class Task extends ActiveRecord
{
    // A task belongs to one user
    public function getUser(): \yii\db\ActiveQuery
    {
        return $this->hasOne(User::class, ['id' => 'user_id']);
    }
 
    // A task has many comments (if you add a comments table)
    public function getComments(): \yii\db\ActiveQuery
    {
        return $this->hasMany(Comment::class, ['task_id' => 'id']);
    }
}
 
// models/User.php
class User extends ActiveRecord
{
    // A user has many tasks
    public function getTasks(): \yii\db\ActiveQuery
    {
        return $this->hasMany(Task::class, ['user_id' => 'id']);
    }
}

Convention: relation getter is getXxx() — accessed as $task->user (magic property via __get).

Using Relations

$task = Task::findOne(1);
 
// Lazy loading — executes SELECT * FROM users WHERE id = ? on first access
$user = $task->user;       // User object
echo $user->username;
 
// Accessing again — no extra query (already cached in $task->_related)
echo $task->user->email;

Eager Loading — Preventing N+1

The classic N+1 problem: 10 tasks → 10 separate queries for users.

// BAD — N+1 queries
$tasks = Task::find()->all();
foreach ($tasks as $task) {
    echo $task->user->username; // SELECT for every task!
}
 
// GOOD — eager loading with with()
// 2 queries total: one for tasks, one for all their users
$tasks = Task::find()
    ->with('user')
    ->all();
 
foreach ($tasks as $task) {
    echo $task->user->username; // no extra query
}
 
// Multiple relations
$tasks = Task::find()
    ->with(['user', 'comments'])
    ->all();
 
// Nested relations
$users = User::find()
    ->with(['tasks.comments'])
    ->all();

N+1 Visualised

via() — Junction Tables (Many-to-Many)

For a task_tags junction table:

// models/Task.php
public function getTags(): \yii\db\ActiveQuery
{
    return $this->hasMany(Tag::class, ['id' => 'tag_id'])
                ->viaTable('task_tags', ['task_id' => 'id']);
}
 
// OR with an intermediate model
public function getTaskTags(): \yii\db\ActiveQuery
{
    return $this->hasMany(TaskTag::class, ['task_id' => 'id']);
}
 
public function getTags(): \yii\db\ActiveQuery
{
    return $this->hasMany(Tag::class, ['id' => 'tag_id'])
                ->via('taskTags');
}

Query Builder — Complex SQL in PHP

When Active Record's fluent API isn't enough, the Query Builder lets you write structured SQL without raw strings.

Basic Query Builder Usage

use yii\db\Query;
 
// SELECT with WHERE, ORDER, LIMIT
$rows = (new Query())
    ->select(['id', 'title', 'status', 'created_at'])
    ->from('tasks')
    ->where(['status' => 'pending'])
    ->andWhere(['>', 'id', 100])
    ->orderBy(['created_at' => SORT_DESC])
    ->limit(20)
    ->all(); // returns array of associative arrays (not ActiveRecord objects)
 
// One row
$row = (new Query())
    ->from('tasks')
    ->where(['id' => 42])
    ->one();
 
// Scalar / column
$count = (new Query())
    ->from('tasks')
    ->where(['status' => 'done'])
    ->count();
 
$titles = (new Query())
    ->select('title')
    ->from('tasks')
    ->column(); // returns flat array of values

WHERE Conditions

// Hash format — AND equality
->where(['status' => 'pending', 'user_id' => 1])
 
// Operator format
->where(['>', 'id', 100])
->where(['<>', 'status', 'done'])
->where(['in', 'status', ['pending', 'in_progress']])
->where(['not in', 'user_id', [3, 5]])
->where(['like', 'title', 'write'])        // LIKE '%write%'
->where(['like', 'title', 'write%', false]) // LIKE 'write%' (raw)
->where(['between', 'id', 10, 50])
->where(['is', 'description', null])        // IS NULL
->where(['is not', 'description', null])    // IS NOT NULL
 
// Combining
->where(['status' => 'pending'])
->andWhere(['>', 'created_at', '2026-01-01'])
->orWhere(['user_id' => 99])
 
// String format (UNSAFE if using user input)
->where('status = "pending"') // DON'T do this with user data
 
// Parameterised string
->where('status = :s AND user_id = :u', [':s' => 'pending', ':u' => 1])

JOINs and Grouping

// JOIN
$rows = (new Query())
    ->select(['t.id', 't.title', 'u.username'])
    ->from(['t' => 'tasks'])
    ->innerJoin(['u' => 'users'], 'u.id = t.user_id')
    ->where(['t.status' => 'pending'])
    ->all();
 
// LEFT JOIN
->leftJoin(['c' => 'comments'], 'c.task_id = t.id')
 
// GROUP BY + HAVING + aggregation
$summary = (new Query())
    ->select(['user_id', 'COUNT(*) AS task_count', 'MAX(created_at) AS latest'])
    ->from('tasks')
    ->groupBy('user_id')
    ->having(['>', 'task_count', 5])
    ->orderBy(['task_count' => SORT_DESC])
    ->all();

Active Query vs. Query Builder

	Active Record (`Task::find()`)	Query Builder (`new Query()`)
Returns	`Task[]` objects	`array[]` associative
Validation/Events	✅ yes	❌ no
Relations	✅ `->with()`	❌ manual joins
Best for	Normal CRUD	Reporting, aggregations, multi-table
Performance	Slightly slower (object hydration)	Faster for large result sets

Executing Raw SQL

// Read
$rows = Yii::$app->db->createCommand('SELECT * FROM tasks WHERE status = :s', [
    ':s' => 'pending',
])->queryAll();
 
$row  = Yii::$app->db->createCommand($sql, $params)->queryOne();
$col  = Yii::$app->db->createCommand($sql, $params)->queryColumn();
$val  = Yii::$app->db->createCommand($sql, $params)->queryScalar();
 
// Write
Yii::$app->db->createCommand('UPDATE tasks SET status = :s WHERE id = :id', [
    ':s'  => 'done',
    ':id' => 42,
])->execute();

Transactions — Data Integrity

When multiple writes must succeed or fail together, wrap them in a transaction.

$db = Yii::$app->db;
$transaction = $db->beginTransaction();
 
try {
    $user = new User();
    $user->username = 'alice';
    $user->email    = 'alice@example.com';
    if (!$user->save()) {
        throw new \RuntimeException('User save failed: ' . json_encode($user->errors));
    }
 
    $task = new Task();
    $task->title   = 'Onboarding task';
    $task->user_id = $user->id;
    $task->status  = Task::STATUS_PENDING;
    if (!$task->save()) {
        throw new \RuntimeException('Task save failed: ' . json_encode($task->errors));
    }
 
    $transaction->commit();
} catch (\Throwable $e) {
    $transaction->rollBack();
    Yii::error($e->getMessage());
    throw $e; // re-throw so the controller can handle it
}

Cleaner callback form:

Yii::$app->db->transaction(function ($db) {
    $user = new User([
        'username' => 'alice',
        'email'    => 'alice@example.com',
    ]);
    if (!$user->save()) {
        throw new \Exception('User save failed');
    }
 
    $task = new Task([
        'title'   => 'Onboarding task',
        'user_id' => $user->id,
        'status'  => Task::STATUS_PENDING,
    ]);
    if (!$task->save()) {
        throw new \Exception('Task save failed');
    }
});
// Any exception triggers automatic rollback

Isolation levels:

$transaction = $db->beginTransaction(\yii\db\Transaction::READ_COMMITTED);
// Other levels: SERIALIZABLE, REPEATABLE_READ, READ_UNCOMMITTED

Putting It All Together — Database-Backed TaskController

Here's controllers/TaskController.php rewritten to use the database:

namespace app\controllers;
 
use Yii;
use yii\web\Controller;
use yii\web\NotFoundHttpException;
use yii\filters\VerbFilter;
use app\models\Task;
 
class TaskController extends Controller
{
    public function behaviors(): array
    {
        return [
            'verbs' => [
                'class'   => VerbFilter::class,
                'actions' => [
                    'delete' => ['POST'],
                ],
            ],
        ];
    }
 
    // GET /task
    public function actionIndex(): string
    {
        $tasks = Task::find()
            ->with('user')
            ->orderBy(['created_at' => SORT_DESC])
            ->all();
 
        return $this->render('index', ['tasks' => $tasks]);
    }
 
    // GET /task/view?id=1
    public function actionView(int $id): string
    {
        $task = $this->findTask($id);
        return $this->render('view', ['task' => $task]);
    }
 
    // GET/POST /task/create
    public function actionCreate(): string|\yii\web\Response
    {
        $task = new Task();
        $task->status = Task::STATUS_PENDING;
 
        if ($task->load(Yii::$app->request->post()) && $task->save()) {
            Yii::$app->session->setFlash('success', 'Task created successfully.');
            return $this->redirect(['view', 'id' => $task->id]);
        }
 
        return $this->render('create', ['task' => $task]);
    }
 
    // GET/POST /task/update?id=1
    public function actionUpdate(int $id): string|\yii\web\Response
    {
        $task = $this->findTask($id);
 
        if ($task->load(Yii::$app->request->post()) && $task->save()) {
            Yii::$app->session->setFlash('success', 'Task updated.');
            return $this->redirect(['view', 'id' => $task->id]);
        }
 
        return $this->render('update', ['task' => $task]);
    }
 
    // POST /task/delete?id=1
    public function actionDelete(int $id): \yii\web\Response
    {
        $this->findTask($id)->delete();
        Yii::$app->session->setFlash('success', 'Task deleted.');
        return $this->redirect(['index']);
    }
 
    private function findTask(int $id): Task
    {
        $task = Task::findOne($id);
        if ($task === null) {
            throw new NotFoundHttpException("Task #$id not found.");
        }
        return $task;
    }
}

What changed from Phase 2:

Task::find()->with('user')->all() replaces the in-memory array
new Task() and $task->save() replace manual array manipulation
Task::findOne($id) replaces array lookup
$task->delete() replaces array splice
Relations (with('user')) load the associated user automatically

Fixtures — Seeding Test Data

Fixtures let you load known data for development and tests.

// tests/fixtures/UserFixture.php
namespace tests\fixtures;
 
use yii\test\ActiveFixture;
 
class UserFixture extends ActiveFixture
{
    public $modelClass = 'app\models\User';
    public $dataFile   = '@tests/fixtures/data/users.php';
    public $depends    = []; // load before TaskFixture
}

// tests/fixtures/data/users.php
return [
    'alice' => [
        'username'   => 'alice',
        'email'      => 'alice@example.com',
        'created_at' => '2026-01-01 00:00:00',
    ],
    'bob' => [
        'username'   => 'bob',
        'email'      => 'bob@example.com',
        'created_at' => '2026-01-02 00:00:00',
    ],
];

// tests/fixtures/TaskFixture.php
namespace tests\fixtures;
 
use yii\test\ActiveFixture;
 
class TaskFixture extends ActiveFixture
{
    public $modelClass = 'app\models\Task';
    public $dataFile   = '@tests/fixtures/data/tasks.php';
    public $depends    = [UserFixture::class];
}

// tests/fixtures/data/tasks.php
return [
    'task1' => [
        'title'      => 'Write unit tests',
        'status'     => 'pending',
        'user_id'    => 1, // alice
        'created_at' => '2026-01-10 09:00:00',
    ],
    'task2' => [
        'title'      => 'Deploy to staging',
        'status'     => 'in_progress',
        'user_id'    => 2, // bob
        'created_at' => '2026-01-11 10:00:00',
    ],
];

Load in test classes:

// tests/unit/TaskTest.php
use yii\test\FixtureTrait;
 
class TaskTest extends \Codeception\Test\Unit
{
    use FixtureTrait;
 
    public function fixtures(): array
    {
        return [
            'users' => UserFixture::class,
            'tasks' => TaskFixture::class,
        ];
    }
 
    public function testFindByStatus(): void
    {
        $pending = Task::find()->where(['status' => 'pending'])->all();
        $this->assertCount(1, $pending);
        $this->assertEquals('Write unit tests', $pending[0]->title);
    }
}

Complete Request Flow — Create Task with Database

Common Patterns

Pagination with ActiveDataProvider

use yii\data\ActiveDataProvider;
 
public function actionIndex(): string
{
    $dataProvider = new ActiveDataProvider([
        'query'      => Task::find()->with('user')->orderBy(['created_at' => SORT_DESC]),
        'pagination' => ['pageSize' => 10],
    ]);
 
    return $this->render('index', ['dataProvider' => $dataProvider]);
}

In the view, use ListView or GridView widget (covered in YII-8).

Search / Filter Model Pattern

// models/TaskSearch.php
namespace app\models;
 
use yii\base\Model;
use yii\data\ActiveDataProvider;
 
class TaskSearch extends Task
{
    public function rules(): array
    {
        return [
            [['id', 'user_id'], 'integer'],
            [['title', 'status'], 'safe'],
        ];
    }
 
    public function scenarios(): array
    {
        return Model::scenarios();
    }
 
    public function search(array $params): ActiveDataProvider
    {
        $query = Task::find()->with('user');
 
        $dataProvider = new ActiveDataProvider([
            'query'      => $query,
            'pagination' => ['pageSize' => 10],
        ]);
 
        if (!$this->load($params) || !$this->validate()) {
            return $dataProvider;
        }
 
        $query->andFilterWhere(['id' => $this->id])
              ->andFilterWhere(['user_id' => $this->user_id])
              ->andFilterWhere(['status' => $this->status])
              ->andFilterWhere(['like', 'title', $this->title]);
 
        return $dataProvider;
    }
}

In the controller:

public function actionIndex(): string
{
    $searchModel = new TaskSearch();
    $dataProvider = $searchModel->search(Yii::$app->request->queryParams);
 
    return $this->render('index', [
        'searchModel'  => $searchModel,
        'dataProvider' => $dataProvider,
    ]);
}

Soft Delete with beforeDelete Event

class Task extends ActiveRecord
{
    public function beforeDelete(): bool
    {
        if (!parent::beforeDelete()) {
            return false;
        }
        // Log deletion
        Yii::info("Task #{$this->id} '{$this->title}' deleted by user " . Yii::$app->user->id);
        return true;
    }
}

Timestamps with TimestampBehavior

use yii\behaviors\TimestampBehavior;
use yii\db\Expression;
 
class Task extends ActiveRecord
{
    public function behaviors(): array
    {
        return [
            [
                'class'              => TimestampBehavior::class,
                'createdAtAttribute' => 'created_at',
                'updatedAtAttribute' => 'updated_at',
                'value'              => new Expression('NOW()'),
            ],
        ];
    }
}

Now created_at and updated_at are set automatically on save — no need to set them manually.

What's Next

You now have a fully database-backed Yii2 application:

Schema versioned with migrations
Tables mapped to Active Record models
Relations eliminating manual JOINs
Query Builder for complex reads
Transactions for data integrity

Phase 4 → Authentication & Authorization (RBAC) — control who can see and do what: sessions, login forms, AccessControl behavior, and full role-based access control with database-backed rules.

← Phase 2: MVC & Routing | → Yii2 Series Overview

Database Integration in Yii2: Active Record, Query Builder & Migrations

What You'll Build

Configuring the `db` Component

Migrations — Versioning Your Schema

Creating a Migration

Running Migrations

Migration Builder Methods

Active Record — Database Rows as PHP Objects

Creating Model Classes

Active Record Architecture

Active Record CRUD

Create — Insert a New Row

Read — Find Rows

Update — Modify a Row

Delete — Remove a Row

save() vs. insert() vs. update()

Relations — Connecting Models

Defining Relations

Using Relations

Eager Loading — Preventing N+1

N+1 Visualised

via() — Junction Tables (Many-to-Many)

Query Builder — Complex SQL in PHP

Basic Query Builder Usage

WHERE Conditions

JOINs and Grouping

Active Query vs. Query Builder

Executing Raw SQL

Transactions — Data Integrity

Putting It All Together — Database-Backed TaskController

Fixtures — Seeding Test Data

Complete Request Flow — Create Task with Database

Common Patterns

Search / Filter Model Pattern

Soft Delete with beforeDelete Event

Timestamps with TimestampBehavior

What's Next

📬 Subscribe to Newsletter

💬 Comments

What You'll Build

Configuring the db Component

Migrations — Versioning Your Schema

Creating a Migration

Running Migrations

Migration Builder Methods

Active Record — Database Rows as PHP Objects

Creating Model Classes

Active Record Architecture

Active Record CRUD

Create — Insert a New Row

Read — Find Rows

Update — Modify a Row

Delete — Remove a Row

save() vs. insert() vs. update()

Relations — Connecting Models

Defining Relations

Using Relations

Eager Loading — Preventing N+1

N+1 Visualised

via() — Junction Tables (Many-to-Many)

Query Builder — Complex SQL in PHP

Basic Query Builder Usage

WHERE Conditions

JOINs and Grouping

Active Query vs. Query Builder

Executing Raw SQL

Transactions — Data Integrity

Putting It All Together — Database-Backed TaskController

Fixtures — Seeding Test Data

Complete Request Flow — Create Task with Database

Common Patterns

Pagination with ActiveDataProvider

Search / Filter Model Pattern

Soft Delete with beforeDelete Event

Timestamps with TimestampBehavior

What's Next

📬 Subscribe to Newsletter

💬 Comments

Configuring the `db` Component