How to add a check constraint in Prisma

While recently reading this article by Craig Kerstiens on the Crunchy Data blog. I wanted to follow the advice and give check constrains a try in a situation I would normally use an Enum in Prisma.

If you're not familiar with check constraints let's quickly cover what they are.

What is a check constraint

Constraints allow developers to specify rules and restrictions for the values that can be inserted into a column, and can be easily modified or removed without having to change the underlying data structure.

Okay, so that is a nice definition pulled from the article I mentioned in the intro, but what can a check constraint actually do?

Check that a number is in specific range

CHECK (5 < visitor_limit < 10)

Guarantee one date column comes before another date column

CHECK (start_date < end_time)

Lock an array length to be no longer than X

CHECK (cardinality(words) > 0)

Alternative to Enum

CHECK (role IN ('admin', 'member', 'guest'))

How to implement check constraints in Prisma

When I jumped into adding a check constraint into Prisma the first thing I noticed is that there is no native way in Prisma to set a check constraint using an attribute (ex: @unique).

So, we need to dive into our migration layer and create our check manually. Thankfully that is really easy is supported by Prisma using prisma migrate .

Step One: Create new migration file

The first thing we want to do is generate a blank migration file without pushing any changes to our database. That is done using the --create-only flag.

npx prisma migrate dev -n add_role_constraint --create-only

Step Two: Add constraint

Now we are going to add our constraint. I'm adding this as a constraint on the role column instead of using an enum. I'm not 100% if these are the only roles that I will use in my application and want to leave it open to change.

I am going to share the whole SQL statement and then we can break it down.

ALTER TABLE "account_user" ADD CONSTRAINT "account_user_role" CHECK ("role" IN ('admin', 'member', 'guest'))

• ALTER TABLE "account_user" : This tells our database that the following statement is going to be making a change to the account_user table

• ADD CONSTRAINT "account_user_role": Here we are naming the constraint so that later on we can query, update, or delete this constraint by name.

• CHECK ("role" IN ('admin', 'member', 'guest')): Finally this is our actual check. Checks use the CHECK keyword followed by a set of parens that holds a Boolean expression. In this case we are using the IN operator to validate that the role column value is one of the strings in the group of strings on the right side.

Step Three: Deploy migration to your database

The final step once you have added the constraint into the migration file is to actually deploy it to your database.

npx prisma migrate deploy

If you give this a shot let me know over at Twitter @wking__