Introduction

Database migrations are used to create and maintain databases programmatically. CodeIgniter provides two main classes for this task namely; Database Forge and Migrations. Database Forge is responsible for generating the SQL statements that manipulate the database. Migrations are responsible for managing single objects in the database such as a table. At the end of this tutorial, we will create migration files for a CodeIgniter Admin panel tutorial project. If you have not read the previous tutorials and you are interested in the CodeIgniter Admin panel then I recommend you read the previous tutorials.

Topics to be covered

We will cover the following topics in this tutorial

• Tutorial pre-requisites
• Why bother about migrations?
• What is database seeding?
• Why bother about database seeding?
• Migrations and seeds storage conversions
• Faker PHP Library
• CodeIgniter Command Line Interface (CLI)
• CodeIgniter Migration
• CodeIgniter Database seeding
• Tutorial Exercise

Tutorial Pre-requisites

This tutorial assumes you are familiar with;

• PHP basics and Object-oriented programming
• CodeIgniter Basics
• You have a web server, PHP and MySQL already configured and running
• You have a cool IDE i.e. NetBeans IDE, Aptana Studio etc.
• You have access to a command line/terminal that you can use to run commands
• You have composer. Composer is a PHP dependencies manager.

Why bother about migrations?

The following are some of the advantages of using migrations to maintain your database.

1. You don’t need to write SQL statements – why is this an advantage? Your clients may wish to work with a specific database i.e. MySQL, SQL Server etc. Migrations are written in pure PHP code. The framework will translate the code into vendor specific SQL statements. This means you don’t have to worry about vendor specific SQL syntax
2. Database Version control – migrations allow you to upgrade and downgrade your database. If the new database changes break something, you can easily roll-back the changes
3. Easy to share database changes – let’s assume more than one developer is working on the code and each has a database on their local machine for testing purposes, changes can easily be accommodated by sharing the migration files and executing them against each developer’s database
4. Eternal database structure backup – Have you ever corrupted your MySQL database and failed to restore the database? To make things even sweeter you don’t have the latest database backup. Migrations will come in handy in such situations. All you have to do is run the migration files and you will have your database.
5. Increased productivity – Migrations are written from within the IDE. You don’t need to have two different programs running at the same time and switching between the two programs from time to time.

What is database seeding?

Database seeding is a term used to describe the process of adding dummy records to the database for testing purposes. CodeIgniter does not have support database seeding out of the box and the concept may be strange to developers who are not familiar with other frameworks such as Ruby on Rails or Laravel.

Why bother about database seeding?

The following are some of the advantages of database seeding

1. Increased productivity – so you have the database, what’s next? You start adding test records one by one. database seeding does this for you within seconds
2. Database load testing made easy – with database seeding, you can add millions of records to the database easily to test how the database will perform when it has a lot of data

Migrations and seeds storage conversions

By default, CodeIgniter looks for migrations in the /application/migration directory.

Most frameworks that have embraced the concept of migrations and seeds usually place them in a database directory with migration and seeds sub folders.

Our application will also adopt the conversion and store migration and seeds in /application/database/migrations and /application/database/seeds directories respectively.

Create the following directories in your project

• /application/database/migrations
• /application/database/seeds

How about a bit of some conversions over configurations.

By default, the boilerplate code for migrations will use the file name as the table name. So if you create a migration file Users, the corresponding table name in the migration boilerplate code will be users. Note: we will capitalize the first letter of the file but the database table name will be in lower case.

Faker PHP library

Faker is a PHP library that generates fake data. It is very useful when working with database seeds. You can use it to generate all sorts of fake data i.e. names, addresses, contact numbers etc. We will use Faker for database seeding purposes when we do not want to provide data ourselves.

Integrating Faker PHP Library into CodeIgniter

We will use composer to install Faker. Composer is a PHP dependencies manager. You can download composer and get more information from the official composer website. I am assuming you have already installed composer and its working fine. I am using windows 8.1 and XAMPP installed to drive C. The knowledge still applies to other operating systems

