Gone are the days where you use the export functionality of your database management tool and share the SQL file to your team mates. Most PHP frameworks today already comes with a database migration tool which you can use to construct and make changes to your database and easily share them through your version control system of choice. In this tutorial I’ll be walking you through Phinx, a database migration tool for PHP.
If you don’t know what a database migration is, it’s basically a way to version your database. Pretty much like what you do with version control systems like Git, SVN or Mercurial. It allows you to make changes to your database and easily rollback any of those changes later on if you make a mistake or if there are some changes that needs to be implemented. You can then share the changes you’ve made to your team by committing it to your version control system and pushing it to the main repository of your project. Your team mates can then pull those changes into their own copy and run the migrations so that they have the same state of the database as you do.
You can install Phinx through Composer by executing the following commands on your terminal. If you have an existing project, you can navigate to your project directory and execute it from there.
Next create a
migrations folder. This is where the database migrations are going to be stored.
Finally, execute the following command to initialize Phinx.
When Phinx was initialized, you might have noticed that it created a
phinx.yml file on the root of your project. This file is the Phinx configuration. This is where you can modify the database used by Phinx for production, development and testing environments. By default Phinx uses the development environment so go ahead and modify the details for that.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
Using Phinx mainly composed of the following workflow:
- Create a migration
- Modify the migration class
- Run the migration
- If you need to make a change to a previous migration, roll it back, make the change to the migration class and run the migration again.
To create a new migration, you use the Phinx shell script. Below is a migration for creating a users table.
This creates a new file in the
migrations directory. For me it created a file named
20150727004941_create_users_table.php. The filename is made up of the timestamp and the machine-friendly version of the name of the migration that you provided.
It’s best practice that you name your migrations based on what they do, so that you can easily look for them if you need to modify something. Each migration should only be doing one specific task so that you can easily roll them back without having to worry about side-effects. Creating a users table should add the fields that are necessary to that table. But if you’re modifying a specific table, in most cases, you should only modify one field at a time. For example, you need to modify the data type from
VARCHAR. Only modify one field. But if it makes sense to change two or more fields in a single migration, then do so. For example, if you need to modify the length of the
password fields so they can accomodate more data.
Going back to the migration file. Open it up if you haven’t yet. Here’s how it looks like by default.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Phinx already creates a skeleton class for you s you only need to modify what’s in the
change function. This is where you need to add the code for modifying your database. In this case the change is to create a
users table. Add the following inside the
1 2 3 4 5 6 7
The table is declared using the
table function. This accepts the name of the table as its argument. The fields are then added by calling the
addColumn method in the table. This accepts 2 required arguments: the name of the field and the data type. Finally, the
create function is called to actually create the table. Once that’s done, save the file.
Let’s pretend we forgot to add the
photo field, so create another migration to add that.
Add the following inside the
1 2 3
Nothing new here, the only thing that’s changed is the method to be called to actually execute the change that you need to make. In this case instead of
update method is used. This is how Phinx will know that you are trying to update the
users table by adding the
You can run all the migrations that haven’t been run yet by using the
It would output something similar to the following when the migration is run.
1 2 3 4 5 6 7 8 9 10 11
You can now check if the table was actually created by opening your database management tool of choice. Also notice that there is a
phinxlog table in your database. This is used by Phinx to keep track of which specific migrations were run and the start and end time for each. The migration is determined by its version, which is basically the first part of the file name for a specific migration file. On my testing, the version is
Rolling Back Changes
To rollback changes you can use the
rollback command which does exactly the opposite of the
migrate command. All it does is rollback everything that has changed on the last migrate.
The command above outputs something similar to the following:
1 2 3 4 5 6 7 8 9 10 11
That’s it! In this tutorial, you’ve learned how to create database migrations with Phinx. Be sure to check out the official documentation if you want to learn more.