Laravel Migration - Update Enum Options
LaravelLaravel Problem Overview
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?
Laravel Solutions
Solution 1 - Laravel
Use the DB::statement
method:
DB::statement("ALTER TABLE users CHANGE COLUMN permissions permissions ENUM('admin', 'user', 'candidate') NOT NULL DEFAULT 'user'");
Solution 2 - Laravel
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);
}
}
Solution 3 - Laravel
If none of the above options worked for you, try this old solution. It still works. It was the only thing that worked in my case.
DB::transaction(function () {
DB::statement('ALTER TABLE mytable DROP CONSTRAINT mytable_status_check;');
DB::statement('ALTER TABLE mytable ADD CONSTRAINT mytable_status_check CHECK (status::TEXT = ANY (ARRAY[\'pending\'::CHARACTER VARYING, \'accepted\'::CHARACTER VARYING, \'canceled\'::CHARACTER VARYING]::TEXT[]))');
});
"my_table" is the name of your table and "status" is the name of the enum column
Solution 4 - Laravel
Modifying Columns
Prerequisites
Before modifying a column, be sure to add the doctrine/dbal dependency to your composer.json file. The Doctrine DBAL library is used to determine the current state of the column and create the SQL queries needed to make the specified adjustments to the column:
Schema::table('users', function (Blueprint $table) {
$table->enum('name', [])->change();
});