Why Database Enums Aren't as Simple as They Look (and What to Use Instead)

Why enums look appealing

When you have a field like status, role, or type that can only take a handful of values, a database enum feels like the obvious choice. You get free validation at the database level, a compact storage format, and, in Prisma's case, a generated TypeScript union type that autocompletes in your editor. What's not to like?

In practice, native database enums cause more problems than they solve once an application grows past its first few months. Here is why, and what to use instead.

The downsides of native enums

1. Changing them means a migration, every time

An enum value is not just data, it is part of your schema. Adding "REFUNDED" to an OrderStatus enum means writing a migration, running it against every environment, and in Prisma's case, regenerating the client and redeploying your app before the new value can even be used. Compare that to a normal lookup row, which a product manager could add through an admin panel in ten seconds.

2. PostgreSQL enums are especially rigid

In PostgreSQL, adding a value with ALTER TYPE ... ADD VALUE is possible, but for a long time it could not run inside the same transaction as other statements, which breaks Prisma's transactional migrations in some workflows. Removing or renaming a value is even harder: Postgres has no DROP VALUE at all. If you shipped a typo like "CANCELED" next to "CANCELLED", you are stuck with both forever, or you rebuild the type from scratch and re-point every column that uses it.

3. MySQL enums encode meaning in position

MySQL stores enum values as an internal integer index based on their declared order. Sorting an enum column with ORDER BY sorts by that index, not alphabetically, which is a common source of confusing bugs. Insert a new value in the middle of the list on an older MySQL version and you can trigger a full table rewrite.

4. No room for metadata

An enum value is just a label. The moment you need a color for a status badge, a sort order, a description, or a flag for whether a status is still selectable by new users, you are back to storing that metadata in application code, in a giant switch statement, completely disconnected from the database.

5. Not editable by non-developers

If your product ever needs an admin, a client, or a non-technical teammate to add or rename a category, a tag, or a status, a hardcoded enum is a wall. Every change needs a developer, a migration, a deploy.

6. Portability and tooling friction

SQLite has no native enum type at all. Switching databases, or supporting more than one, means your enum strategy does not travel with you. Prisma works around this by emulating enums as a check constraint on some providers, which is one more thing that behaves differently across databases.

A NestJS + Prisma example of the problem

A typical first pass looks like this:

// schema.prisma
enum OrderStatus {
  PENDING
  PAID
  SHIPPED
  CANCELLED
}

model Order {
  id        Int         @id @default(autoincrement())
  status    OrderStatus @default(PENDING)
  total     Decimal
  createdAt DateTime    @default(now())
}

It works fine, until support asks for a REFUNDED status, or marketing wants each status to show a different color and icon in the dashboard. Now you need prisma migrate dev, a client regeneration, a deploy, and a matching switch statement somewhere in the frontend for colors and labels.

Alternative 1: a lookup table with a foreign key

The classic relational fix is to turn the enum into its own small table, and reference it with a foreign key. This is more setup, but it buys back everything a hardcoded enum takes away: new values without a migration, metadata per value, and the ability to query "give me every active status" from the database itself.

// schema.prisma
model OrderStatus {
  id        String  @id @default(cuid())
  code      String  @unique
  label     String
  color     String?
  sortOrder Int     @default(0)
  isActive  Boolean @default(true)
  orders    Order[]
}

model Order {
  id        Int         @id @default(autoincrement())
  statusId  String
  status    OrderStatus @relation(fields: [statusId], references: [id])
  total     Decimal
  createdAt DateTime    @default(now())
}

A small NestJS service to load and validate against the current set of statuses, instead of hardcoding them in a DTO:

// order-status.service.ts
@Injectable()
export class OrderStatusService {
  constructor(private prisma: PrismaService) {}

  findActive() {
    return this.prisma.orderStatus.findMany({
      where: { isActive: true },
      orderBy: { sortOrder: 'asc' },
    });
  }

  async assertValid(code: string) {
    const status = await this.prisma.orderStatus.findUnique({ where: { code } });
    if (!status) throw new BadRequestException(`Unknown status: ${code}`);
    return status;
  }
}

Adding REFUNDED is now an INSERT, not a migration. You can seed the initial set with a Prisma seed script and let an admin panel manage it afterward.

Alternative 2: a plain string column with validation at the edges

For smaller projects, a full lookup table can be overkill. A middle ground is to keep the column as a String, define the allowed values once in TypeScript, and validate at the API boundary with class-validator:

// order-status.ts
export const ORDER_STATUSES = ['PENDING', 'PAID', 'SHIPPED', 'CANCELLED'] as const;
export type OrderStatus = (typeof ORDER_STATUSES)[number];

// create-order.dto.ts
export class CreateOrderDto {
  @IsIn(ORDER_STATUSES)
  status: OrderStatus;
}

Optionally back it with a database CHECK constraint for defense in depth, without locking the value set into the schema the way a native enum does. Changing the list is a one-line code change and a normal deploy, not a schema migration.

When a native enum is still the right call

None of this means enums are always wrong. For values that are genuinely part of your domain's core logic and change on a timescale of years, not weeks, think Role: ADMIN | USER, or a boolean-like flag, a native enum is simple, fast, and self-documenting. The rule of thumb: the more likely a value set is to gain new members, need metadata, or be edited by someone outside the engineering team, the further you should move away from a hardcoded enum.

Takeaway

Enums are not broken, they are just a poor fit for anything that changes. Before adding one to a Prisma schema, ask how often this list of values is likely to grow, whether each value will ever need more than a name, and who should be able to change it. The answer usually points to a lookup table, sometimes to a validated string, and only occasionally to a real enum.