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.
1import { serial, text, integer, pgTable } from 'drizzle-orm/pg-core';
2
3export const articles = pgTable('articles', {
4 id: serial('id').primaryKey(),
5 title: text('title'),
6 content: text('content'),
7});
8
9export const addresses = pgTable('addresses', {
10 id: serial('id').primaryKey(),
11 street: text('street'),
12 city: text('city'),
13 country: text('country'),
14});
15
16export const users = pgTable('users', {
17 id: serial('id').primaryKey(),
18 email: text('email').unique(),
19 name: text('name'),
20 password: text('password'),
21 addressId: integer('address_id')
22 .unique()
23 .references(() => addresses.id),
24});
25
26export const databaseSchema = {
27 articles,
28 addresses,
29 users,
30};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.
1npx drizzle-kit generate --name create-users-and-addresses-tablesIf 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.
1npx drizzle-kit migrateInserting 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.
1import { Injectable } from '@nestjs/common';
2import { UserDto } from './user.dto';
3import { DrizzleService } from '../database/drizzle.service';
4import { databaseSchema } from '../database/database-schema';
5import { isRecord } from '../utilities/isRecord';
6import { PostgresErrorCode } from '../database/postgres-error-code.enum';
7import { UserAlreadyExistsException } from './user-already-exists.exception';
8
9@Injectable()
10export class UsersService {
11 constructor(private readonly drizzleService: DrizzleService) {}
12
13 // ...
14
15 async createWithAddress(user: UserDto) {
16 return this.drizzleService.db.transaction(async (transaction) => {
17 const createdAddresses = await transaction
18 .insert(databaseSchema.addresses)
19 .values(user.address)
20 .returning();
21
22 const createdAddress = createdAddresses.pop();
23
24 try {
25 const createdUsers = await transaction
26 .insert(databaseSchema.users)
27 .values({
28 name: user.name,
29 email: user.email,
30 password: user.password,
31 addressId: createdAddress.id,
32 })
33 .returning();
34 return createdUsers.pop();
35 } catch (error) {
36 if (
37 isRecord(error) &&
38 error.code === PostgresErrorCode.UniqueViolation
39 ) {
40 throw new UserAlreadyExistsException(user.email);
41 }
42 throw error;
43 }
44 });
45 }
46}Above, we catch the error and compare it to an enum that contains known PostgreSQL error codes.
1export enum PostgresErrorCode {
2 UniqueViolation = '23505',
3}If the error matches, we throw our custom UserAlreadyExistsException.
1import { BadRequestException } from '@nestjs/common';
2
3export class UserAlreadyExistsException extends BadRequestException {
4 constructor(email: string) {
5 super(`User with ${email} email already exists`);
6 }
7}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.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { eq } from 'drizzle-orm';
5
6@Injectable()
7export class UsersService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async getByEmail(email: string) {
11 const allResults = await this.drizzleService.db
12 .select()
13 .from(databaseSchema.users)
14 .where(eq(databaseSchema.users.email, email))
15 .leftJoin(
16 databaseSchema.addresses,
17 eq(databaseSchema.users.addressId, databaseSchema.addresses.id),
18 );
19
20 const result = allResults.pop();
21
22 if (!result) {
23 throw new NotFoundException();
24 }
25
26 return {
27 ...result.users,
28 address: result.addresses,
29 };
30 }
31
32 // ...
33}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.
1import { serial, text, integer, pgTable } from 'drizzle-orm/pg-core';
2import { relations } from 'drizzle-orm';
3
4// ...
5
6export const addresses = pgTable('addresses', {
7 id: serial('id').primaryKey(),
8 street: text('street'),
9 city: text('city'),
10 country: text('country'),
11});
12
13export const users = pgTable('users', {
14 id: serial('id').primaryKey(),
15 email: text('email').unique(),
16 name: text('name'),
17 password: text('password'),
18 addressId: integer('address_id')
19 .unique()
20 .references(() => addresses.id),
21});
22
23export const usersAddressesRelation = relations(users, ({ one }) => ({
24 address: one(addresses, {
25 fields: [users.addressId],
26 references: [addresses.id],
27 }),
28}));
29
30export const databaseSchema = {
31 articles,
32 addresses,
33 users,
34 usersAddressesRelation,
35};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.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { eq } from 'drizzle-orm';
5
6@Injectable()
7export class UsersService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async getByEmail(email: string) {
11 const user = await this.drizzleService.db.query.users.findFirst({
12 with: {
13 address: true,
14 },
15 where: eq(databaseSchema.users.email, email),
16 });
17
18 if (!user) {
19 throw new NotFoundException();
20 }
21
22 return user;
23 }
24
25 // ...
26}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!