Database migrations in PHP with Phinx

Phinx is a database migration tool written by Rob Morgan in PHP, what that means is that you can tell Phinx that you want to create a new database table, add a column or edit the properties of a column by writing "migrations".

You can then run this migration using the Phinx tool and it will connect to your database with the configuration that you specified and perform the database updates for you automatically.

This is EXTREMELY handy if you are doing automated deployments using a tool like DeployBot (formely Dploy.io).

Installing Phinx

So how do we install this mystical wizardry tool you may ask?

NANANANANAAAA composer!

If you've lived under a rock for the past few years, Composer is a PHP dependency manager, that means that you can define what kind of third-party packages your project requires like the popular mailer package PHPMailer, and have composer download all of these packages and THEIR dependencies.

To install composer, go to https://getcomposer.org/download/ and follow the instructions, on windows it is very much "Next Next Finish"-y.

Now, to install Phinx what you do is open a command line of your choice (i use Cmder), navigate to the directory of your project (example. C:\xampp\htdocs\wahtever) and write the following:

composer require robmorgan/phinx

If you get a "'composer' is not recognized as an internal or external command, operable program or batch file."-error, this is most likely because your PATH environment variable is not updated to include the composer executable, on windows it is located C:\ProgramData\ComposerSetup\bin to add it to your path, please refer to this guide.

Anyways!

when you've run the command to require robmorgan/phinx a composer.json file should have been created in the root of your project directory, it should look something like this:

{
  "require": {
    "robmorgan/phinx": "^0.6.2"
  }
}

Now we need to configure Phinx with our database settings.

Configuring Phinx

Now that we have phinx installed, we need to create a configuration file, Phinx supports 3 configuration file formats, JSON, YAML and PHP.

We're going to use YAML because that is what Phinx generates by default.

To generate the initial configuration file go back to your command line and type the following:

vendor\bin\phinx.bat init

This will generate a file called phinx.yml with the following contents:

paths:
  migrations: %%PHINX_CONFIG_DIR%%/db/migrations
  seeds: %%PHINX_CONFIG_DIR%%/db/seeds

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

Something that is nice about Phinx is that it supports multiple "environments", this allows you to configure different sets of credentials and db information for your local development machine, your staging server and the production server.

I am using Phinx to manage my database schema locally using XAMPP and I have configurations for my development server as well as for my production and beta environment, usually the only difference is the database name, username and password.

When Phinx runs a migration it keeps track of which migration has been run by saving that information in its own database table defined by the option:

default_migration_table: phinxlog

in the phinx.yml file, you are free to change it to something like "migrations", but i usually keep the default setting.

The rest of the options under environment should be self explanatory, you are just configuring the credentials to the databases you are running migrations on.

The options under paths:

paths:
  migrations: %%PHINX_CONFIG_DIR%%/db/migrations
  seeds: %%PHINX_CONFIG_DIR%%/db/seeds

Are the paths to your migrations and seeder folders, this is the location which Phinx will look for your migrations and seeders when you tell it to migrate or run a seeder.

If you have not manually created these paths, phinx will ask you if it should create them automatically for you when creating a new migration or seeder.

If you have never heard of "database seeding" before, it is simply filling the database with test/dummy data, this is useful for development to automatically add test users or add example blog posts.

Writing Migrations

Now that we got our shit all configured, let's write a migration!

To add a migration we can use Phinx from the command line to scaffold out a migration class for us, write the following into your command line, note that the migration name should be CamelCasedLikeThisRightHere.

vendor\bin\phinx.bat create AddUsersTable

Which should output something similar to this:

Phinx by Rob Morgan - https://phinx.org. version 0.6.2

using config file .\phinx.yml
using config parser yaml
using migration path C:\web\htdocs\temp/db/migrations
using seed path C:\web\htdocs\temp/db/seeds
Create migrations directory? [y]/n y
using migration base class Phinx\Migration\AbstractMigration
using default template
created \db\migrations\20160630100412_add_users_table.php

When we open the \db\migrations\20160630100412_add_users_table.php file we should see something like this:

<?php

use Phinx\\Migration\\AbstractMigration;

class AddUsersTable 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
     *
     * The following commands can be used in this method and Phinx will
     * automatically reverse them when rolling back:
     *
     *    createTable
     *    renameTable
     *    addColumn
     *    renameColumn
     *    addIndex
     *    addForeignKey
     *
     * Remember to call "create()" or "update()" and NOT "save()" when working
     * with the Table class.
     */
    public function change()
    {

    }
}

Let's start by creating a table called users and add some common columns.

<?php

use Phinx\\Migration\\AbstractMigration;

