DEV Community

M H Hasib
M H Hasib

Posted on • Updated on

Update existing table's column with migration without losing data in Laravel

Before going to start our coding we need to know about Laravel Migration and how it helps us.

First of all, if we want to build a project. We need to design our database first. So what can we do? We can design our database directly. If you miss some field/column or some datatype, you need to fix it directly into the database which is a bad practice in Laravel.

In another scenario, Suppose you have a project and it needs to set up to your friend's device, you might need to send the SQL file; which is painful.

Now think if you have the feature like you pull the project and run a command, Boom your database is being set up. Isn't it very helpful?

Laravel migration does the same. You don't need to take the pain of SQL file or if you miss a data type or any spelling mistake, you can fix it through your coding. You need not work directly with the database. You will fix it to your code and just run php artisan migrate:refresh. So hopefully you got the benefit of Laravel migration.

Now let's jump to another scenario.

Sometimes we are much worried when we need to add or modify our existing database table.

For example, we have a User Model and users migration table and it contains name, username, email_verified_at password.

<?php

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

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

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

Enter fullscreen mode Exit fullscreen mode

But after deploying our project and after having our many users we found, we need to add a new field in our table like phone_number. Also mentioned, we need to keep our existing data.

So, we will create a new migration file for adding a new column in our existing table.

Now use this command:

php artisan make:migration add_phone_number_to_users_table

Here we need to ensure that the table's name needs to match the new migration file like we use users in both places.

After using the command we can see a new migration file like this:

<?php

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

class AddPhoneNumberToUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            //
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            //
        });
    }
}
Enter fullscreen mode Exit fullscreen mode

Now you can add your desire field in this migration file. As we need a phone_number field.

<?php

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

class AddPhoneNumberToUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->string('phone_number')->unique()->after('email');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            //
        });
    }
}
Enter fullscreen mode Exit fullscreen mode

Here we added phone_number field after email in up() method. You have the flexibility to put that column where ever you want by using after() or before(). You can learn more modifier from Laravel Documentation Column Modifier

Now we are one step behind from our job done.

<?php

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

class AddPhoneNumberToUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->string('phone_number')->unique()->after('email');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('phone_number');
        });
    }
}
Enter fullscreen mode Exit fullscreen mode

Here you can see that we are adding $table->dropColumn('phone_number'); so that if we run the rollback command and it will work properly. If you have many filed you can use array of dropcolumn like this $table->dropColumn(['phone_number']);

And finally, here we go...

Now you need to run this command:

php artisan migrate

And we have done our job. :D

You can visit Laravel Migration Documentation for more migration options.

Hope it will help you.

Top comments (13)

Collapse
 
mukul98s profile image
Mukul Sharma

I though with ORM it's only one way. Thanks for this great article.

Collapse
 
brian001 profile image
Brian Kanyi Karanja

Well discussed. It worked for me. Thank you

Collapse
 
mahmudulhsn profile image
M H Hasib

Thanks.

Collapse
 
fatimaalmashhor profile image
Fatima Almashhor

Thank you for this . it save my life.

Collapse
 
dgrinderhz profile image
Hassan Zekkouri

Thank you. It saved me time.

Collapse
 
inimist profile image
Arvind Kumar

Thanks!

Collapse
 
mahmudulhsn profile image
M H Hasib

Welcome Brother.

Collapse
 
krognome profile image
krognome

So I'm pretty damns sure if I run php artisan migrate it will without any question run all my migration files and delete all of the data. I see people thanking you here like they did not lose all their data but it always removes all the data every time I run that command. So I simply run the 1 migration file with: php artisan migrate --path=/database/migrations/full_migration_file_name_migration.php

Collapse
 
jackleemmerdeur profile image
JackLeEmmerdeur • Edited

I just tried this. Before I always used php artisan migrate:refresh, but I needed schema altering with data retention.

For me php artisan migrate only applied the new migration and left the data of unmodified tables untouched, opposed to the above refresh-modifier.

Maybe it has been, like you described, in an old Laravel version?
If unsure there is also php artisan migrate --pretend. It shows the SQL the migrations would produce without executing them.

Collapse
 
mahmudulhsn profile image
M H Hasib • Edited

php artisan migrate will execute the files which is not in migrations table. Please read the documentation carefully.

Collapse
 
vitalijalbu profile image
vit

So basically guys, if I try to add phone_number inside the users migration file 1, it will drop table and create another one? So my data will be lost

Collapse
 
tanvirprince profile image
Tanvir Rahman Prince

Thanks. This is very helpful

Collapse
 
mahmudulhsn profile image
M H Hasib

You are welcome <3