API with NestJS #111. Constraints with PostgreSQL and Prisma

NestJS SQL

This entry is part 111 of 168 in the API with NestJS

One of the most important aspects of working with a database is ensuring the stored information is correct. One of the fundamental ways of doing that is by using the correct data types for the columns in our tables. Thanks to that, we can make sure that a particular column holds only numbers, for example. In this article, we learn to use constraints to have even more control over our data and reject it when it does not match our guidelines. Doing that on the database level can ensure the integrity of the data to a greater extent than doing that through our TypeScript code.

Primary key

A primary key is a unique identifier for rows in the table. Therefore, all values in the column marked as a primary key need to be unique. Also, they can’t be null.

It is common to use the type when creating primary keys. Under the hood, PostgreSQL creates an column that auto increments every time we add a new row to the table.

Right now it is recommended to use identity columns instead of the serial type with PostgreSQL. If you want to know more, check out this article.
Unfortunately, Prisma does not support that out of the box yet.

To achieve the above with Prisma, we need to create an integer property. By marking it with , we make it a primary key. To ensure PostgreSQL generates the value for the id automatically, we need to use .

postSchema.prisma

Using multiple columns

While we usually use a single column as the primary key, it can consist of multiple columns.

In the above code, we create a primary key that consists of both the and . Since PostgreSQL enforces primary keys to be unique, we can’t have two users sharing the same combination of first and last names. Therefore, it would be better to use a separate id column.

Primary keys consisting of multiple columns are popular when working with many-to-many relationships. If you want to know more, check out API with NestJS #75. Many-to-many relationships using raw SQL queries

To create a composite primary key with Prisma, we need the attribute.

Unique

By using the unique constraint, we can force the values in a particular column to be unique across all of the rows in a specific table.

Thanks to the above, we require each user to have a unique email. To do that with Prisma, we need the keyword.

userSchema.prisma

We can also ensure that a group of columns have a unique value. To do that with SQL, we need a slightly different syntax.

Through the above code, we expect the users to have a unique combination of the first and last names. To achieve that with Prisma, we need the keyword.

Unique constraint violation

An essential part of defining constraints is handling the case when they are not complied with. Prisma has a set of codes that describe the error that occurred. Let’s start creating an enum that holds them.

prismaError.ts

We now need to use the statement whenever we perform an operation that might fail. We also should check if the error that occurred is an instance of the class.

authentication.service.ts

Not null

With the not-null constraint, we enforce a column to have a value other than null.

The official PostgreSQL documentation states that in most database designs, most columns should be marked as non-nullable. Prisma embraces that approach by making all columns non-nullable by default.

If we want to make a particular column nullable with Prisma, we need to add the question mark.

postSchema.prisma.ts

If you want to know how to handle dates with Prisma, check out API with NestJS #108. Date and time with Prisma and PostgreSQL

A reliable approach to avoiding the null constraint violation is validating the data provided by the users through our API. A common way of doing that with NestJS is using the library.

createPost.dto.ts

When using the above validation, it is also important to use the provided by NestJS.

main.ts

Foreign key

By using the foreign key constraint, we ensure that the values from one column match values from another table. We use this when defining relationships.

By using the keyword, we define a foreign key. In the above example, each post needs to refer to an existing user.

Creating a relationship such as the one above using Prisma is straightforward.

userSchema.prisma

postSchema.prisma

However, relationships are a broad topic and deserve a separate article. To know more, check out API with NestJS #33. Managing PostgreSQL relationships with Prisma.

Check

The check constraint is the most constraint available in PostgreSQL. Using it, we can define the requirements for the value in a particular column.

The check constraint can also use multiple columns.

Unfortunately, Prisma does not support check constraints currently. To deal with this problem, we can create a custom migration with raw SQL.

First, let’s add the property to our schema.

product.prisma

Now, let’s generate a migration.

Doing the above generated a new migration.

20230604193023_product_price/migration.sql

Let’s modify the generated file to include the check constraint.

20230604193023_product_price/migration.sql

By doing the above, we add the check constraint even when Prisma does not support it yet.

Handling the check error violation

Since Prisma does not support the check constraint, it does not handle its violation very well, either. To catch this error, we need to use the class.

products.service.ts

However, in our particular case, validating the value through the library makes sense.

createProduct.dto.ts

Thanks to the above, trying to send a POST request would result in a “400 Bad Request” if the price is not a positive number.

Summary

In this article, we’ve gone through various constraints supported by PostgreSQL. We’ve learned how to use them both through raw SQL and Prisma. Learning SQL proves helpful, especially in the case of the check constraint that Prisma does not yet support. Fortunately, we managed to overcome this problem by writing a migration manually. Learning all of the above helped us have more control over the data saved in our database.

Series Navigation<< API with NestJS #110. Managing JSON data with PostgreSQL and PrismaAPI with NestJS #112. Serializing the response with Prisma >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments