Please note that this chapter was written for VERSION 3 of the Laravel PHP Framework.
Originally I had attempted to include a guide to the Fluent Query Builder with this chapter, but I now feel that the chapter has become too long, so I have decided to cover database set-up and migrations only in this chapter. Get ready for a nice long explanation of Fluent in the next chapter.
Migrations are one of my favourite Laravel features. I hate writing SQL, and the Schema class allows me to create my tables easily without writing a single line of that foul "language"! Not only that, but the Schema code also looks absolutely beautiful and reads like a book.
If you have not encountered migrations before, they are a way of describing changes to your database in files so that different installations / development copies of your application are aware of the current schema. Changes to the schema can also be reverted or 'rolled back'. Migrations can also be used to populate tables with example data (also known as seeding).
Database Setup
Head over to your application/config/database.php configuration file. If you have ever installed a PHP application you will be familiar with this kind of file. You have your database access credentials to hand don't you? If not go dig them out right now!
Are you back yet? Great, let's take a closer look.
Scroll down to the 'connections' array key, here you will see a number of options for a few different type of databases. Fill in the connection parameters for your database of choice, I am going to stick to good ol' fashioned mySQL.
<?php
'mysql' => array(
'driver' => 'mysql',
'host' => 'localhost',
'database' => 'codefun',
'username' => 'root',
'password' => 'pandaseatbamboo',
'charset' => 'utf8',
'prefix' => '',
),
There we go, now you will want to scroll up a little bit, and change the 'default' array key to reflect the database you are using. For me it's already on mySQL.
<?php
'default' => 'mysql',
Now we have our database configured, we need some tables to play with. Onwards to migrations!
Migrations
Let's get started by creating a migration file.
We are going to use Laravel's command line interface artisan to create our new migration. To run Artisan you will need to have PHP CLI installed. Normally this will be installed along side your web server. You will also need to open a terminal to the root of your Laravel package where the 'artisan' file exists. Right, let's type our first artisan command :
php artisan migrate:make create_users
We are telling artisan to run the make method on the migrate task. We pass a name for our migration to identify it by, I like to name it after the action being performed. In this case we are creating the users table.
Let's have a look at the result.
Great! New migration created!
Enthusiastic! If we take a look in our application/migrations directory, we will see a new file named 2012_03_30_220459_create_users.php. Well yours might not be called that! You see artisan takes the current date, and adds the time in His format to create the file name. The reason for this is that dates are very important to migrations (and single people). The system needs to be able to know in which order to apply the changes.
Let's open up the file and take a look at the migration class.
<?php
class Create_Users {
/**
* Make changes to the database.
*
* @return void
*/
public function up()
{
//
}
/**
* Revert the changes to the database.
*
* @return void
*/
public function down()
{
//
}
}
As you can see, our migration class consists of two methods. up() is responsible for making all of your database changes, where down() accomplishes the exact reverse. This way a migration can be performed, and rolled back when necessary. If you were to create a table within the up() method, we would DROP the same table in the down() method.
So how do we perform changes on our database, no doubt with some complex ugly SQL query right? Haha, no. We are using Laravel, if it's ugly, it's not in the framework. Let's have a look at creating a table with the Laravel Schema class.
<?php
Schema::create('users', function($table) {
// auto incremental id (PK)
$table->increments('id');
// varchar 32
$table->string('username', 32);
$table->string('email', 255);
$table->string('password', 64);
// int
$table->integer('role');
// boolean
$table->boolean('active');
// created_at | updated_at DATETIME
$table->timestamps();
});
We call the create() method on the Schema class to create a new table, and pass the name of the table to be created, and a closure as parameters. We also need to pass a parameter to the closure. You can call it whatever you like, but I like using $table because I think it makes the code read better.
Inside the closure, we can use the $table parameter to create our fields with a number of handy, well-named methods. Let's take a quick look at them.
increments()
Add an auto-incrementing ID to the table, most of your tables will have this!
string()
Create a VARCHAR field, string sounds a lot better doesn't it?
integer()
Add an integer field to the table.
float()
Add a float field to the table.
boolean()
Add a boolean field to the table.
date()
Add a date field to the table.
timestamp()
Add a timestamp field to the table.
timestamps()
Add created_at and updated_at timestamp fields.
text()
Add a text field to the table.
blob()
Add a blob data field to the table.
You can also use the chainable method ->nullable() to allow the field to receive NULL values.
Full details on the parameters available to the above methods can be found in the official documentation.
Great, so now we have created our table! Since we created the table in the up() method, we now need to DROP the table in the down() method, so that the schema will return to its original form after a roll-back. Fortunately the Schema class has another method that is perfect for this task.
<?php
Schema::drop('users');
Yep, it doesn't get easier than that. The Schema class also has functions for performing other schema tasks, such as dropping columns drop_column(), adding indexes unique() and a number of foreign key methods. I think that covering all of them here would turn the book into an API, something I don't wish to do, besides they are explained well in the official documentation, and if you take a look there we can move on to new things. How about finding out how we run these migrations? Let's do it!
Before we can run our migrations, we need to install the laravel_migrations table so that Laravel can keep track of which migrations have been run. Fortunately artisan has a command which will perform the necessary database changes for you. Let's have a go.
php artisan migrate:install
And the result..
Migration table created successfully.
Great! Now that the laravel_migrations table has been created we can finally run our new migration. Simply use the task name this time, here we go.
php artisan migrate
Migrated: application/2012_03_30_220459_create_users
Whoop! If we take a look at our database we will see that the users table has been created successfully. Wait, we made a mistake! (we didn't, but I like drama and I need an excuse to use roll-back) Let's roll-back our schema to redo the changes.
php artisan migrate:rollback
Rolled back: application/2012_03_30_220459_create_users
Please note that if you would like to provide example data to populate your database with, simply create it with the Fluent Query Builder which will be covered in a later chapter.
Easy as pie! Now that you know how to use migrations, you can build your schema and populate your database whenever you please! In the next chapter we will be looking at the Fluent Query Builder.
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!