How to Implement Database Migration In CodeIgniter?
What is Migration?
Migration is a way for developers to alter a database managed in a structured and well-organized manner.
Migration detects and runs those SQL queries on the developer’s behalf without handing over SQL query files to other developers each time we make some changes to the database.
Migrations are used to create and maintain database queries programmatically.
CodeIgniter framework supports migrations. CodeIgniter mainly supports two classes for migrations; first Database Forge and second is Migrations.
Database Forge is responsible to generate SQL queries which manipulate a selected database. Migrations are responsible to manage the single object in the database.
Migrations are helpful when more than one developer is working on a single project. When a developer has modified or created a migration file for creating a table or alter queries in a database then the developers need to notify others to run the migration file.
Normally when a developer creates a table or modifies anything in the existing database table then developers need to send updated SQL files to run SQL Files who are working with the developers.
Why Migration is Needed?
Some below advantages are available for use migrations.
Let’s assume more than one developer is working on one project. All developers are working on own machine to develop functionalities. If one developer created a Migration file for modifications in the table, the developer will share a migration file with all other developers and developers execute the migration in own machine.
2. Maintain the database version:
Migrations are allowed to downgrade and upgrade the database version. So it’s easy to upgrade and downgrade the database version.
3. No need to write SQL Changes in the file:
If we did any changes in the database tables we need to write SQL queries in the file. Using Migration no need to write SQL queries, just need to execute the migration files and SQL changes are implemented in the database.
4. Database structure backup maintains if SQL file corrupted or failed to Restore the database:
Have the developer’s database SQL file corrupted or failed to restore the database? Sometimes to possible failed to restore the database. Developers have to run migration files and they will have his database.
Setting Up Migration In CodeIgniter
In order to work with migration, developers need to configure CodeIgniter. Currently, we assume that they have configured the application.
Now Open the application/config/migration.php file
In file, developers will see the $config[‘migration_enabled’] with value False. Set it TRUE. For Securities, reason set False, if no longer use Migration.
In file, developers will also see the $config[‘migration_type’] with set ‘timestamp’. Developers set two different migration types when they create migration.
Sequential: Sequential migration naming (001_add_user.php) Timestamp: Timestamp migration naming (20121031104401_add_user.php)
Note: Create a migration file as per the selected type in the migration file. If developers selected timestamp and created migration file with Sequential number, Migration file execute but a table will not create in the database.
Actually migration history maintains in a database table with the last migration. By default, the migration table is Migrations. A developer can change the migration table name. If developers want to modify the table name they can modify the $config[‘migration_table’] value. E.g. if developers want to change the migration table name ‘ Migrate’ then they need to set as below;
$config['migration_table'] = 'Migrate';$config[‘migration_auto_latest’] defines the framework to update the migration to the latest version.
Also, developers can modify the migration file path as they want. If developers want to modify the migration path, then they need to modify the $config[‘migration_path’] parameter. By default, $config[‘migration_path’] Path is APPPATH.’migrations/’.
Create a folder called migrations in the application/ folder.
Create Migration With Example
We will create table ‘users’ that contains the following fields: ‘sr_no’,’name’,’email’
I have set up below values in application/config/migration.php file
$config['migration_enabled'] = TRUE;
$config['migration_type'] = 'sequential';
$config['migration_table'] = 'Migrations';
$config['migration_path'] = APPPATH.'migrations/';As above mentions migration type ‘sequential’, we create “001_create_user.php” file inside the “application/migrations”.
In the migrations folder, we can create our database changes files. Let’s we want to create the ‘users’ table. So create a file with the name ‘001_create_user.php’ and open file write basic code for migrations.
defined('BASEPATH') OR exit('No direct script access allowed');
class Migration_create_users extends CI_Migration
{
public function up()
{
}
public function down()
{
}
?>As developers can see, the class name must start with ‘Migration_’ and attach with string ‘create_user’.
Every migration has two class methods: an up() method and a down() method. The up() method to tell our application want to create when migration is run. The down() method to tell our application if we want to undo the particular migration.
In our case, if we run migration the up() method to create the ‘users’ table. If we want to undo the migration, down() method delete the ‘users’ table for us.
Forge Class
The Database Forge class used to manage the database operations such like create table, delete table, rename the table field name. Now we create the construct method inside the migration file.
defined('BASEPATH') OR exit('No direct script access allowed');
class Migration_create_users extends CI_Migration
{
public function __construct()
{
parent::__construct();
$this->load->dbforge();
}
public function up()
{
}
public function down()
{
}
?>up() method
As above mentioned before, the up() method used when we want to run migration. Now we define code in up() method.
defined('BASEPATH') OR exit('No direct script access allowed');
class Migration_create_users extends CI_Migration
{
public function __construct()
{
parent::__construct();
$this->load->dbforge();
}
public function up()
{
$fields = array
(
'sr_no' => array(
'type' => 'INT',
'constraint' => 11,
'auto_increment' => TRUE
),
'name' => array(
'type' => 'VARCHAR',
'constraint' => 60
),
'email' => array(
'type' => 'VARCHAR',
'constraint' => 255
)
);
}
public function down()
{
}
?>As below, now we simply assign dbforge the field by add_field() method.
$this->dbforge->add_field($fields);As we want to ‘sr_no’ as primary key, So we want to assign field by add_key() method.
this->dbforge->add_key(‘sr_no’,TRUE);As we want to create table ‘users; , So we want to assign dbforge the field by create_table() method.
$this->dbforge->create_table('users',TRUE); down() method
As above mentioned before, the down() method used when we want to undo the migration. Now we define code in down() method.
$this->dbforge->drop_table('users', TRUE);Now we will see the migration file
defined('BASEPATH') OR exit('No direct script access allowed');
class Migration_create_users extends CI_Migration
{
public function __construct()
{
parent::__construct();
$this->load->dbforge();
}
public function up()
{
$fields = array
(
'sr_no' => array(
'type' => 'INT',
'constraint' => 11,
'auto_increment' => TRUE
),
'name' => array(
'type' => 'VARCHAR',
'constraint' => 60
),
'email' => array(
'type' => 'VARCHAR',
'constraint' => 255
)
);
$this->dbforge->add_field($fields);
this->dbforge->add_key(‘sr_no’,TRUE);
$this->dbforge->create_table('users',TRUE);
}
public function down()
{
$this->dbforge->drop_table('users', TRUE);
}
?>Controller Class
We have created the migration file. Now for executing the migration file, we need to create a controller file in ‘application/controllers’ folder. So create file with name ‘Migration.php’
class Migrate extends CI_Controller
{
public function index()
{
echo ‘Controller file index method run.’;
}
public function CreateMigration($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 file has executed successfully.’;
}
}
public function undoMigration($version = NULL)
{
$this->load->library(‘migration’);
$migrations = $this->migration->find_migrations();
$migrationKeys = array();
foreach($migrations as $key => $migration)
{
$migrationKeys[] = $key;
}
if(isset($version) && array_key_exists($version,$migrations) && $this->migration->version($version))
{
echo ‘The migration was undo’;
exit;
}
elseif(isset($version) && !array_key_exists($version,$migrations))
{
echo ‘The migration with selected version doesn’t exist.’;
}
else
{
$penultimate = (sizeof($migrationKeys)==1) ? 0 : $migrationKeys[sizeof($migrationKeys) – 2];
if($this->migration->version($penultimate))
{
echo ‘The migration has been reverted successfully.’;
exit;
}
else
{
echo ‘Couldn\’t roll back the migration.’;
exit;
}
}
}
public function resetMigration()
{
$this->load->library(‘migration’);
if($this->migration->current()!== FALSE)
{
echo ‘The migration was revert to the version set in the config file.’;
return TRUE;
}
else
{
echo ‘Couldn\’t reset migration.’;
show_error($this->migration->error_string());
exit;
}
}
}As above, developers can see we have created three methods : CreateMigration(), undoMigration() and resetMigration(). Using CreateMigration() method, accept the parameter that in our case we have set the ‘sequential’. If developers pass the ‘sequential’ number, Codeignter will call that version of the file in migration. Using undoMigration() method and resetMigration() method that will reset migration to the set in file $config[‘migration_version’].
Route File
We have created the migration and controller files to executing the migration. Now for define the route path in route.php file. “application/config”
$route['MigrationIndex'] = 'Migrate/index';
$route['createMigration'] = 'Migrate/CreateMigration;
$route['undoMigration'] = 'Migrate/undoMigration;
$route['resetMigration'] = 'Migrate/resetMigration;If developers execute the MigrationIndex in the browser output will show as below
Controller file index method run
If developers execute the createMigration in the browser output will show as below and two tables will create in database: ‘Migrations’ and ‘users
The migration file has executed successfully.
If developers execute the undoMigration in the browser output will show as below and delete the ‘users’ table from the database.
The migration was undo.
Are multiple developers are working on your CodeIgniter project and facing issues while altering the database? we have a solution for you as Migration functionality in the CodeIgniter project. We have expertise in implementing Migration function, contact our expert OneClick team.
