What are the allowed DataTypes for migration

Question

What are the allowed options to use as DataTypes in forge for codeigniter 4 migration except these.

'INT'
'TEXT'
'VARCHAR'
'datetime'

Update 1: According to first comment

'BLOB'
'DATE'
'ENUM'

Update 2: According to second comment

'TINYINT'
'DECIMAL'

Answer 1

Recently, I have made a complete new database using Codeigniter migration instead of using phpmyadmin or any direct using MySQL command. So, I have got experience on it. You can use almost all useful MySQL types. You already know the following types:

• INT
• varchar
• TEXT
• ENUM
• BLOB
• decimal
• DATETIME

But along with those above types, the following types are also available to migrate in Codeigniter 4. You can use these datatypes when you need in your database.

• DATE
• tinyint
• smallint
• mediumint
• bigint
• float
• double
• real
• timestamp
• time
• year
• char
• tinytext
• mediumtext

Hope this above information is helpful to you.

On the other hand, I must share the pattern of codeigniter migration so that it gets easier for you to understand it more clearly. If we see the user guide of Codeigniter 4, we can understand the logic of Codeigniter 4 supported MySQL types with migration feature.

Please check the following code to get the code to understand the logic.

$fields = [
    'users' => [
        'type'       => 'VARCHAR',
        'constraint' => 100,
    ],
];

This code generates this MySQL command when the field is added.

users VARCHAR(100)

Based on the logic, I think we can use all types of MySQL.

Please check out this migration code that I have used to create a new table named 'cc_type_test'. I have created almost all types of MySQL fields so that you can understand it properly.

<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;
use CodeIgniter\Database\RawSql;

class TypeTest extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'test_id' => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'expire_date' => [
                'type'           => 'DATE',
            ],
            'code' => [
                'type'       => 'tinyint',
            ],
            'code2' => [
                'type'       => 'smallint',
            ],
            'code3' => [
                'type'       => 'mediumint',
            ],
            'code4' => [
                'type'       => 'bigint',
            ],
            'price' => [
                'type' => 'float',
            ],
            'price2' => [
                'type' => 'double',
            ],
            'price3' => [
                'type' => 'real',
            ],
            'TimeStamp' => [
                'type' => 'timestamp',
            ],
            'JustTime' => [
                'type' => 'time',
            ],
            'Year' => [
                'type' => 'year',
            ],
            'Charecter' => [
                'type' => 'char',
            ],
            'short_detail' => [
                'type' => 'varchar',
                'constraint'     => 225,
            ],
            'Tinytext' => [
                'type' => 'tinytext',
            ],
            'Mediumtext' => [
                'type' => 'mediumtext',
            ],
            'Longtext' => [
                'type' => 'longtext',
            ],
            'Image' => [
                'type' => 'blob',
            ],
            'discount' => [
                'type' => 'decimal',
                'constraint' => '10,4',
                'null' => true,
                'default' => null,
            ],
            'is_default' => [
                'type' => 'ENUM',
                'constraint' => ['1', '0'],
                'default' => '0',
            ],
            'sort_order' => [
                'type' => 'INT',
                'constraint' => 3,
            ],
            'createdDtm' => [
                'type' => 'DATETIME',
                'default' => new RawSql('CURRENT_TIMESTAMP'),
            ],
            'createdBy' => [
                'type' => 'INT',
                'constraint' => 11,
                'null' => true,
                'default' => null,
            ],
            'updatedBy' => [
                'type' => 'INT',
                'constraint' => 11,
                'null' => true,
                'default' => null,
            ],
            'updatedDtm DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
        ]);
        $this->forge->addKey('test_id', true);
        $this->forge->createTable('cc_type_test');
    }

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

After creating the migration file and running the migration command php spark migrate, the table cc_type_test is created in my database. Here is the image of how the table looks like with all the field types I have used in my migration file.

https://i.sstatic.net/LTSMS.png

Hope, This is more understandable and it will help you to create migration in your Codeigniter4 project.

As almost every table contains primary key and many times we need to use unique key in our table, I am sharing how we can use these keys in Codeigniter4 migration. We can also use the PRIMARY KEY and UNIQUE constraints to define the primary key and unique keys in a table. The following code is an example.

Example Code:

$this->forge->addField([
    'id' => [
        'type' => 'INT',
        'constraint' => 11,
        'auto_increment' => true,
        'unsigned'       => true,
    ],
    'email' => [
        'type' => 'VARCHAR',
        'constraint' => 255,
        'unique' => true,
    ],
]);


$forge->addPrimaryKey('id', 'pd_name');
// gives PRIMARY KEY `pd_name` (`id`)

$forge->addUniqueKey(['email'], 'key_name');
// gives UNIQUE KEY `key_name` (`email`)

I hope, this information will help you a lot about Codeigniter4 migration.