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
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
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
CHECKkeyword followed by a set of parens that holds a Boolean expression. In this case we are using the
INoperator to validate that the
rolecolumn 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__