How to Update table Structure using Migration in CodeIgniter 4

Migration makes table creation and managing them easier. Using this you can recreate tables or update a table without losing its data.

In this tutorial, I show how you can update table structure using migration in CodeIgniter 4.

Database Configuration
Create Table
Update Table Structure Using migrate:refresh
Update Table Structure without losing data
Conclusion

1. Database configuration

Open .env file which is available at the project root.

NOTE – If dot (.) not added at the start then rename the file to .env.

Remove # from start of database.default.hostname, database.default.database, database.default.username, database.default.password, and database.default.DBDriver.
Update the 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

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.