Code Bright: Migrations

← Back to Index

This title was written for Laravel version 4. If you're looking for material on the latest version of Laravel, then please check out Code Smart.

We have a rather impressive system at Dayle Manor. A system that will allow all of the days tasks to be completed without any fuss by my army of red panda butlers. Let me share it with you. Here's a list of tasks for my butlers. You can give them a hand you like?

  • 9:00 AM - Wash and dress Dayle.
  • 10:00 AM - Cook and grill a number of rare and exotic meats for breakfast.
  • 12:00 PM - (Lunch) The pandas will climb a tree and sleep for a while.
  • 02:00 PM - Polish the Apple hardware collection.
  • 04:00 PM - Prepare the writing throne for the next Code Bright chapter.
  • 09:00 PM - Drag sleeping Dayle from the writing throne, and tuck him into bed.

So that's my list for the red pandas. They have quite a busy day, and I don't know what I would do without them. The problem is that the list has a very specific order. We don't want the pandas to tuck me into bed before I have visited the writing throne, otherwise you won't get a new chapter. Also, there wouldn't be a lot of point in doing these tasks twice. The pandas need to ensure that they are done once, sequentially.

The pandas are so smart, it was them that came up to the solution to the problem all on their own. I gave them the original list on a notepad with a pencil, and well, they get rather excited when you give them gifts. There was a lot of playful rolling. Anyway, they decided that they would write their own list, double the fun right?

The pandas decided to write a secondary list. Whenever they completed a task, which of course were completed in time order from the first list, they would write the time and name of the task on the second list. This way, the same task would never be repeated.

Not a bad idea, I have to admit. Fortunately some clever chaps invented a similar idea for databases. Let's take a look at migrations.

Basic Concept

When building your database, you could create it's structure by hand. Type up some nifty SQL to describe your columns, but what happens when you accidentally drop the database? What if you are working as a team? You don't want to have to pass your SQL dumps around the team all the time to keep the database synchronized.

That's where migrations come in handy. Migrations are a number of PHP scripts that are used to change the structure or content of your database. Migrations are time stamped, so that they are always executed in the correct order.

Laravel keeps a record of which migrations have already been executed within another table on your default database connection. This way, it will only ever execute any additional migrations that have been added.

Using migrations, you and your team will always have the same database structure, in a consistant, stable state. You know what? Actions speak louder than words. Let's create a new migration and start the learning process.

Creating Migrations

To create a migration we need to use the Artisan command line interface. Go ahead and open a terminal window, and navigate to the project folder using whichever shell you call home. We learned about schema building in the previous chapter, and I told you there was a better place to use the schema. Well of course I was talking about migrations. Let's recreate the schema build that we used to create the users table. We will start by using Artisan to build a create_users migration.

$ php artisan migrate:make create_users
Created Migration: 2013_06_30_124846_create_users
Generating optimized class loader
Compiling common classes

We call the Artisan migrate:make command, and provide a name for our new migration. Laravel has new generated a new migration template within the app/database/migrations directory. The template will be located in a file named after the parameter that you supplied to the migrate:make command, with an attached timestamp. In this instance, our template is located within the following file.

app/database/migrations/2013_06_30_124846_create_users.php

Let's open up the file in our text editor and see what we have.

<?php

use Illuminate\Database\Migrations\Migration;

class CreateUsers extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        //
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
    }

}

Here we have our migration class. Now it's important that you always use the Artisan command to generate migrations, you don't want to risk breaking the timestamps and thus the history of your database structure. Be a good reader, use the command.

Within the migration class we have two public methods, up() and down(). Now imagine a line between these two methods, or write one within a comment if you didn't learn about imagination from our friend Barney.

You see either side of the line, a direct opposite must happen. Whatever you do in the up() method, you must undo within the down() method. You see, migrations are bi-directional. We can run a migration to update the structure or content of our database, but we can also undo that migration to revert it to its orignal state.

First let's fill in the up() method.

<?php

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('users', function($table)
    {
        $table->increments('id');
        $table->string('name', 128);
        $table->string('email');
        $table->string('password', 60);
        $table->timestamps();
    });
}

Hopefully there's nothing confusing within this schema construction snippet. If you don't understand any of it, simply take another look at the 'Schema Builder' chapter.

Right, we know that what goes up, must come down. For that reason, let's tackle the down() method and create the inverse of the structure change within the up() method.

Here we go...

<?php

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::drop('users');
}

Alright, alright, I suppose it's not the direct opposite. I'm guessing you wanted to drop all of the columns individually and then the table. Well, you see, they would both end in the same result. The users table would be dropped. So why not do it in one line?