class AddUsersTable extends AbstractMigration
{
    public function change()
    {
        $table = $this->createTable("users");

        // We don't need to add an "id" column, Phinx adds this by default.
        $table->addColumn("username", "string", ["length" => "200"]);
        $table->addColumn("password", "string", ["length" => "60");
        $table->create();
    }
}

This will create a new table called "users" and add two columns to them, username and password.

The first parameter for the addColumn method is the column name, the second is the column type, Phinx supports these column types.

The third parameter is the options, I chose to limit the length of the username to 200 characters, and the password to 60 characters (default length of the hash generated by PHP's hash_password function).

More options are available, you can read up on the various options in the documentation.

http://docs.phinx.org/en/latest/migrations.html#valid-column-options

To run this migration we simply write the following in the command line:

vendor\bin\phinx.bat migrate

This will run your migrations on the default environment (development by default), or you can specify the environment with the -e option:

vendor\bin\phinx.bat migrate -e production

This will make Phinx connect to your database and perform the actions you told it to do (create a users table with two columns: username and password).

Note: Phinx automatically adds an auto-incrementing column called ID to all tables since this is such a common operation, you can read more about this in the documentation.

Database Seeders with Phinx and Faker

Now that we've created our database table and columns, it's time to populate that table with some dummy test data, for this we are going to be using Phinx in conjunction with a PHP library called Faker, which is used to generate fake data.

Let's install faker via composer, it is the same thing as installing Phinx, type this in the command line:

composer require fzaninotto/faker

Now we have to create a new seeder, to do this with Phinx, type this in the command line:

vendor\bin\phinx.bat seed:create UserSeeder

Which should output something like this:

Phinx by Rob Morgan - https://phinx.org. version 0.6.2

using config file .\\phinx.yml
using config parser yaml
using migration path C:\\web\\htdocs\\temp/db/migrations
using seed path C:\\web\\htdocs\\temp/db/seeds
Create seeds directory? [y]/n y
using seed base class Phinx\\Seed\\AbstractSeed
created .\\db\\seeds\\UserSeeder.php

Open the generated UserSeeder.php file, it should look like this:

<?php

use Phinx\\Seed\\AbstractSeed;

class UserSeeder extends AbstractSeed
{
    /\*\*
     \* Run Method.
     \*
     \* Write your database seeder using this method.
     \*
     \* More information on writing seeders is available here:
     \* http://docs.phinx.org/en/latest/seeding.html
     \*/
    public function run()
    {

    }
}

Now write this:

<?php

use Phinx\\Seed\\AbstractSeed;

class UserSeeder extends AbstractSeed
{
    public function run()
    {
        // Get a new instance of Faker
        $faker = Faker\\Factory::create();

        // Create a new empty array.
        $users = [];

        // Loop through this 100 times to create 100 users
        for ($i = 0; $i < 100; $i++) {
            $users[] = [
                'username' => $faker->userName,
                'password' => password\_hash("password", PASSWORD\_DEFAULT)
            ];
        }

        // Insert all the generated users into the "users" table
        $this->insert("users", $users);
    }
}

Now we want to run this seeder to populate our users table with test users.

To run all of our seeders, type the following into the command line:

vendor\bin\phinx.bat seed:run

If you however want to only run one specific seeder, you can use the -s option like this:

vendor\bin\phinx.bat seed:run -s UserSeeder

You can also specify which environment you want to run the seeder in by using the -e option as shown previously:

bash vendor\bin\phinx seed:run -s UserSeeder -e development

This should output something similar to this:

Phinx by Rob Morgan - https://phinx.org. version 0.5.4

using config file .\\phinx.yml
using config parser yaml
using migration path C:\\web\\htdocs\\temp/app/db/migrations
using seed path C:\\web\\htdocs\\temp/app/db/seeds
using environment development
using adapter mysql
using database example

 == UserSeeder: seeding
 == UserSeeder: seeded 0.3029s

All Done. Took 0.3092s

In case of Fuckup, Rollback Migration!

If When you fuckup a migration for whatever reason, you can rollback a migration using the rollback command.

vendor\bin\phinx rollback -e development

If you want to rollback to a specific migration version then use the -t option, the version of a migration is the preceding number that is in the filename of a migration, example: 20160630100412_add_users_table.php the version is the part in bold.

$ phinx rollback -e development -t 20160630100412

Aaaaaand that is pretty much it, at least that is what I wanted to write about, the bare basics of database migrations with Phinx.

Using database migrations helps me severely with deploying new versions of my applications and saves me a lot of headaches, I hope that you will start using it and have as much use of it as I have.

To learn more about Phinx, please go read the documentation.

Thanks for reading.