API with NestJS #150. One-to-one relationships with the Drizzle ORM

NestJS SQL

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

When building a database, the tables we set up often connect to each other. Managing these relationships is one of the crucial parts of working with databases.

In the previous article, we learned how to use NestJS with Drizzle to set up a simple project with PostgreSQL. This time, we go further and write more complex code that involves the one-to-one relationship.

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

What is a one-to-one relationship?

When designing a database with users and their addresses, we could add the , , and to the table. However, as the database grows, it might make sense to split the table if we can group specific columns together. To do that, we need to use a one-to-one relationship.

When creating a one-to-one relationship, each row in the first table corresponds to exactly one row in the second table, and vice versa.

In our schema, the address is optional. When we have a one-to-one relationship that is optional we might also refer to it as one-to-zero-or-one relationship.

Creating a migration with the one-to-one relationship

To define a relationship, we need to define a foreign key. It is a column that references another table. A good example is the column, which contains an id of a particular address that belongs to a certain user.

database-schema.ts

What’s important is that we add a unique constraint to the column. Thanks to that, we ensure that only one user can refer to a particular address. Attaching more than one user to the same address would result in an error.

To create a migration using the Drizzle Kit, we need to run the appropriate command.

If you want to know more about migrations with Drizzle, check out API with NestJS #149. Introduction to the Drizzle ORM with PostgreSQL

We can now run our migration to add the tables to our database.

Inserting both entities in a single query

We want to insert both the user and the address into the database simultaneously.

First, we create an address. Then, we create the user and use the ID of the created address as the foreign key. If, for some reason, creating the user fails, we don’t want to leave the unnecessary address in the database. To achieve that, we can use a transaction. The crucial aspect of a transaction is that it either succeeds entirely or completely fails. If creating the user fails, Drizzle will roll back the address from the database.

Transactions are a broader topic and deserve a separate article.

users.service.ts

Above, we catch the error and compare it to an enum that contains known PostgreSQL error codes.

postgres-error-code.service.ts

If the error matches, we throw our custom .

user-already-exists.exception.ts

Fetching the data from two tables

Our queries can retrieve rows from several tables simultaneously and combine them. One effective method to achieve this is by using a join query.

The most basic type of join is the inner join. The key point is that it only returns records with matching values in both tables. In situations where the address is optional, like in our scenario, executing the query for a user without an address would yield no results.

To resolve the problem, we should use an outer join. Outer joins keep the rows that don’t have corresponding values. Specifically, we should apply a left join, which retrieves all records from the left table along with the matching records from the right table. For us, the left table is , and the right table is .

users.service.ts

With this approach, our query successfully handles users who don’t have an address.

Using the Query API

Alternatively, we can use the Query API built into Drizzle to avoid manually dealing with joins. To do that, we first need to define the relationship explicitly in our database schema.

database-schema.ts

Above, we call the function and export the variable. Now, Drizzle has more information about our relationship and can implicitly perform the joins for us.

users.service.ts

By using the function, we can tell Drizzle which relationships we want to include in our query.

Summary

In this article, we’ve explained the one-to-one relationship and how to implement it with Drizzle. We’ve also learned how to create multiple entities in a single query and how to combine data from two different tables. We’ve done that both by doing joins manually and through the Query API, which simplifies our code.

There is still more to learn about the Drizzle ORM, so stay tuned!

Series Navigation<< API with NestJS #149. Introduction to the Drizzle ORM with PostgreSQLAPI with NestJS #151. Implementing many-to-one relationships with Drizzle ORM >>
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Vitalii
Vitalii
6 months ago

Nice article. Thanks! Tell me please would it be a wrong strategy to not have address_id in User table, to have just user_id as a primary key for addresses and join it by that user_id which is unique and which is used as primary key for Address table? Are there any pitfalls which I don’t see?