Yii DB Migrations

A database migrations engine for the Yii framework. It comes in the form of an extension which can be dropped into any existing Yii framework application.

It relies on the database abstraction layer of the Yii framework to talk to the database engine.

It’s largely inspired on the migrations functionality of the Ruby on Rails framework.

Why bother in the first place?

You might wonder why you need database migrations in the first place. Sure, you could use plain SQL files to apply updates to the database, but they have a big disadvantage: they are database specific.

Imagine you initially create your database using SQLite and later on decide to change to MySQL. When you use plain SQL files, you will need to rewrite them using MySQL’s flavor of SQL. Also, you will need to create some tool that knows what has been applied already to the database and what’s not applied yet.

Meet database migrations. By defining a higher level interface to creating and maintaining the database schema, you can define it in a database agnostic way.

By using PHP functions to create tables, indexes, fields, … you can write the schema once and apply it to any database backend supported. The added bonus is that the yii-dbmigrations extension keeps track of which migrations have been applied already and which ones still need to be applied.

Current limitations

Be aware that the current version is still very alpha.

There are only two database engines supported:

• MySQL
• SQLite

Installing the extension

Putting the files in the right location

Installing the extension is quite easy. Once you downloaded the files from github, you need to put all the files in a folder called “yii-dbmigrations” and put it in the protected/extensions folder from your project.

Configuring the database connection

You need to make sure you configure database access properly in the configuration file of your project. As the console applications in the Yii framework look at the protected/config/console.php file, you need to make sure you configure the db settings under the components key in the configuration array. For MySQL, the configuration will look as follows:

'components' => array(
    'db'=>array(
        'class' => 'CDbConnection',
        'connectionString'=>'mysql:host=localhost;dbname=my_db',
        'charset' => 'UTF8',
        'username'=>'root',
        'password'=>'root',
    ),
),

For SQLite, the configuration looks as follows:

'components' => array(
    'db'=>array(
        'connectionString'=>'sqlite:'.dirname(__FILE__).'/../data/my_db.db',
    ),
),

Configuring the command map

To make the yiic tool know about the migrate command, you need to add a commandMap key to the application configuration in protected/config/console.php.

In there, you need to put the following configuration:

'commandMap' => array(
    'migrate' => array(
        'class'=>'application.extensions.yii-dbmigrations.CDbMigrationCommand',
    ),
),

Testing your installation

To test if the installation was succesfull, you can run the yiic command and should see the following output:

Yii command runner (based on Yii v1.0.10)
Usage: protected/yiic <command-name> [parameters...]

The following commands are available:
 - migrate
 - message
 - shell
 - webapp

To see individual command help, use the following:
   protected/yiic help <command-name>

It should mention that the migrate command is available.

Creating migrations

Naming conventions

You can create migrations by creating files in the protected/migrations folder or in the migrations folder inside your module. The file names for the migrations should follow a specific naming convention:

m20090614213453_MigrationName.php

The migration file names always start with the letter m followed by 14 digit timestamp and an underscore. After that, you can give a name to the migration so that you can easily identify the migration.

You can also use the protected/yiic migrate create <MigrationName> to create a new empty migration:

$ protected/yiic migrate create TestMigration
Migrations directory: protected/migrations/

Created migration: m20091114210716_TestMigration.php

Implementing the migration

In the migration file, you need to create a class that extends the parent class CDbMigration. The CDbMigration class needs to have the up and down methods as you can see in the following sample migration:

class m20090611153243_CreateTables extends CDbMigration {

    // Apply the migration
    public function up() {

        // Create the posts table
        $t = $this->newTable('posts');
        $t->primary_key('id');
        $t->string('title');
        $t->text('body');
        $t->index('posts_title', 'title');
        $this->addTable($t);

    }

    // Remove the migration
    public function down() {

        // Remove the table
        $this->removeTable('posts');

    }

}

Supported functionality

In the migration class, you have the following functionality available:

