Phinx: DB migrations

What is it:

PHP database structure and fixture migration method.

Official description:
Phinx allows developers to alter and manipulate databases in a clear and concise way. It avoids the use of writing SQL by hand and instead offers a powerful API for creating migrations using PHP code. Developers can then version these migrations using their preferred SCM system.

Why use it:

As programming moves forward with ORM methods like Doctrine 2 existing systems that don’t incorporate this seem like hard work to manage.
How do you pass any database changes to other developers and to live systems? Phinx, and its free.
You create your migration scripts that will add/edit/delete any table or content data you require. As other developers work on the project they run the migrations and keep everyones system in-sync.
So when you merge in your branch to a repository, the migration scripts come in too.
You also have the option to revert back in time to see how the database was before a certain point in time.

Install

curl -s https://getcomposer.org/installer | php
php composer.phar require robmorgan/phinx
php composer.phar install

Test

php vendor/bin/phinx
This should show you the options available if installed correctly.

Setup connection to mysql

php vendor/bin/phinx init .
nano phinx.yml

Change the settings to match access to your database
Take note of default_database: as this states the default database to connect to.
Phinx will create a new table to keep track of which migrations you have completed.
You are now ready to start

Create a new migration

php vendor/bin/phinx create MyNewMigration
It has to be CamelCase with no underscores.
This will create a new migration in the format YYYYMMDDHHMMSS_my_new_migration.php where the first 14 characters are replaced with the current timestamp down to the second.

If you work with tickets (like most development teams do) then you should have the ticket name in there too.
i.e.
php vendor/bin/phinx create Tick1CreateUserTable
Enter “y” to confirm creating the migration

You will now be told where to migration is available to edit
i.e. created ./migrations/20150307195045_tick1_create_user_table.php

Function options

up() – The migration up you require.
down() – The revert migration to put your database back to what it was before the migration.
change() – This does both the up and down migration for you (limited options)

So if you use change() you can remove the up() and down() functions.
If you use up() and down() you can leave change() commented out or just remove it.

Change() options available:

  • createTable
  • renameTable
  • addColumn
  • renameColumn
  • addIndex
  • addForeignKey

Note:
When writing migrations don’t try to fit too much in one migration. If you are adding lots of tables then you can break them up into more migration files.

Example migration

change()

up() and down()
Does exactly the same as the above change() call.

Go to http://docs.phinx.org/en/latest/index.html to see all migration options.

Running the migrations

As a timestamp is added to the start of the migration file, this keeps all migrations in order so steps are not missed

Migrate forwards
php vendor/bin/phinx migrate

List revisions:

php vendor/bin/phinx status

Rollback to revision:

php vendor/bin/phinx rollback -t 20150307195045

Roll back to the start:
php vendor/bin/phinx rollback -t 0
To change the database connection use the -e option:
i.e.
-e production

Hope this helps you easily manage your database migrations.

2 comments for “Phinx: DB migrations

  1. phpPhil
    May 27, 2015 at 12:57 am

    We are considering using Phinx, but there is also Doctrine Migrations which seems to be popular in the Symfony community.

    Just interested, what tipped your decision over to use Phinx and what are in your opinion the pros and cons for either one?

  2. GlynRob
    June 4, 2015 at 10:05 pm

    If you are using a framework that supports an ORM then use that. Symfony is a great example.
    If you are using an older codebase with more traditional model schemas, then Phinx would be suitable. I consider Phinx a solution when you don’t have an ORM option available easily.

Leave a Reply

Your email address will not be published. Required fields are marked *