API with NestJS #124. Handling SQL constraints with Kysely

NestJS SQL

This entry is part 124 of 158 in the API with NestJS

When using SQL databases, we can set constraints to ensure our data remains accurate and reliable during insertions, updates, or deletions. In this article, we’ll explore different SQL constraints and demonstrate how to apply them using Kysely and NestJS to maintain data integrity.

Check out this repository for the full code from this article.

Not-null constraint

In SQL databases, null represents the absence of value. Using the not-null constraint, we can ensure the column does not accept the null value. Let’s take a look at a migration we created with Kysely in one of the previous articles:

20230806213313_add_articles_table.ts

When we call the method, we define a new column by providing its name and type. We also can supply a third argument that allows us to describe the column further. By calling the function, we add the not-null constraint to our column.

Not-null constraint violation error handling

Thanks to the not-null constraint, the database throws an error when trying to save a null value. To indicate that the not-null constraint violation causes the error, PostgreSQL uses the code. To avoid having to remember it later, let’s create an enum to store error codes.

postgresErrorCode.enum.ts

In TypeScript, all caught errors have the type by default. Let’s create an interface dedicated to database errors.

databaseError.ts

The is a function that we wrote earlier. It checks if a particular value is of the type.

Thanks to the type guard, we can check if a particular value matches the interface. Let’s use it in our repository.

articles.repository.ts

If the caught error matches the interface and contains the expected code, we throw the to inform NestJS that we want to respond with a 400 Bad Request status code.

If the error is not something we recognize, we want to rethrow it. Thanks to that, we are not hiding or suppressing errors. Instead, we move them up to the higher-level error handlers.

A lot of not-null constraint violations can be avoided by validating the data sent by the users of our API. If you want to know more, check out API with NestJS #4. Error handling and data validation

Unique constraint

With the unique constraint, we can ensure that all values in a particular column are unique across the entire table. A good example is the column we added in one of the recent articles.

20230813165809_add_users_table.ts

By calling the function, we add the unique constraint to the column. PostgreSQL will throw an error if we try to create two users with the same email.

Unique constraint violation error handling

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

postgresErrorCode.enum.ts

We should now check for the above code in our repository.

users.repository.ts

Primary key constraint

When adding the primary key constraint, we indicate that a particular column serves as a unique identifier for the rows in the table.

20230827204025_add_categories_table.ts

When we call the function, we indicate that all values in the column should not equal null and be unique.

Primary key constraint violation error handling

There isn’t a high chance of violating the primary key constraint because we usually let PostgreSQL generate the key for us. However, we could check for the unique violation and the not-null violation.

categories.repository.ts

Using multiple columns as a primary key

We can’t create a table that has more than one primary key. However, we can define a primary key that consists of multiple columns. This is very common when designing many-to-many relationships.

If you want to know more about many-to-many relationships with Kysely, check out API with NestJS #122. Many-to-many relationships with Kysely and PostgreSQL

20230827204025_add_categories_table.ts

Foreign key constraint

To define relationships, we need the foreign key constraint. It requires a value in one table’s column to match a value in another table’s column.

20230827204025_add_categories_table.ts

When calling the function above, we ensure that the column matches the id from the table.

The ensures that the contains the matching id from the table.

Foreign key constraint violation error handling

A very good example of violating the foreign key constraint is trying to refer to a record that does not exist. To handle it, let’s add the necessary code to our enum.

postgresErrorCode.enum.ts

We can now handle the violation appropriately in our repository.

categories.repository.ts

Check constraint

With the check constraint, we can specify the requirements for a value in a particular column in a more generic way. Let’s add a constraint that ensures the article does not contain empty content.

20230909022554_article_title_length_constraint.ts

Thanks to the above, the column can’t hold empty strings.

Check constraint violation error handling

To handle the check constraint violation, we need to start by adding the error code to our enum.

postgresErrorCode.enum.ts

Let’s check if the constraint is violated in the existing block in the repository.

articles.repository.ts

Summary

In this article, we’ve gone through constraints in PostgreSQL and implemented them through Kysely. When doing that, we also added error handling to handle any of our constraints being violated. Thanks to doing that, we ensured the integrity of our data on the database level.

Series Navigation<< API with NestJS #123. SQL transactions with KyselyAPI with NestJS #125. Offset and keyset pagination with Kysely >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments