PHP - - By Alexander Cogneau

For those who struggle with maintaining their database schema, or who have problems applying updates and often revert them, there is a solution. Laravel, the MVC framework which I wrote about previously, offers you migrations.

In short, migrations are files which contain a class definition with both an up() and a down() method. The up() method is run when the migration is executed to apply changes to the database. The down() method is run to revert the changes. If you need to update the database, you just create a new migration and voilà. Not happy with it? Revert it and you’re back on track.

It all seems pretty cool, doesn’t it? But how does it work for Laravel? First you have to configure your database connection, and then you use Artisan, Laravel’s command line interface, to install the migrations table and run, revert, create… migrations.

Open a console and navigate to the root directory of your Laravel install. Run the following command:

php artisan migrate:install

This command causes Artisan to create a special table in your database to keep track of what migrations have already been executed.

To create a new migration, run this command:

php artisan migrate:make create_users_table

This creates the migration file which handles the users table. You can find your migration file inside the application/migrations folder. Artisan adds the date and time of the command’s execution as a prefix to the file, so the file would be named something like “2012_07_25_071925_create_users_table.php”. Be sure to use a descriptive name so that it’s clear what the migration does from just a glance.

Open the file and you’ll see a class with the two methods up() and down().

<?php
class Create_Users_Table
{
    public function up() {
        Schema::create("users", function($table) {
            $table->increments("id");
            $table->string("username", 32);
            $table->string("email", 320);
            $table->string("password", 64);
            $table->timestamps();
        });
    }

    public function down() {
        Schema::drop("users");
    }
}

The up() method runs when the migration is executed and creates the users table which holds five columns. The first is an auto-incrementing ID column, followed by VARCHAR columns for a username, email, and password. The first parameter to string() is the name of the column (e.g. “username”) and the second is the size of the column (e.g. 32 characters long). The final columns are created by the timestamps() method which creates “created_at” and “updated_at” columns.

The down() method is simpler than its predecessor and simply tells the database to drop the users table.

Now this file won’t do anything if it just sits there. To execute all outstanding migrations, run:

php artisan migrate

Currently it is not possible to run a specific migration. You can however run all the migrations in the application folder by the command:

php artisan migrate application

You can do the same for a specific bundle by using the same command but with the bundle’s name.

Now let’s say you realize that you made a design error and you want to roll back the last migration run. Simply type:

php artisan migrate:rollback

It’s not yet possible to automatically roll-back to a point before a specific migration, so you’ll have to run the command repeatedly until you reach that migration. You can however reset all migrations that you’ve ever ran just by running:

php artisan migrate:reset

As it stands now, the database would allow users to register the same username or email address any number of times. We want to restrict that and only allow an address or username to be used once. Of course, we don’t want to roll back the previous migration because we would lose data if we did, so instead we create a new one.

php artisan migrate:make users_add_username_email

The up() method should add a unique index on the fields, like this:

<?php
public function up() {
    Schema::table("users", function($table) {
        $table->unique("username");
        $table->unique("email");
    });
}

In the <code>down()</code> method, you want simply to remove the indexes.

<?php
public function up() {
    $table->drop_unique("username");
    $table->drop_unique("email");
}

That’s it! I hope you find Laravel’s migrations a solution to avoid all the hours of pain you’ve gone through managing your database schema. See you next time!

Image via Fotolia

Sponsors