The Pitfalls of Custom Enum Ordering in Prisma Compared to TypeORM & MikroORM

#prisma #typescript #database #backend

"Who use Prisma in production?" The statement is true in my daily work. During my work on a project with Prisma as ORM, whenever I worked on a feature, I usually get lots of open issues which blocks my development and getting things done.

I came up with this article based on my curiosity on how other ORM can handle the mapping of fields and custom enum ordering equally well. We’ll walk through the steps, highlight the issue, and compare Prisma with its counterparts.

Step-by-Step Guide

1. Understanding the requirement

1.1 Custom ordering requirement

Modern ORMs offer a convenient way to sort by specific fields or enum indices. This is especially handy when you have an enum that represents a strict order (like priority levels: LOW, MEDIUM, HIGH).

Why this matters: If you want items always ordered by HIGH first, then MEDIUM, then LOW, you might rely on the enum’s internal indexing or a custom field to achieve consistent sorting.

1.2 Mapped fields requirement

Prisma maps fields in your schema (e.g., someField) to columns in your database (e.g., some_field). Under most circumstances, Prisma automatically translates the naming between your application layer (camelCase) and your database layer (snake_case).

Why this matters: SQL is case-insensitive, and many databases automatically convert unquoted identifiers to lowercase when creating tables. This means that any capitalization used in camelCase is lost, obscuring the intended separation between words. Using snake_case ensures that word boundaries are clearly maintained, making your database schema more portable and consistent across different SQL databases.

2. The Drawback: Using Raw SQL for Custom Enum Order

When you need to order by an enum’s indexed order that isn’t directly supported by Prisma’s standard query capabilities, you might resort to a custom raw SQL query:

const query = Prisma.$queryRaw`
    SELECT * 
    FROM tasks
    ORDER BY 
      CASE 
        WHEN severity = HIGH THEN 1
        WHEN severity = NORMAL THEN 2
        WHEN severity = LOW THEN 3
        ELSE 999999
      END,
      created_at DESC
  `;

The issue: The result returned from prisma.$queryRaw will not automatically map column names from snake_case to camelCase. This means you end up with fields like my_enum_column in your JavaScript objects instead of the expected myEnumColumn.

3. Why This Becomes a Problem

When your application logic expects a property named myEnumColumn (camelCase), but the database returns my_enum_column (snake_case), you have to manually handle that conversion. This extra step can introduce errors or force you to create unnecessary helper functions to remap fields.

Inconsistent Code: You’ll have to remember which parts of your code deal with raw SQL (snake_case) versus Prisma’s default query system (camelCase).
Extra Boilerplate: You might add transformations to your service layer just to rename fields properly.

4. Alternatives in TypeORM or MikroORM

Other ORMs such as TypeORM or MikroORM often provide decorators or built-in mechanisms to handle custom ordering directly in their query builder or entity configuration.

TypeORM:

createQueryBuilder(Task, "task")
  .orderBy(`
    CASE 
      WHEN severity = "HIGH" THEN 1
      WHEN severity = "MEDIUM" THEN 2
      WHEN severity = "LOW" THEN 3
    END
  `, 'ASC')
  .getMany();

Even if it involves writing some SQL logic, TypeORM’s query builder often keeps the field mapping in sync with your entity definitions.

MikroORM:

const caseExpression = `
    CASE 
      WHEN severity = "HIGH" THEN 1
      WHEN severity = "MEDIUM" THEN 2
      WHEN severity = "LOW" THEN 3
    END
  `;
const allTasks = await em.find(Task, {}, { orderBy: { [raw(caseExpression)]: 'ASC' } });

MikroORM’s entity definitions and naming strategies often ensure consistent field naming without requiring raw SQL queries.

Why They Might Work Better: In these ORMs, you often define the enum fields and the ordering logic in your entity configuration (ex: Task.entity.ts). If you write a custom expression, you’re still able to leverage the ORM’s mapping layer, thus avoiding the mismatch of camelCase vs. snake_case.

5. My workarounds in Prisma

If you’re heavily invested in Prisma and still want to achieve custom enum ordering without dealing with mismatched field names:

Write a small utility function that converts snake_case keys to camelCase after you execute a raw SQL query, ensuring consistency.

type CamelCase<S extends string> =
  S extends `${infer P1}_${infer P2}${infer P3}`
    ? `${Lowercase<P1>}${Uppercase<P2>}${CamelCase<P3>}`
    : Lowercase<S>;

type CamelizeKeys<T> = {
  [K in keyof T as CamelCase<string & K>]: T[K];
};

export function camelize<T extends Record<string, any>>(
  obj: T,
): CamelizeKeys<T> {
  const result: any = {};

  for (const key in obj) {
    if (Object.prototype.hasOwnProperty.call(obj, key)) {
      const camelKey = key.replace(/_([a-z])/g, (_, letter) =>
        letter.toUpperCase(),
      );
      result[camelKey] = obj[key];
    }
  }

  return result;
}

Conclusion

Handling custom enum ordering is a common need in many applications. With TypeORM or MikroORM, you can often maintain a consistent field mapping while still using a flexible query builder for sorting. Prisma, however, requires a raw SQL approach for more complex enum sorting logic, leading to a mismatch between camelCase properties in your application and snake_case columns in your database.

I encourage you to try out these steps and see which approach works best in your application. Try to avoid using Prisma on production, you can consider 2 options above to help your application get things done.