Database Migrations in CodeIgniter

What is Migration ?

Quoting from CI documentation:

Migrations are a convenient way for you to alter your database in a structured and organized manner. You could edit fragments of SQL by hand but you would then be responsible for telling other developers that they need to go and run them. You’d also have to keep track of which changes need to be run against the production machines next time you deploy.

Migrations come in extremely handy when you are working with a team of developers. Normally whenever you created a new database table or modified existing one by adding/removing certain fields or even added some data to database, you would tell your fellow developers to run those SQL queries in their machines so that database remains in sync.

This isn't ideal, we needed some way so that:

• Framework (CodeIgniter or whatever) automatically detects and runs those SQL queries on our behalf without handing over SQL query files to other developers each time we make some change to database

• And keeps versions of our database changes similar to versioning of files when we use git. This means we can go back and forth easily to specific version of database changes. Something went wrong on production site and you need to revert to specific database change, no problem simply tell CodeIgniter which change to revert to and you are done.

That's what migrations do.

You now see how important and useful migrations are. One of the first frameworks that really shined up with this idea was Laravel and then I saw similar concept in Android applications. CodeIgniter also allows us to setup migrations which is really cool.

Setting Up Migrations in CodeIgniter

In order to enable migrations:

• Open application/config/migration.php file and set the value for $config['migration_enabled'] from false to true.
• Create a folder called migrations in application/config/ folder and that's it.

In migrations folder, we can create our database changes. Let's say we want to create a new users table. To do so, create a file called 001_add_users.php in migrations folder and paste this code in it:

class Migration_Add_user extends CI_Migration
{
    public function up()
    {
        $this->dbforge->add_field(
           array(
              'id' => array(
                 'type' => 'INT',
                 'constraint' => 5,
                 'unsigned' => true,
                 'auto_increment' => true
              ),
              'name' => array(
                 'type' => 'VARCHAR',
                 'constraint' => '100',
              ),
              'email' => array(
                 'type' => 'TEXT',
                 'null' => true,
              ),
           )
        );

        $this->dbforge->add_key('id', TRUE);
        $this->dbforge->create_table('users');
    }

    public function down()
    {
        $this->dbforge->drop_table('users');
    }
}

There are few things to notice:

• We see that this migration class extends CI_Migration class.
• It has only two methods up() and down()
• It uses dbforge object to build up queries in OOP way
• The migration files should be prefixed with three digits like 001_first_migration.php, 002_second_migration.php and so on

So that's how a typical migration class should look like. But we are only interested in up() and down() methods. The up() method is called by system whenever you need to update the database and down() method is used to revert that change that was done by the up() method. For example, if in up() method you create a new table then in down() method you should specify code to remove that table. Similarly if in up() method you add a new field to some table then in down() method you should specify code that deletes that field. So both of these methods are opposite to each other. In each migration you should always specify the code for both.

Now that we have created our migration, we need to run it. Let's create a controller called Migrate that will run our migrations:

class migrate extends CI_Controller {
    public function index()
    {
        // load migration library
        $this->load->library('migration');

        if ( ! $this->migration->current())
        {
            echo 'Error' . $this->migration->error_string();
        } else {
            echo 'Migrations ran successfully!'
        }   
    }    
}

Now if you visit your application url at http://yoursite.com/migrate and all goes well, you should success message and the new table users should be created in the database. If you run migration for the first time, you would also notice that a table called migrations should also be created in your database by CodeIgniter. This table can keep track of your database migration changes.

Now refresh page or re-visit http://yoursite.com/migrate and you would notice nothing happens, this time CodeIgniter won't do anything with your migration since it has already been run. To prove it further, delete users table manually from your database and then re-visit http://yoursite.com/migrate page and you will see CodeIgniter didn't create users table again. That's because CodeIgniter knows that it has already run that particular migration. Now if you add some other migrations in migrations folder and then visit migrate URL again, this time CodeIgniter will run your new migrations. How does CodeIgniter know which migrations to run ? It keeps track of them migrations table that it creates as well as config['migration_version'] setting in application/config/migration.php file. That's how CodeIgniter versions your database changes.

You see how simple and powerful migrations are. Now that you have made changes to database, you don't need to worry how other team members will embrace those changes, all you need to ask them is to visit the migration url and database on their machines will also get those changes!

In the above example, we used $this->migration->current() method to go to current migration version that is set in config['migration_version']. There are couple of more methods that allow you to migrate based on most recent version of migration based on file system and one that allows you to go to specific migration version. To lean more about dbforge methods, check out its documentation.

So if you have never used database migrations before, you should definitely use them because they are awesome!