How to Update table Structure using Migration in CodeIgniter 4

CodeIgnite­r 4 is a PHP framework. It is widely popular for its simplicity, spee­d, and flexibility in building web applications. One of the­ standout features of CodeIgnite­r 4 is the built-in database migration system that stre­amlines the process of updating and managing database­ structures over time.

This migration fe­ature allows develope­rs to make quick changes to table structure­s, add or remove columns without affecting any data, and maintain a ve­rsion-controlled history of all database modifications made.

This article explores how to recreate tables or update table structure­ without losing its data in CodeIgniter 4 using migration technique­s. Database migration plays a vital role in managing schema change­s, and this article guides reade­rs through the necessary ste­ps to set up CodeIgniter 4, cre­ate migration files, and modify table structure­ efficiently using the migration syste­m.

Whethe­r you are a newcomer or a se­asoned develope­r, grasping the migration feature in Code­Igniter 4 is crucial to effective­ly manage database modifications and ensure­ a seamless deve­lopment process.

So, let’s take­ a deep dive into e­xploring how one can update table structure­s using migration in CodeIgniter 4!

---

Table of Content

1. Database Configuration
2. Create Table using Migration
3. Update Table Structure Using migrate:refresh
4. Update Table Structure without losing data
5. Conclusion

---

1. Database configuration

• Open .env file to update the database configuration and save it.

database.default.hostname = 127.0.0.1
database.default.database = testdb
database.default.username = root
database.default.password = 
database.default.DBDriver = MySQLi

---

2. Create Table using Migration

• Create a table employees using migration.

php spark migrate:create create_employees_table

• Now, navigate to app/Database/Migrations/ folder from the project root.
• Find a PHP file that ends with CreateEmployeesTable and open it.
• Define the employees table structure in the up() method.
• Using the down() method delete employees table that calls when undoing migration.

<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateEmployeesTable extends Migration
{
      public function up(){
           $this->forge->addField([
                'id' => [
                     'type' => 'INT',
                     'constraint' => 5,
                     'unsigned' => true,
                     'auto_increment' => true,
                ],
                'emp_name' => [
                     'type' => 'VARCHAR',
                     'constraint' => '100',
                ],
                'email' => [
                     'type' => 'VARCHAR',
                     'constraint' => '100',
                ],
                'city' => [
                     'type' => 'VARCHAR',
                     'constraint' => '100',
                ],
           ]);
           $this->forge->addKey('id', true);
           $this->forge->createTable('employees');
      }

      public function down(){
           $this->forge->dropTable('employees');
      }
}

• Run the migration –

php spark migrate

I added some records to the table.

---

3. Update Table Structure Using migrate:refresh

Using migrate:refresh you can recreate tables.

---

Steps –

• Again open CreateEmployeesTable migration PHP file in app/Database/Migrations/ folder.
• Modify table structure in the up() method –

public function up(){
      $this->forge->addField([
          'id' => [
                'type' => 'INT',
                'constraint' => 5,
                'unsigned' => true,
                'auto_increment' => true,
          ],
          'fullname' => [
                'type' => 'VARCHAR',
                'constraint' => '191',
          ],
          'email' => [
                'type' => 'VARCHAR',
                'constraint' => '100',
          ],
          'city' => [
                'type' => 'VARCHAR',
                'constraint' => '100',
          ],
          'age' => [
                'type' => 'INT',
                'constraint' => '3',
          ],
      ]);
      $this->forge->addKey('id', true);
      $this->forge->createTable('employees');
}

• Here, I did the following –
  • Change column name from emp_name to fullname and change constraint value from 100 to 191.
  • Added a new column age.
• Refresh the migration –

php spark migrate:refresh

NOTE – Above command will recreate the whole database and delete its data.

---

4. Update Table Structure without losing data

To do this create a new migration –

php spark make:migration update_and_addfield_to_employees_table

• Open a PHP file that ends with UpdateAndAddfieldToEmployeesTable.php in app/Database/Migrations/ folder.
• Define table modification in up() method –
  • Rename column name from emp_name to fullname.
  • Add a new column age.
• Reset table structure using down() method –
  • Rename column name from fullname to emp_name.
  • Delete age column.

NOTE – If you have created more than 1 column while table altering then in the down() method mention the column names between [] separated by comma in dropColumn().

<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class UpdateAndAddfieldToEmployeesTable extends Migration
{

     public function up(){

         ## Rename column name from emp_name to fullname 
         $alterfields = [
              'emp_name' => [
                    'name' => 'fullname',
                    'type' => 'VARCHAR',
                    'constraint' => '100',
              ],
         ];
         $this->forge->modifyColumn('employees', $alterfields);

         ## Add age column
         $addfields = [
              'age' => [
                    'type' => 'INT',
                    'constraint' => '3',
              ],
         ];
         $this->forge->addColumn('employees', $addfields);
     }

     public function down(){
         
         ## Delete 'age' column
         $this->forge->dropColumn('employees', ['age']);

         ## Rename column name from fullname to emp_name
         $fields = [
              'fullname' => [
                    'name' => 'emp_name',
                    'type' => 'VARCHAR',
                    'constraint' => '100',
              ],
         ];
         $this->forge->modifyColumn('employees', $fields);
     }
}

• Run the migration –

php spark migrate

After execution employees table structure is changed and data is not deleted.

---

5. Conclusion

Use migrate:refresh only when you want to recreate all tables using migration otherwise, create a new migration file for updating the existing table.

You can also alter more than 1 table using a single migration file.

If you found this tutorial helpful then don't forget to share.