Laravel’s migrations and schema builder are very powerful and by using these features, you won’t have to write any SQL code. Laravel provides migrations to manage the database and its version controlling. That means for each change in your database, you will make a migration file which will hold that particular change in the database which allows us to revert the changes easily and quickly if needed.

Seeding gives the ability to populate the database with basic data for development and testing.

These features can be very useful in a project where changes may be frequent and you try to manage the project in a quick run. By migrations and seeding, you can deploy database quickly and can save the time for other important development stuff.

If you have not configured your database then you can do it easily. Just edit config/database.php file to configure your database settings.

Migrations

Creating Migration

All the migration files live in database/migrations  directory. To create a new migration file, you can use artisan make:migration command. Like if you want to create a migration for users table then you run the following artisan command.

php artisan make:migration create_users_table –-table=users –-create
Created Migration: 2016_03_04_123456_create_users_table

This command will create a migration file in database/migrations/ directory with name including date and timestamp, something like 2016_03_04_123456_create_users_table.php. As you can see, we write the name for migration as what we want to do, like here we may want to create users table, then we can name it the same as we say create_users_table, and it will create the file with date and timestamp as a prefix for the migration version control. Now you can also provide further information to Laravel by using --table=users and --create parameters, this will generate the migration file with some boilerplate. Now when you open this file, you will see code something like this:

<?php

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

class CreateUsersTable 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');
   }
}

 

As you can see, to define the schema changes, there are up() and down() methods in the migration. The up() method will be used when you run the migration and down() method is the reverse of up() method which will be used to rollback the changes in database made in up() method. Now in this example, up() method will create the users table, it means down() method will do the reverse, i.e. drop the users table.

Schema builder and blueprint

To manage all the database related stuff, Laravel provides Schema Class. As you can see in above example it will create the users table by using Schema::create() method where the first argument is the table name - ‘users’ and second is the closure.

So this is the schema construction of our users table. Let’s fill the up() method which will create the users table with attributes and their types.

<?php

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

class CreateUsersTable extends Migration {

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

                 $table->string('name',100);

                 $table->string('email')->unique();

                 $table->string('password');

                 $table->rememberToken();

                 $table->timestamps();
           });
     }

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

 

Isn’t it simple? Now the down() method will drop the users table. 

/**
 * Reverse the migrations.
 *
 * @return void
 */

public function down()
{
      Schema::drop('users');
}

 

Laravel’s blueprint object provides following method in schema construction for migrations. 

$table->increments(‘id’)

creates an auto-incrementing integer column

$table->bigIncrements(‘id’)

creates an auto-incrementing big integer column

$table->integer('views');

creates a INT column

$table->bigInteger('views');

creates a BIGINT column

$table->binary('name');

creates a BLOB column

$table->string('name');

creates a VARCHAR column

$table->string('name', 255);

creates a VARCHAR column of the given length

$table->text('name');

creates a TEXT column

$table->char('name', 8);

creates a CHAR column with the given length

$table->boolean('active');

creates a BOOLEAN column

$table->date('birthdate');

creates a DATE column

$table->dateTime('created_at');

creates a DATETIME column

$table->decimal('amount', 5,2);

creates a DECIMAL column with the given precision and scale

$table->double('column', 10,5);

creates a DOUBLE column, with 10 digits in total and 5 after the decimal point

$table->enum('gender',['Female', Male']);

creates an ENUM column

$table->float('amount');

creates a FLOAT column

$table->json('options');

creates a JSON column

$table->longText('description');

creates a LONGTEXT column

$table->mediumInteger('name');

creates a MEDIUMINT column

$table->mediumText('name');

creates a MEDIUMTEXT column

$table->morphs('taggable');

creates two columns: INTEGER taggable_id and STRING taggable_type

$table->nullableTimestamps();

similar to timestamps (next), but allows NULL values

$table->rememberToken();

It adds a remember_token VARCHAR column

$table->tinyInteger('name');

It creates a TINYINT column

$table->softDeletes();

adds a deleted_at column for soft deletes

$table->time('name');

creates a TIME column

$table->timestamp('name');

It creates a TIMESTAMP column

$table->timestamps();

It creates created_at and deleted_at columns

$table->string('column')->unique();

makes the column to have unique values

$table->primary('column');

makes the column primary key

$table->primary(array('first', 'last'));

makes composite primary key

$table->dropPrimary('table_column_primary');

drop the primary key

$table->renameColumn('from', 'to');

renames the column

$table->dropColumn(string|array);

drops the column

$table->index('column');

makes the column indexed

$table->foreign('user_id')->references('id')->on('users');

used to make the column foreign key
->nullable() allows null values in column
->default($value) Define a default value for a column
->unsigned() Set INTEGER to UNSIGNED

 

So now we’ve created our users migration file but we’re not done yet. We have to run the migration to create the table in database. To run the migration, you’ve to run the following command

php artisan migrate

This command will run the up method in all the migration files in migration folder. In our case it will create users table in the database.

When you run this command for the first time then Laravel will also create migrations table that will be used to keep track of all the migrations to run the outstanding migrations.

Great! We’ve just created the users table using migrations

Rolling back migrations

As we’ve seen, when we run php artisan migrate command, it will execute up() method. But what if we want to rollback the changes we’ve done in up() method? Well, the following command does it.

php artisan migrate:rollback

The above command will run the down() method in the migration files, which rollbacks the changes done in up() method.

A little note – the rollback command rolls back only the last migration that ran last time we used migrate command.

To rollback all the migrations you can use migrate:reset command.

Now we have learn how to make, run and rollback the migrations, we can go to seeding.

Seeding Database

Seeding gives the ability to populate the database with basic data for development and testing. We need to create a new class UserTableSeeder.php in database/seeds/ folder. This class will extends Laravel’s Illuminate\Database\Seeder class and there will be a run() method which will execute while seeding.

<?php

use Illuminate\Database\Seeder

class UserTableSeeder extends Seeder {

     /**
      * Run the database seeds.
      *
      * @return void
      */
     public function run()
     {
           \DB::table('users')->insert([

                 'id'=>'1', 

                 'name'=>'Jane', 

                 'email'=>'jane@users.com',

                 'password'=>bcrypt('jane1234'),

           ]);
           \DB::table('users')->insert([

                'id'=>'2', 

                'name'=>'John', 

                'email'=>'john@users.com',

                'password'=>bcrypt('john123'),

           ]);
     }
}

 

By passing array, you can insert multiple rows into the table. There are many libraries like Faker which can generate large amount of test data in database.

After creating UserTableSeeder.php, we have to call it in database/seeds/DatabaseSeeder.php by just adding the following line in run() method.

 

$this->call('UserTableSeeder');

 

So DatabaseSeeder.php will be like this-

<?php

use Illuminate\Database\Seeder;
use Illuminate\Database\Eloquent\Model;

class DatabaseSeeder extends Seeder {

     /**
      * Run the database seeds.
      *
      * @return void
      */
     public function run()
     {
          Model::unguard();

          // calling UserTableSeeder
          $this->call('UserTableSeeder');
     }
}

 

Before running the seeds we have to run composer dump-autoload command to autoload the newly created classes.

After composer dump-autoload command we are ready to run the seed command.

php artisan db:seed

You can also seed the database while running migration by the following command.

php artisan migrate –seed

Great we’ve seeded the database successfully.

So, now we have learnt database migrations, schema building and database seeding in Laravel. 

#Laravel