Laravel 5 Migrations

Download: Laravel Migrations Tutorial Files

Introduction

Laravel migrations provide mechanisms for creating and modifying database tables. Migrations are database agnostic. This means you do not have to worry about the specific SQL syntax for the database engine that you are creating tables for. Migrations also allow you to roll back the most recent changes that you made to a database.

This tutorial assumes you have the tutorial project Larashop already set-up. If you do not have it, then read the tutorial on Laravel Hello World and create the project using composer.

Topics to be covered

We will cover the following topics

  • Requirements for running migrations
  • Artisan migration command
  • Migration structure
  • How to create a table using a migration
  • Laravel migration rollback
  • Laravel migration how-tos
  • Database seeding
  • Larashop database dictionary
  • Migrations for Larashop database dictionary

Requirements for running migrations

In this section, we will;

  1. Create the database the for online shopping store tutorial project.
  2. Set the database connection parameters for Laravel
  3. Set the database connection parameters for artisan command line tool.

Creating Larashop database

  1. Open PHPMyAdmin or what ever MySQL database management tool that you are using.
  2. Run the following command to create a database
CREATE DATABASE `larashop`;

HERE,

CREATE DATABASE larashop; creates a database called Larashop in MySQL

Setting database connection parameters for Laravel

  1. Open /config/database.php
  2. Locate the following lines of code
'mysql' => [
    'driver'    => 'mysql',
    'host'      => env('DB_HOST', 'localhost'),
    'database'  => env('DB_DATABASE', 'forge'),
    'username'  => env('DB_USERNAME', 'forge'),
    'password'  => env('DB_PASSWORD', ''),
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
    'strict'    => false,
],

Update the following array keys to match the settings on your instance of MySQL

'database' => env('DB_DATABASE', 'larashop'),
'username'  => env('DB_USERNAME', 'root'),
'password'  => env('DB_PASSWORD', 'melody'),

Setting database connection parameters for Artisan

One of the challenges that most developers face when working with migrations in Laravel 5 from the artisan command line tool is the following message.

Access denied for user 'homestead'@' localhost' (using password: YES)

You will get the above message even if you have set the correct parameters in /config/database.php This is because the artisan command line tool uses the database connection parameters specified in .env file.

The solution

  1. Go to your project route using windows explorer or whatever tool you use to browser files on your system.
  2. Open /.env file

You will get the following

APP_ENV=local
APP_DEBUG=true
APP_KEY=aqk5XHULL8TZ8t6pXE43o7MBSFchfgy2

DB_HOST=localhost
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

CACHE_DRIVER=file
SESSION_DRIVER=file
QUEUE_DRIVER=sync

MAIL_DRIVER=smtp
MAIL_HOST=mailtrap.io
MAIL_PORT=2525
MAIL_USERNAME=null
MAIL_PASSWORD=null
MAIL_ENCRYPTION=null

Update the following variables

DB_HOST=localhost
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

to

DB_HOST=localhost
DB_DATABASE=larashop
DB_USERNAME=root
DB_PASSWORD=melody

Note: the database, username and password must match the ones on your system.

Save the changes

Artisan migration command

In this section, we will create;

  1. The migration table in our database
  2. A migration file that we will use to create a table for hard drinks.

When you create a migration file, Laravel stores it in /database/migrations directory. You can specify a different path if you would like to but we won’t cover that in this tutorial. We will work with the default path.

Open the command prompt or terminal depending on your operating system

For this tutorial, we are using windows. Run the following command to browse to the command prompt.

cd C:\xampp\htdocs\larashop

Step 1: Create migration table

Run the following artisan command to create a migration table in Larashop database.

php artisan migrate:install

You will get the following message

Migration table created successfully.

Run the following command to create a migration file

php artisan make:migration create_drinks_table

HERE,

  • php artisan make:migration executes the make migration method via the artisan command.
  • createdrinkstable specifies the name of the migration file that will be created. Note: a timestamp will be added to the beginning of the migration file

You will get the following results.

Created Migration: 2015_08_27_072434_create_drinks_table

Migration structure

We will now examine the contents of the created migration file Open the file /database/migrations/20150827072434createdrinkstable.php You will get the following file. Note: all comments have been removed for brevity’s sake in this example.

<?php

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

class CreateDrinksTable extends Migration
{
    public function up()
    {
        //
    }
    
    public function down()
    {
        //
    }
}

HERE,

  • class CreateDrinksTable extends Migration defines the CreateDrinksTable class that extends Migration class
  • public function up() defines the function that is executed when the migration is run
  • public function down() defines the function that is executed when you run migration rollback

How to create a table using a migration

Now that we have successfully created a migration file, we will add the table definition fields in the migration Modify the contents of /database/migrations/20150827072434createdrinkstable.php

<?php

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

class CreateDrinksTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up() {
        Schema::create('drinks', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name',75)->unique();
            $table->text('comments')->nullable();
            $table->integer('rating');
            $table->date('brew_date');
            $table->timestamps();        });
    }

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

HERE,

  • Schema::create('drinks', function (Blueprint $table) {..} calls the create function of the Schema class. The create function is responsible for creating the database table. function (Blueprint $table) is a closure function with a $table parameter. The $table parameter is used to define the structure of the database.
  • $table->increments('id'); increments is used to define an auto increment field. The name of the field is id as specified in the parameter of increments
  • $table->string('name',75)->unique(); string is used to define varchar fields. The second parameter is the length of the field. ->unique() is used to mark the column as unique.
  • $table->text('comments')->nullable(); is used to define text fields. ->nullable() is used to allow the column to accept null values.
  • $table->integer('rating'); integer is used to define int fields.
  • $table->date('brew_date'); is used to define date fields.
  • $table->timestamps(); is used to automatically create two time stamp fields namely created_at and updated_at.

Go back to the command prompt Run the following command

php artisan migrate

You will get the following output

Use composer here

Open PHPMyAdmin and view the tables in Larashop You will get the following

Use composer here

Note: users and password_resets tables have been created for us. This is because Laravel has migration files for these two tables by default.

Laravel migration rollback

One of the advantages of migrations is that they allow you to roll back to the previous state before you run the migrations. In this section, we will roll back the creation of the tables.

  1. Go back to the command prompt
  2. Run the following command
php artisan migrate:rollback

You will get the following output

Rolled back: 2014_10_12_000000_create_users_table.php
Rolled back: 2014_10_12_100000_create_password_resets_table.php
Rolled back: 2015_08_27_090421_create_drinks_table.php

We need the tables back so run the following command

php artisan migrate

Laravel Migration How-tos

This section shows you how to perform various Laravel migration tasks in Laravel.

Laravel migration insert data

This how-to shows you how to create a migration file that inserts data too into the newly created table. We will create an employees table and add 33 seed records using Faker Library. Read Laravel Faker Tutorial for more details.

Open the command prompt / terminal and browser to the project root.

Run the following artisan command to generate the employees migration files

php artisan make:migration employees

Open /database/migrations/xxxxxxxxx_employees.php xxxxxxxxx stands for the time stamp that the migration will append to the file name.

Add the following code

<?php

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

class Employees extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('employees', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('contact_number');
            $table->timestamps();       
        });

        $faker = Faker\Factory::create();

        $limit = 33;

        for ($i = 0; $i < $limit; $i++) {
            DB::table('employees')->insert([ //,
                'name' => $faker->name,
                'email' => $faker->unique()->email,
                'contact_number' => $faker->phoneNumber,
            ]);
        }
    }

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

HERE,

  • $faker = Faker\Factory::create(); creates an instance of Faker factory
  • $limit = 33; sets the number of records that we want to add to the database.
  • for ($i = 0; $i < $limit; $i++) { DB::table('employees')->insert([…]);} uses a for loop to add records to the database 33 times. $faker->name generates a faker name, $faker->unique()->email generates a fake unique email address, and $faker->phoneNumber generates a fake

Run the following command to run the migration

php artisan migration

You will get the following message

Migrated: xxxxxxx_employees.php

Run the following SELECT query in MySQL against Larashop database.

SELECT * FROM employees;

You will get a list of employee names with email addresses and contact numbers.

Laravel migration add column / drop colum

Let’s say we want to add a new column to our employees table for gender. We want to add the new column after the contact_number field.

Run the following command

php artisan make:migration add_gender_to_employees --table=employees

HERE

  • --table=employees tells Laravel we want to work with an existing table called employees

Open the new migration file in /database/migration/xxxxxxxxxxxxxxx_add_gender_to_employees.php

Modify it to the following

<?php

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

class AddGenderToEmployees extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('employees', function (Blueprint $table) {
            $table->string('gender')->after('contact_number');
        });
    }

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

HERE,

  • public function up(){…} uses Schema::table('employees'…) to add a new column gender
  • public function down(){…} drops the new column from the table when we reverse the command. $table->dropColumn('gender'); is the command that drops the table.

Laravel migration change column type

We first need to install Doctrine Database Abstract Layer DDBAL in Laravel before we can change columns. DDBAL is used for Laravel migration alter table tasks.

Add "doctrine/dbal": "v2.4.2" to composer.json as shown below

"require": {
    "php": ">=5.5.9",
    "laravel/framework": "5.1.*",
    "gloudemans/shoppingcart": "~1.3",
    "doctrine/dbal": "v2.4.2"
}

Run following composer command

composer update

We created the gender column with the default size of 255. Let’s we want to change it to 5 as the maximum size.

Run the following command to create a new migration file

