Working with Arrays of Enums in Prisma

When building applications using Prisma, one of the challenges you may face is working with arrays of enums. In this article, we’ll explore how to use the where clause in Prisma’s SQL queries to filter data based on an array of enums.

Understanding PRISMA and its Query Language

Before diving into the specifics of using arrays of enums in Prisma, it’s essential to understand the basics of PRISMA and its query language. PRISMA is a powerful ORM (Object-Relational Mapping) tool that allows you to interact with databases using a high-level, SQL-like syntax.

In PRISMA, the where clause is used to filter data based on conditions. However, when working with arrays of enums, the available options are limited. In this case, we’re stuck with only one option: equals.

The Problem with Using equals

The equals operator in Prisma’s query language is useful for filtering data where a single value matches a specific condition. For example:

model User {
  id       String    @id @default(uuid())
  name     String?
  email    String    @unique
}

const users = await prisma.user.findMany({
  where: {
    email: 'example@example.com',
  },
})

In this example, the findMany method filters all users with an email address of example@example.com.

However, when working with arrays of enums, the equals operator is not suitable. This is because the equals operator only checks for exact matches between two values. When working with arrays, we need to check if any element in the array matches a specific condition.

The Solution: Using Raw Queries

One workaround to work around this limitation is to use raw queries via Prisma’s $queryRaw field. Here’s an example:

model User {
  id       String    @id @default(uuid())
  roles    Role[]
}

enum Role {
  SUPER_ADMIN
  ADMIN
  USER
  EMAIL_VERIFIED
  UNVERIFIED
}

const users = await prisma.user.findMany({
  where: {
    roles: {
      notIn: ['SUPER_ADMIN'],
    },
  },
})

In this example, we’re using the notIn operator to exclude users with a role of SUPER_ADMIN. However, we need to be careful when working with raw queries. We must ensure that our query is safe and doesn’t introduce any security vulnerabilities.

Using in Operator

Another approach to work around this limitation is to use the in operator in combination with an array of values. Here’s an example:

model User {
  id       String    @id @default(uuid())
  roles    Role[]
}

enum Role {
  SUPER_ADMIN
  ADMIN
  USER
  EMAIL_VERIFIED
  UNVERIFIED
}

const users = await prisma.user.findMany({
  where: {
    roles: {
      in: ['ADMIN', 'USER'],
    },
  },
})

In this example, we’re using the in operator to filter users with a role of either ADMIN or USER.

Best Practices for Working with Arrays of Enums

When working with arrays of enums in Prisma, here are some best practices to keep in mind:

• Always use raw queries via $queryRaw when working with complex filtering logic.
• Be cautious when using the notIn operator to avoid security vulnerabilities.
• Use the in operator to filter data based on an array of values.

Conclusion

Working with arrays of enums in Prisma can be challenging, but there are ways to overcome these limitations. By understanding the available options and best practices for working with raw queries and operators like notIn and in, you can build more powerful and efficient applications using Prisma.

Example Use Cases

Here are some example use cases that demonstrate how to work with arrays of enums in Prisma:

// Example 1: Using `$queryRaw`
model User {
  id       String    @id @default(uuid())
  roles    Role[]
}

enum Role {
  SUPER_ADMIN
  ADMIN
  USER
  EMAIL_VERIFIED
  UNVERIFIED
}

const users = await prisma.user.findMany({
  where: {
    $queryRaw: 'SELECT * FROM "User" WHERE "roles" @> ARRAY[\'ADMIN\']::"Role[]"',
  },
})

// Example 2: Using `in` operator
model User {
  id       String    @id @default(uuid())
  roles    Role[]
}

enum Role {
  SUPER_ADMIN
  ADMIN
  USER
  EMAIL_VERIFIED
  UNVERIFIED
}

const users = await prisma.user.findMany({
  where: {
    roles: {
      in: ['ADMIN', 'USER'],
    },
  },
})

Raw Query Example

Here’s an example raw query that demonstrates how to use the @> operator to filter data based on an array of values:

model User {
  id       String    @id @default(uuid())
  roles    Role[]
}

enum Role {
  SUPER_ADMIN
  ADMIN
  USER
  EMAIL_VERIFIED
  UNVERIFIED
}

const users = await prisma.user.findMany({
  where: {
    $queryRaw: `
      SELECT "id", "mailing_address", "roles" FROM "User"
      WHERE "roles" @> ARRAY[\'SUPER_ADMIN\']::"Role[]" OR "roles" @> ARRAY[\'ADMIN\']::"Role[]";
    `,
  },
})

By following these best practices and examples, you can build more powerful and efficient applications using Prisma.

Last modified on 2024-05-11