Follow

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.

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,

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

Another approach to install migration plugin

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

Load migration plugin

To integrate migration plugin, run the following command,

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.

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

users-migrations_01

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 up function.
  • When using the up() method, it is not compulsory to use the down() method.

Example


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

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,

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

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.

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,

This will generate a script for countries table.

Step 5 – Insert record

We can also insert records into the 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,

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,

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

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 –

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,

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

Add primary key 

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

Generating migration script from an existing DB

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

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

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

Posted By: Bharadwaj Gummadi, Osmosee

Are you interested? follow us and get notified of new posts

One thought on “Schema Migration in CakePHP 3.x

Leave A Reply

thirteen − 1 =