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:

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:

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:

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

 

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:

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:

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.

Which should output something similar to this:

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

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

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:

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

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:

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

Which should output something like this:

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

Now write this:

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:

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

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

This should output something similar to this:

In case of Fuckup, Rollback Migration!

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

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.


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.

Leave a Reply

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