Laravel migrations provide mechanisms for creating and modifying database tables. Migrations are database agnostic, this means you don’t have to worry about the specific SQL syntax for the database engine that you are creating tables for.
Well, in this articles I will cover the following sections: 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.
Requirements for Running Migrations
1. Create the database for Laravel project
2. Set the database connection parameters for Laravel project
3. Set the database connection parameters for artisan command line
1. Create the Database for Laravel Project
Open up terminator or what ever MySQL database management tool that you are using and run the command below:
1
| |
CREATE DATABASE foodie; creates a database called foodie in MySQL.
2. Set the Database Connection Parameters for Laravel Project
Open up /config/database.php file and modify to the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | |
3. Set the Database Connection Parameters for Artisan Command Line
One of the challenges that most developers face when working with migrations in Laravel 5.x.x from the artisan command line is the following message:
1
| |
You will get the above message even you have set the correct parameters in /config/database.php file, because the artisan command line uses the database connection parameters specified in .env file.
The solutions is go to the project open up /.env file and modify to the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | |
The database, username and password must match the ones on your system.
Artisan Migration Command
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 will stores it in /database/migrations folder. You can specify a different path if you would like to but we won’t cover that in this articles. We will work with the default path.
Create Migration Table
Open up the terminator and run the following artisan command to create a migration table:
1
| |
php artisan make:migration executes the make migration method via the artisan command.
create_drinks_table specifies the name of the migration file that will be created.
You will get the following results:
1
| |
Migration Structure
You will get the following file with the contents below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | |
- 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/20170808072434createdrinkstable.php file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | |
- Schema::create('drinks', function (Blueprint $table) {...} calls the create function of the Schema class. The create function is responsible for creating the database table.
- (Blueprint $table) is a closure function with a $table parameter.
- $table parameter is used to define the structure of the database.
- $table->increments('id'); increments is used to define an auto increment field.
- $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 terminator and run the command below:
1
| |
And then you will get many tables drinks and users, password_resets which Laravel has migrated those two tables by defaults.
Laravel Migration Rollback
One of the advantages of migrations is that it 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 terminator and run the command below:
1
| |
And then you will get the following output:
1 2 3 | |
Laravel Migration How-tos
This section I will show how to perform various Laravel migration tasks.
Laravel Migration Insert Data
This “how-to” shows you how to create a migration file that inserts data into the newly created table. We will create an employees table and add 33 seed records using Faker Library.
Open up the terminator and run the command below:
1
| |
Open up /database/migrations/xxxxxxxxx_employees.php file and add the following codes:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | |
$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. $faker->phoneNumber generates a fake phone number.
Open up the terminator and run the following command to run the migration:
1
php artisan migration
Laravel Migration Add Column/Drop Colum
We will add a new gender column to employees table.
Open up the terminator and run the following command:
1
php artisan make:migration add_gender_to_employees —table=employees
—table=employees tells Laravel we want to work with an existing table called employees.
Open up /database/migration/xxxxxxx_add_gender_to_employees.php and modify to the following:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?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');
});
}
}
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 have created the gender column with the default size of 255. We want to change it to 5 as the maximum size.
Open up the terminator and run the following command:
1
php artisan make:migration modify_gender_in_employees —table=employees
Open up /database/migrations/xxxxxxx_modify_gender_in_employees.php file and modify to the following:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?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();
});
}
}
$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.
Open up the terminator and run the following command to run the migration:
1
php artisan migrate
Laravel Migration Nullable
By default, Laravel assumes all columns are required unless you tell it so let’s assume the gender field is optional.
Open up the terminator and run the following command to create a migration file:
1
php artisan make:migration make_gender_null_in_employees –table–employees
Open up /database/migrations/xxxxxxx_make_gender_null_in_employees.php file and modify to the following:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?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();
});
}
}
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.
Open up the terminator and run the following command to create a migration file for depts table:
1
php artisan make:migration depts
Open up /database/migrations/xxxxxxxxx_depts.php file and add the following codes:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?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');
}
}
Open up the terminator and run the following command to create the depts table:
1
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).
Open up the terminator and run the following command to create the migration for adding the dept_id to the employees table:
1
php artisan make:migration add_dept_id_in_employees —table=employees
Open up /database/migrations/xxxxxxxxx_add_dept_id_in_employees.php file and add the following codes:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<?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');
});
}
}
Open up the terminator and run the following command to execute the migration:
1
php artisan migrate
Database Seeding
In this section, we will add dummy data to our database. Seeding is a term that is used to describe the process of adding data to the database.
Open up the terminator and run the following command:
1
php artisan make:seeder DrinksTableSeeder
Open up /database/seeds/DrinksTableSeeder.php file and add the following codes:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?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',
]);
}
}
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.
The above table uses an array that matches database field name to values and inserts the record into the specified table drinks. Now let’s run the seed and add our dummy record to the database.
Open up the terminator and run the following command:
1
php artisan db:seed —class=DrinksTableSeeder