• execute: execute a raw SQL statement that doesn’t return any data.
• query: execute a raw SQL statement that returns data.
• createTable: create a new table
• newTable: creates a new table definition
• addTable: adds the table definition to the migration
• renameTable: rename a table
• removeTable: remove a table
• addColumn: add a column to a table
• renameColumn: rename a column in a table
• changeColumn: change the definition of a column in a table
• removeColumn: remove a column from a table
• addIndex: add an index to the database or table
• removeIndex: remove an index from the database or table

When specifying fields, we also support a type mapping so that you can use generic type definitions instead of database specific type definitions. The following types are supported:

• primary_key
• string
• text
• integer
• float
• decimal
• datetime
• timestamp
• time
• date
• binary
• boolean
• bool

You can also specify a database specific type when defining a field, but this will make your migrations less portable across database engines.

Short syntax to create tables

There is also a short syntax to create tables which is a lot more concise than
using the createTable syntax. To use this syntax, you need to perform 3 different steps:

Create a new table definition

The first step is to create a new table definition in your migration:

$t = $this->newTable('<tableName>');

Add the fields and indexes

Now, you can add fields and indexes to your table definition. The name of the function indicates the type of field to add:

$t->primary_key('id');
$t->string('title');
$t->text('body');

Adding indexes is done by the index and unique functions:

$t->index('title');
$t->unique('title');

Creating the table

To actually create the table, you need to add the definition to the migration using the addTable function:

$this->addTable($t);

It will now create the table and indexes.

Applying migrations

Once you created the migrations, you can apply them to your database by running the migrate command:

protected/yiic migrate

You will see the following output:

Migrations directory: protected/migrations/

Creating initial schema_version table
=== Applying: m20090611153243_CreateTables =====================================
    >> Creating table: posts
    >> Adding index posts_title to table: posts
=== Marked as applied: m20090611153243_CreateTables ============================

=== Applying: m20090612162832_CreateTags =======================================
    >> Creating table: tags
    >> Creating table: post_tags
    >> Adding index post_tags_post_tag to table: post_tags
=== Marked as applied: m20090612162832_CreateTags ==============================

=== Applying: m20090612163144_RenameTagsTable ==================================
    >> Renaming table: post_tags to: post_tags_link_table
=== Marked as applied: m20090612163144_RenameTagsTable =========================

It will apply all the migrations found in the protected/migrations directory provided they were not applied to the database yet.

It will also apply any migrations found in the migrations directory inside each module which is enabled in the application.

If you run the migrate command again, it will show nothing as the migrations were applied already:

Migrations directory: protected/migrations/

Since the migrate command checks based on the ID of each migration, it can detect that a migration is already applied to the database and therefor doesn’t need to be applied anymore.

protected/yiic migrate command reference

The complete reference to the migrate command can be obtained by running the protected/yiic help migrate command. This outputs the following help:

USAGE
  migrate [create|version|up|down|list]

DESCRIPTION
  This command applies the database migrations which can be found in the
  migrations directory in your project folder.

PARAMETERS
 * create: creates a new migration in the migrations directory.

 * version: options, the ID of the migration to migrate the database to.

 * up: optional, apply the first migration that is not applied to the database
   yet.

 * down: remove the last applied migration from the database.

 * redo: redo the last migration (removes and installs it again)

 * list: list all the migrations available in the application and show if they
   are applied or not.

EXAMPLES
 * Apply all migrations that are not applied yet:
        migrate

 * Migrate up to version 20090612163144 if it's not applied yet:
        migrate 20090612163144

 * Migrate down to version 20090612163144 if it's applied already:
        migrate 20090612163144

 * Apply the first migration that is not applied to the database yet:
        migrate up

 * Remove the last applied migration:
        migrate down

 * Re-apply the last applied migration:
        migrate redo

 * List all the migrations found in the application and their status:
        migrate list

 * Create a new migration
        migrate create

 * Create a new name migration
        migrate create MyMigrationName

Data types

The yii-dbmigrations package supports the following data types:

Author information

This extension is created and maintained by Pieter Claerhout. You can contact the author on:

• Website: http://www.yellowduck.be
• Twitter: http://twitter.com/pieterclaerhout
• Github: http://github.com/pieterclaerhout/yii-dbmigrations/tree/master