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 allow 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.

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.

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.