Laravel Migration - Update Enum Options

Question

I'm trying to add an enum option to a table (without losing the current dataset) using the schema builder.

The only thing I've really been able to find about column alteration is http://www.flipflops.org/2013/05/25/modify-an-existing-database-column-in-a-laravel-migration/ and I believe that was written for Laravel3.

Even so, I tried using the DB::query('ALTER TABLE ...'); command but it errored out with call_user_func_array() expects parameter 1 to be a valid callback, class 'Illuminate\Database\MySqlConnection' does not have a method 'query'.

DB::query("ALTER TABLE users CHANGE COLUMN permissions permissions ENUM('admin', 'user', 'candidate')"); 

I also tried doing this:

Schema::table('users', function ($table) {     $table->enum('permissions', array('admin', 'user', 'candidate'))->default('user'); }); 

but it errors out saying the column already exists.

What's the best way to do what I'm trying to do without losing all the data in that column?

Answer 1

Use the DB::statement method:

DB::statement("ALTER TABLE users CHANGE COLUMN permissions permissions ENUM('admin', 'user', 'candidate') NOT NULL DEFAULT 'user'"); 

Answer 2

I have adapted Joseph's answer into a method you can add to your migration and then call with an array of values instead of hardcoding them inside of a statement. It doesn't have any fancy handling of values with quotes in them, so please only use sensible values with it, or modify it for yourself.

private function setEnumValues($table, $column, array $values, $nullable = false, $default = null) {     $quotedValues = collect($values)         ->map(function ($value) {             return "'${value}'";         })         ->join(', ');      $suffix = '';      if (!$nullable) {         $suffix .= ' NOT NULL';     }      if ($default) {         $suffix .= " DEFAULT '${default}'";     }      $statement = <<<SQL ALTER TABLE ${table} CHANGE COLUMN ${column} ${column} ENUM(${quotedValues}) ${suffix} SQL;      \Illuminate\Support\Facades\DB::statement($statement); } 

You might use it in a migration like so:

<?php  use Illuminate\Database\Migrations\Migration;  class AddQueuedStatusToPaymentsTable extends Migration {     /**      * Run the migrations.      *      * @return void      */     public function up()     {         // Add the new 'queued' value          $this->setEnumValues(             'payments',             'status',             [                 'queued',                 'processing',                 'successful',                 'failed',             ],             false, // Not nullable             'queued' // Mark it as the default for all new payments         );     }      /**      * Reverse the migrations.      *      * @return void      */     public function down()     {         // Remove the new 'queued' value                  $this->setEnumValues(             'payments',             'status',             [                 'processing',                 'successful',                 'failed',             ],             false, // Not nullable             'processing' // Set the default back to processing for all new payments         );     }      private function setEnumValues($table, $column, array $values, $nullable = false, $default = null)     {         $quotedValues = collect($values)             ->map(function ($value) {                 return "'${value}'";             })             ->join(', ');          $suffix = '';          if (!$nullable) {             $suffix .= ' NOT NULL';         }          if ($default) {             $suffix .= " DEFAULT '${default}'";         }          $statement = <<<SQL ALTER TABLE ${table} CHANGE COLUMN ${column} ${column} ENUM(${quotedValues}) ${suffix} SQL;          \Illuminate\Support\Facades\DB::statement($statement);     } }