Keep Your Yii Databases in Sync: A Practical Migration Playbook

Why Manual Schema Updates Fail

In many projects, developers tweak the database directly in each environment – a new column here, a table dropped there – and then hope the changes propagate. The result is drift: dev has a column that production lacks, staging has an extra index that never made it to QA. Bugs surface only after a user reports a missing field. Fixing the problem means hunting down the forgotten change, reproducing the error, and patching the schema again.

The Yii Migration System to the Rescue

Yii’s migration framework turns ad‑hoc changes into a versioned, script‑based pipeline. Each migration file is a PHP class that declares up() (apply) and down() (revert) methods. When you run php yii migrate, Yii executes the up() methods in file‑name order and records the applied migrations in a dedicated table (by default yii_migration). This guarantees every environment applies the same changes in the same sequence.

Typical Migration File

class m210101_123456_create_user_table extends Migration
{
    public function up()
    {
        $this->createTable('user', [
            'id' => $this->primaryKey(),
            'email' => $this->string()->notNull()->unique(),
            'created_at' => $this->integer()->notNull(),
        ]);
    }

    public function down()
    {
        $this->dropTable('user');
    }
}

Running Migrations

Execute the following from the project root. You need the web server’s database user and the ability to write to the migration table.

php yii migrate

After a successful run, inspect the migration table:

SELECT * FROM yii_migration;

Column	Description
migration	File name of the migration
apply_time	Unix timestamp when applied

Practical Workflow for a Team

1. Generate with Gii: php yii gii/migration. This scaffolds the class and ensures a unique timestamp.
2. Commit the file to version control. Keep all migrations in the same branch so the sequence is preserved.
3. Review the migration for data impact. If it alters data (e.g., dropColumn), add a pre‑migration data‑backup script.
4. Test locally: php yii migrate --migrationPath=@app/migrations --migrationTable=app_migration on a fresh DB.
5. In CI, run migrations before unit tests to ensure the test environment matches production.
6. On deployment, run php yii migrate --interactive=0 to auto‑apply remaining migrations.

Trade‑Offs and Limitations

While migrations enforce consistency, they also introduce constraints:

• Hard‑coded data in up() can create duplicate rows if applied on a non‑empty database. Use safeUp() and safeDown() for transactions.
• Non‑reversible changes (e.g., dropColumn without a backup) can’t be rolled back automatically. Always write a down() that restores the previous state.
• Branch drift occurs if migrations live in feature branches that are merged late. Keep migrations in a dedicated branch or enforce merge order.
• Large schema changes may require schema dumps rather than incremental migrations, which can be more complex to version.

Actionable Checklist

• Use php yii migrate --migrationPath=@app/migrations in every environment.
• Verify the migration table matches the file names and timestamps.
• Run unit tests that query the new tables to catch regressions early.
• Back up any data before dropping columns or tables.
• Automate migration execution in CI/CD pipelines to avoid manual oversights.

Adopting Yii’s migration system turns database drift from a silent hazard into a visible, versioned process. With disciplined use, you can keep dev, staging, and production in lockstep and focus on delivering features instead of chasing schema bugs.