php artisan make:migration modify_gender_in_employees --table=employees

Open /database/migrations/xxxxxxxxx_modify_gender_in_employees.php

Modify it to the following

<?php

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

class ModifyGenderInEmployees extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('employees', function (Blueprint $table) {
            $table->string('gender', 5)->change();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('employees', function (Blueprint $table) {
            $table->string('gender', 255)->change();
        });
    }
}

HERE,

  • $table->string('gender', 5)->change(); maintains the varchar data type and sets the character limit to 5. If we wanted to change the data type too, we would have specified a different data type.
  • $table->string('gender', 255)->change(); rollback the migration to the previous state.

Run the following command to run the migration

php artisan migrate

Check the field size in MySQL, it will be set to 5.

Laravel migration nullable

By default, Laravel assumes all columns are required unless you tell it so. Let’s assume the gender field is optional.

Run the following command to create a migration file

php artisan make:migration make_gender_null_in_employees –table-employees

Modify the code to the following

<?php

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

class MakeGenderNullInEmployees extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('employees', function (Blueprint $table) {
            $table->string('gender', 5)->nullable()->change();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('employees', function (Blueprint $table) {
            $table->string('gender', 5)->change();
        });
    }
}

HERE,

$table->string('gender', 5)->nullable()->change(); allows the gender column to accept null values.

Run the following command to execute the migration

php artisan migrate

Laravel migration foreign key

Let’s say we want to group our employees by their departments, we can add a foreign key for the dept_id

Run the following command to create a migration file for depts table

php artisan make:migration depts

Modify the code to the following

<?php

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

class Depts extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('depts', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

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

Run the following command to create the depts table

php artisan migrate

The primary and foreign key relationship requires both tables to have the same data type and length. We used Schema’s increments to define the primary key for depts id. Schema’s increments creates an unsigned integer INT(10), Schema’s integer creates signed integer INT(11).

We need to use Schema’s unsignedInteger when creating dept_id so that both the primary and foreign keys will be INT(10).

Run the following command to create the migration for adding the dept_id to the employees table.

php artisan make:migration add_dept_id_in_employees --table=employees

Modify the code of the new migration file to the following

<?php

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

class AddDeptIdInEmployees extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('employees', function (Blueprint $table) {
            $table-> unsignedInteger ('dept_id')->after('gender');
            $table->foreign('dept_id')
            ->references('id')->on('depts')
            ->onDelete('cascade');
        });
    }

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

Run the following command to execute the migration

php artisan migrate 

The above command will create a foreign key on employees table

Database seeding

In this section, we are going to add dummy data to our database. Seeding is a term that is used to describe the process of adding test data to the database.

  1. Go back to the command prompt
  2. Run the following command
php artisan make:seeder DrinksTableSeeder

Open /database/seeds/DrinksTableSeeder.php

<?php

use Illuminate\Database\Seeder;

class DrinksTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        //
    }
}

HERE,

  • class DrinksTableSeeder extends Seeder defines the table DrinksTableSeeder that extends the Seeder class
  • public function run() defines the function that is executed when you run the seed command from artisan

Modify the above code to the following

<?php

use Illuminate\Database\Seeder;

class DrinksTableSeeder extends Seeder {

    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run() {
        DB::table('drinks')->insert([
            'name' => 'Vodka',
            'comments' => 'Blood of creativity',
            'rating' => 9,
            'brew_date' => '1973-09-03',
        ]);
    }
}

HERE,

  • The above table uses an array that matches database field name to values and inserts the record into the specified table drinks.

Let’s now run the seed and add our dummy record to the database.

  1. Go back to the command prompt
  2. Run the following command
php artisan db:seed --class=DrinksTableSeeder

HERE,

  • php artisan db:seed executes the seed command
  • --class=DrinksTableSeeder specified the name of the seeder class that should be executed

Open PHPMyAdmin and view the rows of drinks. You will have a single record of Vodka.

Larashop database dictionary

Now that we have covered the basics of migrations and database seeding, its time our put our knowledge into practice. Our online shopping store will have the following tables and fields.

**Author: Rodrick Kazembe | Date: 2015-08-27 | Version: 1 **

All tables will have the following files common fields for record audit purposes

S/N FIELD DATA TYPE DESCRIPTION
1 created_at Timestamp Timestamp when record was created
2 updated_at Timestamp Timestamp when record was last updated
3 created_at_ip Varchar(45) IP address used to create the record
4 updated_at_ip Varchar(45) IP address used to last update record

Table: Posts

S/N FIELD DATA TYPE DESCRIPTION
1 id INT Primary key (AUTOINCREMENT)
2 url Varchar(255) Page URL
3 title Varchar(140) Page title
4 description Varchar(170) Description that shows in search engine results
5 content Text The content of the page or blog post
6 blog Tinyint(1) Determines if a post is a page is blog

