API with NestJS #121. Many-to-one relationships with PostgreSQL and Kysely

SQL

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

Designing relationships is one of the crucial aspects of working with SQL databases. In this article, we continue using Kysely with NestJS and implement many-to-one relationships.

Check out this repository if you want to see the full code from this article.

Introducing the many-to-one relationship

When implementing the many-to-one relationship, a row from the first table is connected to multiple rows in the second table. What’s essential, a row from the second table can connect to just one row from the first table.

An example is an article with a single author, while the user can be an author of many articles. A way to implement it is to save the author’s id in the table as a foreign key. A foreign key is a value that matches a column from a different table.

Whenever we create a foreign key in our database, PostgreSQL defines the foreign key constraint to ensure the consistency of our data. Thanks to that, it prevents us from having an value that refers to a user that does not exist. We can’t:

  • create an article and provide the that points to a user that cannot be found in the table,
  • update an existing article and change the to match a user that does not exist,
  • delete a user with an id used in the column
    • we would have to delete the article first or change its author
    • alternatively, we could use the option to force PostgreSQL to delete all articles the deleted user is an author of

Defining the many-to-one relationship with Kysely

In one of the previous parts of this series, we learned how to write SQL migrations when using Kysely. This time, we want to add the column that is not nullable. Unfortunately, we might already have some articles in our database, and adding a new non-nullable column without a default value would cause an error.

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

To solve the above problem, we can provide a default value for the column. To do that, we need to have a default user. Let’s add a seed file to our directory. Creating seed files is a way to populate our database with initial data.

Adding the seed file

First, let’s add the email and password of the admin to the environment variables.

.env

Now we can add the seed file to our migrations.

20230817223154_insert_admin.ts

Creating the migration

When writing the migration file that adds the column, we can implement the following approach:

  1. get the id of the admin,
  2. add the column as nullable,
  3. set the value in the column for articles that don’t have it,
  4. make the column non-nullable.
20230817230950_add_author_id.ts

Many-to-one vs one-to-one

In the previous part of this series, we’ve covered the one-to-one relationship. When writing the migration, we’ve run the following query:

Adding the unique constraint ensures that a particular address belongs to only one user.

On the contrary, when adding the column, we ran the query without adding the unique constraint:

Thanks to the above approach, multiple articles can share the same author.

Creating an article with the author

The next thing we need to do is to modify the TypeScript definition of our table.

articlesTable.ts

Let’s also add the author’s id to the article’s model.

articles.model.ts

We must also handle the column when inserting the article into our database.

articles.service.ts

When figuring out who is the author of the new article, we don’t expect the information to be provided directly through the body of the POST request. Instead, we get this information by decoding the JWT token.

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

articles.controller.ts

Fetching articles of a particular user

We can query the articles written by a particular author using the function.

articles.repository.ts

Let’s use a different method from our repository based on whether the author’s id is provided.

articles.service.ts

A good way to use the above feature through our REST API is with a query parameter. Let’s define a class that validates if it is provided using the correct format.

getArticlesByAuthorQuery.service.ts

We can now use the above class in our controller.

articles.controller.ts

Combining the data from both tables

It is common to want to combine the data from more than one table. Let’s create a model containing detailed information about the article and its author.

articleWithAuthor.model.ts

We need to perform a join to fetch the author’s data together with the article.

The default type of join is the inner join. It returns records that have matching values in both tables. Since every article requires an author, it works as expected.

In the previous article, we implemented the outer join when fetching the user together with the address since the address is optional. Outer joins preserve the rows that don’t have matching values in both tables.

We must perform two joins to query the article, author, and possible address.

articles.service.ts

Summary

In this article, we’ve explained the one-to-many relationship in SQL and implemented it using Kysely and NestJS. When doing that, we had to make a SQL query that used more than one join. We also learned how to write a migration that adds a new non-nullable column and how to avoid errors when running it on an existing database. There is still more to cover regarding relationships with PostgreSQL and Kysely, so stay tuned!

Series Navigation<< API with NestJS #120. One-to-one relationships with the Kysely query builderAPI with NestJS #122. Many-to-many relationships with Kysely and PostgreSQL >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments