Migrations are a tool in CakePHP for managing database schema changes. They provide a version-controlled way to modify your database structure, making it easier to track, share, and deploy changes across different environments or installations of your application. CakePHP has tools to generate snapshots of your database structure and migrations between them. Given that migrations can also execute query language statements on your database, they can also be used to modify data. Willow CMS has evolved through several releases since going live for this site, with some of those releases requiring changes to the database schema and data. In this post I’ll talk you through some practical examples of how I handled this.

Creating Migrations - a basic example

In CakePHP, migrations are created using the bake migration command. This command generates a PHP class that defines the SQL instructions for the migration. For example, to create a migration for adding a new email column to the users table, you can run:

bin/cake bake migration AddEmailToUsers email:string

This command creates a migration file named AddEmailToUsers in your config/Migrations directory with a method called change() that defines the necessary change. You can use the methods in CakePHP’s Table class to define the schema changes. Here is an example change() method for adding the email column:

use Migrations\AbstractMigration;

class AddEmailToUsers extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * https://book.cakephp.org/phinx/0/en/migrations.html#the-change-method
     * @return void
     */
    public function change(): void
    {
        $table = $this->table('users');
        $table->addColumn('email', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => false,
        ]);
        $table->update();
    }
}

Migrations can also be rolled back using the bin/cake migrations rollback command. When you execute that command the last migration file recorded as run will be rolled back. Your code to perform the rollback actions should be added to the migrations file via the public function down(): void {} method.

Running Migrations

Once you have defined the schema changes, apply them using the migrate command:

bin/cake migrations migrate

This command runs all pending migrations and updates your database schema. The neat thing is that migrations are marked as completed once run via a record in the phinxlog table in the database which means it’s safe to use migrations in an automated manner such as in the Willow CMS Development Envinronment setup_dev_env.sh script or in the startup script for a production installation.

Here you can see the phinxlog table on my development environment following a run of the setup script and a run of the 4 migrations since V1.

Let’s get into each of these migrations…

Migrations - Some Real Examples

V1 - Create or Drop the Base Tables

