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);
    }
}

Answer 3

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

Answer 4

In my case I wanted to add 'Wide_Skyscraper' to my existing enum options, the accepted answer points to the solution using DB::transaction and DB::statement, but none of the answers indicate a safe way to try/catch wrap and rollBack on issues when entering a RAW statement into the SQL.

Your migration file's UP method:

use Illuminate\Support\Facades\DB;

//...

public function up()
{
    try {
        DB::transaction(function () {
            DB::statement("ALTER TABLE `advertisements` MODIFY COLUMN `type` ENUM('MREC', 'Skyscraper', 'Landscape', 'Wide_Skyscraper')");
        });
    } catch (Exception $e) {
        DB::rollBack();
    }
}

You can just have a comment in your down method, don't remove the method.

public function down()
{
    // Do nothing.
}

Hope this helps someone.

Answer 5

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(); 
});

https://laravel.com/docs/5.8/migrations#modifying-columns