3.5.7. Add Data Model Check Constraints

In this chapter, you'll learn how to add check constraints to your data model.

What is a Check Constraint?#

A check constraint is a condition that must be satisfied by records inserted into a database table, otherwise an error is thrown.

For example, if you have a data model with a price property, you want to only allow positive number values. So, you add a check constraint that fails when inserting a record with a negative price value.

How to Set a Check Constraint?#

To set check constraints on a data model, use the checks method. This method accepts an array of check constraints to apply on the data model.

For example, to set a check constraint on a price property that ensures its value can only be a positive number:

Code
1import { model } from "@medusajs/framework/utils"2
3const CustomProduct = model.define("custom_product", {4  // ...5  price: model.bigNumber(),6})7.checks([8  (columns) => `${columns.price} >= 0`,9])

The item passed in the array parameter of checks can be a callback function that accepts as a parameter an object whose keys are the names of the properties in the data model schema, and values the respective column name in the database.

The function returns a string indicating the SQL check constraint expression. In the expression, use the columns parameter to access a property's column name.

You can also pass an object to the checks method:

Code
1import { model } from "@medusajs/framework/utils"2
3const CustomProduct = model.define("custom_product", {4  // ...5  price: model.bigNumber(),6})7.checks([8  {9    name: "custom_product_price_check",10    expression: (columns) => `${columns.price} >= 0`,11  },12])

The object accepts the following properties:

• name: The check constraint's name.
• expression: A function similar to the one that can be passed to the array. It accepts an object of columns and returns an SQL check constraint expression.

Apply in Migrations#

After adding the check constraint, make sure to generate and run migrations if you already have the table in the database. Otherwise, the check constraint won't be reflected.

To generate a migration for the data model's module then reflect it on the database, run the following command:

Terminal
❯npx medusa db:generate custom_module❯npx medusa db:migrate

The first command generates the migration under the migrations directory of your module's directory, and the second reflects it on the database.

Examples#

This section covers common use cases where check constraints are particularly useful.

1. Enforce Text Length#

Ensure that text properties meet minimum or maximum length requirements:

Code
1const User = model.define("user", {2  username: model.text(),3  password: model.text(),4})5.checks([6  {7    name: "password_length_check", 8    expression: (columns) => `LENGTH(${columns.password}) >= 8`,9  },10])

In the above example, the check constraint fails if the password property is less than 8 characters long.

2. Validate Email Format#

Ensure email addresses contain the @ symbol:

Code
1const Customer = model.define("customer", {2  email: model.text(),3})4.checks([5  {6    name: "email_format_check",7    expression: (columns) => `${columns.email} LIKE '%@%'`,8  },9])

In the above example, the check constraint fails if the email property does not contain the @ symbol.

3. Enforce Date Ranges#

Ensure dates fall within valid ranges:

Code
1const Event = model.define("event", {2  start_date: model.dateTime(),3  end_date: model.dateTime(),4})5.checks([6  {7    name: "date_order_check",8    expression: (columns) => `${columns.end_date} >= ${columns.start_date}`,9  },10])

In the above example, the check constraint fails if the end_date is earlier than the start_date.