Laravel 5 Migrations

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;

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

Creating Larashop database

Open PHPMyAdmin or what ever MySQL database management tool that you are using.
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

Open /config/database.php
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

Go to your project route using windows explorer or whatever tool you use to browser files on your system.
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

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;

The migration table in our database
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.

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
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
Open PHPMyAdmin and view the tables in Larashop You will get the following
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. 
Go back to the command prompt
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
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
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
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
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
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
 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.
Go back to the command prompt
Run the following command
php artisan make:seeder DrinksTableSeeder
Open /database/seeds/DrinksTableSeeder.php
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
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.
Go back to the command prompt
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
Open the command prompt.
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
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
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
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
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
return to the command prompt
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
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
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
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

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

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

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