The migrations in CodeIgniter or how to have a “Git” for your database (part 1 of 3)

(created at: July 22, 2015; last update: July 29, 2015)

Well… this title is a bit misleading, isn’t it? Let’s just start by talking about what is a Git.

Git is some sort of history tracker for your code. That is, whenever you modify something in your code, Git keeps a track of how the code looked before your update, and how it looks after the update. By using it, you and those that work with you can find out how the coding evolved and return to previous versions if something went wrong or want to create a new branch.

The same happens with the migrations, these being related to the database operations. Migrations help you and your team mates keep track of the changes you make to the database tables.

So let’s get to work and see how we can use them.

What we want to do

Before starting to use migrations we first need to know what we want to create with them. As an example, let’s suppose we want to create a “users” table.

First, the configuration

In order to work with migrations we first need to set up the configuration. I will assume that you already have the database enabled.

After that open application/config/migration.php.

In there you will see the $config[‘migration_enabled’]. Set it to TRUE. For security reasons, it is good to leave it back to FALSE when you no longer do migrations.

If you have CodeIgniter 3 (and I hope you do, because most of my tutorials use CI3), you will also see $config[‘migration_type’]. I usually set this to ‘timestamp’, because this way I can see at what time a change was made. Don’t worry the ‘timestamp’ in this particular context is not really timestamp but a datetime format (‘YYYYMMDDHHIISS’).

The migration history (actually, the last migration) is kept inside a database table. By default the table is named ‘migrations’. You can change the name by modifying the value of $config[‘migration_table’].

$config[‘migration_auto_latest’] tells the framework to update the migration to the latest version.

You also have the possibility to set an initial migration point. If you know for sure that you won’t return beyond a migration version anymore, you can set the version at $config[‘migration_version’]. In our case the timestamp.

You also have $config[‘migration_path’] which tells CodeIgniter where to look for the migration files. If you don’t have a directory called “migrations” inside your application directory, create it now.

Our first migration

OK, so we want to create a table called “users” that has the following fields: “id”,”username”, “email”,”password”.

To do this we create a file named “20150722101900_create_users_table.php” inside “application/migrations”. “Why would we give it such a long name?” you might ask. Well… the first part is the timestamp that we’ve talked about earlier, while the second part is a short explanation of what the migration does. Isn’t this neat and clean?

Now, we open this file and right the basic start for the migration:

<?php

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

class Migration_Create_users_table extends CI_Migration {

  public function up()
  {
  
  }

  public function down()
  {

  }
}

As you can see, the class name must start with “Migration_” and be followed by the string that we’ve attached to the timestamp, starting with uppercase “Create_users_table”.

Every migration class has two methods: an up() method and a down() method. The up() method tells our application what to create when the migration is run. The down() method tells the application what to do if we want to undo this particular migration. In our case, when we run the migration we want that the “users” table to be created. In case we want to undo the migration, the down() function should delete the “users” table.

The Forge class