Open the command prompt. For me I have Cygwin installed and I am using NetBeans IDE so I am using the terminal within NetBeans IDE. This makes me insanely productive as I work with a single program only.

Run the following command to navigate to the project root.

cd "C:\xampp\htdocs\ci-my-admin"

Run the following composer command to install Fake library

composer require fzaninotto/faker

When composer finishes the installation, it will generate the following files

• composer.json this file contains dependencies information that composer uses to download and install packages
• composer.lock – this file should not be edited. It is used by composer to keep track of what has been installed.
• vendor directory – this is the directory where composer installs packages.

Loading Faker Library in CodeIgniter

We will now load all the required classes for Fake

Open /index.php file in the root of the project

Just before the line that loads core/CodeIgniter.php, add the following line

/*
 * --
 * LOAD THE COMPOSER AUTOLOAD FILE
 * --
 */
include_once './vendor/autoload.php';

HERE,

• The above code loads the composer auto load file. The file autoload.php was created by composer and it will handle loading Faker for us.

CodeIgniter Command Line Interface (CLI)

Most frameworks have built-in command lines and almost all users of these frameworks have to interact with the command line. CodeIgniter has the base for the command line interface but it’s not implemented by default.

Frameworks that have fully embraced the concepts of migrations and seeding come with command line commands that automate the generation of migrations and seeders and for executing the files. CodeIgniter supports migrations and these are usually executed via the web browser.

In this tutorial, we will create a command line tool that will mimic what other frameworks do. We will be able to automate the process of creating database migrations and seeders. As if this isn’t sinful enough, we will also be able to run migrations and database seeds from the command line.

I am assuming you have the tutorial files from the previous tutorial where we converted an HTML template into a CodeIgniter application.

Our command line tool will have the following functions

S/N	FUNCTION	DESCRIPTION	PARAMETER(S)	USAGE
1	__construct	Class initialization function	None	None
2	message	Displays hello world in the console	Name	php index.php tools message "Rodrick Kazembe"
3	help	Displays the available commands and what tasks they perform	None	php index.php tools help
4	migration	Creates a new migration file	Migration file_name	php index.php tools migration "users"
5	migrate	Executes all migration files that haven’t been executed yet	None	php index.php tools migrate"
6	seeder	Create a new seed file	Seed file name	php index.php tools seeder "UsersSeeder"
7	seed	Executes a specific seed file	Seed file name	php index.php tools seed "UsersSeeder"

Create a new file tools.php in /application/controllers/tools.php

Add the following code

<?php

class Tools extends CI_Controller {

    public function __construct() {
        parent::__construct();

        // can only be called from the command line
        if (!$this->input->is_cli_request()) {
            exit('Direct access is not allowed. This is a command line tool, use the terminal');
        }

        $this->load->dbforge();

        // initiate faker
        $this->faker = Faker\Factory::create();
    }

    public function message($to = 'World') {
        echo "Hello {$to}!" . PHP_EOL;
    }

    public function help() {
        $result = "The following are the available command line interface commands\n\n";
        $result .= "php index.php tools migration \"file_name\"         Create new migration file\n";
        $result .= "php index.php tools migrate [\"version_number\"]    Run all migrations. The version number is optional.\n";
        $result .= "php index.php tools seeder \"file_name\"            Creates a new seed file.\n";
        $result .= "php index.php tools seed \"file_name\"              Run the specified seed file.\n";

        echo $result . PHP_EOL;
    }

    public function migration($name) {
        $this->make_migration_file($name);
    }

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

        if ($version != null) {
            if ($this->migration->version($version) === FALSE) {
                show_error($this->migration->error_string());
            } else {
                echo "Migrations run successfully" . PHP_EOL;
            }

            return;
        }

