Why the enum field type at the base level is evil

Why the enum field type at the base level is evil

Developers often wonder why a field type is not recommended enum in the database, and in this article we will look at all the pros and cons of this type.

Column type enum is used to store data that can take on specific values ​​from a predefined set. It provides a limit on the values ​​that a column can take and allows strict control over the data. This can be useful for storing statuses, categories, types, or any other values ​​that can only be specified from a limited set of options.

But this is theoretical. And what in practice? Let’s take a look.

Suppose we have a table with a list of payments that contains a column status with the following values:

CREATE TABLE `payments`(  
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `status` ENUM('new', 'progress', 'done', 'fauled') NOT NULL,
   KEY(`id`)
) CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

After some time, a grammatical error in the word “failed” was noticed and a decision was made to correct it.

The nuance of changing the enum field is that when it is edited, the values ​​of the column in are reset null for all rows of the table, or even we will get an error altogether Data truncated for column 'status' at row 3. That is, to change correctly enum field, you need to save the data somewhere. The action plan will be as follows:

  1. Create a new one enum a column with the correct data set;

  2. Copy the value from the old column to the new one and immediately apply the value correction;

  3. Delete the old one enum column;

  4. Rename the new one enum the column

When using the Laravel framework, it will look like this:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        // Создаём новую колонку
        Schema::table('payments', function (Blueprint $table) {
            $table->enum('tmp_status', ['new', 'progress', 'done', 'failed']);
        });

        // Копируем значения из enum колонки в новую с корректировкой значения
        DB::statement('UPDATE payments SET tmp_status = (IF status = \'fauled\' THEN \'failed\' ELSE status END IF)');

        // Удаляем старую колонку
        Schema::table('payments', function (Blueprint $table) {
            $table->dropColumn('status');
        });

        // Переименовываем колонку
        Schema::table('payments', function (Blueprint $table) {
            $table->renameColumn('tmp_status', 'status');
        });
    }
};

In SQL, these actions would look like this:

ALTER TABLE `payments`
  ADD COLUMN `tmp_status` ENUM('new', 'progress', 'done', 'failed') NOT NULL AFTER `status`;

UPDATE `payments` SET `tmp_status` = (
    IF `status` = 'fauled'
    THEN 'failed'
    ELSE `status` END IF
);

ALTER TABLE `payments`
  DROP COLUMN `status`;

ALTER TABLE `payments`
  CHANGE `tmp_status` `status` ENUM('new', 'progress', 'done', 'failed') CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci NULL;

When using the Laravel framework, you can’t just take and apply the “change” method on the type field enum.This throws an error:

Unknown column type “enum” requested. Any doctrine type you use to register with \Doctrine\DBAL\Types\Type::addType()…

To solve this problem, you have to use the following crutch before applying the changes:

protected function fixEnum(): void
{
    $platform = Schema::getConnection()->getDoctrineSchemaManager()->getDatabasePlatform();
    $platform->registerDoctrineTypeMapping('enum', 'string');
}

And, in addition, it will still be necessary on the application side to process the value either as a class constant or as enum class. Example:

class Payment extends Model
{
    protected $casts = [
        'status' => StatusEnum::class,
    ];
}

enum StatusEnum: string
{
    case New      = 'new';
    case Progress="progress";
    case Done="done";
    case Failed   = 'failed';
}

And now imagine that as soon as this task is completed, a new one has arrived – add a new status – Refund

An alternative to this pain is the rejection of DB type fields enum for integer. In this case, both when changing the name and when adding a new one, all you need to do is to change the content itself enum class Example:

// До
enum StatusEnum: int
{
    case New      = 0;
    case Progress = 1;
    case Done     = 2;
    case Fauled   = 3;
}

// После
enum StatusEnum: int
{
    case New      = 0;
    case Progress = 1;
    case Done     = 2;
    case Failed   = 3;
    case Refund   = 4;
}

And that’s all. No pain and the task is completed in just a few seconds.

That is why many developers refuse to use type columns enum in the database, saving not only the time spent on the task, but also nerves and, as a pleasant bonus, the chance of losing data is reduced to zero.

You can also answer the question “why integer and not string”: the fact is that the field type string prone to grammatical errors and all they provide is the convenience of reading such data in one specific table without using SQL queries using JOIN functions. But if a mistake is made in the word or the word needs to be replaced with another, you will have to send a request to the database, which is not necessary when using integer values.

Thus, the type of fields enum the basis is irrational and senseless influence except in extremely rare and specific cases.

Conclusion

At the same time, it is necessary to always validate the data before writing to the database, even if the column type is used enumotherwise, there is a high probability of catching a 500 error from the base instead of correct processing.

In the same Laravel, the validation rule is implemented very simply:

public function rules(): array
{
    return [
        'status' => [Rule::enum(StatusEnum::class)],
    ];
}

And at the exit, in case of problems, we will receive the correct code of four hundred series with decoding instead of `500 Whoops! Something wrong’.

It is very convenient and as practical as possible.

Related posts