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.
We will cover the following topics in this tutorial
This tutorial assumes you are familiar with;
The following are some of the advantages of using migrations to maintain your database.
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.
The following are some of the advantages of database seeding
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/seedsHow 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 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.
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 packagescomposer.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.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,
autoload.php was created by composer and it will handle loading Faker for us.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 librarypublic 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 consolepublic function migration($name) {…} creates a migration filepublic 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 filepublic function seed($name) {…} executes a seed fileprotected function make_migration_file($name) {…} is a protected function used internally by the class to create the migration file boiler plate codeprotected function make_seed_file($name) {…} is a protected function used internally by the class to create the seeder file boiler plate code.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
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
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',
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;
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 directory20150930141517_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 systemOpen /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 classpublic 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.
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,
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
Use the command line to generate seed files for categories and products then run the seeds to generate dummy data.
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.
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.
© 2014 to 2019 KodeBlog