Wednesday, September 28, 2022
HomeJavaScriptAPI with NestJS #74. Designing many-to-one relationships utilizing uncooked SQL queries

API with NestJS #74. Designing many-to-one relationships utilizing uncooked SQL queries


Studying tips on how to design and implement relationships between tables is a vital ability for a backend developer. On this article, we proceed working with uncooked SQL queries and find out about many-to-one relationships.

Yow will discover the code from this text in this repository.

Understanding the many-to-one relationship

When making a many-to-one relationship, a row from the primary desk is linked to a number of rows within the second desk. Importantly, the rows from the second desk can hook up with only one row from the primary desk. An easy instance is a submit that may have a single creator, whereas the consumer might be an creator of many posts.

A strategy to implement the above relationship is to retailer the creator’s id within the desk as a overseas key. A overseas secret is a column that matches a column from a special desk.

After we create a overseas key, PostgreSQL defines a overseas key constraint. It ensures the consistency of our knowledge.

PostgreSQL will stop you from having an that refers to a nonexistent consumer. For instance, we will’t:

  • create a submit and supply that doesn’t match a report from the desk,
  • modify an present submit and supply that doesn’t match a consumer,
  • delete a consumer that the column refers to,
    • we must both take away the submit first or change its creator,
    • we might additionally use the key phrase,
      • it might power PostgreSQL to delete all posts the consumer is an creator of when deleting the consumer.

Making a many-to-one relationship

We wish each entity within the desk to have an creator. Subsequently, we should always put a NON NULL constraint on the column.

Sadly, we have already got a number of posts in our database, and including a brand new non-nullable column with no default worth would trigger an error.

ERROR: column “author_id” of relation “posts” accommodates null values

As a substitute, we have to present some preliminary worth for the column. To do this, we have to outline a default consumer. A superb resolution for that’s to create a seed file. With seeds, we will populate our database with preliminary knowledge.

Working the above command creates the file that we will now use to outline a script that creates our consumer.

01_create_admin.ts

When utilizing we will use the signal to use parameters handed to the question.

After creating the above seed file, we will run to execute it.

Making a migration

When making a migration file for the column, we will use the next method:

  1. verify the id of the default consumer,
  2. add the column as nullable,
  3. set the worth for present posts,
  4. add the constraint for the column.
20220908005809_add_author_column.ts

It’s essential to acknowledge that with Knex, every migration runs inside a transaction by default. This implies our migration both succeeds totally or makes no modifications to the database.

Transactions in SQL are an awesome matter for a separate article.

Many-to-one vs. one-to-one

Within the earlier article, we’ve coated working with one-to-one relationships. When doing so, we ran the next question:

By including the distinctive constraint, we be certain that no two customers have the identical tackle.

In distinction, when including the column, we ran a question with out the distinctive constraint:

Because of the above, many posts can share the identical creator.

Creating posts with authors

To date, we’ve relied on the consumer to supply the entire knowledge of a submit when creating it. Quite the opposite, when determining the submit’s creator, we don’t anticipate the consumer to supply the id explicitly. As a substitute, we get that info from the JWT token.

If you wish to know extra about authentication and JWT tokens, take a look at API with NestJS #3. Authenticating customers with bcrypt, Passport, JWT, and cookies

posts.controller.ts

The following step is to deal with the property in our INSERT question.

posts.repository.ts

Because of the above, we insert the into the database and might use it in our mannequin.

submit.mannequin.ts

Getting the posts of a specific consumer

To get the posts of a consumer with a specific id, we will use a question parameter.

posts.controller.ts

Because of utilizing the class, we will validate and rework the question parameter offered by the consumer.

getPostsByAuthorQuery.ts

Then, if the consumer calls the API with the , for instance, we use a special technique from our repository.

posts.service.ts

Creating a question that will get the posts written by a specific creator is a matter of a easy clause.

posts.repository.ts

Querying a number of tables

There is perhaps a case the place we wish to fetch rows from each the and desk and match them. To do this, we want a question.

By utilizing the key phrase, we carry out the inside be a part of. It returns information with matching values in each tables. Since there aren’t any posts that don’t have the creator, it’s acceptable on this case.

Within the earlier article, we used an outer be a part of when fetching the consumer with the tackle as a result of the tackle is non-compulsory. Outer joins protect the rows that don’t have matching values.

Since we wish to fetch the submit, creator, and doable tackle, we have to use two statements.

posts.repository.ts

Let’s additionally create the class that extends .

postWithAuthor.mannequin.ts

Abstract

On this article, we’ve carried out an instance of a one-to-many relationship utilizing uncooked SQL queries. When doing that, we additionally wrote an SQL question that makes use of multiple assertion. We’ve additionally discovered tips on how to write a migration that provides a brand new column with a constraint. There’s nonetheless extra to cowl in the case of implementing relationships in PostgreSQL, so keep tuned!

Collection Navigation

<< API with NestJS #73. One-to-one relationships with uncooked SQL queries

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments