Schema Migration in CakePHP 3.x

Introduction

In this blog we’re going to look at the purpose of the schema migration tool provided by CakePHP and the benefits of using it in our project(s).

What does it do

CakePHP provides a plugin called migration which is supported by the core team. This plugin is a wrapper for the database migrations library Phinx.

Using a migration file we can create or drop tables, add or remove columns, create indexes and even insert data into our database. A migration file contains PHP script which handles our schema.

Why should we use it

If we are working as a team that comprises of two or more people, version control tools will help us to merge files and stay in sync. Unfortunately we can’t sync database structure through these tools, and that’s the reason schema migration tools came into the picture. They allows us to represent the current state of our schema via simple scripts, instead of a bunch of SQL queries.

CakePHP provides us schema generation. This helps us to create or delete databases as needed. In addition the CakePHP core team introduced the migration plugin to allow user’s to reverse or redo certain changes to the schema at will.

Advantages

• Version control – we don’t need to worry anymore about the state of our database schema.
• No need of writing SQL queries.
• Easy to deploy on different environments. The schema design is bundled with the code.

Prerequisite

• PHP  >=5.5.9
• CakePHP  >= 3.X
• Little bit knowledge on CakePHP and MYSQL

Quick start

Let’s see how we would use the migrations functionality in an application. We are going to bake a schema for a basic shopping app application and see how migrations are integrated into the development process.

Step 1 – Create a shopping app application

Create a new CakePHP application using cake bake. If you are not sure how to do this, click here to find out.

$ composer create-project --prefer-dist cakephp/app shopping_app

Then create a database with name as shopping_app and configure our app to connect to the database by changing Datasources in config.php file  app/config/app.php

Check the home page http://localhost/shopping_app , it should show an all green status which indicates that everything is fine.

Step 2 – Install migration plugin

After installing a CakePHP app, we should have migration plugin included by default, if not edit the composer.json file to include the following,

"require" : {
    "cakephp/migrations": "dev-master"
}

Run composer update and then load the plugin into our application by editing the bootstrap.php and add the following line,

Plugin::load('Migrations');

ANOTHER APPROACH TO INSTALL MIGRATION PLUGIN

We can also integrate migration plugin by running the following command from our application root directory.

$ composer require cakephp/migrations "@stable"  // if composer installed at global level

or through phar file 

$ php composer.phar require cakephp/migrations "@stable"

LOAD MIGRATION PLUGIN

To integrate migration plugin, run the following command,

$ bin/cake plugin load Migrations

It will update config/bootsrap.php file automatically.

Step 3 – Create a table

So far we are done with basic setup, we need to start using migration. In this step, we’re going to see how to create a table using migration.

Let’s generate a migration script for users table by running the following command.

$ bin/cake bake migration CreateUsers

If the command ran successfully, you’ll see the following,

You’ll now have a PHP script file with a basic structure for users table at Config/Migrations/20161216070319_CreateUsers.php. Please open the file,

If we observe, the CreateUsers migration has its status set as down, generally we’ve two types of status up and down.

UNDERSTANDING UP AND DOWN STATUS

• If the migration has been run then the status will be up, otherwise the status will be down.
• The function up() in our migration file is for all those things which we want to add or alter as we move forward in your project.
• The function down() is for reversing or rolling back the changes made by the upfunction.
• When using the up() method, it is not compulsory to use the down() method.

EXAMPLE

public function up() {
 $users = $this->table('users');
 $users->create();
}

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

---

The below command will execute all migration files under the folder – app/Config/Migration  which have status as down.

$ bin/cake migrations migrate

Now if we check shopping_app database, we can see users table with an autoincrement field called id. Now it’s time to add columns to this table.

Step 4 – Add columns to table

We can add /update columns to an existing table in two different ways,

• Either we can generate a new migration script file using the cake bake command. (Preferable)
• Or manually edit the already existing file.

GENERATE NEW MIGRATION

Let us add name, email to the users table,

$ bin/cake bake migration AddColumnsToUsers name:string[50] email:string[100]

The above command line will generate a migration file (20161216122714_AddColumnsToUsers.php) that has the following code,

<?php
use Migrations\AbstractMigration;

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

EDIT OR WRITE A NEW FILE MANUALLY

We can add more columns by changing change function in the created file. Here is the modified change function.

public function change()
 {
$table = $this->table('users');
$table->addColumn('name', 'string', [
'default' => null,
'limit' => 50,
'null' => false,
    ])
    ->addColumn('email', 'string', [
'default' => null,
'limit' => 100,
'null' => false,
   ])
   ->addColumn('address', 'string', [
'default' => null,
'limit' => 300,
'null' => false,
   ])
   ->addColumn('phone_number', 'string', [
'default' => null,
'limit' => 12,
'null' => false,
   ])
  ->addColumn('gender', 'integer', [
'comment' => '1 - Male, 2 - Female',
'default' => null,
'limit' => 4,
'null' => false,
   ])
   ->addColumn('created', 'datetime', [
'default' => null,
'limit' => null,
'null' => false,
   ])
   ->addColumn('modified', 'datetime', [
'default' => null,
'limit' => null,
'null' => false,
   ])
   ->addIndex( [
'email',
      ], ['unique' => true]
   );
$table->update();
 }

Once we are done with these modifications, check the status and run the migration. After running the migration we will see all the columns added to the user’s table.

GENERATE SCRIPT FOR REMAINING TABLES

In a similar manner we can create scripts for the remaining tables that are needed for shopping app,

• Products
• Carts
• Countries etc.

USING CAKE BAKE

We can bake migration script using cake bake as shown below,

bin/cake bake migration CreateCountries id:integer[11] name:string[100] country_code:string[10]

This will generate a script for countries table.

Step 5 – Insert record

We can also insert records into table by writing script manually. For example, let’s insert records into countries table. Create a file 20170222062777_InsertCountries.php and add the below script,

<?php
useMigrationsAbstractMigration;

// Class to insert records into countries table

<?php
useMigrationsAbstractMigration;

// Class to insert records into countries table

class InsertCountries extends AbstractMigration
{
  public function up()
  {
$singleRecord = ['id' => 1, 'name' => 'India', 'country_code' => '+91'];
$table = $this->table('countries');
$table->insert($singleRecord);
$table->saveData();

    // Inserting multiple records

$records = [['id' => 2, 'name' => 'Germany', 'country_code' => '+49'], 
      ['id' => 3, 'name' => 'China', 'country_code' => '+86']];

    // this is a handy shortcut

$this->insert('countries', $records);
  }

  /**
   * This method is used to revert the changes whatever we did in up() method.
   */
  public function down()
  {
$this->execute('DELETE FROM shopping_app.countries where id in (1, 2, 3 )');
  }
}

We can’t use the insert methods inside a change() method. Please use up() and down() methods.

Step 6 – Rollback

If in case we don’t want to keep the changes that we just made, we can simply roll back the changes with the below command,

$ bin/cake migrations rollback

The above command will revert back the last migration that was run.

We can also pass a migration version number to rollback to a specific version

$ bin/cake migrations rollback -t 20170218060743

After a rollback, the status of the migrations that were rolled back will be changed to down.

The end

That’s it. We are done with a basic database schema for shopping app. If any database changes are to be made, we can generate the migration files and commit them. Other developers in the team can update their databases by pulling the files and then running the command given below –

$ bin/cake migrations migrate

Some more migration features

Migration plugin provides more features, that might be useful for us.

REMOVE COLUMN FROM TABLE 

We can generate a migration to remove a column by using the command line,

$ bin/cake bake migration RemoveGenderFromUsers gender

Running this command will generate a script as below, if you migrate this script it will remove the gender column from users table.

public function change() {
$table = $this->table('users');
$table->removeColumn('gender');
$table->update();
 }

ADD PRIMARY KEY 

We can add primary column to any column simply by calling addPrimaryKey,

public function change() {
$table = $this->table('users');
$table->addColumn('id', 'integer', [
'autoIncrement' => true,
'limit' => 11
    ])
    ->addPrimaryKey('id')
    ->create();
}

GENERATING MIGRATION SCRIPT FROM AN EXISTING DB

We can generate migration script from an existing database using migration_snapshot,

$ bin/cake bake migration_snapshot Initial

It will generate a migration file as YYYYMMDDHHMMSS_Initial.php containing the create statements for all the tables in our database.

EXECUTE CUSTOM QUERIES

We can execute custom queries with the execute() and query() methods. The execute() method returns the number of affected rows whereas the query() method returns the result as a PDOStatement.

For more information, please go through this tutorial.

MIGRATION HELP COMMAND

$ bin/cake migrations help

Conclusion

We hope this blog will help you in getting started with CakePHP migrations. As you can see it is simple to use and will make managing our database schema simpler. This blog is meant to be a starter guide. For more in-depth usage and functionalities, we request you to go through the official guide here.

Please leave your comments and let us know if you’ve any doubts.

Reference

• CakePHP document
• Phinx