Yii2 Migrations & Seeding

1. Introduction to Yii2 Migrations

Yii2 migrations provide a structured way to manage database schema changes using PHP code. Instead of manually creating tables, adding columns, or modifying indexes, migrations allow you to define these changes in code and apply them using console commands.

1.1 Why Use Migrations?

• Version Control for Database Changes – Ensures consistency across environments.
• Automated Schema Management – No need to manually alter databases.
• Easy Rollbacks – Allows reverting changes in case of mistakes.
• Better Collaboration – Helps teams synchronize database structures.

---

2. Creating a Migration

Run the following command:

php yii migrate/create create_table_student

This generates a migration file inside the migrations folder (@app/migrations). The file will be named something like:

m230101_123456_create_table_student.php

---

3. Writing a Migration

Modify the generated file by defining schema changes in up() and down() methods.

use yii\db\Migration;
class m230101_123456_create_table_student extends Migration
{
    public function up()
    {
        $this->createTable('student', [
            'id' => $this->primaryKey(),
            'name' => $this->string(255)->notNull(),
            'email' => $this->string(255)->unique(),
            'created_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP'),
        ]);
    }
    public function down()
    {
        $this->dropTable('student');
    }
}

---

4. Running Migrations

To apply migrations:

php yii migrate

To revert the last migration:

php yii migrate/down 1

To apply only a specific migration:

php yii migrate/up m230101_123456_create_table_student

---

5. Seeding (Inserting Initial Data)

Yii2 does not have built-in seeding, but you can create migrations that insert initial data.

5.1 Creating a Seeder Migration

php yii migrate/create insert_dummy_data

Modify the generated file:

use yii\db\Migration;
class m230101_234567_insert_dummy_data extends Migration
{
    public function up()
    {
        $this->batchInsert('student', ['name', 'email'], [
            ['Rahul', 'rahul@example.com'],
            ['Mohit', 'mohit@example.com'],
            ['Vivek', 'vivek@example.com'],
        ]);
    }
    public function down()
    {
        $this->delete('student', ['email' => ['rahul@example.com', 'mohit@example.com', 'vivek@example.com']]);
    }
}

Run:

php yii migrate

---

6. Running Migrations Inside Modules

In Yii2 modules, migrations should be placed inside the migrations directory of the module.

Example Module Structure

modules/
    location/
        migrations/
            m230101_123456_create_table_location.php
        models/
        controllers/

6.1 Running Module Migrations

To run migrations inside a module, specify the path:

php yii migrate --migrationPath=@app/modules/location/migrations

To revert:

php yii migrate/down --migrationPath=@app/modules/location/migrations

---

7. Limitations of Yii2 Migrations

Although Yii2 migrations are useful, they have some limitations:

7.1 Time-Consuming for Large Databases

• In real-world applications, a single table may contain hundreds or thousands of columns.
• Writing migrations for such large tables can slow down development since developers spend more time on schema changes instead of actual business logic.

7.2 Migration Execution Speed

• Running migrations on huge tables with millions of rows can be slow and may cause downtime in production if not handled properly.

7.3 Complex Schema Changes

• Altering large tables (e.g., adding or modifying multiple columns at once) can be tricky.
• Migrations may fail on different database systems due to varying SQL syntax (e.g., MySQL vs. PostgreSQL).

7.4 Limited Support for Some Advanced Database Features

• Yii2 migrations provide basic support for indexes and constraints, but some advanced database features (e.g., partitioning, materialized views) require raw SQL.

---

8. When to Use Migrations?

Scenario	Use Migrations?	Reason
Small to Medium Applications	Yes	Keeps database schema changes under version control.
Large Applications with Frequent Changes	Use with caution	If database structure changes too often, developers spend too much time writing migrations.
Production Environments	Yes	Ensures consistent schema across all servers.
Large Tables with 1000+ Columns	No	Writing migrations for large tables is time-consuming and may lead to performance issues. Instead, use raw SQL scripts.
Minor Schema Changes (e.g., Adding a Single Column)	Yes	Easy to manage and roll back if needed.
Major Schema Overhaul (Dropping/Recreating Large Tables)	No	Use database-specific scripts instead.

---

 9. Alternative Approaches for Large Databases

If migrations are too slow or difficult for large projects, consider these alternatives:

Use Database Dumps for Schema Management

mysqldump -u root -p database_name > schema.sql

Instead of writing Yii2 migrations, you can store the SQL dump in version control.

Raw SQL Scripts for Large Tables

• Instead of writing a migration, create an SQL script and execute it manually:

ALTER TABLE large_table ADD COLUMN new_column INT(11) NOT NULL;

Use a Dedicated Database Migration Tool

• Tools like Liquibase or Flyway provide more advanced migration capabilities.

---

10. Summary

Feature	Command / Method
Create Migration	php yii migrate/create migration_name
Run All Migrations	php yii migrate
Rollback Last Migration	php yii migrate/down 1
Run Specific Migration	php yii migrate/up migration_name
Insert Data (Seeder)	Use $this->batchInsert() in a migration
Module Migration	php yii migrate --migrationPath=@app/modules/module_name/migrations
Large Database Alternative	Use SQL dumps or manual scripts