What is the best way to copy data from one field to another when creating a migration of a new field?

Question

I have a database table having a field that has a boolean field type. Now, as per the new requirement, the field should be changed to the small integer type.

In order to achieve it, I created a migration and added the script in the same migration file to copy the value from the old field to the new field. However, I think this is not the best approach that I have followed. Can someone please help and advise about the best way to handle this scenario.

public function up()
{
    Schema::table('skills', function (Blueprint $table) {
        $table->tinyInteger('skill_type_id')->nullable()->comment = '1 for advisory skills, 2 for tools, 3 for language & framework';
    });
    $skill_object = (new \App\Model\Skill());
    $skills = $skill_object->get();
    if (_count($skills)) {
        foreach($skills as $skill) {
            $skill_type  = 1;
            if ($skill->is_tool) {
                $skill_type = 2;
            }
            $skill_object->whereId($skill->id)->update(['skill_type_id' => $skill_type]);
        }
    }
}

score 2 · Answer 1 · answered Apr 09 '19 at 13:06

2

You can do it with 02 migrations, the first one is to create the new field, as already did. The second is create a migration with raw statement to copy value from old field to new field. If you don't need anymore old field, you can create a third migration deleting the old field.

public function up()
{
    Schema::table('skills', function (Blueprint $table) {
       DB::statement('UPDATE skills SET skill_type_id = IF(is_tool, 2, 1)');
    }
}

answered Apr 09 '19 at 13:06

Luis de Brito

692
4
7

1

what is the benefit of creating 3 migrations? I am looking for the best approach to handle this scenario. – Inderpreet Singh Apr 09 '19 at 13:17
With 03 migrations you will guarantee execution order, keep clean migration intentions and will be easy rollback in case you need. – Luis de Brito Apr 09 '19 at 15:17

score 2 · Answer 2 · answered Apr 10 '19 at 04:38

You can do this(update the data) from the following way in your scenario.

Create separate routes and update the data after the migrations.
Create seeder(having the same query as above in migrations file) run the seeder.

But above both solutions are little risky if you are trying to do this with your production database. If someone mistakenly hit URL and run seeder multiple time, It's difficult to manage.

I believe the best way to solve your problem by seed(modify) the data on the same migrations file after modifying the schema because migrations won't run again (even mistakenly), Once it migrated. You are doing the correct way as I believe.

score 1 · Answer 3 · answered Apr 10 '19 at 12:05

You are free to develop your own way to achieve this task, but as far as migrations are concerned, these are meant for controlling and sharing the application's database schema among the team, not the actual data ;)

You can create separate seeder for this task.
It will keep your migration clean and easy to rollback if needed.

NOTE: Don't include this seeder class in DatabaseSeeder.

These kind of seeder class are only meant for update the existing data after fixing the current functionality(I am taking into consideration, you have already fixed the code as per your new requirement). So, there is not need to worry about re run the same seeder class.

score 1 · Answer 4 · answered Apr 12 '19 at 07:45

Considering (laracast, stack-overflow), i will prefer to go by your way over the suggestions provided above as neither i have to maintain extra route nor additional migration (03).

The only improvement i can suggest here is you can use databse-transaction something like this :

// create new column
DB::transaction(function () {
  update new column
  delete old column
});

What is the best way to copy data from one field to another when creating a migration of a new field?

4 Answers4