Before we continue to the next section, let's take a look at a few tricks that relate to creating migrations. Using the --create and --table switches on the migrate:make command we can automatically create a stub for the creation or updating of a new table.

We simply run...

php artisan migrate:make create_users --create="users"

...and we receive the following migration stub.

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsers extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function(Blueprint $table)
        {
            $table->increments('id');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('users');
    }

}

Great! That shortcut has saved us a heap of time. You will notice that as well as adding the Schema::create() and Schema::drop() methods for our new table, Laravel has also added the increments() and timestamps() methods. This makes it easy to create Eloquent ORM compatible models very quickly. Don't worry too much about Eloquent for now, we will discover all about it soon enough.

One final trick for the creation of migrations, is how to store them in a different location to the default app/database/migrations directory. We can use the --path switch to define a new location for our migration class.

$ php artisan migrate:make create_users --path=app/migs
Created Migration: 2013_06_30_155341_create_users
Generating optimized class loader
Compiling common classes

Now our migration will be created within the app/migs directory relative to the root of our project. However, when running your migrations, Artisan won't look in this new location by default, so be sure to let it know where to find you migrations,. We will discover more about this within the next section.

Running Migrations

We went to all this effort to create our new migration, it would be a shame not to run it wouldn't it? Let's prepare the database to use migrations. Remember how I told you that Laravel uses a database table to record the status of its migrations? Well first we need to create that table.

Now you can call the migrations table whatever you like. The configuration for the table name is located within app/config/database.php.

/*
|--------------------------------------------------------------------------
| Migration Repository Table
|--------------------------------------------------------------------------
|
| This table keeps track of all the migrations that have already run for
| your application. Using this information, we can determine which of
| the migrations on disk have not actually be run in the databases.
|
*/

'migrations' => 'migrations',

Simply change the migrations index to the name of the table which you wish to use to track your migration status. A sensible default has been provided.

We can install our migrations table by running another Artisan command. Let's run the install command now.

$ php artisan migrate:install
Migration table created successfully.

Now let's examine our database, and look for the migrations table to see what has been created.

mysql> describe migrations;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| migration | varchar(255) | NO   |     | NULL    |       |
| batch     | int(11)      | NO   |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

A new table with two fields has been created. Don't trouble yourself with the implementation of the migrations table, just rest assured that it has been created, and the migrations system has been installed.

Okay, well I have lied to you again. I don't know how this keeps happening? Perhaps I should visit a psychiatrist or something. Well anyway, I told you that we needed to install the migrations table, and I lied.

You see, Laravel will automatically create the table for us if it doesn't exist when your migrations are executed. It will install the migrations system for you. At least you know about the migrate:install command now though right? It's almost as if I planned this whole deception...

Right, let's get started and run our migration for the first time. We can use the migrate command to do this.

$ php artisan migrate
    Migrated: 2013_06_30_124846_create_users

The output from the command is a list of migrations that have been executed. Let's take a look at our database to see if our 'users' table has been created.

mysql> describe users;
+------------+------------------+
| Field      | Type             |
+------------+------------------+
| id         | int(10) unsigned |
| name       | varchar(128)     |
| email      | varchar(255)     |
| password   | varchar(60)      |
| created_at | timestamp        |
| updated_at | timestamp        |
+------------+------------------+
6 rows in set (0.01 sec)

I shortened the table a little to be more inline with the book's formatting, but you can see that our 'users' table has been created correctly. Awesome!

Now let's add a 'title' column to our users table. You might be tempted to open up the migration that we have already made and update the schema to include the new column. Please don't do that.

You see, if one of your teammates had been working on the project, and had already ran our first migration then he wouldn't receive our change, and our databases would be in different states.

Instead, let's create a new migration to alter our database. Here we go.

$ php artisan migrate:make add_title_to_users
Created Migration: 2013_06_30_151627_add_title_to_users
Generating optimized class loader
Compiling common classes

You will notice that I have given the new migration a descriptive name, you should follow this pattern. Let's alter the schema of our users table within the up() method to add the title column.

<?php

use Illuminate\Database\Migrations\Migration;

class AddTitleToUsers extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function($table)
        {
            $table->string('title');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
    }

}

Great, that should add the column that we need to our 'users' table. Now say it with me.

What goes up, must come down.

You're right, we need to provide the down() method for this migration class. Let's alter the table to remove the 'title' column.

<?php

use Illuminate\Database\Migrations\Migration;

class AddTitleToUsers extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function($table)
        {
            $table->string('title');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function($table)
        {
            $table->dropColumn('title');
        });
    }

}

Perfect, now Laravel is able to execute our migration, and also revert all changes if needed. Let's execute our migrations again.

$ php artisan migrate
    Migrated: 2013_06_30_151627_add_title_to_users

Laravel knows that our previous migration has already been executed, and so it only executes our latest migration class. Let's examine the table once more.

mysql> describe users;
+------------+------------------+
| Field      | Type             |
+------------+------------------+
| id         | int(10) unsigned |
| name       | varchar(128)     |
| email      | varchar(255)     |
| password   | varchar(60)      |
| created_at | timestamp        |
| updated_at | timestamp        |
| title      | varchar(255)     |
+------------+------------------+
7 rows in set (0.00 sec)

As you can see, our new column has been added to the users table. If our migration was commited and shared with the rest of the team, they could simply run migrate to bring their own databases in line with the new structure.

If for some reason we do need to alter one of our existing migration files, we can use the migrate:refresh Artisan command to revert all migrations, and then run them once more. Let's try this now with our users table.

$ php artisan migrate:refresh
Rolled back: 2013_06_30_151627_add_title_to_users
Rolled back: 2013_06_30_124846_create_users
Nothing to rollback.
Migrated: 2013_06_30_124846_create_users
Migrated: 2013_06_30_151627_add_title_to_users

Our migrations have been rolled back using the down() methods, and then executed once again in the correct order using their respective up() methods. Our database is once again in its perfect state.

Remember how we used the --path switch in the previous chapter to write our migrations to a new location on the filesystem? Well I promised you that I would show you how to execute them. I may lie once in a while, but I never go back on a promise. Let's take a look at how we can execute our non standard migrations.

$ php artisan migrate --path=app/migs
Migrated: 2013_06_30_155341_create_users

See, it's easy? We just use the --path switch again to specify the location where our migrations are stored relative to the application root.

I told you that migrations are bi-directional, so that means that we must be able to roll them back? Let's move on.

Rolling Back

Rolling, rolling, rolling on the riverrrrr...

Sorry about that, I was a little distracted. Let's see... ah yes! Rolling back migrations. We know that we can use migrate to execute our migrations, but how do we roll them back?

Well let's assume that we used the migrate command to restructure our database based upon one of our teammates migration. Unfortunately, our friends schema changes have broken some of our code, leaving our application broken.

We need to rollback the changes that our teammate has made. To do this we can use the rollback command. Let's give it a try.

$ php artisan migrate:rollback
Rolled back: 2013_06_30_151627_add_title_to_users

When we use the rollback command, Laravel rolls back only the migrations that were ran the last time we used migrate. It's as if the last time that we ran migrate never happened.

If we want to roll back all migrations, we can use the reset command.

$ php artisan migrate:reset
Rolled back: 2013_06_30_151627_add_title_to_users
Rolled back: 2013_06_30_124846_create_users
Nothing to rollback.

You should note that the reset command will not remove our migrations table.

Migration Tricks

Oh, you want more do you? I see. Well don't worry, I'm not going to hold anything back. Let's learn a few extra features of the migrations system.

Do you remember the array of connections that we discovered in the database configuration file at app/config/database.php? We can perform our migrations on another connection by supplying the --database switch to any of the migration commands, like this:

$ php artisan migrate --database=mysql
Migrated: 2013_06_30_124846_create_users
Migrated: 2013_06_30_151627_add_title_to_users

Now our migrations will be performed on the connection that we nicknamed mysql within the configuration file.

Hrm... you still don't sound impressed? Well alright, I have another trick for you. Sometimes I think that I spoil you... but I have to admit it. You are a great listener.

We can execute our migrations without altering the database, and we can see the intended SQL queries that are the result of our migrations. This way we can check to see what the next migration will do, without risking any damage to our database. This is really useful for debugging.

To see the intended SQL result of a migration command, just add the --pretend switch. Here's an example.

$ php artisan migrate --pretend
CreateUsers: create table `users` (`id` int unsigned not null
auto_increment primary key, `name` varchar(128) not null,
`email` varchar(255) not null, `password` varchar(60) not null,
`created_at` timestamp default 0 not null, `updated_at` timestamp
default 0 not null) default character set utf8 collate utf8_unicode_ci
AddTitleToUsers: alter table `users` add `title` varchar(255) not null

Now we can see the queries that would have been executed against our database if the --pretend switch wasn't provided. Neat trick, right?

Yep, you got me...

I told you so!

In the next chapter we will be taking a look at the Eloquent ORM. Eloquent is a wonderful method of representing your database rows as PHP objects, to that they fit in nicely with object-oriented programming.

My books are available online for free to encourage learning. However, if you'd like for me to keep writing, then please consider buying a digital copy over at Leanpub.com.

It's available in PDF, ePub, and Kindle format, and contains a bunch of extras that you won't find on the site. I have a full-time job, and I write my books in my spare time. Please consider buying a copy so that I can continue to write new books from the comfort of my sofa!