Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 36 ยท Schema and Models

Enums, Native Types and Scalar Lists

Define Prisma enums for fixed sets of values, use them in queries and defaults, map their names, and learn when scalar lists and Json are better than a new table.

Many columns hold one value out of a small, fixed set: an order is PENDING, PAID or SHIPPED; a user is a USER or an ADMIN. A plain String column would accept typos like "shiped". A Prisma enum makes the set explicit, so the database, the generated client and your editor all agree on the allowed values.

Declaring an enum

PRISMA
enum Role {
  USER
  EDITOR
  ADMIN
}

enum OrderStatus {
  PENDING
  PAID
  SHIPPED
  CANCELLED
}

model Account {
  id     Int         @id @default(autoincrement())
  email  String      @unique
  role   Role        @default(USER)
  status OrderStatus @default(PENDING)
}

Enum values are written in SCREAMING_SNAKE_CASE by convention. The generated TypeScript gives you a union of string literals and a runtime object:

TypeScript
import { Role } from "../generated/prisma/client";

await prisma.account.create({
  data: { email: "[email protected]", role: Role.EDITOR },
});

await prisma.account.findMany({ where: { role: { in: [Role.EDITOR, Role.ADMIN] } } });

Writing role: "BOSS" is now a compile error, and so is a switch over Role that forgets a case, if you enable exhaustive checks.

How the database stores enums

  • PostgreSQL and MySQL have real enum types, and Prisma creates them (CREATE TYPE "Role" AS ENUM (...) on PostgreSQL).
  • SQLite and SQL Server have no enum type; support depends on your Prisma version and database, and older versions rejected enums on SQLite. In Prisma 6 and later, enums work on SQLite as text columns. Check your version's notes if you target both.

Adding a value to an enum later is a migration (an ALTER TYPE on PostgreSQL). Removing or renaming a value is harder, because existing rows may use it. Plan for that: prefer adding values, and rename with a careful migration or @map (below).

Mapping enum names

You can keep friendly names in code and different spellings in the database:

PRISMA
enum Role {
  USER   @map("user")
  ADMIN  @map("admin")

  @@map("account_role")
}

The client sees Role.USER, while the database stores 'user' in a type called account_role. Mapping is also how you adopt a legacy database whose values are lowercase.

A stand-in enum

This stand-in imitates what the generated enum gives you: a frozen object, a guard that rejects unknown values and an exhaustive switch.

Node.js Stand-in: enum values and guards

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Simulating an enum column in SQL

When a database has no enum type, a CHECK constraint gives the same protection. This runs on real SQLite and shows what "the database rejects a bad value" means.

Node.js A CHECK constraint as an enum

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Scalar lists

On PostgreSQL, CockroachDB and MongoDB you can store a list of scalars in one column:

PRISMA
model Recipe {
  id          Int      @id @default(autoincrement())
  title       String
  ingredients String[]
  ratings     Int[]
  tags        String[] @default([])
}
TypeScript
await prisma.recipe.create({
  data: { title: "Pancakes", ingredients: ["flour", "milk", "egg"], ratings: [5, 4] },
});

await prisma.recipe.findMany({ where: { ingredients: { has: "milk" } } });
await prisma.recipe.findMany({ where: { tags: { hasEvery: ["quick", "vegan"] } } });
await prisma.recipe.update({
  where: { id: 1 },
  data: { tags: { push: "breakfast" } },
});

The list filters are has, hasEvery, hasSome, isEmpty and equals; updates support set and push.

When should you use a list and when a separate model?

Use a scalar list when Use a related model when
items belong to one row only items are shared between rows (tags used by many posts)
you rarely query across rows you filter, count or join on the items
order matters and the list is small items need their own fields (a rating with an author)

On MySQL and SQLite there are no scalar lists. Use a Json field for a small array, or a related model for anything you query.

Unsupported and other native types

If your database has a type Prisma does not model (PostgreSQL tsvector, point, a custom domain), declare it as Unsupported:

PRISMA
model Place {
  id       Int                    @id @default(autoincrement())
  name     String
  location Unsupported("point")?
}

The column exists and migrations create it, but the client cannot read or write it. You use $queryRaw for those fields, and models with a required Unsupported field cannot be created through create at all. It is a pragmatic escape hatch, not a feature to build on.

Choosing between String, enum and a lookup table

  1. Enum: values are stable and few, and your code switches on them (roles, order status).
  2. Lookup table (a Country or Category model): values change often, are managed by admins or carry extra data (a label, a sort order).
  3. Plain String: free text.

If non-developers add the values, a table is the right choice, because adding an enum value needs a deploy and a migration.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main benefit of an enum over a String column for a status field?

    An enum limits the column to a fixed set, in the database and in the generated TypeScript types.
  2. 2. Which filter finds Recipe rows whose ingredients list contains milk?

    Scalar list fields use has, hasEvery, hasSome, isEmpty and equals.
  3. 3. Which databases support scalar lists such as String[] natively in Prisma?

    MySQL and SQLite have no array column type, so use Json or a related model there.
  4. 4. Admins add new product categories every week. What is the best model?

    Values that change without a deploy belong in a table, not in the schema's enum.

Summary

  • Enums restrict a field to a fixed set of values and appear as typed constants in the client.
  • Use @map and @@map to control the stored names; adding enum values is easy, removing them is not.
  • Scalar lists (String[]) exist on PostgreSQL, CockroachDB and MongoDB with has, hasEvery, push and friends.
  • Use a lookup table when values change often; use Unsupported only as an escape hatch.

Next: Identifiers, Unique Constraints, Indexes and Name Mapping