API with NestJS #74. Designing many-to-one relationships using raw SQL queries

JavaScript NestJS SQL

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

Learning how to design and implement relationships between tables is a crucial skill for a backend developer. In this article, we continue working with raw SQL queries and learn about many-to-one relationships.

You can find the code from this article in this repository.

Understanding the many-to-one relationship

When creating a many-to-one relationship, a row from the first table is linked to multiple rows in the second table. Importantly, the rows from the second table can connect to just one row from the first table. A straightforward example is a post that can have a single author, while the user can be an author of many posts.

A way to implement the above relationship is to store the author’s id in the table as a foreign key. A foreign key is a column that matches a column from a different table.

When we create a foreign key, PostgreSQL defines a foreign key constraint. It ensures the consistency of our data.

PostgreSQL will prevent you from having an that refers to a nonexistent user. For example, we can’t:

  • create a post and provide that does not match a record from the table,
  • modify an existing post and provide that does not match a user,
  • delete a user that the column refers to,
    • we would have to either remove the post first or change its author,
    • we could also use the keyword,
      • it would force PostgreSQL to delete all posts the user is an author of when deleting the user.

Creating a many-to-one relationship

We want every entity in the table to have an author. Therefore, we should put a NON NULL constraint on the column.

Unfortunately, we already have multiple posts in our database, and adding a new non-nullable column without a default value would cause an error.

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

Instead, we need to provide some initial value for the column. To do that, we need to define a default user. A good solution for that is to create a seed file. With seeds, we can populate our database with initial data.

Running the above command creates the file that we can now use to define a script that creates our user.

01_create_admin.ts

When using we can use the sign to use parameters passed to the query.

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

Creating a migration

When creating a migration file for the column, we can use the following approach:

  1. check the id of the default user,
  2. add the column as nullable,
  3. set the value for existing posts,
  4. add the constraint for the column.
20220908005809_add_author_column.ts

It is crucial to acknowledge that with Knex, each migration runs inside a transaction by default. This means our migration either succeeds fully or makes no changes to the database.

Transactions in SQL are a great topic for a separate article.

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

In the previous article, we’ve covered working with one-to-one relationships. When doing so, we ran the following query:

By adding the unique constraint, we ensure that no two users have the same address.

In contrast, when adding the column, we ran a query without the unique constraint:

Thanks to the above, many posts can share the same author.

Creating posts with authors

So far, we’ve relied on the user to provide the complete data of a post when creating it. On the contrary, when figuring out the post’s author, we don’t expect the user to provide the id explicitly. Instead, we get that information from the JWT token.

If you want to know more about authentication and JWT tokens, check out API with NestJS #3. Authenticating users with bcrypt, Passport, JWT, and cookies

posts.controller.ts

The next step is to handle the property in our INSERT query.

posts.repository.ts

Thanks to the above, we insert the into the database and can use it in our model.

post.model.ts

Getting the posts of a particular user

To get the posts of a user with a particular id, we can use a query parameter.

posts.controller.ts

Thanks to using the class, we can validate and transform the query parameter provided by the user.

getPostsByAuthorQuery.ts

Then, if the user calls the API with the , for example, we use a different method from our repository.

posts.service.ts

Creating a query that gets the posts written by a particular author is a matter of a simple clause.

posts.repository.ts

Querying multiple tables

There might be a case where we want to fetch rows from both the and table and match them. To do that, we need a query.

By using the keyword, we perform the inner join. It returns records with matching values in both tables. Since there are no posts that don’t have the author, it is acceptable in this case.

In the previous article, we used an outer join when fetching the user with the address because the address is optional. Outer joins preserve the rows that don’t have matching values.

Since we want to fetch the post, author, and possible address, we need to use two statements.

posts.repository.ts

Let’s also create the class that extends .

postWithAuthor.model.ts

Summary

In this article, we’ve implemented an example of a one-to-many relationship using raw SQL queries. When doing that, we also wrote an SQL query that uses more than one statement. We’ve also learned how to write a migration that adds a new column with a constraint. There is still more to cover when it comes to implementing relationships in PostgreSQL, so stay tuned!

Series Navigation<< API with NestJS #73. One-to-one relationships with raw SQL queriesAPI with NestJS #75. Many-to-many relationships using raw SQL queries >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments