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

Ladder can be downloaded from Bitbucket or via Composer. It requires a few steps to get up and running, however.

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:

cp ladder/config/config.php.example ladder/config/config.php
cp ladder/config/database.php.example ladder/config/database.php
cp ladder/config/diff.php.example ladder/config/diff.php
cp ladder/config/editor.php.example ladder/config/editor.php
cp ladder/config/table.php.example ladder/config/table.php

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):

class Create_Users_Table_Migration_00001 extends Migration {
    protected $min_version = '0.8.1';

    public function up() {
        $this->create_table('users')
            ->column('email', 'varchar', array('limit' => 128, 'null' => FALSE))
            ->column('password', 'varchar', array('limit' => 32, 'null' => FALSE));
    }

    public function down() {
        $this->table('users')->drop();
    }   
}

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()

Let’s create another migration, this time to create the roles table:

php ladder/ladder.php create create_roles_table

The file itself:

class Create_Roles_Table_Migration_00002 extends Migration {
    protected $min_version = '0.8.1';

    public function up() {
        $this->table('roles')
            ->column('nme', 'varchar', array('limit' => 128, 'null' => FALSE));            
    }

    public function down() {
        $this->table('roles')->drop();
    }   
}

Now you’ll want to actually run the migrations. To do so:

php ladder/ladder.php migrate

If you take a look in the database, you’ll find four tables:

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:

php ladder/ladder.php create add_status_to_users_table

The migration will look something like this:

class Add_Status_To_Users_Table_Migration_00003 extends Migration {
    protected $min_version = '0.8.1';

    public function up() {
        $this->table('users')
            ->column('status', 'integer', array('null' => FALSE, 'default' => 0));            
    }

    public function down() {
        $this->table('users')->drop_column('status');      
    }
}

As before, run with:

php ladder/ladder.php migrate

Now, if you check your database you’ll see that the users table has a new column.

Database Seeding

In addition to writing migrations to manage the database schema, you can also use Ladder to pre-populate (i.e., seed) the database, too.

For example, you could extend the create_users_table migration to create a default root user account:

public function up() {
    // as before
    $root_user_id = $this->table('users')
        ->insert(array(
                'email' => 'root@example.com',
                'password' => NULL,
            ))
            ->insert_id;

    $this->set('root_user_id', $root_user_id);
}

public function down() {
    // as before
    if ((bool) $user_id = $this->get('root_user_id')) {
        $this->table('users')->delete(array(
            'id' => (int) $user_id,
        ));
    }
}

Notice that we’re using the set() and get() methods to take advantage of Ladder’s key/value storage, storing the user ID so we can refer to it later.

You can also import data from CSV files in your migrations, for example:

$this->table('users')->import_csv('/path/to/users.csv');

To update rather than insert CSV data, you’d do this:

$this->table('users')->import_csv('/path/to/users.csv', TRUE, array('email'));

In this case, the send parameter indicates we want to run an UPDATE, and the third contains a list of key fields for determining which records to update.

Adding additional databases

You’ll notice the default database configuration file ladder/config/database.php demonstrates how you can add additional database connections; perhaps for staging or live databases. For example:

$config['default'] = array(
    'hostname'  => 'localhost',
    // etc
);

$config['staging'] = array(
    'hostname'  => 'server2.myreal.host.name',
    // etc
);

$config['live'] = array(
    'hostname'  => 'server2.myreal.host.name',
    // etc
);

You can specify the connection to use when running Ladder like this:

php ladder/ladder.php --config=live

Additional Commands

Status

You can get the status of the database using the status command:

php ladder/ladder.php status

Sample output:

Migration Status
Latest migration is 3
Connecting to localhost:3306... Connected. Server version 5.1.62.
<   ladder: 1
Completed in 0.285s

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.