CakePHP3 Database Migrations with Phinx

[dropcap]C[/dropcap]ontinuing on from my last post CakePHP3 Database Migration Commands as I promised this is a follow up with actual working examples that you can use right away. The database migrations I have put together use CakePHP3 Model & Database Conventions and so you can be sure all tables will be picked up and correctly related to each other with a “bin/cake bake all {model}” command.

I thought we could create a simple “countries” and “states” model/table migration. This will give us an idea how to create a Migration for each database table and also how to create the Migration with a has many foreign key relationship.

Create Countries Table Migration

So let’s start by creating a new Migration that will allow also to create a “countries” table.

This will generate an empty migrations file in “config/Migrations”. The migration file should have a datetime stamp appended to the file with create_countries_table. So your file should look something like this “20150316082226_create_countries_table.php”.

Next we need to open this file and add our code that will create the countries table. So our countries table will have the following fields and data types, id(integer), name(string), acronym(string), created(datetime) and modified(datetime). The created and modified field are special CakePHP fields that will be automatically updated when database records are created or edited. This is a “behavior” that is attached to the Model/Table/CountriesTable class when we use cake bake.

The Up Method

So there is a little bit happening here, there are three methods that are automatically generated by CakePHP Migrations (Phinx), that is the change, up and down methods. The change method is commented out by default. We are only concerned with the up and down. Within the UP method we are calling a table object and passing into it declarations. In it’s most basic form it’s simply as follows.

Describe the Table

Obviously you need to tell Phinx what you want to name the column and the type of columns you need e.g. integer, string, text, datetime and so on. For more please see the Phinx Valid Column Types. See the basic example below. Notice that we also added an index to the name column. Also note that we have specified a “length” of 255 to the name column. This will create a varchar(255) column in our database. We have also specified “null” as false, this is a default behavior and so it doesn’t need to be stated however I like to be verbose as it makes it easy to simply change it in the future if you later decide to allow null on that column.

In the above example by default Phinx will create an ID column for you named “id”. If you need to override this you can turn off id => false and specify you own id field with ‘primary_key’ => [‘your_id’]. So depending on your table naming conventions you can leave it or override the default behavior. Example with id override below.

Note that because we have specified our own id column we can have control over it’s attributes. Signed false means that it’s cannot have a – or + symbol in front of it. The identity true attribute means the field will be auto_incremented with a unique id each time a new row is created.

Insert Data

After the table is created you can use the following code to perform database tasks with SQL. The most common thing you might do is insert some default or dummy data.

The Down Method

Migrations are all about being able to reverse changes or “rollback” database tables to a previous state. So note in the down method you can place the following code.

Migrate the Database

Now just run the command line migration tool

Using your favorite database tool like Sequel Pro or MySQL Workbench you should see a new countries table in your database. If you wish to rollback use the following command line. Run the rollback command for each migration.

Add States Table Migration

Let’s now create our States Migration. Run the following in your terminal.

Now find the newly generated create_states_table.php file and add the following data into your up and down methods.

This migration includes all the column specifications. You will see a few new ones. The most important is the “country_id” foreign key column to link us to our countries table. This is named country_id and not countries_id according to the CakePHP convention. This makes sense as the states.country_id is only referring to a single parent country.

There are also a few interesting column types to suit longitude and latitude information. Make sure you look closely at the column type and the new attributes.

The rest should be much the same as the CreateCountriesTable migration.

Add Foreign Keys Migration

Next we need to add a foreign key constraint to the states table. This will link the states table to the countries table on the states.country_id colum to the countries.id column.

I personally prefer to create a separate migration for all my foreign keys. I also usually make this the very last migration after all my tables have been created. The reason is because when you rollback all the foreign keys need to be dropped first from all the tables in the database. This will allow you to rollback and drop the previously created tables without an error. If you don’t drop the foreign keys before you attempt a rollback the migration will fail because of a foreign key check error. The opposite is also true, if you try to migrate a table with foreign keys and the linked table hasn’t been created yet the migration will fail.

So let’s do the following in the terminal.

Open up the generated add_foreign_keys.php file and dump in the following code.

Notice that the code for adding and dropping a foreign key are almost the same but see the subtle differences.

Migration Complete

Just one more thing, re-run that migration again in the command line.

For more helpful tips on CakePHP3 Migration Commands see my last post.

I hope that’s given you an insight into how to handle CakePHP migrations in any new CakePHP3 project you might undertake. The advantages of migrations are evident, you can keep it under source control and all members on your team can stay on the same database schema.

This knowledge however is not restricted to the CakePHP3 framework, you can include the Phinx Migration library into any PHP project. So your new found knowledge is portable.

Go migrate something!

Leave a Reply

Your email address will not be published. Required fields are marked *