The migrations in CodeIgniter or how to have a “Git” for your database (part 2 of 3: Alter tables with Migrations in CodeIgniter)

^{(created at: July 23, 2015; last update: July 23, 2015)}
Well… you got here. So I will be assuming that you’ve already followed the first episode of this tutorial (http://avenir.ro/the-migrations-in-codeigniter-or-how-to-have-a-git-for-your-database/) and all went well up until the end. If something went wrong now is the time to tell me (in a comment to to previous tutorial, please).

Now we will go a bit deeper… So… By referring to what we did in the previous tutorial (a table named “users” that has “id”, “username”, “email”, and “password” as fields), we will try to see how we can alter the table the way we want to.

Let’s pretend that we want to add another column to the table: “name”. Don’t jump to the idea that we should open the migration file we’ve written in the previous tutorial. We won’t. That’s the idea with the “git” part in our title. We want to have a history of our changes. If we would have modified what we’ve just created where would the history be?

So we will create another migration file, let’s name it “add_name_field_to_users_table”. Remember the “timestamp” we’ve talked about in the previous tutorial? We need to append that to our file name. Also, make sure that the “timestamp” represents a date and time that are after the timestamp of the migration file that creates the table, as the migrations are made in the order of the timestamps. So let’s create a file named “20150724111200_add_name_field_to_users_table.php” (wow… that’s a loong name, isn’t it?… if you find a better name that will help you understand what is done in the migration file, go ahead an name it the way you want).

Now let’s see what we can write in there. We start with the same template:

<?php

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

class Migration_Add_name_field_to_users_table extends CI_Migration {

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

  public function up()
  {

  }

  public function down()
  {

  }
}

On the up() method we mention the fields we want to create in a $fields array:

$fields = array(
  'name' => array(
    'type' => 'VARCHAR',
    'constraint' => 100
  )
);

Now we only have to call the dbforge in order to alter the table:

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

And that’s it. But wait, why don’t we put the column just after the “username” column and not at the end?

We can do this by changing the $fields array and adding a key named “after” with the name of the column as value:

$fields = array(
  'name' => array(
    'type' => 'VARCHAR',
    'constraint' => 100,
    'after' => 'username'
  )
);

Take note that “after” and “before” work only with a My_SQL database.

Now that we’ve finished with the up() method, we move on to the down() method. We only have to ask the dbforge class to remove the column:

$this->dbforge->drop_column('users', 'name');

Now let’s look at the migration file again:

<?php

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

class Migration_Add_name_field_to_users_table extends CI_Migration {

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

  public function up()
  {
    $fields = array(
      'name' => array(
        'type' => 'VARCHAR',
        'constraint' => 100,
        'after' => 'username'
      )
    ); 
    $this->dbforge->add_column('users', $fields); 
  }

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

Cool, now just go to the controller method do_migration of the Migrate controller (assuming you’ve created it with me in the previous tutorial…), and the new column should have been created if everything went ok.

But… Oups…

We just remembered that we don’t want to do a “name” column, but two columns: “first_name” and “last_name”. So why not rename the “name” column to “first_name” and add the “last_name” column?

We create a file named: 20150724122000_rename_name_field_add_last_name_in_users_table.php (this is like the contest for the longest file names…)

To change the definition of the columns we simply call the modify_column() method of the dbforge class, and pass it the table name and the columns we want to change. To create the new definitions we must use an array that has as keys the name of the columns and an array as the value with the new definitions. So, our array would look like this:

$fields = array(
  'name' => array(
    'name' => 'first_name'
  );
);

$this->dbforge->modify_column('users', $fields);

OK… sorry for the fact that the column name is “name”… That will turn a lot of heads…

Now we only have to create one more column:

$fields = array(
  'last_name' => array(
    'type' => 'VARCHAR',
    'constraint' => 100,
    'after' => 'first_name'
  )
);

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

On the down() method we do the reverse:

<?php

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

class Migration_Rename_name_field_add_last_name_in_users_table extends CI_Migration {

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

  public function up()
  { 

    $fields = array(
      'name' => array(
        'name' => 'first_name'
      )
    );
    $this->dbforge->modify_column('users', $fields);

    $fields = array(
      'last_name' => array(
        'type' => 'VARCHAR',
        'constraint' => 100,
        'after' => 'first_name'
      )
    );
    $this->dbforge->add_column('users', $fields);
  }

  public function down()
  {
    $fields = array(
      'first_name' => array(
        'name' => 'name'
      )
    );
    $this->dbforge->modify_column('users', $fields);
    $this->dbforge->drop_column('users', 'last_name');
  }
}

I hope you’ve got the taste of migrations. But, as practice makes perfect, it’s time to use what you’ve learned. So go ahead and try using the migrations. I can’t stress it enough, DO READ THE FINE MANUAL on the CodeIgniter site:

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

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

In the next tutorial, I’ll show you a faster way to work with migrations… Hold tight.