Learning Management Platform for Written Tutorial Series.

Laravel 6 Migrations

Laravel 6 Migrations

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.

Topics to be covered

We will cover the following topics in this lesson

  • Requirements for running migrations
  • Artisan migration command
  • Migrations for New Tables
  • Migrations for Adding Columns to Existing Tables
  • Migrations for Modifying Existing Columns
  • Defining Relationships in Migrations
  • Rolling Back Migrations
  • Refreshing Migrations

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 MySQL Workbench 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.

Database Connection Parameters

Let us now edit the configuration file and enable it to communicate with the database server

Open .env file in our tutorial project.

Edit the database connection parameters like so

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=larashop
DB_USERNAME=root
DB_PASSWORD=xxxxx

Artisan migrate command

Database migrations are located in the /database/migrations directory

By default, Laravel 6 comes with the following migration files already created for you

  • 2014_10_12_000000_create_users_table.php is the migration file for creating the users table in the database
  • 2014_10_12_100000_create_password_resets_table.php is the migration file for creating the reset passwords table
  • 2019_08_19_000000_create_failed_jobs_table.php is the migration file for creating failed jobs table.

Let's execute the migration files that ship with the framework by default before we start creating our own migration files

Run the following command from the terminal

php artisan migrate

HERE,

  • The above command runs our migration files against the database larashop in MySQL

When you execute the above code, you should be able to see the following results in the terminal

Laravel 6 Migrations

The console will print out the migration file name and how long it took to execute each file.

Let's now check the database larashop that we created. You should be able to see the following results

Larashop Database

As you can see from the above image, we have four tables in our database. We didnt have a migration file for the table migrations but it has also been created. The migrations table is used to track which migration files have been executed against our database.

Larashop database dictionary

Before we create our own custom migration files, let us start with the database dictionary. Let's suppose that we have to create a database for a simple online store. We can do it like so.

Author: Rodrick Kazembe | Date: 2019-09-08 | Version: 1

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

S/NFieldData TypeDescription
1created_atTimestampTimestamp when record was created
2updated_atTimestampTimestamp when record was last updated
3created_at_ipVarchar(45)IP address used to create the record
4updated_at_ipVarchar(45)IP address used to last update record

Table: Posts

