How To Implement Database Migrations with CodeIgniter?

Migrations give you the ability to keep your database across all environments the same without comprimising the data inside the database. Think it as a version control for your database schema. You should be doing this at all times especially when there are multiple developers so all developers don't need to worry about having the latest database table.

A feature that was added fairly recently (CodeIgniter 2.1.0) is Migrations. This is a useful Class that allows for you to create and manage your database structure through your code. Database migrations are a way of effectively versioning your database changes.

STEP BY STEP

• You first need to create your migrations folder, application/migrations/

• You need to create a file inside this new directory (i.e. application/migrations/) and the naming format has to start with your version number followed by the name of your class (make it is descriptive of the changes that you are making i.e. your first one might be: initial_schema). My first migration file was called: 001_initial_schema.php and my next one could be 002_alter_schema.php

001_initial_schema.php

if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Migration_Initial_schema extends CI_Migration {

 public function up()
 {
  
           $this->load->dbforge();

           $this->dbforge->add_field(array(
   'blog_id' => array(
    'type' => 'INT',
    'constraint' => 5,
    'unsigned' => TRUE,
    'auto_increment' => TRUE
   ),
   'blog_title' => array(
    'type' => 'VARCHAR',
    'constraint' => '100',
   ),
   'blog_description' => array(
    'type' => 'TEXT',
    'null' => TRUE,
   ),
  ));

             $this->dbforge->add_key('blog_id', TRUE);  
             $this->dbforge->create_table('blog');
 }

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

  }

You'll notice that there are two functions (up and down) in this class. The up function is run when you upgrade to/past this version and likewise the down function is run if you downgrade your database (using the config file) below this version. Your down function will always do the reverse of what your up function does. Essentially restoring your database to the state that it was before that version was added.

• Once your first migration is complete, you simply need to open application/config/migrations.php and edit:
$config['migration_enabled'] = FALSE;
to:
$config['migration_enabled'] = TRUE;

A bit lower down in this same file you will see the configuration property called migration_version i.e. $config['migration_version'] = 1;
As you see here, you need to set migration_version every time you create a new migration file. This is how the system knows what the current database version should be for your site. When it recognises that you're running on a lower version than this it will upgrade your database through all the steps leading up to this version. So once you add a migration you would increase this version number to match.

• Last step! Almost there! Now we actually have to call the actual migration. You can create a simple controller called controllers/migrate.php, like the one displayed below, which when visited calls the migration library.
migrate.php

if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Migrate extends CI_Controller {

  public function index()
  {
    $this->load->library('migration');

    if ( ! $this->migration->current()) {
      show_error($this->migration->error_string());
    }
  }

}

So there you have it, you now have a version controlled database that can be rolled forwards/backwards whenever you want.

Adding a second schema (called '002_alter_schema.php') to the application/migrations you can see how the database can be procedurally altered.

if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Migration_Alter_schema extends CI_Migration {

  public function up()
  {

    $this->load->dbforge();

    $fields = array(
      'author_first_name VARCHAR(50) DEFAULT NULL',
      'author_last_name VARCHAR(50) DEFAULT NULL'
    );

    $this->dbforge->add_column('blog', $fields);
  }

  public function down()
  {
    
    $this->load->dbforge();

    $this->dbforge->drop_column('blog', 'author_first_name');
    $this->dbforge->drop_column('blog', 'author_last_name');
  }
}

Looking at the above two migration examples, switching between versions is very simple. The simplicity comes from the creation of well thought out tear up (creation) and tear down (deletion) methods.

Don't forget to check on backend mysql table migrations to ensure that which version is currently active.

P:S: You can only have one migration file per version. Currently you have to create all the tables in one single file. The issue can arise when multiple developers maintaining different parts of the application.

Ref: http://robertsettle.com/2012/07/codeigniter-migrations/, http://eddmann.com/posts/database-migrations-with-codeigniter/, http://stevenlu.com/posts/2012/04/28/using-codeigniter-migrations/, https://ellislab.com/codeigniter/user-guide/libraries/migration.html