API with NestJS #85. Defining constraints with raw SQL

JavaScript NestJS SQL

This entry is part 85 of 180 in the API with NestJS

Having significant control over the data we store in our database is crucial. One of the ways to do that is to choose suitable column types. We can also use constraints to go further and reject the data that does not match our guidelines. By doing that, we can have an additional layer of security that ensures the integrity of our data.

Not null constraint

With the not-null constraint, we can enforce a column to have a value other than null. For example, let’s look at the table we’ve implemented in one of the previous parts of this series.

Above, we’re using an identity column. If you want to know more, check out Serial type versus identity columns in PostgreSQL and TypeORM

Error handling for non-null constraints

When using the constraint, PostgreSQL throws an error when trying to save a null value for the constrained column. When using with TypeScript, the type of the error is unknown.

If you want to know more about the type, check out Understanding any and unknown in TypeScript. Difference between never and void

posts.repository.ts

Because of the above, we need a way to narrow the type down. The best way to do that is to implement a type guard.

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

databaseError.ts

Above we also use the type guard function that helps us determine if the value is a valid object and not an array. If you want to check it out, see the file in the repository.

PostgreSQL uses a set of error codes to let us know what constraint was violated. Let’s put the not-null violation error code in an enum.

postgresErrorCode.enum.ts

Thanks to all of the above, we can use the function to determine if a particular value matches the database.

posts.repository.ts

Unique constraint

The unique constraint ensures that all values in a particular column are unique across the table. A good example is the table we created in one of the previous parts of this series.

Thanks to using the constraint above, PostgreSQL throws an error if we try to create two users with the same email.

Error handling for unique constraints

To handle the unique constraint, we should add it to our enum.

postgresErrorCode.enum.ts

We can now use it in our repository.

users.repository.ts

Above, we throw a custom error using the extending the .

userAlreadyExists.exception.ts

Using the unique constraint with a group of columns

Using different syntax allows us to expect a group of columns to have a unique value.

Above, we expect users to have a unique combination of their first and last names. However, they can still share the same first name if their last name differs.

Primary key constraint

The primary key is a very common constraint. When using it, we indicate that a particular column serves as a unique identifier for the rows in the table.

When we use the constraint, we indicate that all values in a particular column should be unique and not equal to null. To ensure that, PostgreSQL creates a unique index to keep track of all of the values.

If you want to know more about indexes, read API with NestJS #82. Introduction to indexes with raw SQL queries

Error handling for primary keys

In the case of the primary keys, we need to worry about the unique violation and not-null violation. Fortunately, we already have them as part of our enum.

categories.repository.ts

In a lot of the cases there isn’t a high chance of violating the primary key constraint because we usually let PostgreSQL to generate it for us.

Using a group of columns as a primary key

We can’t have a table that has more than one primary key. However, we can have a primary key that consists of multiple columns.

Due to how the above constraint is designed, we can’t have two users with the same combination of first and last names.

Foreign key constraint

We use the foreign key constraint when defining relations. To do that, we need to use the keyword.

Above, we define a many-to-many relation. If you want to know more, read API with NestJS #75. Many-to-many relationships using raw SQL queries

Because of the foreign keys in the table, each post needs to refer to a valid category.

Error handling for foreign key constraints

A good example of a foreign key constraint violation is referring to an entity that does not exist. To be able to handle it, let’s add the appropriate code to our enum.

postgresErrorCode.enum.ts

Thanks to the above, we can now handle the constraint violation appropriately.

posts.repository.ts

Check constraint

The check constraint is more generic than the previous examples. We can use it to specify the requirements for a value in a particular column. Let’s inspect the migration we wrote in one of the previous parts of this series.

We can modify the above table and add a constraint to an existing column by creating a new migration.

20221201022319_add_comment_length_constraint.ts

With the above constraint, we specify that the column can’t hold an empty string.

Handling the check constraint violation

To handle the violation of the above constraint, we need to add the appropriate code to our enum.

postgresErrorCode.enum.ts

We can now use the code to check if the constraint was violated.

comments.repository.ts

Summary

In this article, we’ve gone through constraints in PostgreSQL. We’ve learned how to apply them to our tables and how they can help manage our database. When doing that, we’ve also implemented error handling so that we can react accordingly when a particular constraint is violated.

Series Navigation<< API with NestJS #84. Implementing filtering using subqueries with raw SQLAPI with NestJS #86. Logging with the built-in logger when using raw SQL >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments