Help

4.6.8. 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
import { model } from "@medusajs/framework/utils"

const CustomProduct = model.define("custom_product", {
  // ...
  price: model.bigNumber(),
})
.checks([
  (columns) => `${columns.price} >= 0`,
])

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
import { model } from "@medusajs/framework/utils"

const CustomProduct = model.define("custom_product", {
  // ...
  price: model.bigNumber(),
})
.checks([
  {
    name: "custom_product_price_check",
    expression: (columns) => `${columns.price} >= 0`,
  },
])

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:

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
const User = model.define("user", {
  username: model.text(),
  password: model.text(),
})
.checks([
  {
    name: "password_length_check", 
    expression: (columns) => `LENGTH(${columns.password}) >= 8`,
  },
])

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
const Customer = model.define("customer", {
  email: model.text(),
})
.checks([
  {
    name: "email_format_check",
    expression: (columns) => `${columns.email} LIKE '%@%'`,
  },
])

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
const Event = model.define("event", {
  start_date: model.dateTime(),
  end_date: model.dateTime(),
})
.checks([
  {
    name: "date_order_check",
    expression: (columns) => `${columns.end_date} >= ${columns.start_date}`,
  },
])

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

Was this chapter helpful?