Database Versioning with Ladder Migrations

Share this article

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.

Frequently Asked Questions on Database Versioning and Ladder Migrations

What are the key benefits of using database versioning tools?

Database versioning tools offer several benefits. They help maintain consistency and control over the database schema, which is crucial when multiple developers are working on the same project. They also provide a history of changes, allowing developers to track and understand the evolution of the database over time. Additionally, these tools can automate the process of applying changes to the database, reducing the risk of human error and saving valuable time.

How does ladder migration work in database versioning?

Ladder migration is a strategy used in database versioning where changes are applied in small, incremental steps. Each step or “rung” of the ladder represents a change to the database schema. This approach allows for greater control and flexibility, as developers can easily move up or down the ladder, applying or undoing changes as needed.

What are some popular database versioning tools?

There are several popular database versioning tools available, each with its own strengths and features. Some of the most commonly used tools include Liquibase, Flyway, and Dolt. Liquibase is known for its flexibility and support for a wide range of database systems. Flyway is praised for its simplicity and ease of use. Dolt, on the other hand, is a relatively new tool that combines the features of a traditional database with those of a version control system.

How do I choose the right database versioning tool for my project?

Choosing the right database versioning tool depends on several factors, including the specific needs of your project, the database systems you’re using, and your team’s familiarity with the tool. It’s important to consider the tool’s compatibility with your database systems, its ease of use, the level of control it offers, and the support and resources available.

Can I use source control for my database items?

Yes, using source control for database items is a common practice. It allows developers to track changes to the database schema, making it easier to manage and coordinate changes. This is especially useful in a team environment, where multiple developers may be working on the same database.

What are the challenges of database versioning?

Database versioning can present several challenges. One of the main challenges is managing conflicts when multiple developers are making changes to the same database schema. Another challenge is ensuring that changes are applied in the correct order to maintain the integrity of the database. Additionally, database versioning can be complex and time-consuming, especially for large databases with many tables and relationships.

How can I manage conflicts in database versioning?

Managing conflicts in database versioning typically involves using a version control system that supports conflict resolution. This can be done through a process known as merging, where the system combines changes from different developers and resolves any conflicts. In some cases, manual intervention may be required to resolve conflicts.

How does database versioning support database testing?

Database versioning supports database testing by allowing testers to easily create and manage different versions of the database. This makes it possible to test different scenarios and configurations without affecting the production database. Additionally, database versioning can help ensure that tests are run against the correct version of the database, reducing the risk of errors and inconsistencies.

Can I use database versioning for non-relational databases?

Yes, database versioning can be used for both relational and non-relational databases. However, the specific tools and techniques used may vary depending on the type of database. It’s important to choose a database versioning tool that supports the type of database you’re using.

How does database versioning contribute to DevOps practices?

Database versioning plays a crucial role in DevOps practices by facilitating collaboration and coordination among developers, testers, and operations teams. It allows changes to the database schema to be tracked and managed in a controlled manner, reducing the risk of errors and inconsistencies. This can help improve the efficiency and reliability of the software development and deployment process.

Lukas WhiteLukas White
View Author

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.

databasedatabase migrationdatabase versioningmigrationPHPvcsversion controlversioning
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week