Database Versioning with Ladder Migrations

Version control systems are invaluable for tracking changes in your code, particularly when you’re working in a team. However, most applications don’t consist solely of application code. Managing changes to the database has always been a little more challenging, particularly when you’re adding new features which require changes to the schema.

Suppose you’re working on a module and realize one of the database tables needs an additional column. You might be tempted to open up a database query tool or the command line and simply add the column. However, this doesn’t leave a record of the change in the same way as version-controlled application code would. This gets exacerbated when working in a team – if a colleague pulls your code changes without running those same database updates, then it’s entirely possible their version of the application will break. This becomes even more problematic when you’re releasing a product update, when it could break the application not just for your fellow developers, but for your users.

One solution is to move responsibility for creating and modifying the database schema into code, using migrations. That way, changes can be managed along with the rest of your application, and features we take for granted in version control – such as being able to compare versions and keep an audit trail – can be used for database changes. It also allows these changes to be seamlessly incorporated into releases, since they can be made part of the same branch or tag.

Many major frameworks have their own migration implementation, but for those that don’t – and if you’re not using any framework – there’s Ladder.

Introducing Ladder

Ladder is a tool for creating, running and managing database migrations. A migration is simply a PHP class, and can therefore be checked into version control along with the rest of your application code.

You can make as many alterations to the schema in a single migration as you wish, although it’s probably best to restrict a migration to a single table or feature.

Migrations are always run in sequence. So, suppose you write one to create a products table, and a few weeks down the line create a new migration to add an additional column to it. Trying to run the latter before the former will produce an error. Ladder gets around this by numbering migrations sequentially, as well as storing a record of what migrations have been run (and when) in the database itself.

Installation

The simplest – though admittedly least elegant – way to install it is to download or clone it, and place it in a directory called ladder in your project root. The problem with using Composer is that running composer update will overwrite your configuration files.

There are five configuration files, each of which needs to be manually created; the easiest way is to copy the provided examples:

You’ll probably only need to modify database.php – which contains the database connection details – and editor.php, in which you can specify your preferred text editor, as well as opt to open new migrations automatically upon creation.

Creating a Migration

Let’s begin by creating a migration to create the users table.

On the command line:

php ladder/ladder.php create create_users_table

This will create a file called ladder/migrations/00001_create_users_table, which by default contains a basic structure with some example calls commented out. If you’ve set auto-edit to true in ladder/config/editor.php, this file will immediately open in the specified text editor. You can call the migration whatever you wish, but it’s helpful to be able to see at a glance what a migration does from its filename.

Here’s how that file might look (I’ve removed the lines which are commented out, for clarity):

The up() method is called when a migration is run, and the down() method when it’s rolled back – so it always needs to do the reverse of the up() method. In this example, the up() method creates a table called users, and the down() method drops it.

The create_table() method returns a reference to the new table, and the Table class has a column() method to add a new column. This has a fluent interface, so you can chain them together to create multiple columns at the same time. You’ll notice that there’s no ID column – this gets created automatically – i.e., an auto-incrementing, integer-based primary key called id.

table() returns a reference to a table, but it also creates it if it doesn’t already exist – therefore, you could safely change the create_table() call to table()

migrations is created automatically, and is used to keep track of which migrations have been run migrations_kvdata is also created for you, and can be used to for arbitrary key/value storage by your migrations users and roles are the tables we’ve just added.

If you paid close attention to the roles migration, though, you’ll notice that it’s created a column called nme instead of name. At this point we can fix this by “undoing” the migration, modifying the class and then running it again. To rollback the migration:

php ladder/ladder.php remove 2

The number 2 indicates which migration to rollback to – it’s the prefix of the migration filename, without the leading zeroes.

Now you can simply make the correction, then run the migration again:

php ladder/ladder.php migrate

Because the migrations table has stored a record of what’s been run and what hasn’t, you don’t need to worry that your first migration will be re-run.

There’s also a quicker way, by using reapply instead:

php ladder/ladder.php reapply 2

This will call the second migration’s down() method, then its up() method, all in one go.

Now let’s suppose that sometime down the line we develop a new feature which requires that the users table includes a status field. To do this, we need to create a new migration:

diff and diff-save

You can dump the state of the database to a file using the diff-save command:

php ladder/ladder.php diff-save

This will create a file containing the current state of the database as a serialised array in ladder/cache/ladder. You can now make changes to the schema manually, and then revert the database back to the locally stored state with the diff command:

php ladder/ladder.php diff

Version

Finally, you can get the version of Ladder you’re using with the following command:

php ladder/ladder.php version

Summary

In this article I’ve introduced Ladder, for maintaining database schemas and pre-populating data. To explore further, take a look at the Bitbucket page and the website.

Lukas is a freelance web and mobile developer based in Manchester in the North of England. He's been developing in PHP since moving away from those early days in web development of using all manner of tools such as Java Server Pages, classic ASP and XML data islands, along with JavaScript - back when it really was JavaScript and Netscape ruled the roost. When he's not developing websites and mobile applications and complaining that this was all fields, Lukas likes to cook all manner of World foods.

Free Guide:

7 Habits of Successful CTOs

"What makes a great CTO?" Engineering skills? Business savvy? An innate tendency to channel a mythical creature (ahem, unicorn)? All of the above? Discover the top traits of the most successful CTOs in this free guide.