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): Promise { 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): Promise { 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; 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; email: string; name: 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 {} 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, name: userData.name, 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; name: 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; name: 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.a