API with NestJS #152. SQL constraints with the Drizzle ORM

NestJS SQL

This entry is part 152 of 172 in the API with NestJS

When working with SQL databases, we can configure constraints to ensure our data does not get corrupted. In this article, we explore and implement different SQL constraints using PostgreSQL, NestJS, and the Drizzle ORM.

Not-null constraint

By default, SQL columns can hold nulls, representing an absence of value. We need the not-null constraint using the function to prevent that.

database-schema.ts

Not-null error handling

Thanks to adding the not-null constraint, the database now throws an error when we try to save a null value into the database. PostgreSQL uses the error code. To avoid having to remember it, let’s create an enum to store error codes.

postgres-error-code.enum.ts

In TypeScript, all caught errors are initially of the type. Let’s create an interface and a type guard to deal with that.

database-error.ts

If you want to know more about type guards, check out Structural type system and polymorphism in TypeScript. Type guards with predicates

The function checks if a particular value is of the type.

Using the function, we can check if a particular error matches the interface.

articles.service.ts

If we don’t recognize the error, we want to re-throw it to make sure the exception is properly propagated and not ignored.

Unique constraint

Using the unique constraint, we can ensure that all values in a certain column are unique across the entire table. A great example is the column in the table. Thanks to the unique constraint, we ensure that no users share the same email.

database-schema.ts

Using the function, we tell Drizzle ORM to add the unique constraint. Because of that, PostgreSQL will throw an error when we try to create two users with the same email.

Unique constraint error handling

To handle the violation of the unique constraint, we should start by adding the error code to our enum.

postgres-error-code.enum.ts

We can now use it in our service to respond with a custom exception if someone tries to use an occupied email address.

users.service.ts

Primary key constraint

When we add a primary key constraint, we choose a particular column to be a unique identifier for the rows in the table.

database-schema.ts

Primary key constraint error handling

In most cases, we won’t have a chance to violate the primary key constraint because we usually let PostgreSQL generate the key for us. An example where that might not be the case is when dealing with many-to-many relationships.

Under the hood, the primary key constraint consists of the not-null and unique constraints. To implement error handling for the primary key constraint, you should look for the not-null and unique constraint violations.

Foreign key constraint

We use the foreign key constraint to define relationships. It ensures that a value in one table’s column matches a value in another table’s column.

database-schema.ts

Foreign constraint error handling

A good example of when the above constraint might cause an error is when we try to delete a user who is the author of at least one article. To handle that, we first need to adjust our enum.

postgres-error-code.enum.ts

We can now use it to handle the foreign constraint violation.

users.service.ts

Check constraint

We can use the check constraint to specify a particular column’s requirements more generically. Unfortunately, Drizzle Kit does not support it out of the box yet. To add it, we need to create a custom migration.

Now, we need to write a SQL migration that adds the constraint manually. Let’s ensure that the title of each article is not an empty string.

0003_add-articles-title-check.sql

We can now apply it using the command.

Check constraint error handling

Let’s start by adding the check constraint violation to our enum.

postgres-error-code.enum.ts

We can now use the enum to handle the check constraint violation.

articles.service.ts

Summary

In this article, we’ve gone through various constraints PostgreSQL offers and implemented them using the Drizzle ORM. We also learned how to implement constraint violation handling in a NestJS application that uses Drizzle. Thanks to that, we can now better care for the integrity of the data in our database.

Series Navigation<< API with NestJS #151. Implementing many-to-one relationships with Drizzle ORMAPI with NestJS #153. SQL transactions with the Drizzle ORM >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments