Managing Database Schema Evolution with CodeIgniter 4 Migrations

The Problem: Schema Drift Across Environments

When developing in teams or deploying to multiple environments (development, staging, production), keeping database schemas synchronized is a constant challenge. Manually exporting SQL dumps is error-prone and makes it difficult to track who changed what and when. CodeIgniter 4 solves this with Migrations: a version-control system for your database that allows you to define schema changes in PHP files and apply them predictably across any environment.

How Migrations Work

Migrations are stored in app/Database/Migrations. Each file follows a strict naming convention: YYYYMMDDHHMMSS_Description.php. This timestamp ensures that migrations are executed in the exact order they were created, preventing dependency errors (e.g., trying to add a foreign key to a table that hasn't been created yet).

Every migration class must implement two methods:

• up(): Defines the changes to apply (creating tables, adding columns).
• down(): Defines how to reverse those exact changes.

To maintain database-agnostic code, CodeIgniter provides the Forge class (accessed via $this->forge). This Schema Builder translates PHP methods into the specific SQL dialect of your configured database driver (MySQL, PostgreSQL, SQLite, etc.).

Worked Example: Creating a Users Table

To create a new migration, you can use the CLI command php spark make:migration CreateUsersTable or create the file manually. Below is a complete implementation for a basic users table.

forge->addField([
            'id'         => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'username'   => ['type' => 'VARCHAR', 'constraint' => '100'],
            'email'      => ['type' => 'VARCHAR', 'constraint' => '255', 'unique' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
        ]);

        // Set the primary key
        $this->forge->addKey('id', true);

        // Execute the table creation
        $this->forge->createTable('users');
    }

    public function down()
    {
        // Revert the change by dropping the table
        $this->forge->dropTable('users');
    }
}

Executing Migration Commands

Run these commands from the project root via the terminal. Ensure your database credentials are correctly set in app/Config/Database.php.

Command	Action	Risk/Note
php spark migrate	Applies all pending migrations.	Safe for production; only runs new files.
php spark migrate:rollback	Reverts the last batch of migrations.	Deletes data in the affected tables.
php spark migrate:reset	Rolls back all migrations.	Destructive; wipes the entire schema.
php spark migrate:status	Lists applied and pending migrations.	Read-only; no risk.

Verifying the Result

To confirm the migration was successful, check the following:

1. Database Inspection: Use a database client to verify the users table exists with the specified columns.
2. The Migrations Table: CodeIgniter automatically creates a migrations table. Check this table to ensure a row exists for CreateUsersTable with the correct timestamp.
3. CLI Status: Run php spark migrate:status. The migration should be listed as "Applied".

Limitations and Common Mistakes

Data Seeding vs. Schema Migration

A common mistake is using migrations to insert default data (e.g., admin users). Migrations should be reserved for structure. For data, use CodeIgniter's Seeders. Mixing the two clutters the migration history and makes rollbacks unpredictable.

Asymmetric Logic

If your up() method adds a column, your down() method must remove that specific column. If you use raw SQL ($this->db->query()) in up() but use Forge in down(), you may encounter syntax errors or partial rollbacks. Always mirror the logic exactly.

Database Engine Constraints

Some database engines do not support multiple ALTER TABLE operations in a single statement. If you are adding multiple columns and indexes, you may need to split them into separate migration files or ensure the Forge builder is handling them as individual queries to avoid engine-specific failures.

Timestamp Collisions

If multiple developers create migrations simultaneously and manually name them, timestamp collisions can occur. Always use php spark make:migration to ensure a unique, sequential timestamp is generated.