SQL to Migration for CodeIgniter 4

Overview

What is SQL to Migration?

The SQL to Migration is a CodeIgniter 4 CLI command designed to convert SQL dump files (e.g., from phpMyAdmin) into CodeIgniter 4 migration files. It supports table creation, foreign keys, triggers, and database prefixes.

Note: This tool simplifies the process of converting existing SQL databases into CodeIgniter 4 migrations, making database versioning and deployment much easier.

Installation

How to Install

1. Install via Composer

Add the package to your CodeIgniter 4 project:

composer require bertugfahriozer/sql2migration

2. Verify Command Availability

Check if the Spark command is registered:

php spark list

You should see sql2migration in the list.

Usage

How to Use SQL to Migration

To convert an SQL file into CodeIgniter 4 migration files:

php spark sql2migration /path/to/your/database.sql

Input

Path to the SQL file (e.g., /path/to/database.sql)

Output

Migration files are generated in app/Database/Migrations/

Apply the migrations:

php spark migrate

Features

Key Features

Table Creation

Generates migration files from CREATE TABLE statements with various column types and constraints.

Foreign Keys

Converts ALTER TABLE foreign key definitions into separate migration files with various actions.

Triggers

Creates migration files for CREATE TRIGGER statements with different timing and event combinations.

Prefix Support

Automatically strips table prefixes based on your CodeIgniter database configuration.

Debugging

Provides detailed CLI logs for troubleshooting during the conversion process.

Examples

Example Usage

Example SQL File

The following SQL file demonstrates the package's capabilities:

CREATE TABLE `ci4ms_users` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `ci4ms_orders` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

ALTER TABLE `ci4ms_orders`
  ADD CONSTRAINT `fk_orders_users` FOREIGN KEY (`user_id`) REFERENCES `ci4ms_users` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;

CREATE TRIGGER `ci4ms_update_timestamp` BEFORE UPDATE ON `ci4ms_users` FOR EACH ROW SET NEW.updated_at = NOW();

Generated Migration Files

Running the command produces the following migration files:

Table Migration

<?php
namespace App\Database\Migrations;
use CodeIgniter\Database\Migration;

class CreateUsersTable extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id' => [
                'type' => 'INT',
                'constraint' => '11',
                'unsigned' => true,
                'auto_increment' => true,
            ],
            'username' => [
                'type' => 'VARCHAR',
                'constraint' => '255',
            ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('users');
    }

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

Troubleshooting

Common Issues and Solutions

Ensure the namespace is correctly added in app/Config/Autoload.php
Run composer dump-autoload

Verify the ALTER TABLE format in your SQL file
Check CLI output for debugging

Ensure database.default.DBPrefix is correctly set in .env

If migrations fail, roll back with php spark migrate:rollback
Verify the SQL file structure and syntax

Convert SQL to CodeIgniter Migrations