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

August 14, 2023

This entry is part 120 of 175 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 address_street, address_city, and address_country to our users table. However, it might make sense to split it into a separate table called addresses 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

import { Kysely } from 'kysely';

export async function up(database: Kysely<unknown>): Promise<void> {

await database.schema

.createTable('addresses')

.addColumn('id', 'serial', (column) => {

return column.primaryKey();

})

.addColumn('street', 'text')

.addColumn('city', 'text')

.addColumn('country', 'text')

.execute();

await database.schema

.alterTable('users')

.addColumn('address_id', 'integer', (column) => {

return column.unique().references('addresses.id');

})

.execute();

}

export async function down(database: Kysely<unknown>): Promise<void> {

await database.schema.dropTable('country').execute();

await database.schema.alterTable('users').dropColumn('address_id');

}

In the above code, we create the addresses table. We also add the address_id column to the existing users table as a foreign key that refers to the primary key of the addresses 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 address_id column. Thanks to that, trying to connect more than one user to the same row in the addresses table would throw an error.

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

addressesTable.ts

import { Generated } from 'kysely';

export interface AddressesTable {

id: Generated<number>;

street: string | null;

city: string | null;

country: string | null;

}

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

usersTable.ts

import { Generated } from 'kysely';

export interface UsersTable {

id: Generated<number>;

email: string;

password: string;

address_id: number | null;

}

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

database.ts

import { ArticlesTable } from '../articles/articlesTable';

import { Kysely } from 'kysely';

import { UsersTable } from '../users/usersTable';

import { AddressesTable } from '../users/addressesTable';

interface Tables {

articles: ArticlesTable;

users: UsersTable;

addresses: AddressesTable;

}

export class Database extends Kysely<Tables> {}

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 WITH 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

import { Injectable } from '@nestjs/common';

import { User } from './user.model';

import { CreateUserDto } from './dto/createUser.dto';

import { Database } from '../database/database';

@Injectable()

export class UsersRepository {

constructor(private readonly database: Database) {}

// ...

async createWithAddress(userData: CreateUserDto) {

const databaseResponse = await this.database

.with('created_address', (database) => {

return database

.insertInto('addresses')

.values({

street: userData.address?.street,

city: userData.address?.city,

country: userData.address?.country,

})

.returningAll();

})

.insertInto('users')

.values((expressionBuilder) => {

return {

password: userData.password,

email: userData.email,

address_id: expressionBuilder

.selectFrom('created_address')

.select('id'),

};

})

.returning((expressionBuilder) => {

return [

'id',

'email',

'name',

'password',

'address_id',

expressionBuilder

.selectFrom('created_address')

.select('street')

.as('address_street'),

expressionBuilder

.selectFrom('created_address')

.select('city')

.as('address_city'),

expressionBuilder

.selectFrom('created_address')

.select('country')

.as('address_country'),

];

})

.executeTakeFirstOrThrow();

return new User(databaseResponse);

}

Thanks to using the with 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:

address_id: expressionBuilder

.selectFrom('created_address')

.select('wrong_column_name')

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

users.service.ts

import { Injectable } from '@nestjs/common';

import { CreateUserDto } from './dto/createUser.dto';

import { UsersRepository } from './users.repository';

import { isRecord } from '../utils/isRecord';

import { PostgresErrorCode } from '../database/postgresErrorCode.enum';

import { UserAlreadyExistsException } from './exceptions/userAlreadyExists.exception';

@Injectable()

export class UsersService {

constructor(private readonly usersRepository: UsersRepository) {}

// ...

async create(user: CreateUserDto) {

try {

if (user.address) {

return await this.usersRepository.createWithAddress(user);

}

return await this.usersRepository.create(user);

} catch (error) {

if (isRecord(error) && error.code === PostgresErrorCode.UniqueViolation) {

throw new UserAlreadyExistsException(user.email);

}

throw error;

}

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 PostgresErrorCode enum that contains various codes that PostgreSQL can emit when an error happens.

postgresErrorCode.enum.ts

export enum PostgresErrorCode {

UniqueViolation = '23505',

}

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

interface AddressModelData {

id: number;

street?: string | null;

city?: string | null;

country?: string | null;

}

export class Address {

id: number;

street: string | null;

city: string | null;

country: string | null;

constructor({

id,

street = null,

city = null,

country = null,

}: AddressModelData) {

this.id = id;

this.street = street;

this.city = city;

this.country = country;

}

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

user.model.ts

import { Exclude } from 'class-transformer';

import { Address } from './address.model';

interface UserModelData {

id: number;

email: string;

password: string;

address_id?: number | null;

address_street?: string | null;

address_city?: string | null;

address_country?: string | null;

}

export class User {

id: number;

email: string;

@Exclude({ toPlainOnly: true })

password: string;

address?: Address;

constructor({

id,

email,

name,

password,

address_id = null,

address_street = null,

address_country = null,

address_city = null,

}: UserModelData) {

this.id = id;

this.email = email;

this.name = name;

this.password = password;

if (address_id) {

this.address = new Address({

id: address_id,

street: address_street,

city: address_city,

country: address_country,

});

}

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.

SELECT users.*,

addresses.street as address_street, addresses.city as address_city, addresses.country as address_country

FROM users

JOIN addresses ON users.address_id = addresses.id

WHERE email=$1

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 users, and the right table is the addresses.

users.repository.ts

import { Injectable } from '@nestjs/common';

import { User } from './user.model';

import { Database } from '../database/database';

@Injectable()

export class UsersRepository {

constructor(private readonly database: Database) {}

async getByEmail(email: string) {

const databaseResponse = await this.database

.selectFrom('users')

.where('email', '=', email)

.leftJoin('addresses', 'addresses.id', 'users.address_id')

.select([

'users.id as id',

'users.email as email',

'users.name as name',

'users.password as password',

'addresses.id as address_id',

'addresses.city as address_city',

'addresses.street as address_street',

'addresses.country as address_country',

])

.executeTakeFirst();

if (databaseResponse) {

return new User(databaseResponse);

}

// ...

}

Thanks to using Kysely, the above code is type-safe. TypeScript would prevent us from making a typo in 'addresses.country as address_country', 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 >>