Funke Olasupo

Posted on Jun 6, 2021

Adding and Removing columns from existing tables using Laravel migrations.

#laravel #php #database #codenewbie

Teaser😎

Have you been at that point where you finished setting up your database and realized you forgot to add a column 😨 or you put in a wrong column and you have to remove it 😣? Are you like me that usually refreshes the entire database for minor changes ? That's poor programming practice and what would you do if it was a company's database?
Nevertheless, I discovered an easy way out so please, enjoy the read!😎

Introduction

Laravel migrations simply allows you to easily perform certain actions to the database without going to the database manager (eg. phpMyAdmin). They can also serve as a version control for your database.

A default laravel migration file comes with a class definition containing both an up() and a down() method. The up() method is run when migration executes to apply changes to the database while the down() method is run to revert those changes.

P.S: Ensure that you have connected your laravel application to database before proceeding. If you're not sure how to do that, here is a simple guide.

Generating Migrations

A migration can be simply generated with the following command:

P.S: Migration files are in the database/migrations directory. The name of the table to be created is tests, you can change it to any preferred name.



php artisan make:migration create_tests_table

Laravel will use the name of the migration to attempt to guess the name of the table and whether or not the migration will be creating a new table. If Laravel is able to determine the table name from the migration name, Laravel will pre-fill the generated migration file with the specified table.

The migration file should look like this by default:



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

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

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

P.S : Schema::create is only used when a table is to be created initially. A common error is trying to use it to add a column to an existing table.

The tests table that should have two columns, name(string) and age(integer) will be written in the up() method as follows:



public function up()
    {
        Schema::create('tests', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->integer('age');
            $table->timestamps();
        });
    }

Running Migrations

To execute migrations to the database, run this Artisan command:



php artisan migrate

This command runs all outstanding migrations.

P.S: Confirm the database that it's been updated with the columns and their respective types.

Other Migration Commands

php artisan migrate:rollback : This rolls back the last batch of migrations.
php artisan migrate:reset : This rolls back all your applications migrations.
php artisan migrate:refresh : This rolls back all your migrations and execute the migrate command. Its like recreating your entire database.
php artisan migrate:fresh : This drops all the tables and executes the migrate command again.

P.S: The rollback always executes the corresponding down() method.

Updating Tables : Adding columns to an existing table.

A gender(string) column is added to the tests table by the following steps:

Create a migration file


 artisan make:migration add_gender_to_tests_table --table=tests

Using Schema::table in the up() method which will be provided by default, columns can be added as follows:



 public function up()
    {
        Schema::table('tests', function (Blueprint $table) {
            $table->string('gender');
        });
    }

Setting up the rollback option

The down() method should also be updated because of rollbacks.



public function down()
    {
        Schema::table('tests', function (Blueprint $table) {
            $table->dropColumn('gender');
        });
    }

Now execute the migrations.

To run the migrations, use this Artisan command :
php artisan migrate.

P.S : Confirm that the gender column has been added to the tests table on your database.

Note: Laravel places the added column last on the table, however it can be placed at any desired position on the table.

For the gender to be placed after the name, the up() method would rather be like this:



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

This looks more organized and better.

The gender column is successfully added to the tests table.

Updating Tables: Removing columns from an existing table.

There are several ways to remove a column from a table.

1.Remove a column

To remove the name column from tests table:

Create the migration file with this Artisan command:



php artisan make:migration drop_gender_from_tests_table --table=tests

Update the up() method with column you want to drop. ```php

public function up()
{
Schema::table('tests', function (Blueprint $table) {
$table->dropColumn('name');
});
}

* Run migrations
Execute the migrations with this `Artisan` command.
`php artisan migrate`

> __P.S : Confirm that the name column has been dropped on the tests table.__

![The tests table has dropped name column](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4nwkcslpco07mjwospjw.png)

> __P.S : A migrations file name is unique so every migration file should have different names when creating them.__

2.__Remove multiple columns__

In order to remove more than one column from your table , the same steps are followed as above but the `up()` method is slightly different. The column names are passed into an array as a single argument to `dropColumn()` like this:

```php


  public function up()
    {
        Schema::table('tests', function (Blueprint $table) {
            $table->dropColumn(['age', 'gender']);
        });
    }

Here are the results on the database:

3.Remove a column if it exists.

As usual, follow the same guides as outlined in the first method of removing column with the only slight difference in the up() method. However, the column will be checked if its existing before its dropped.
The up() method will be as follows:



 public function up()
    {
        Schema::table('tests', function (Blueprint $table) {
             //If the id column exists on tests table
            if (Schema::hasColumn('tests', 'id')){

                //drop the id column
                Schema::table('tests', function (Blueprint $table) 
    {
                    $table->dropColumn('id');
                });
            }