Level 122
They can be an issue, and offer no real benefit IMO
2 likes
Jun 2, 2025
18
Level 63
Databases and enum
Hello,
Hmmm ... I wonder if setting an enum field in a field declaration (for example with MariaDB) is a good idea.
If the application evolves with future new items in the enum, I need to change the database definition. Whereas if I just change the enum items in the code, there are fewer changes to do.
What about your opinion about having enum in the database tables ?
Thanks for sharing your experience.
V
Level 14
depends on the enum, and how often it changes.
IMO for something like ticket statuses: opened, closed, pending; an enum field would be better due to smaller footprint than storing as varchar and better indexing. However for something that you are not sure it would change in the future, example user roles, better to NOT use them.
1 like
Level 31
@krisi_gjika everything tends to change in future, so why complicate your life?
1 like
Level 14
@Glukinho enums are fine if you need to add new states to them once in a while, you just run an alter query. But don't use them if you think you may need to remove something. I always prefer the database to be responsible for the integrity of it's own data.
Example: an enum casted field, should not accept an enum value that is not defined in the database. If I pass the wrong enum value to the Model::create I want the operation to fail there, not cause some undefined behaviour later when I try to read that value.
Also when ordering rows via enum fields I want to be explicit about the order of the enum states and not sort alphabetically.
2 likes
Level 73
I would not use enums in the database, it will not benifit you in any particular way, and as others said it might add complexity to your app later on.
It also would remove some of the integrity constraints built into the database.
1 like
Level 24
@vincent15000 For me, avoid use enum in migration
$table->enum('status', ['b', 'a', 'c']);
i don't know other database, but MySQL sorting the enum column is based on the key not the value
so becareful of this, the result is
order by `status` asc
b
a
c
not
a
b
c
and also when Testing the sorting enum column, you need test the key not the value
test('table can sort records by enum status', function () {
...
$getIndex = fn (\BackedEnum $enum): int => array_search($enum, Status::cases());
$getIndex($model->status)
...
});
1 like
Level 63
@newbie360 I wrote tests with the value and not the key ... and it works fine.
Level 80
@vincent15000 I never use enum-type columns in migrations, because you can’t edit cases once you create the column; you have to completely drop it and re-create the column each and every time you want to change cases.
For this reason, I’ll just define the column as a string-type column, but then use an enumeration in my application code to enforce values:
$table->string('status');
enum OrderStatus: string
{
case Open = 'open';
case Processing = 'processing';
case Complete = 'complete';
case Cancelled = 'cancelled';
}
class Order extends Model
{
protected function casts(): array
{
return [
'status' => OrderStatus::class,
];
}
}
3 likes
Level 51
Since their introduction, @martinbean I find my models having two or three casted enum strings and they're so nice and clean. Once you adopt this simple pattern, @vincent15000 you'll be using these everywhere!
1 like
Level 122
@mercury64 but now we have enum classes in php which are way more performant and flexible than a static table.
The best primer on enum in php. https://stitcher.io/blog/php-enums
2 likes
Level 63
@jaseofspades88 I have also some enum (status for example) in my models, effectively it's nice and clean.
My question was essentially for the database.
Level 63
@martinbean This is effectively the main question : how to update the column type without removing and recreating it.
The only way I have found is to update the column manually. But it's not an ideal solution. And I don't want to have to remove it.
Level 14
@vincent15000 not that complicated really:
public function up()
{
// use hardcoded values instead of Enum constants
// if the values of the constants change in the future
// it should not affect the existing schema if this migration is rerun
// since this could have unexpected consequences for existing data
$values = collect(['open', 'closed', 'pending'])
->map(fn ($val) => "'{$val}'")
->implode(',');
DB::statement("ALTER TABLE `tickets` CHANGE `status` `status` enum({$values});");
}
public function down()
{
$values = collect(['open', 'closed'])
->map(fn ($val) => "'{$val}'")
->implode(',');
DB::statement("ALTER TABLE `ticktes` CHANGE `status` `status` enum({$values});");
}
1 like
Level 80
This is effectively the main question : how to update the column type without removing and recreating it.
@vincent15000 You don’t. You just use a string-type column instead, and enforce the enumeration cases in your application instead.
2 likes
Level 122
So you have your answer?
1 like
Level 63
@Snapey Yes sure ;) ... I will probably remove the enum type from the field in the database and use a simple string type instead of.
As the enum values are already handled in the application with a PHP enum class, it will be ok for me.
Level 1
Adding, removing, or even reordering enum values necessitates an ALTER TABLE operation, leading to potential downtime, complex migration scripts, and tight coupling between your application code and database schema.
2 likes
Please or to participate in this conversation.