        if ($this->migration->latest() === FALSE) {
            show_error($this->migration->error_string());
        } else {
            echo "Migrations run successfully" . PHP_EOL;
        }
    }

    public function seeder($name) {
        $this->make_seed_file($name);
    }

    public function seed($name) {
        $seeder = new Seeder();

        $seeder->call($name);
    }

    protected function make_migration_file($name) {
        $date = new DateTime();
        $timestamp = $date->format('YmdHis');

        $table_name = strtolower($name);

        $path = APPPATH . "database/migrations/$timestamp" . "_" . "$name.php";

        $my_migration = fopen($path, "w") or die("Unable to create migration file!");

        $migration_template = "<?php

class Migration_$name extends CI_Migration {

    public function up() {
        \$this->dbforge->add_field(array(
            'id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'auto_increment' => TRUE
            )
        ));
        \$this->dbforge->add_key('id', TRUE);
        \$this->dbforge->create_table('$table_name');
    }

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

}";

        fwrite($my_migration, $migration_template);

        fclose($my_migration);

        echo "$path migration has successfully been created." . PHP_EOL;
    }

    protected function make_seed_file($name) {
        $path = APPPATH . "database/seeds/$name.php";

        $my_seed = fopen($path, "w") or die("Unable to create seed file!");

        $seed_template = "<?php

class $name extends Seeder {

    private \$table = 'users';

    public function run() {
        \$this->db->truncate(\$this->table);

        //seed records manually
        \$data = [
            'user_name' => 'admin',
            'password' => '9871'
        ];
        \$this->db->insert(\$this->table, \$data);

        //seed many records using faker
        \$limit = 33;
        echo \"seeding \$limit user accounts\";

        for (\$i = 0; \$i < \$limit; \$i++) {
            echo \".\";

            \$data = array(
                'user_name' => \$this->faker->unique()->userName,
                'password' => '1234',
            );

            \$this->db->insert(\$this->table, \$data);
        }

        echo PHP_EOL;
    }
}
";

        fwrite($my_seed, $seed_template);

        fclose($my_seed);

        echo "$path seeder has successfully been created." . PHP_EOL;
    }

}

HERE,

• public function __construct(){…} defines the constructor method. if (!$this->input->is_cli_request()) {…} checks if the request is not from the command line. If it’s not from the command line then executed stops and a message that says Direct access is not allowed. This is a command line tool, use the terminal is returned.
• $this->load->dbforge(); loads the forge database class. It is a specialized class that handles creation of databases, tables and altering tables by adding, modifying or dropping columns.
• $this->faker = Faker\Factory::create(); creates an instance variable of Faker PHP library
• public function message($to = 'World') {…} displays a simple message to the console. It’s not needed actually but we still included it.
• public function help() {…} displays the help menu in the console
• public function migration($name) {…} creates a migration file
• public function migrate($version = null) {…} runs all pending migration files. The migration file number is optional. It’s useful for rolling back migrations.
• public function seeder($name) {…} creates a seeder file
• public function seed($name) {…} executes a seed file
• protected function make_migration_file($name) {…} is a protected function used internally by the class to create the migration file boiler plate code
• protected function make_seed_file($name) {…} is a protected function used internally by the class to create the seeder file boiler plate code.

Testing the command line tool

Open the command prompt / terminal and browser to the project root

Run the following commands to display a simple message and help menu

php index.php tools message "Rodrick Kazembe"
php index.php tools help

You will get the following results

CodeIgniter Migration

Now that we have created a cool command line tool that automates generating boiler plate code, let’s get our hands dirty. Remember our admin panel has three categories, brands, categories and products. We will generate migration files for these tables

Database Configuration

It’s possible to create a database using CodeIgniter but we will create one manually.

Create a database in MySQL and name it ci_my_admin

Open /application/config/database.php

Set a valid username, password and database as shown below

'hostname' => 'localhost',
'username' => 'root',
'password' => 'melody',
'database' => 'ci_my_admin', 

CodeIgniter Migration Configuration

