Super useful PHP tools: Part 2
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
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:
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.
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!