Super useful PHP tools: Part 2

Canada geese

(Part1 Part2 Part3)

I have been programming PHP for about 5–6 years now. But until before two years if you were to mention the word migration to me, something like the above picture would have popped in my head. I feel quite embarrassed about it now . But hey, there is a reason for that.

See, I have never been a big fan of using frameworks like Laravel, Symphony etc. I prefer to assemble my application by using small components from different sources. Occasionally I write my own small utilities. For DB access I had written a few functions which perfectly served my purpose. Of course I had to create, modify, delete tables. So I created few scripts exclusively to deal with tables. Nothing wrong in it. But it was painful. I was a completely self taught web developer and a freelancer. I just didn’t know any better. Had I used one of the well known frameworks I would have been automatically introduced to migrations of the database kind.

Coming to the point: if you do not use any of the famous frameworks you have to figure out a mechanism for managing changes to your database. Phinx solves the problem neatly.

Installing is straightforward:

composer require robmorgan/phinx
mkdir -p db/migrations
vendor/bin/phinx init

After the init above there should be a phinx.yml in your working directory. Mine looks as follows:

Note the default charset “utf8”. If you are starting a new MySQL project make sure you are enforcing utf8 charset at database level as well as table level. MySQL defaults to “latin1” which can cause problems if you are dealing with non-ASCII characters. Luckily Phinx uses sensible defaults. Modify other setting appropriate for your environment. This is what I have for the purpose of this post:

testing:
        adapter: mysql
        host: "127.0.0.1"
        name: "test-db"
        user: root
        pass: root
        port: 3306
        charset: utf8

Make sure you do not check in phinx.yml into repository as it contains passwords.

Typical Phinx use involves following steps:

• Create a migration using create command
• Run the migration using migrate command
• If required rollback migration using rollback command

Phinx has extensive documentation. But at times I have found it confusing. Here I want to describe some common tasks which will get you started quickly.

Create a new table

Let’s start with something very simple. We will create a table for photographers with two columns name and phone. Run the command:

vendor/bin/phinx create CreatePhotographersTable

Note the migration name “CreatePhotographersTable”. It MUST be in CamelCase. After running the command Phinx creates a new file in the db/migrations directory. For me it created 20171126153534_create_photographers_table.php. If you open the newly generated file you will see an empty “change” function. Enter the following code in the change function:

We are adding two columns both of type “varchar” but with different lengths. Since Phinx works with my difference databases it provides a generic column type named “string” instead of MySQL specific “varchar”. Complete list of supported types is here. The third argument to addColumn is an array in which we can specify attributes of the columns. In this case we are specifying lengths of the varchar columns. Other possible options are here. Now run the migration:

vendor/bin/phinx migrate -e testing

The above command runs the migration for the environment “testing”. If everything goes well Phinx will print some helpful messages on screen followed by something like “All Done. Took 0.5s”. At this stage if you to examine your databases you will see two new tables phinxlog and photographers. The table phinxlog is where Phinx keeps track of which migrations have already been run.

Add new column to existing table

Now lets add a column name “portfolio-url” to the photographers table above.Run the command:

vendor/bin/phinx create AddPortfolioToPhotographers

Again, there should be a new file in db/migrations directory. Change it to following:

Here we are using a new column option “after”. Run the migration:

vendor/bin/phinx migrate -e testing

Inspect the photographers table and confirm that a new column has indeed been added.

Undo changes to table

We just added a new column in the step above. For some reason if we want to undo the change i.e. delete the newly added column in this case, we can do it with a “rollback”. Run the following:

vendor/bin/phinx rollback -e testing

Voila! The new column is gone!

The previous command rolls back the latest changes. If you want to rollback several steps that is easy too. Examine the phinxlog table and take a look at the version column. You can rollback upto the state you want by using the appropriate version number as follows:

vendor/bin/phinx rollback -e testing -t 20171126153534

Hope the basic concepts are clear now. For the rest of the article I will just show the change functions and skip the create and migrate commands.

Add an index to table

Add foreign key

Add timestamps to tables

I think I have given you enough information to get started with Phinx. You can grab all code in this post from github.

Bonus

If you have made it thus far, let me introduce another tool quickly. Do you want to take MySQL backups painlessley? Use mysqldump-php.

Hope this helps!

(Part1 Part2 Part3)