API with NestJS #120. One-to-one relationships with the Kysely query builder

NestJS SQL

This entry is part 120 of 146 in the API with NestJS

When we design the architecture of our database, we usually end up with tables that relate to each other in some way. Managing such relationships is one of the crucial aspects of working with SQL databases. In this article, we explain the one-to-one relationship and handle it using Kysely with PostgreSQL and NestJS.

You can see the full code from this article in this repository.

Introducing the one-to-one relationship

When storing the information about addresses, we could add the , , and to our table. However, it might make sense to split it into a separate table called and implement a one-to-one relationship. When we implement the one-to-one relationship, a particular row from the first table has one matching row from the second table and the other way around.

In the above situation, the addess is optional. The case of a one-to-one relationship that is optional can also be referred to as one-to-zero-or-one relationship.

While one-to-one might not be the most common type of relationship, there is a chance we might encounter it when working with various databases. When deciding on whether or not to create a one-to-one relationship, there are quite a few factors to take into consideration. I suggest reading this question on StackOverflow if you want to encounter various opinions to help you make your own choice.

Managing one-to-one relationships with Kysely

In the previous part of this series, we learned how to use Kysely to manage migrations. Let’s start by creating a new migration to add the addresses table and develop a relationship with the users.

20230813192709_add_addresses_table.ts

In the above code, we create the table. We also add the column to the existing table as a foreign key that refers to the primary key of the table. Thanks to that, PostgreSQL recognizes there is a connection between our tables.

Also, in our application, only one user can refer to a particular address. Because of that, we add the unique constraint to the column. Thanks to that, trying to connect more than one user to the same row in the table would throw an error.

We can now add the Table to the TypeScript definition of our database.

addressesTable.ts

Let’s also add the to the definition of our table.

usersTable.ts

The last step is to ensure that both tables are added to our interface.

database.ts

Inserting rows into two tables in a single query

We want to insert the user and the address into the database simultaneously. One way of doing that is to create a Common Table Expression Query using the statement. With this approach, we can create both the address and the user in a single, atomic SQL query and either succeeds completely or fails as a whole. The address won’t be stored in the database if something goes wrong when inserting the user.

users.repository.ts

Thanks to using the function from Kysely instead of writing the SQL query manually, our code is type-safe. For example, TypeScript would complain if we would make a typo in the following section of the code:

Let’s use the new method whenever the user’s signing-up data contains the address.

users.service.ts

Above, we’re also checking if there was an error when creating a user because the provided email is occupied already. To do that, we created the enum that contains various codes that PostgreSQL can emit when an error happens.

postgresErrorCode.enum.ts

Error handling with PostgreSQL and Kysely is a topic that deserves a separate article.

Creating the models

Since our query now includes the address, we need to adjust our models.

adress.model.ts

We also need to use the above model in the  class.

user.model.ts

Thanks to all of the above, we can now sign up while providing the details of our address.

Joining the data from two tables

Our SQL queries can retrieve rows from multiple tables simultaneously and match them based on ids. To do that, we need to perform a join. The default type of join in SQL is the inner join.

The crucial thing about the inner join is that it returns records that have matching values in both tables. In our database structure, the address is optional. Because of that, our query would not return a user that does not have an address, even if the user with the particular email is in our database.

To fix the above issue, we need to perform an outer join. Outer joins preserve the rows that don’t have matching values. In our case, we need to do the left join that returns all records from the left table and the matched records from the right table. The left table is the , and the right table is the .

users.repository.ts

Thanks to using Kysely, the above code is type-safe. TypeScript would prevent us from making a typo in , for example.

By using the left join, we ensure that our query works as expected for users that don’t have addresses.

Summary

In this article, we explained the one-to-one relationships using the example of users and addresses. We’ve also learned how to implement them when working with PostgreSQL and Kysely to generate SQL queries in a type-safe manner. While doing that, we used common table expressions to ensure we created both the user and the address in a single SQL query. We also had the chance to understand the difference between inner and outer joins. There is still much to learn when implementing relationships with Kysely and PostgreSQL, so stay tuned!

Series Navigation<< API with NestJS #119. Type-safe SQL queries with Kysely and PostgreSQLAPI with NestJS #121. Many-to-one relationships with PostgreSQL and Kysely >>
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Dany
Dany
6 months ago

Great series of tutos! Would be cool to create a blog post using DrizzleORM! 🙂

Last edited 6 months ago by Dany