The Database Forge Class is the class that CodeIgniter uses by default to manage database operations (http://www.codeigniter.com/user_guide/database/forge.html). By using this class’ methods we can manipulate our tables in the migrations. So let’s create a constructor inside our migration to load the class:

<?php

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

class Migration_Create_users_table extends CI_Migration {

  public function __construct()
  {
    parent::__construct();
    $this->load->dbforge();
  }

  public function up()
  {

  }

  public function down()
  {

  }
}

Now let’s start with the up() method.

The up() method

As I said before, the up() method is used when we run the migration. So let’s tell the method what we want it to do.

First of all we will tell it what fields we want to be created, by passing their schema in an array

So, we want an “id” that will be an int autoincremented, an username that will be a VARCHAR 60, an email that will be a VARCHAR 255, and a password that will be VARCHAR 255.

In “forge language”, that will translate into an array like the one below:

$fields = array(
  'id' => array(
    'type' => 'INT',
    'constraint' => 11,
    'unsigned' => TRUE,
    'auto_increment' => TRUE
  ),
  'username' => array(
    'type' => 'VARCHAR',
    'constraint' => 60
  ),
  'email' => array(
    'type' => 'VARCHAR',
    'constraint' => 255
  ),
  'password' => array(
    'type' => 'VARCHAR',
    'constraint' => 255
  )
);

Self explaining, right?

Now we simply pass the dbforge the fields by using the add_field() method:

$this->dbforge->add_field($fields);

We also tell the dbforge that we want the id to be used as primary key field:

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

If we also want to create index keys we use the same add_key() method but without passing the second parameter as TRUE (this parameter tells the dbforge class that we want it to be PRIMARY, and by default is set to FALSE).

$this->dbforge->add_key('username');

Now we only have to tell the dbforge to create the table:

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

The second paramater, if set to TRUE will do a “IF NOT EXISTS” condition before creating the table. If we don’t want to verify the existence of the table we simply don’t pass a second parameter.

The down() method

The down() method simply undo what the up() method did. In our case it will delete the table:

$this->dbforge->drop_table('users', TRUE);

Now let’s see our Migration file again:

<?php

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

class Migration_Create_users_table extends CI_Migration {

  public function __construct()
  {
    parent::__construct();
    $this->load->dbforge();
  }

  public function up()
  {
    $fields = array(
      'id' => array(
        'type' => 'INT',
        'constraint' => 11,
        'unsigned' => TRUE,
        'auto_increment' => TRUE
      ),
      'username' => array(
        'type' => 'VARCHAR',
        'constraint' => 60
      ),
      'email' => array(
        'type' => 'VARCHAR',
        'constraint' => 255
      ),
      'password' => array(
        'type' => 'VARCHAR',
        'constraint' => 255
      )
    );
    $this->dbforge->add_field($fields);
    $this->dbforge->add_key('id',TRUE);
    $this->dbforge->add_key('username');
    $this->dbforge->create_table('users',TRUE); 
  }

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

Cool… Now let’s move on to the migration…

The controller that calls the migration

Once we’ve created the migration file we must run it. To do this, we have to create a controller that will call the migration. So let’s make a new file named “Migrate.php” inside
“application/controllers”:

<?php

class Migrate extends CI_Controller
{

  public function index()
  {
    echo 'Hello'; 
  }

  public function do_migration($version = NULL)
  {
    $this->load->library('migration');

    if(isset($version) && ($this->migration->version($version) === FALSE))
    {
      show_error($this->migration->error_string());
    }

    elseif(is_null($version) && $this->migration->latest() === FALSE)
    {
      show_error($this->migration->error_string());
    }

    else
    {
      echo 'The migration has concluded successfully.';
    }
  }
  
  public function undo_migration($version = NULL)
  {
    $this->load->library('migration');
    $migrations = $this->migration->find_migrations();
    $migration_keys = array();
    foreach($migrations as $key => $migration)
    {
      $migration_keys[] = $key;
    }
    if(isset($version) && array_key_exists($version,$migrations) && $this->migration->version($version))
    {
      echo 'The migration was reset to the version: '.$version;
      exit;
    }
    elseif(isset($version) && !array_key_exists($version,$migrations))
    {
      echo 'The migration with version number '.$version.' doesn\'t exist.';
    }
    else
    {
      $penultimate = (sizeof($migration_keys)==1) ? 0 : $migration_keys[sizeof($migration_keys) - 2];
      if($this->migration->version($penultimate))
      {
        echo 'The migration has been rolled back successfully.';
        exit;
      }
      else
      {
        echo 'Couldn\'t roll back the migration.';
        exit;
      }
    }
  }

  public function reset_migration()
  {
    $this->load->library('migration');
    if($this->migration->current()!== FALSE)
    {
      echo 'The migration was reset to the version set in the config file.';
      return TRUE;
    }
    else
    {
      echo 'Couldn\'t reset migration.';
      show_error($this->migration->error_string());
      exit;
    }
  }
}

As you can see, we’ve created a method named do_migration(). This method can accept a parameter that in our case will represent the “timestamp”. If you pass it a timestamp, CodeIgniter will call that version of the migration. If the version called is before the current version CodeIgniter will undo all migrations after that version.

Also, I’ve created an undo_migration() method, and a reset_migration() method that will reset the migrations to the $config[‘migration_version’] set in the configuration file. These methods are actually components of my Matches CLI script, of which I will talk in the third part of this tutorial.

Now we visit the do_migration() method with our browser (I sure hope you know how to access a controller in CodeIgniter…). If everything went OK, you should see in your database a table named “users”.

Important note on security

As you can see this is a security hole, as anyone can access that controller from the internet. To avoid any complication I would advise you to enable the migrations only on the development environment (you can see here how to do it – Step 2: Set up the environments), or allow that controller to be run only by authenticated users.

In the next tutorials I will show you how to alter the tables by using migrations and how to use the Matches to simplify your work.

One final note

Please, PLEASE do read the fine manual on the CodeIgniter site. Is one of the best resources on the web:

– about Forge: http://www.codeigniter.com/user_guide/database/forge.html

– about Migrations: http://www.codeigniter.com/user_guide/libraries/migration.html