DEV Community

loading...

Adding and Removing columns from existing tables using Laravel migrations.

roxie profile image Funke Olasupo ・5 min read

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 
Enter fullscreen mode Exit fullscreen mode

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');
    }
}
Enter fullscreen mode Exit fullscreen mode

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();
        });
    }
Enter fullscreen mode Exit fullscreen mode

Running Migrations

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

php artisan migrate
Enter fullscreen mode Exit fullscreen mode

This command runs all outstanding migrations.

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

The tests table has an id, name and gender column

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

php 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');
        });
    }
Enter fullscreen mode Exit fullscreen mode
  • 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');
        });
    }
Enter fullscreen mode Exit fullscreen mode
  • 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.

gender column has been added as last column on tests table

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');
        });
    }
Enter fullscreen mode Exit fullscreen mode

This looks more organized and better.
The test table has gender column after name

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
Enter fullscreen mode Exit fullscreen mode
  • Update the up() method with column you want to drop.
public function up()
    {
        Schema::table('tests', function (Blueprint $table) {
            $table->dropColumn('name');
        });
    }
Enter fullscreen mode Exit fullscreen mode
  • 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

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:

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

Here are the results on the database:
The table has dropped age and gender

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');
                });
            }
Enter fullscreen mode Exit fullscreen mode

After running the migrations, here is the final output of our database:
The id column is dropped

The id, name, age and gender column is successfully removed from the table.

Conclusion

Now, you don't need to refresh your database always for these minor changes😎
This entire code is open source on Github😍.
Thank you for reading🤝.

Discussion (0)

Forem Open with the Forem app