Database migrations in Laravel

Migrations in Laravel are the closest thing to a version control system for a database. They allow you to track changes made to the database schema and provide a mechanism for the automated installation of those changes.

How many times have we had to create an installation script to modify or add a field in the database of a production system? How do we know if that change has already been installed? Laravel migrations seek to address some of the questions that arise from this scenario, such as the following:

- Can we automate making a database change?
- Is it possible to know if a database change has been executed?
- Is it possible to roll back that change?
- Can we track changes in the database?

Let's see what migrations are all about.

Creating Migrations

The general command to create a migration is as follows:

php artisan make:migration migration_name

Where migration_name is the name of the migration you want to create. So, to see what a migration consists of, let's execute the following command in a Laravel-configured project.

php artisan make:migration table_catalogue

The output of this command will be similar to the following:

Created migration: 2019_07_09_023027_table_catalogue

The migration file name is randomly generated by artisan and corresponds to the current date concatenated with a timestamp type and the argument we entered in the make:migration command. This new migration will be located in the database/migrations directory and will have the following content:

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

class TableCatalogue extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        //
    }

    /**
      * Reverse the migrations.
      *
      * @return void
     */
    public function down()
    {
        //
    }
}

Basically, our migration has two methods, up and down, which are used to add new tables, columns, indexes, and so on, and to reverse the changes made, respectively. Next, let's see how to create a table, define its fields, constraints, and more.

Table Definition

For defining a database table, it is much simpler to execute the following command, which creates a scaffolding for the table structure:

php artisan make:migration create_catalogue_table

The first thing to note here (before reviewing the migration) is that the previous command refers to a pattern defined by create_tablename_table, which will always create the table with the name enclosed in _ (underscore) characters. For our particular case, it will create a migration file for the catalogue table.

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

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

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

Secondly, we can observe that Laravel has automatically created the reverse of the migration, so we don't have to worry about it. Both the up and down methods use the Schema facade, which provides a great number of utilities for defining the database schema and validations. For now, let's see how we can create different data types (or at least the most commonly used ones) in databases. Let's take a look at the definition of our catalogue table with several data types.

Schema::create('catalogue', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('description');
    $table->string('brand')->nullable();
    $table->string('model', 30)->nullable();
    $table->text('observations');
    $table->date('next_review');
    $table->integer('minimum_stock');
    $table->decimal('unit_value', 8, 2);
    $table->enum('status', ['active', 'inactive']);
    $table->timestamps();
});

For simplicity, we have only included the content of the up method. Let's analyze each line of the previous definition based on the assumption that it has been executed in the Mysql database engine.

$table->bigIncrements('id');

The bigIncrements method creates a bigint field with a length of 20, unsigned, and serves as the primary key of the table.

$table->string('description');

The string method creates a varchar field with a length of 255.

$table->string('brand')->nullable();

In this case, the only difference from the previous definition is that the column will be created with the ability to insert null values.

$table->string('model', 30)->nullable();

If a second parameter is added to the string method, it will also define the length of the field.

$table->text('observations');

The text method creates a text column in the table.

$table->date('next_review');

The date method creates a date column in the table.

$table->integer('minimum_stock');

The integer method creates a column of type int with a length of 11 in the table.

$table->decimal('valor_unitario', 8, 2);

The decimal method creates a column of type decimal with precision 8 and scale 2.

$table->enum('estado', ['activo', 'inactivo']);

The enum method creates a column of type enum with the allowed values passed as the second parameter in the array.

Table Modification

To modify a table definition, you should use the change method as the last method in the field definition statement in the following way:

Schema::table('calogue', function (Blueprint $table) {
    $table->string('description', 50)->nullable()->change();
});

This type of modification is usually used in a production system where a functional version of the software is already installed. It is also done in a separate migration from the table creation. Otherwise, in development environments, it makes more sense to make this modification in the migration for table creation.

To delete a column, simply use the drop method.

Schema::table('calogue', function (Blueprint $table) {
    $table->dropColumn('marca');
});

SQL Code in Migrations

If the alteration to be made in the database is too specific and you cannot find a native way to do it in Laravel, you can use the DB facade.

DB::statement("UPDATE credit_concepts SET description = SUBSTR(description, 1, 60)");

Running Migrations

The way to run pending migrations is by using the command:

php artisan migrate

Laravel will execute the migrations that have not been executed yet, ensuring that in production environments, the commands to create the tables that already exist are not run again. If you run this command in a clean Laravel project, you will see an output similar to the following:

Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table
Migrating: 2019_07_09_025421_create_calogue_table
Migrated:  2019_07_09_025421_create_calogue_table

Laravel includes two default migrations to create a users table (users) and a password reset table (create_password_resets_table). If you don't consider them necessary, you can delete these migrations. For a more in-depth exploration of this topic, I recommend consulting the official documentation at the following link:

https://laravel.com/docs/master/migrations

I hope this post has been helpful and has provided you with the necessary tools to continue with more advanced topics. Until next time!.

---