Take a look at the source for v1, the first migration for Willow which although long is actually super basic. The up method has uses the table methods addColumn and create to create each table in the database. The only difference in this file to the first example is the methods are chained.

    public function up(): void
    {
        $this->table('aiprompts', ['id' => false, 'primary_key' => ['id']])
            ->addColumn('id', 'uuid', [
                'default' => null,
                'limit' => null,
                'null' => false,
            ])
            ->addColumn('task_type', 'string', [
                'default' => null,
                'limit' => 50,
                'null' => false,
            ])
            // more addColumns......
            ->create();
        
        // more calls to $this->table......

The down method undoes those statements with the drop and save table methods.

    public function down(): void
    {
        $this->table('aiprompts')->drop()->save();
        // more calls to $this->table......

There is no data in this migration, it just creates/drops the tables required by Willow CMS. For even the most basic CakePHP app, you will need a migration like this if you intend to run PHPUnit tests. CakePHP will run your migrations each time you run the tests so that your test data (fixtures) can be loaded.

ChangeExpiresAtToDatetime - Change a Column Data Type

Take a look at the source for ChangeExpiresAtToDatetime which is another easy one, this time changing the data type for a column in the blocked_ips table.

The up method uses the changeColumn method to specify the change to datetime for the expires_at column.

    public function up(): void
    {

        $this->table('blocked_ips')
            ->changeColumn('expires_at', 'datetime', [
                'default' => null,
                'limit' => null,
                'null' => true,
            ])
            ->update();
    }

The down method changes it back again to date.

    public function down(): void
    {

        $this->table('blocked_ips')
            ->changeColumn('expires_at', 'date', [
                'default' => null,
                'length' => null,
                'null' => true,
            ])
            ->update();
    }

InsertSettings - Load Default Data into the Settings Table

Take a look at the source for InsertSettings which is using just the insert and save table methods to load the default settings for Willow CMS into the settings table.

    public function change(): void
    {
        $this->table('settings')
            ->insert([
                'id' => Text::uuid(),
                'ordering' => 7,
                'category' => 'AI',
                'key_name' => 'articleSummaries',
                'value' => '0',
                'value_type' => 'bool',
                'value_obscure' => false,
                'description' => 'Automatically generate concise and compelling summaries for your articles and pages. When enabled, the system will analyze the content and create a brief synopsis that captures the key points. These summaries will appear on the article index page and other areas where a short overview is preferable to displaying the full text.',
                'data' => null,
                'column_width' => 2,
            ])
            // More insert statements below......

I actually didn’t bother with a down method to cater for rollbacks with this migration but perhaps I should add one. I’ll put a ticket on the Willow CMS Tickets board.

AddRobotsTemplate - Load More Default Data

Take a look at the source for AddRobotsTemplate.php which is adding another setting. You might be thinking why not just put this into the previous InsertSettings migration? Well, for one I didn’t have a need for this setting and two, I’d already deployed the latest version of Willow CMS to production - which includes having run the previous migration already on the production database as part of the automated deployment which builds a new Docker image and in turns runs the CakePHP migrations commands to upgrade the database.

So this is a nice example of how CakePHP will have marked the previous migration as run in the phinxlog table so that it is never run again (unless rolled back) and therefore this new migration is needed. There is some funkyness going on in this file when it comes to the default data for the setting which is a chunk of text, most easily stored using EOT markers for storing in a $robotsTemplate variable which is then refered to in the chained calls to load the setting. Migrations are just PHP code so you can do any kind of additional code and logic around the calls to table methods.

    public function change(): void
    {
        $robotsTemplate = <<table('settings')
            ->insert([
                'id' => Text::uuid(),
                'ordering' => 4,
                'category' => 'SEO',
                'key_name' => 'robots',
                'value' => $robotsTemplate,
                'value_type' => 'textarea',
                'value_obscure' => false,
                'description' => 'The template for robots.txt file. Use {LANG} as a placeholder for the language code. This template will be used to generate the robots.txt file content.',
                'data' => null,
                'column_width' => 4,
            ])
            ->save();
    }
}

Newslugstable - Modifying a Table Schema and Migrating Data

Take a look at the source for Newslugstable where I use the $this->execute method to execute SQL statements on the database as part of the migration. The slugs table on the production database for this site wil already contain data that needs to be migrated to the new table structure. To do this, the up method takes these steps:

1. Use addColumn table method to create new table columns, with relaxed defaults (allow null values)
2. Use execute method to migrate the data from the old columns to the new columns using a SQL statement
3. Use changeColumn table method to change the new columns to no longer allow null values
4. Use the addIndex table method to create some indexes for the new column structure
5. Use the removeColumn table method to remove the old columns that are no longer needed
6. use the execute method and some code to create some new data in the slugs table (since tags also use a new SlugBehavior and should have records in that table)

    public function up(): void
    {
        // First, add the new 'model' column
        $this->table('slugs')
            ->addColumn('model', 'string', [
                'limit' => 20,
                'null' => true, // Temporarily allow null
                'after' => 'id',
            ])
            ->update();

        // Add the new foreign_key column
        $this->table('slugs')
            ->addColumn('foreign_key', 'uuid', [
                'null' => true, // Temporarily allow null
                'after' => 'model',
            ])
            ->update();

        // Update existing records to set model and foreign_key
        $this->execute("UPDATE slugs SET model = 'Articles', foreign_key = article_id");

        // Now make the new columns required
        $this->table('slugs')
            ->changeColumn('model', 'string', [
                'limit' => 20,
                'null' => false,
            ])
            ->changeColumn('foreign_key', 'uuid', [
                'null' => false,
            ])
            ->update();

        // Add indexes for the new structure
        $this->table('slugs')
            ->addIndex(['model', 'slug'], [
                'name' => 'idx_slugs_lookup',
            ])
            ->addIndex(['model', 'foreign_key'], [
                'name' => 'idx_slugs_foreign',
            ])
            ->update();

        // Remove the modified column as it's no longer needed
        $this->table('slugs')
            ->removeColumn('modified')
            ->update();

        // Finally, remove the old article_id column
        $this->table('slugs')
            ->removeColumn('article_id')
            ->update();

        // Migrate existing tag slugs to the slugs table
        $connection = $this->getAdapter()->getConnection();
    
        $tags = $connection->execute("
            SELECT id, slug, DATE_FORMAT(created, '%Y-%m-%d %H:%i:%s') as created 
            FROM tags 
            WHERE slug IS NOT NULL 
            AND slug != ''
        ")->fetchAll('assoc');

        foreach ($tags as $tag) {
            $this->table('slugs')
                ->insert([
                    'id' => Text::uuid(),
                    'foreign_key' => $tag['id'],
                    'model' => 'Tags',
                    'slug' => $tag['slug'],
                    'created' => $tag['created'],
                ])
                ->save();
        }
    }

The down method undoes these changes. For completeness it should probably create the indexes for the columns that we re-create, but since I don’t intend to ever rollback this migration permanently, I didn’t bother.

    public function down(): void
    {
        // Add back the article_id column
        $this->table('slugs')
            ->addColumn('article_id', 'uuid', [
                'null' => true,
                'after' => 'id',
            ])
            ->update();

        // Restore data from foreign_key to article_id where model is 'Articles'
        $this->execute("UPDATE slugs SET article_id = foreign_key WHERE model = 'Articles'");

        // Make article_id required again
        $this->table('slugs')
            ->changeColumn('article_id', 'uuid', [
                'null' => false,
            ])
            ->update();

        // Add back the modified column
        $this->table('slugs')
            ->addColumn('modified', 'datetime', [
                'null' => true,
            ])
            ->update();

        // Remove the new polymorphic columns and indexes
        $this->table('slugs')
            ->removeIndex(['model', 'slug'])
            ->removeIndex(['model', 'foreign_key'])
            ->removeColumn('model')
            ->removeColumn('foreign_key')
            ->update();
    }

Here you can see the slugs table structure before the migration…

… and after…

Wrapping Up

We’ve covered the basics of migrations in CakePHP, from creating and running simple migrations to more complex scenarios like changing column types and migrating existing data. The key takeaways are:

• Structure and Version Control: Migrations bring order to your database changes, making them trackable and reversible.
• Simplified Deployment: Use migrations to confidently deploy schema updates across various environments, from development to production.
• Automation: Integrate migrations into your deployment scripts for seamless, automated database updates.

Until next time, happy baking!