Database Migrations

Introduction

Hopefully you have read the previous entries which I posted: Overview of Laravel, Working with databases in Laravel and Laravel Models. Based on that I assume you now have a good grasp of how this powerful framework is structure. For this post, I will be showing how to create the actual databases tables in Laravel using Migrations. This module helps you move the SQL scripts that you created for each table and migrate it to Laravel. Laravel defines migrations as the version control for your database, allowing a team to easily modify and share the application’s database schema. If you have browsed through the Laravel documentation, you will notice the section Migration is huge, however, we will be focusing on few sub-sections only. This is because we will not be able to cover all of them in one blog post. Therefore, only the sections concerning the creation of tables will be covered. If you recall from the previous blog, I talked about creating models which are basically classes that correspond to the tables in our database. So, we will see how to create tables, add columns, indexes, foreign keys and then I will show you an example migrated from the SQL format and the corresponding Laravel code for it.

Generating Migrations

The new migration files will be placed in the database/migrations directory. Each migration file name contains timestamp which allows Laravel to determine the order of the migrations. Laravel gives you the possibility to use –table and –create options to indicate the name of the table and whether the migration will be creating a new table. First, let’s see how to create a migration using the make:migration Artisan command:

php artisan make:migration create_users_table

Next, to use the options to indicate the name of the table and whether the migration will be creating a new table:

php artisan make:migration add_votes_to_users_table — table=users

php artisan make:migration create_users_table — create=users

Migration Structure

A migration class contains two methods: up and down. These are the two method we will be using to add new tables, columns, indexes and foreign keys in the database. Specifically, the up method is the one we will be using whereas the down method simply reverses the operation performed by the up method. So, within these methods will be able to use the Laravel schema builder to create and modify tables. Note that there are many methods available on the schema builder which you can check out at: https://laravel.com/docs/5.2/migrations#creating-tables. Now let’s see what a migration class looks like:

<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateFlightsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create(‘flights’, function (Blueprint $table) {
$table->increments(‘id’);
$table->string(‘name’);
$table->string(‘airline’);
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop(‘flights’);
}
}

As you have noticed, much of the work is done in the up method and the down method is used to drop the table whenever needed. Also, the class must extends Migration class located in the namespaces:

use Illuminate\Database\Schema\Blueprint;

use Illuminate\Database\Migrations\Migration;

Creating Tables: To create a new database table, use the create method from the schema facade. This method accepts tow arguments; the first one is the name of the table. The second one is a closure which receives a Blueprint object used to define the new table. Here is how this method is called:

Schema::create(‘users’, function (Blueprint $table) {
$table->increments(‘id’);
});

After creating the table using the above code, we can move on and see how to create columns for the table.

Creating columns: Creating columns comes down to updating an existing table or adding new columns while creating the table as we saw in our first example. For example, to update an existing table, you will need to use the table method on the schema facade. Just like the create method, the table method accepts two arguments: the name of the table and a closure that receives a Blueprint instance which is used to add columns to the table. Here is a sample code:

Schema::table(‘users’, function ($table) {
$table->string(‘email’);
});

Now let’s see an actual example where I create a new migration class which corresponds to a table I have in my SQL file. First, the following script is the SQL script for the table called ‘devices’:

