One-to-one relationships with the Drizzle ORM

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 address_country, address_city, and address_street to the users table. However, as the database grows, it might make sense to split the users 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 address_id column, which contains an id of a particular address that belongs to a certain user. database-schema.ts import { serial, text, integer, pgTable } from 'drizzle-orm/pg-core'; export const articles = pgTable('articles', { id: serial('id').primaryKey(), title: text('title'), content: text('content'), }); export const addresses = pgTable('addresses', { id: serial('id').primaryKey(), street: text('street'), city: text('city'), country: text('country'), }); export const users = pgTable('users', { id: serial('id').primaryKey(), email: text('email').unique(), name: text('name'), password: text('password'), addressId: integer('address_id') .unique() .references(() => addresses.id), }); export const databaseSchema = { articles, addresses, users, };What’s important is that we add a unique constraint to the address_id 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.npx drizzle-kit generate --name create-users-and-addresses-tables 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.npx drizzle-kit migrate 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 import { Injectable } from '@nestjs/common'; import { UserDto } from './user.dto'; import { DrizzleService } from '../database/drizzle.service'; import { databaseSchema } from '../database/database-schema'; import { isRecord } from '../utilities/isRecord'; import { PostgresErrorCode } from '../database/postgres-error-code.enum'; import { UserAlreadyExistsException } from './user-already-exists.exception'; @Injectable() export class UsersService { constructor(private readonly drizzleService: DrizzleService) {} // ... async createWithAddress(user: UserDto) { return this.drizzleService.db.transaction(async (transaction) => { const createdAddresses = await transaction .insert(databaseSchema.addresses) .values(user.address) .returning(); const createdAddress = createdAddresses.pop(); try { const createdUsers = await transaction .insert(databaseSchema.users) .values({ name: user.name, email: user.email, password: user.password, addressId: createdAddress.id, }) .returning(); return createdUsers.pop(); } catch (error) { if ( isRecord(error) && error.code === PostgresErrorCode.UniqueViolation ) { throw new UserAlreadyExistsException(user.email); } throw error; } }); } }Above, we catch the error and compare it to an enum that contains known PostgreSQL error codes. postgres-error-code.service.ts export enum PostgresErrorCode { UniqueViolation = '23505', }If the error matches, we throw our custom UserAlreadyExistsException. user-already-exists.exception.ts import { BadRequestException } from '@nestjs/common'; export class UserAlreadyExistsException extends BadRequestException { constructor(email: string) { super(`User with ${email} email already exists`); } } 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 users, and the right table is addresses. users.service.ts import { Injectable, NotFoundException } from '@nestjs/common'; import { DrizzleService } from '../database/drizzle.service'; import { databaseSchema } from '../database/database-schema'; import { eq } from 'drizzle-orm'; @Injectable() export class UsersService { constructor(private readonly drizzleService: DrizzleService) {} async getByEmail(email: string) { const allResults = await this.drizzleService.db .select() .from(databaseSchema.users) .where(eq(databaseSchema.users.email, email)) .leftJoin( databaseSchema.addresses, eq(databaseSchema.users.addressId, databaseSchema.addresses.id), ); const result = allResults.pop(); if (!result) { throw new NotFoundException(); } return { ...result.users, address: result.addresses, }; } // ... }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 import { serial, text, integer, pgTable } from 'drizzle-orm/pg-core'; import { relations } from 'drizzle-orm'; // ... export const addresses = pgTable('addresses', { id: serial('id').primaryKey(), street: text('street'), city: text('city'), country: text('country'), }); export const users = pgTable('users', { id: serial('id').primaryKey(), email: text('email').unique(), name: text('name'), password: text('password'), addressId: integer('address_id') .unique() .references(() => addresses.id), }); export const usersAddressesRelation = relations(users, ({ one }) => ({ address: one(addresses, { fields: [users.addressId], references: [addresses.id], }), })); export const databaseSchema = { articles, addresses, users, usersAddressesRelation, };Above, we call the relations() function and export the usersAddressesRelation variable. Now, Drizzle has more information about our relationship and can implicitly perform the joins for us. users.service.ts import { Injectable, NotFoundException } from '@nestjs/common'; import { DrizzleService } from '../database/drizzle.service'; import { databaseSchema } from '../database/database-schema'; import { eq } from 'drizzle-orm'; @Injectable() export class UsersService { constructor(private readonly drizzleService: DrizzleService) {} async getByEmail(email: string) { const user = await this.drizzleService.db.query.users.findFirst({ with: { address: true, }, where: eq(databaseSchema.users.email, email), }); if (!user) { throw new NotFoundException(); } return user; } // ... }By using the query.users.findFirst 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! The post API with NestJS #150. One-to-one relationships with the Drizzle ORM appeared first on Marcin Wanago Blog - JavaScript, both frontend and backend.