Table: Products | Assumptions: At any given time, a product can only have a single category and brand.

S/N FIELD DATA TYPE DESCRIPTION
1 id INT Primary key (AUTOINCREMENT)
2 name Varchar(255) Product name
3 title Varchar(140) Product title
4 description Varchar(500) Product description
5 price int Product price
6 category_id int Product category id
7 brand_id int Product brand id

Table: Categories

S/N FIELD DATA TYPE DESCRIPTION
1 id int Primary key (AUTOINCREMENT)
2 name Varchar(255) Category name

Table: Brands

S/N FIELD DATA TYPE DESCRIPTION
1 id int Primary key (AUTOINCREMENT)
2 name Varchar(255) Brand name

Migrations for Larashop database dictionary

In this section, we will create migration files for the above tables and write the table definitions for them. We will also add some dummy records to our tables via database seeding.

Creating migration files for our database dictionary

  1. Open the command prompt.
  2. Run the following commands to generate migrations for our above tables
php artisan make:migration create_posts_table
php artisan make:migration create_products_table
php artisan make:migration create_categories_table
php artisan make:migration create_brands_table

Open the respective migration files and update them with the following code

create_posts_table

<?php

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

class CreatePostsTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up() {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->string('url', 255)->unique();
            $table->string('title', 140);
            $table->string('description', 170);
            $table->text('content');
            $table->boolean('blog');
            $table->timestamps();
            $table->string('created_at_ip');
            $table->string('updated_at_ip');
        });
    }

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

create_products_table

<?php

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

class CreateProductsTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up() {
        Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name', 255)->unique();
            $table->string('title', 140);
            $table->string('description', 500);
            $table->integer('price');
            $table->integer('category_id');
            $table->integer('brand_id');
            $table->timestamps();
            $table->string('created_at_ip');
            $table->string('updated_at_ip');
        });
    }

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

create_categories_table

<?php

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

class CreateCategoriesTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up() {
        Schema::create('categories', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name', 255)->unique();
            $table->timestamps();
            $table->string('created_at_ip');
            $table->string('updated_at_ip');
        });
    }

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

create_brands_table

<?php

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

class CreateBrandsTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up() {
        Schema::create('brands', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name', 255)->unique();
            $table->timestamps();
            $table->string('created_at_ip');
            $table->string('updated_at_ip');
        });
    }

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

Let’s now run our migrations

  1. return to the command prompt
  2. run the following command

php artisan migrate

Larashop Database seeding

Let’s now add some dummy records to our database. 1. return to the command prompt 2. Run the following commands

php artisan make:seeder ProductsTableSeeder
php artisan make:seeder CategoriesTableSeeder
php artisan make:seeder BrandsTableSeeder

Open the respective files and update them with the following code

ProductsTableSeeder

<?php

use Illuminate\Database\Seeder;

class ProductsTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        DB::table('products')->insert(['name' => 'Mini skirt black edition', 'title' => 'Mini skirt black edition','description' => 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna','price' => 35,'category_id' => 1,'brand_id' => 1,]);
        DB::table('products')->insert(['name' => 'T-shirt blue edition', 'title' => 'T-shirt blue edition','description' => 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna','price' => 64,'category_id' => 2,'brand_id' => 3,]);
        DB::table('products')->insert(['name' => 'Sleeveless Colorblock Scuba', 'title' => 'Sleeveless Colorblock Scuba','description' => 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna','price' => 13,'category_id' => 3,'brand_id' => 2,]);
    }
}

CategoriesTableSeeder

<?php

use Illuminate\Database\Seeder;

class CategoriesTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        DB::table('categories')->insert(['name' => 'MENS']);
        DB::table('categories')->insert(['name' => 'WOMENS']);
        DB::table('categories')->insert(['name' => 'KIDS']);
        DB::table('categories')->insert(['name' => 'FASHION']);
        DB::table('categories')->insert(['name' => 'CLOTHING']);
    }
}

BrandsTableSeeder

<?php

use Illuminate\Database\Seeder;

class BrandsTableSeeder extends Seeder {

    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run() {
        DB::table('brands')->insert(['name' => 'ACNE']);
        DB::table('brands')->insert(['name' => 'RONHILL']);
        DB::table('brands')->insert(['name' => 'ALBIRO']);
        DB::table('brands')->insert(['name' => 'ODDMOLLY']);
    }
}

Summary

In this tutorial, we looked at what migrations are, and how to use them. We also looked at database seeding. We defined a database dictionary and created migration and seed files for our database dictionary.

What’s next?

The next tutorial uses Eloquent ORM to interact with our database and display the data in the shopping cart.

Tutorial History

Tutorial version 1: Date Published 2015-08-30

Tutorial version 2: Date Updated 2015-10-08 –added section on Laravel migration How-tos

Related Tutorials