The default migration directory in CodeIgniter is /application/migrations. We need to tell CodeIgniter to look in /application/database/migrations.

Open /application/config/migration.php

Locate the following line

$config['migration_path'] = APPPATH . 'migrations/';

Update it to the following

$config['migration_path'] = APPPATH.'database/migrations/';

Migrations are disabled by default in CodeIgniter. We will need to enable migrations

Locate the following line

$config['migration_enabled'] = FALSE;

Update it to the following line

$config['migration_enabled'] = TRUE;

Generating migration file boiler plate code using the command line

Run the following command in the terminal

php index.php tools migration Brands

You will get the following message

...application/database/migrations/20150930141517_Brands.php migration has successfully been created.

HERE,

• …/ will be the full path to your application directory
• 20150930141517_Brands.php is the migration file name. It has a timestamp at the beginning. CodeIgniter will use the timestamp to identify migration files that have not yet been run. Note: the time stamp will be different for you as it picks the current timestamp from your system

Open /application/database/migrations/20150930141517_Brands.php

You will get the following

<?php

class Migration_Brands extends CI_Migration {

    public function up() {
        $this->dbforge->add_field(array(
            'id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'auto_increment' => TRUE
            )
        ));
        $this->dbforge->add_key('id', TRUE);
        $this->dbforge->create_table('brands');
    }

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

}

HERE,

• class Migration_Brands extends CI_Migration {…} the migration file extends the CI_Migration class
• public function up() {…} defines the function that is executed when running the migration. $this->dbforge->add_field(…) uses the forge class to add fields to the database. $this->dbforge->add_key('id', TRUE); defines a primary key field id. You can change it to a different field name. I prefer conventions over configurations so I will leave it as id. $this->dbforge->create_table('brands'); creates a table named brands. Our command line tool used the migration name to guess the table name. Again, this is a convention over configuration practice that speeds up development. You can change the table name if you want to.
• public function down() {…} is the function that is executed when rolling back the database migration.

Update 20150930141517_Brands.php to the following

<?php

class Migration_Brands extends CI_Migration {

    public function up() {
        $this->dbforge->add_field(array(
            'id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'auto_increment' => TRUE
            ),
            'description' => array(
                'type' => 'VARCHAR',
                'constraint' => 100
            )
            ,
            'created_from_ip' => array(
                'type' => 'VARCHAR',
                'constraint' => 100
            ),
            'updated_from_ip' => array(
                'type' => 'VARCHAR',
                'constraint' => 100
            )
            ,
            'date_created' => array(
                'type' => 'DATETIME'
            ),
            'date_updated' => array(
                'type' => 'DATETIME'
            )
        ));
        $this->dbforge->add_key('id', TRUE);
        $this->dbforge->create_table('brands');
    }

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

Run the following commands to generate the migration files for categories and products

php index.php tools migration Categories
php index.php tools migration Products

20150930143344_Categories.php

Update 20150930143344_Categories.php with the following code <?php

class Migration_Categories extends CI_Migration {

    public function up() {
        $this->dbforge->add_field(array(
            'id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'auto_increment' => TRUE
            ),
            'description' => array(
                'type' => 'VARCHAR',
                'constraint' => 100
            )
            ,
            'created_from_ip' => array(
                'type' => 'VARCHAR',
                'constraint' => 100
            ),
            'updated_from_ip' => array(
                'type' => 'VARCHAR',
                'constraint' => 100
            )
            ,
            'date_created' => array(
                'type' => 'DATETIME'
            ),
            'date_updated' => array(
                'type' => 'DATETIME'
            )
        ));
        $this->dbforge->add_key('id', TRUE);
        $this->dbforge->create_table('categories');
    }

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

20150930143352_Products.php

Update 20150930143352_Products.php with the following code

<?php

class Migration_Products extends CI_Migration {