S/NFieldData TypeDescription
1idINT (AUTOINCREMENTPrimary key
2urlVarchar(255)Page URL
3titleVarchar(140)Page title
4descriptionVarchar(170)Description that shows in search engine results
5contentTextThe content of the page or blog post.
6blogTinyint(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/NFieldData TypeDescription
1idINT (AUTOINCREMENTPrimary key
2nameVarchar(255)Product name
3titleVarchar(140)Page title
4descriptionVarchar(500)Product description
5priceintProduct price
6Category_idTextProduct category id
7Brand_idTinyint(1)Product brand id

Table: Categories

S/NFieldData TypeDescription
1idINT (AUTOINCREMENTPrimary key
2nameVarchar(255)Category name

Table: Brands

S/NFieldData TypeDescription
1idINT (AUTOINCREMENTPrimary key
2nameVarchar(255)Brand name

Laravel Migrations for New Tables

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.

Let's start with the migration for the categories table

Run the following command on the terminal to create the migration file

php artisan make:migration create_categories

HERE,

  • php artisan make:migration create_categories artisan uses make:migration command to create the migration file called create_categories.

If you check the migrations directory, then you should be able to see the following file

2019_09_08_175956_create_categories.php

Note: Laravel appends the current timestamp at the beginning of the file name. Laravel executes migration files using the FIFO. First in First out based on the timestamps.

Open the migration file /database/migrations/2019_09_08_175956_create_categories.php

<?php

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

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

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

HERE,

  • class CreateCategories extends Migration defines a class CreateCategories that extends the built-in class Migration
  • public function up(){...} defines the method that is called when we execute our migration file.
  • Schema::create('categories', function (Blueprint $table) {...} calls the create method on the Schema class to create a table called categories. We are using an anonymous function that accepts a parameter $table that is of the type Blueprint. Inside our anonymous function, we define the table fields like $table->bigIncrements('id');, $table->timestamps();.
  • public function down(){...} defines the function that us used to reverse the migrations. The statement Schema::dropIfExists('categories'); drops the table from the database when this method is called.

Let's now add our own custom fields to the migration file

Modify the up method like so

    public function up()
    {
        Schema::create('categories', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('created_at_ip',45)->nullable();
            $table->string('updated_at_ip',45)->nullable();
            $table->timestamps();
        });
    }

HERE,

  • $table->bigIncrements('id'); defines the primary key called id that is of data type BIGINT and it is an auto-increment field. You can use a different name for the primary key but its best to use the conventional one which is id.
  • $table->string('name'); creates a field name that is of the data type VARCHAR the default length is set to 255
  • $table->string('created_at_ip',45)->nullable();$table->string('updated_at_ip',45)->nullable(); creates fields created_at_ip and updated_at_ip that are of VARCHAR datatype and an explicit length of 45. The method nullable() allows our fields to accept null value. If this is skipped then the user will have to provide a value for the particular field.
  • $table->timestamps(); calls the timestamps method on the $table object. This creates two special fields namely created_at and updated_at. These are used to store the date that the record was created and when was the last time that the record was updated.

Let's now execute our migration file

Run the following command in the terminal

php artisan migrate

You should be able to get results similar to the following

Migrating: 2019_09_08_175956_create_categories
Migrated:  2019_09_08_175956_create_categories (0.49 seconds)

If you check the database then you should be able to see our new created table categories

Laravel Migrations for Adding Columns to Existing Tables

In the above example, we created a categories table that technically had only a single field name. The id, IP addresses and timestamps are kind of standard to our database tables so they technically dont count.

Suppose we want to add more columns to our table like url and description. We can do it like so

php artisan make:migration add_more_fields_to_categories --table=categories

HERE,

  • php artisan make:migration add_more_fields_to_categories --table=categories creates a migration file called _add_more_fields_to_categories and the option --table=categories tells artisan to generate boiler plate code that will manipulate the categories table

Open the newly created migration file

Add the following code to the up method

public function up()
{
    Schema::table('categories', function (Blueprint $table) {
        $table->string('url')->after('name');
        $table->string('description')->after('url');
    });
}

HERE,

  • $table->string('url'); defines our new field url and it is created after the column name
  • $table->string('description'); defines our new field description and it is created after the column url

Let's now modify the code for the down method like so

public function down()
{
    Schema::table('categories', function (Blueprint $table) {
        $table->dropColumn('url');
        $table->dropColumn('description');
    });
}

HERE,

  • $table->dropColumn('url'); drops the column url from the categories table
  • $table->dropColumn('description'); drops the column description from the categories table.

You can now execute our migrations file like so

php artisan migrate

Executing the above command should give you the following results

Migrating: 2019_09_08_182834_add_more_fields_to_categories
Migrated:  2019_09_08_182834_add_more_fields_to_categories (0.61 seconds)

You can now check the categories table definition in the database. You should be able to see the fields that we just defined.

Migrations for Modifying Existing Columns

We can also change columns that we have already defined. For example, we can change the length of a field or rename it. For us to do this, we need to work with a third-party package that we can easily install using composer like so

composer require doctrine/dbal

After dbal is installed, you can change columns like so

php artisan make:migration modify_ip_address_fields --table=categories

Open the newly created migration file and make the following modifications to the up method

    public function up()
    {
        Schema::table('categories', function (Blueprint $table) {
            $table->string('created_at_ip', 100)->change();
            $table->string('updated_at_ip', 100)->change();
        });
    }

HERE,

  • $table->string('created_at_ip', 100)->change(); changes the column size from 45 which we originally define to 100. The change method is what enforce the charges to take place.

Let's now modify the down method like so

Modify the down method as follows

    public function down()
    {
        Schema::table('categories', function (Blueprint $table) {
            $table->string('created_at_ip', 45)->change();
            $table->string('updated_at_ip', 45)->change();
        });
    }

HERE,

  • $table->string('created_at_ip', 45)->change(); changes the field size from 100 back to the original 45 when the migration is rolled back.

You can also change rename columns, make them nullable like so

$table->string('created_at_ip', 45)->nullable()->change();
$table->renameColumn('name', 'category');

Defining Relationships in Migrations

In the above examples, we worked with only a single table. In this section, we will create the all the tables for our online store and define all the relationships between the tables.

Run the following commands to create the migration files

php artisan make:migration create_brands
php artisan make:migration create_products
php artisan make:migration create_posts

The code for create_brands is as follows

<?php

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

class CreateBrands extends Migration
{
    public function up()
    {
        Schema::create('brands', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('url');
            $table->string('description');
            $table->string('created_at_ip',45)->nullable();
            $table->string('updated_at_ip',45)->nullable();
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('brands');
    }
}

The code for create_products is as follows

<?php

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

class CreateProducts extends Migration
{
    public function up()
    {
        Schema::create('products', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('category_id')->unsigned()->index();
            $table->bigInteger('brand_id')->unsigned()->index();
            $table->string('name');
            $table->string('url');
            $table->string('description');
            $table->string('created_at_ip',45)->nullable();
            $table->string('updated_at_ip',45)->nullable();
            $table->timestamps();

            $table->foreign('category_id')->references('id')->on('categories');
            $table->foreign('brand_id')->references('id')->on('brands');
        });
    }

    public function down()
    {
        Schema::dropIfExists('products');
    }
}

HERE,

  • $table->bigInteger('category_id')->unsigned()->index(); defines the foreign key category_id. Notice the data type for the foreign key is bigInteger
  • $table->bigInteger('brand_id')->unsigned()->index(); defines the foreign key brand_id
  • $table->foreign('category_id')->references('id')->on('categories'); defines a relationship between products and categories table
  • $table->foreign('brand_id')->references('id')->on('brands'); defines a relationship between products and brands

The code for create_posts is as follows

<?php

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

class CreatePosts extends Migration
{
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('url');
            $table->string('title');
            $table->string('description');
            $table->text('content');
            $table->boolean('blog')->default(0);
            $table->string('created_at_ip',45)->nullable();
            $table->string('updated_at_ip',45)->nullable();
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('posts');
    }
}

Run the following command to execute our migrations

php artisan migrate

Executing the above command should create the categories, products and posts table. The relationships between products, and brands and categories will also be created.

Rolling Back Migrations

In the above section, we created three more tables. Rollback allows us to undo the latest changes that we did to our database.

Run the following command to rollback the three tables that we created

php artisan migrate:rollback

You should be able to get results similar to the following

Rolling back: 2019_09_08_193700_create_posts
Rolled back:  2019_09_08_193700_create_posts (0.35 seconds)
Rolling back: 2019_09_08_193639_create_products
Rolled back:  2019_09_08_193639_create_products (0.8 seconds)
Rolling back: 2019_09_08_193537_create_brands
Rolled back:  2019_09_08_193537_create_brands (0.78 seconds)

To recreate the above tables, all ypu have to do is run the migration command again like so

php artisan migrate

Refreshing Migrations

Refreshing migrations rollbacks all the migrations that we had executed earlier on then reruns them afresh again. This command is usually relevant when you to want to have a fresh start with the database after you have created some records in it.

Run the following command to refresh the database migrations.

php artisan migrate:refresh

Executing the above command produces the following results

Rolling back: 2019_09_08_190022_modify_ip_address_fields
Rolled back:  2019_09_08_190022_modify_ip_address_fields (1.3 seconds)
Rolling back: 2019_09_08_182834_add_more_fields_to_categories
Rolled back:  2019_09_08_182834_add_more_fields_to_categories (2.33 seconds)
Rolling back: 2019_09_08_175956_create_categories
Rolled back:  2019_09_08_175956_create_categories (0.35 seconds)
Rolling back: 2019_08_19_000000_create_failed_jobs_table
Rolled back:  2019_08_19_000000_create_failed_jobs_table (0.51 seconds)
Rolling back: 2014_10_12_100000_create_password_resets_table
Rolled back:  2014_10_12_100000_create_password_resets_table (0.42 seconds)
Rolling back: 2014_10_12_000000_create_users_table
Rolled back:  2014_10_12_000000_create_users_table (0.54 seconds)
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (1.01 seconds)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (0.84 seconds)
Migrating: 2019_08_19_000000_create_failed_jobs_table
Migrated:  2019_08_19_000000_create_failed_jobs_table (0.38 seconds)
Migrating: 2019_09_08_175956_create_categories
Migrated:  2019_09_08_175956_create_categories (0.53 seconds)
Migrating: 2019_09_08_182834_add_more_fields_to_categories
Migrated:  2019_09_08_182834_add_more_fields_to_categories (1.02 seconds)
Migrating: 2019_09_08_190022_modify_ip_address_fields
Migrated:  2019_09_08_190022_modify_ip_address_fields (4.41 seconds)
Migrating: 2019_09_08_193537_create_brands
Migrated:  2019_09_08_193537_create_brands (1.21 seconds)
Migrating: 2019_09_08_193639_create_products
Migrated:  2019_09_08_193639_create_products (5.84 seconds)
Migrating: 2019_09_08_193700_create_posts
Migrated:  2019_09_08_193700_create_posts (0.7 seconds)

Summary

Database migrations allow us to automatically create and manage our database tables. You can use the command line to generate a migration file, define its fields and when you execute it, the table is automatically created for you. Migrations can also be used to define relationships between tables. Changing the table definition such as rename, modify data type, field length etc are also possible using database migrations. You can also undo changes made to the database by rolling back the migrations.

What next?

If you enjoyed this lesson then show us your appreciation by creating a free accounts on our site. As always we appreciate your comments down below.


...