— — — — — — — — — — — — — — — — — — — — — — — — — — — -
— Table `devices`
— — — — — — — — — — — — — — — — — — — — — — — — — — — -
CREATE TABLE IF NOT EXISTS `devices` (
`id` INT NOT NULL AUTO_INCREMENT, /* Column #1 */
`key` CHAR(32) NOT NULL, /* Column #2 */
`created` DATETIME NOT NULL, /* Column #3 */
`last_received` DATETIME NULL, /* Column #4 */
`os_version` VARCHAR(45) NULL, /* Column #5 */
`os_arch` INT NULL, /* Column #6 */
`country_id` INT NOT NULL, /* Column #7 */
`locale_id` INT NULL, /* Column #8 */
PRIMARY KEY (`id`),
UNIQUE INDEX `key_UNIQUE` (`key` ASC), /* Index #1 */
INDEX `uniqueusers_fk_idx` (`country_id` ASC), /* Index #
2 */
INDEX `uniqueusers_fk2_idx` (`locale_id` ASC), /* Index #3 */
CONSTRAINT `uniqueusers_fk1` /* Foreign key #1 */
FOREIGN KEY (`country_id`)
REFERENCES `countries` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `uniqueusers_fk2` /* Foreign key #2 */
FOREIGN KEY (`locale_id`)
REFERENCES `locales` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

Now we are going to create a migration class for the above table. Before we do that, let’s explain how the columns, indexes and foreign keys in the above table will be represented in our class:

Columns:

1. As stated in the previous section, each table has an id column which is automatically created for us:

$table->increments(‘id’);

2. The next column is the key which is of type CHAR (32). The NOT NULL is added automatically by Laravel:

$table->char(‘key’, 32);

3. The next column is the created and is of type DATETIME.

$table->dateTime(‘created’);

4. The next column is last_received and the datatype is DATETIME. This column can be NULL, so the modifier is added.

$table->dateTime(‘last_received’) -> nullable();

5. The column name is os_version and the datatype is VARCH (45).

$table->string(‘os_version’, 45);

6. The column name is os_arch and the datatype is INT.

$table->integer(‘os_arch’);

7. The column name is country_id and the datatype is INT.

$table->integer(‘country_id’) ->unsigned();

8. The next column is the locale_id and the datatype is INT.

$table->integer(‘locale_id’) -> unsigned();

Indexes:

The schema builder supports several types of indexes. So, to create an index, you simply need to call the unique key word onto the column definition. In our example, there is only on key so the unique index will be assigned to this column.

$table->unique(‘key’);

Foreign Key Constraints:

In our example, the foreign key is country_id and it references the id column in the countries table. One thing to note is when adding the foreign key constraint, you may specify the action for the “on delete” and “update” properties. For us, since ON DELETE and ON UPDATE have NO ACTION, the corresponding method ->onDelete () and ->onUpdate () can be omitted.

$table->foreign(‘country_id’)->references(‘id’)->on(‘countries’);

The second foreign key in our example is the locale_id and it references the id column in the locales table.

$table->foreign(‘locale_id’)->references(‘id’)->on(‘locales’);

For more information on creating columns, indexes and foreign keys constraints, check out the Laravel Documentation on database migrations. The following is the entire program created in Laravel to implement the “devices” table that we are using as an example:

<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateDevicesTable extends Migration
{
/**
* Run the migrations.
* @return void
*/
public function up()
{
Schema::create(‘devices’, function (Blueprint $table) {
$table->increments(‘id’);
$table->char(‘key’, 32);
$table->dateTime(‘created’);
$table->dateTime(‘last_received’)->nullable();
$table->string(‘os_version’, 45);
$table->integer(‘os_arch’);
$table->integer(‘country_id’)->unsigned();
$table->integer(‘locale_id’)->unsigned();
$table->unique(‘key’);
$table->foreign(‘country_id’)->references(‘id’)->on(‘countries’);
$table->foreign(‘locale_id’)->references(‘id’)->on(‘locales’);
});
}
/**
* Reverse the migrations.
* @return void
*/
public function down()
{
Schema::drop(‘devices’);
}
}

Conclusion

With this sample code, you now know how to create a migration class for your SQL table. So, if you have been following all along these posts, I hope you have learned the basic structure of this popular and powerful framework. Also, you are now excited to go ahead and learned more about this framework. To recap, Laravel is PHP’s framework for building powerful web applications implementing the MVC model architecture. With Laravel, you can create an entire web application and add a database of your choice to all in an easy and structured manner. Furthermore, the Laravel documentation provides you with all the tools you will need to learn and develop new web applications using this framework. Finally, I hope you have enjoyed reading these blogs and I wish you great success in your journey to learning Laravel.

Show your support

Clapping shows how much you appreciated Mamadou Diallo’s story.