Too often I see people managing their application database schemas with an SQL file or two, sometimes with nothing version controlled at all! This is just an accident waiting to happen - as an application grows, the number of installations of the database grows with it, and so do the number of versions of the schema as the codebase changes. How do you manage which database is at what version and ensure that it matches the codebase which is using it? The answer is by writing migrations.

Migrations are an extremely effective to manage incremental changes in a schema using code, and can be run forwards as well as in reverse. So what tools do we have in the PHP community to acheive this? There are actually a multitude of tools, there is one built into Doctrine, they are also built into the Laravel framework, amongst several others. However, these tools are quite tightly coupled to their respective frameworks. Phinx is a fantastic schema migration tool which isn't tightly coupled to anything and is compatible with MySQL, PostgreSQL, SQLite and SQL Server.

This tutorial walks through installing and setting up Phinx and getting some simple migrations running, as well as rolling back changes.

Setting up Phinx

So firstly we need to install Phinx, this can be done by using composer (if you are unfamiliar with composer, you need to check out https://getcomposer.org/ - it is a vital tool for managing PHP dependencies).

composer require robmorgan/phinx

This will install Phinx into your vendor directory. If you are for some reason unable to use composer the source code for Phinx is available at https://github.com/robmorgan/phinx

Next we need to initialise Phinx by running php vendor/bin/phinx init - this creates a Phinx config file called phinx.yml (check out http://yaml.org/ if you aren't familiar with YAML)

In this config file we can specify the different database configurations for each environment, such as development and production, which are provided as an example in the file by default. We will use these two environments for our purposes.

phinx.yml

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

You will notice a few things:

  • The paths: migrations: setting. This is the path to the directory in which we will store our migration files. We will leave this as the default

  • The environments: default_migration_table: setting. This is the name of the table which is used to store the log of changes to in the database, again we will leave this as default.

  • The environments: default_database: setting. This defines the default environment to use when running migrations, i.e. the one which is run when an environment isn't specified. We'll leave this as development.

  • It the goes on to specify a couple of environments: development and production. In each you can specify the details for connecting to the database (if you don't want to save you DB details within your version control you can use environment variables - see http://docs.phinx.org/en/latest/configuration.html#external-variables)

Writing your first migration

Now we are all set up, we can create our first migration. Phinx comes with a nice tool to generate our migration file for us, which saves a little bit of work.

Run php vendor/bin/phinx create FirstMigration

It will prompt you to check if its ok to create the migrations/ directory, press enter to confirm. This creates a file under migrations/ with a timestamp and the friendly name you specified. Open this file and we will begin to write our initial database schema.

the change method in migrations/XXX_first_migration.php

public function change()
{
    $books_table = $this->table('books');
    $books_table
        ->addColumn('title', 'string')
        ->addColumn('author_name', 'string')
        ->addColumn('catalogue_no', 'integer')
        ->create();
}

Here we have a single table, books which we define the fields of using Phinx's object syntax. We first create the table using the table method, followed by using the addColumn method to add each column. Note that an auto incrementing primary key for the table called id is created. To commit the and run the changes, the create method is then called. And that's it, that's all we need to create this table!

Running the migration

Running Phinx migrations is extremely easy, and the most basic (and most used) command is simply php vendor/bin/phinx migrate - this runs all the migrations from the current version up until the latest version, using the default environment (development in our case).

There are two possible arguments you are likely to use when running migrations if you are not simply running migrate:

  • -e [environment_name] tells Phinx which environment to use for database configuration. For example php vendor/bin/phinx migrate -e production

  • -t [target_datetime] tells Phinx to migrate forwards to a given migration, defined by its datetime, for example php vendor/bin/phinx migrate -t 20151123211207

After running our migration the database will contain the books table, as well as the phinxlog table, which will contain a single record with the time and version of the migration. This is used by Phinx to detect which version the database is on. Now we have a working migration, this is a great time to commit to version control so that this change is stored against the version of the code which uses it.

Adding schema changes

Now we have our new books table up and running, but we decide we want to store our authors in a different table to they can be used elsewhere. So to create the new table we need create a new migration.

Run php vendor/bin/phinx create AddAuthorTable

This will create a new migration file in the migrations/ directory. In the change method of this new file we want to define what will change in our database.

the change method in migrations/XXX_add_author_table.php

public function change()
{
    $books_table = $this->table('books');
    $books_table
        ->addColumn('author_id', 'integer')
        ->update();

    $authors_table = $this->table('authors');
    $authors_table
        ->addColumn('first_name', 'string')
        ->addColumn('last_name', 'string')
        ->create();
}

Run this migration on your development environment by simply running:

php vendor/bin/phinx migrate

Your books table will now have a new column author_id, and you will have a new table called authors. You will also notice that your phinxlog has a new line giving the date and time of the new migration being run.

Rolling back changes

Ok, so you've got your books table, and your fancy authors table, but you realise things are getting complex, so you decide that you want to go back to just having your books table. You rollback your code to a previous version, but the problem you now have is that your database schema is not inline with the code. You need to rollback your schema version by using the migrations which you have written.

  • First get the datetime of the migration you would like to roll back to.

  • Run php vendor/bin/phinx rollback to go back to the previous version, or you can specify a target migration to go back to using it's datetime and the -t flag, for example php vendor/bin/phinx rollback -t 20151123211207. You can also choose the environment to run in by using the -e flag as you can with the migrate command.

Once you have run your rollback, you will notice that the authors table is gone, and the books table no longer has an author_id column. Also, the phinxlog table will have removed the rows for the rolled-back migrations.

In conclusion

For more detail about Phinx and to discover its full functionality, you should take a looks at the docs at http://docs.phinx.org/en/latest/index.html. Also the code used above is available at https://github.com/gaw508/tutorials/tree/master/database-schema-migrations-php-phinx-tutorial

I hope you found this crash course on using Phinx useful, and as ever if you enjoyed it please sign up to my newsletter using the form on the site.