    public function up() {
        $this->dbforge->add_field(array(
            'id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'auto_increment' => TRUE
            ),
            'name' => array(
                'type' => 'VARCHAR',
                'constraint' => 100
            )
            ,
            'category_id' => array(
                'type' => 'INT',
                'constraint' => 11
            )
            ,
            'brand_id' => array(
                'type' => 'INT',
                'constraint' => 11
            )
            ,
            'model' => array(
                'type' => 'VARCHAR',
                'constraint' => 150
            )
            ,
            'tag_line' => array(
                'type' => 'VARCHAR',
                'constraint' => 250
            )
            ,
            'features' => array(
                'type' => 'VARCHAR',
                'constraint' => 350
            )
            ,
            'price' => array(
                'type' => 'INT',
                'constraint' => 11
            )
            ,
            'qty_at_hand' => array(
                'type' => 'INT',
                'constraint' => 11
            )
            ,
            'editorial_reviews' => array(
                'type' => 'VARCHAR',
                'constraint' => 750
            )
            ,
            'created_from_ip' => array(
                'type' => 'VARCHAR',
                'constraint' => 100
            ),
            'updated_from_ip' => array(
                'type' => 'VARCHAR',
                'constraint' => 100
            )
            ,
            'date_created' => array(
                'type' => 'DATETIME'
            ),
            'date_updated' => array(
                'type' => 'DATETIME'
            )
        ));
        $this->dbforge->add_key('id', TRUE);
        $this->dbforge->create_table('products');
    }

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

Run the following command to run the migrations

php index.php tools migrate

You will get the following message

Migrations run successfully

Check your database in phpMyAdmin or whatever tool you use

You will be able to see the following databases

Congratulations, you just played with the command line to create your database using migrations. Note: migrations table was automatically created for us. It contains the latest migration timestamp.

Migrations plus a terminal will insanely make you productive as a developer.

CodeIgniter Database seeding

Now that we have our database, what’s next? We will need to add some dummy records to it for testing purposes as we develop our admin panel. We will need a seeder class developed by Kenji Suzuki. You can download it from https://github.com/kenjis/codeigniter-tettei-apps/blob/develop/application/libraries/ Seeder.php. save it in application/database/Seeder.php

Run the following command to generate a seeder for Brands table

php index.php tools seeder BrandsSeeder

You will get the following message

...application/database/seeds/BrandsSeeder.php seeder has succesfully been created.

Open ...application/database/seeds/BrandsSeeder.php and update it with the following code

<?php

class BrandsSeeder extends Seeder {

    private $table = 'brands';

    public function run() {
        $this->db->truncate($this->table);

        //seed many records using faker
        $limit = 13;
        echo "seeding $limit brands";

        for ($i = 0; $i < $limit; $i++) {
            echo ".";

            $data = array(
                'description' => $this->faker->unique()->word,
                'created_from_ip' => $this->faker->ipv4,
                'updated_from_ip' => $this->faker->ipv4,
                'date_created' => $this->faker->date($format = 'Y-m-d'),
                'date_updated' => $this->faker->date($format = 'Y-m-d'),
            );

            $this->db->insert($this->table, $data);
        }

        echo PHP_EOL;
    }
}

HERE,

• The above code uses a for loop to generate 13 records with data generated using faker.

Run the following command to run BrandsSeeder

php index.php tools seed BrandsSeeder

You will get the following message

seeding 13 brands..............

Open your database and check the brands data.

You will get results similar to the following

Tutorial exercise

Use the command line to generate seed files for categories and products then run the seeds to generate dummy data.

Summary

In this tutorial, we learnt about the advantages of migrations and how you can take full advantage of migrations to develop database powered applications rapidly.

What’s next?

The next tutorial will be on CodeIgniter Models. The models will interact with the database and retrieve data. We will extend the CodeIgniter Model and add one or two features that will make it awesome.

If you found this tutorial useful, support us by using the social media buttons to like and share the tutorial. If you didn’t find it useful, please use the comments section below to let us know how we can do better next time.