With Laravel Migration, How to change data type of a column and update its existing data to fit the new data type, without using raw SQL queries?
Here is how I performed this:
The up() method:
...use App\User;/** * Run the migrations. * * @return void */public function up(){ // do the following steps in order: // 1- add a new column with the desired data type to the table // 2- fill the new column with the appropriate data // 3- delete the old column // 4- rename the new column to match name of the deleted column Schema::table('users', function(Blueprint $table) { // 1- add a new column with the desired data type to the table // note that after() method is used to order the column and works only with MySQL $table->boolean('is_suspended_new')->default(0)->after('is_suspended'); }); // 2- fill the new column with the appropriate data // note that you may need to use data in the old column as a guide (like in this example) $users = User::all(); if ($users) { foreach ($users as $user) { $u = User::find($user->id); if ($u->is_suspended === 'yes') { $u->is_suspended_new = 1; } elseif ($u->is_suspended === 'no') { $u->is_suspended_new = 0; } $u->save(); } } Schema::table('users', function(Blueprint $table) { // 3- delete the old column $table->dropColumn('is_suspended'); }); Schema::table('users', function(Blueprint $table) { // 4- rename the new column to match name of the deleted column $table->renameColumn('is_suspended_new', 'is_suspended'); });}
The down() method:
/** * Reverse the migrations. * * @return void */public function down(){ // IMPORTANT NOTE: // because renaming (enum) column types is not supported in laravel 4.2 (as mentioned here https://laravel.com/docs/4.2/schema#renaming-columns) // so we will make some changes in the order of the steps as follows: // // do the following steps in order: // 1- rename the exiting column // 2- add a new column with the desired data type and give it a name matches name of the existing column before renaming // 3- fill the new column with the appropriate data // 4- delete the old column Schema::table('users', function(Blueprint $table) { // 1- rename the existing column $table->renameColumn('is_suspended', 'is_suspended_old'); }); Schema::table('users', function(Blueprint $table) { // 2- add a new column with the desired data type to the table // and give it a name matches name of the existing column before renaming // note that after() method is used to order the column and works only with MySQL $table->enum('is_suspended', ['yes', 'no'])->default('no')->after('is_suspended_old'); }); // 3- fill the new column with the approprite data // note that you may need to use data in the old column as a guide (like in this example) $users = User::all(); if ($users) { foreach ($users as $user) { $u = User::find($user->id); if ($u->is_suspended_old == 1) { $u->is_suspended = 'yes'; } elseif ($u->is_suspended_old == 0) { $u->is_suspended = 'no'; } $u->save(); } } Schema::table('users', function(Blueprint $table) { // 4- delete the old column $table->dropColumn('is_suspended_old'); });}