Wern Ancheta

Adventures in Web Development.

Database Migrations in PHP With Phinx

| Comments

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.

Installation

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.

1
2
composer require robmorgan/phinx
composer install --no-dev

Next create a migrations folder. This is where the database migrations are going to be stored.

Finally, execute the following command to initialize Phinx.

1
php vendor/bin/phinx init

Configuration

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
paths:
    migrations: %%PHINX_CONFIG_DIR%%/migrations

environments:
    default_migration_table: phinxlog
    default_database: development
    production:
        adapter: mysql
        host: localhost
        name: production_db
        user: root
        pass: ''
        port: 3306
        charset: utf8

    development:
        adapter: mysql
        host: localhost
        name: development_db
        user: root
        pass: ''
        port: 3306
        charset: utf8

    testing:
        adapter: mysql
        host: localhost
        name: testing_db
        user: root
        pass: ''
        port: 3306
        charset: utf8

Usage

Using Phinx mainly composed of the following workflow:

  1. Create a migration
  2. Modify the migration class
  3. Run the migration
  4. 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.

Creating Migrations

To create a new migration, you use the Phinx shell script. Below is a migration for creating a users table.

1
php vendor/bin/phinx create CreateUsersTable

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 INT to 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 username and 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
<?php

use Phinx\Migration\AbstractMigration;

class CreateUsersTable extends AbstractMigration
{
    /**
     * Change Method.
     *
     * Write your reversible migrations using this method.
     *
     * More information on writing migrations is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-abstractmigration-class
     */
    public function change()
    {

    }
}
?>

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 change function.

1
2
3
4
5
6
7
<?php
  $table = $this->table('users');
  $table->addColumn('username', 'string')
      ->addColumn('email', 'string')
      ->addColumn('password', 'string')
      ->create();
?>

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.

1
php vendor/bin/phinx migrate AddPhotoFieldToUsersTable

Add the following inside the change method.

1
2
3
$table = $this->table('users');
$table->addColumn('photo', 'string', array('after' => 'username'))
        ->update();

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 create, the update method is used. This is how Phinx will know that you are trying to update the users table by adding the photo field.

Running Migrations

You can run all the migrations that haven’t been run yet by using the migrate command.

1
php vendor/bin/phinx migrate

It would output something similar to the following when the migration is run.

1
2
3
4
5
6
7
8
9
10
11
warning no environment specified, defaulting to: development
using adapter mysql
using database tester

 == 20150727004941 CreateUsersTable: migrating
 == 20150727004941 CreateUsersTable: migrated 0.3325s

 == 20150727013547 AddPhotoFieldToUsersTable: migrating
 == 20150727013547 AddPhotoFieldToUsersTable: migrated 0.5018s

All Done. Took 0.6773s

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 20150727004941.

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.

1
php vendor/bin/phinx rollback

The command above outputs something similar to the following:

1
2
3
4
5
6
7
8
9
10
11
warning no environment specified, defaulting to: development
using adapter mysql
using database tester

 == 20150727013547 AddPhotoFieldToUsersTable: reverting
 == 20150727013547 AddPhotoFieldToUsersTable: reverted 0.4672s

 == 20150727004941 CreateUsersTable: reverting
 == 20150727004941 CreateUsersTable: reverted 0.1503s

All Done. Took 0.6175s

Conclusion

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.

Comments