Many-to-one relationships with PostgreSQL and Kysely

August 21, 2023

Designing relationships is one of the crucial aspects of working with SQL databases. In this article, we continue using Kysely with NestJS and implement many-to-one relationships.

Check out this repository if you want to see the full code from this article.

Introducing the many-to-one relationship#

When implementing the many-to-one relationship, a row from the first table is connected to multiple rows in the second table. What’s essential, a row from the second table can connect to just one row from the first table.

An example is an article with a single author, while the user can be an author of many articles. A way to implement it is to save the author’s id in the articles table as a foreign key. A foreign key is a value that matches a column from a different table.

Whenever we create a foreign key in our database, PostgreSQL defines the foreign key constraint to ensure the consistency of our data. Thanks to that, it prevents us from having an author_id value that refers to a user that does not exist. We can’t:

create an article and provide the author_id that points to a user that cannot be found in the users table,
update an existing article and change the author_id to match a user that does not exist,
delete a user with an id used in the author_id column
- we would have to delete the article first or change its author
- alternatively, we could use the CASCADE option to force PostgreSQL to delete all articles the deleted user is an author of

Defining the many-to-one relationship with Kysely#

Previously, we learned how to write SQL migrations when using Kysely. This time, we want to add the author_id column that is not nullable. Unfortunately, we might already have some articles in our database, and adding a new non-nullable column without a default value would cause an error.

ALTER TABLE articles
ADD COLUMN author_id int REFERENCES users(id) NOT NULL

ERROR: column “author_id” of relation “articles” contains null values

To solve the above problem, we can provide a default value for the author_id column. To do that, we need to have a default user. Let’s add a seed file to our migrations directory. Creating seed files is a way to populate our database with initial data.

Adding the seed file#

First, let’s add the email and password of the admin to the environment variables.

.env#

ADMIN_EMAIL=admin@admin.com
ADMIN_PASSWORD=strongPassword

Now we can add the seed file to our migrations.

20230817223154_insert_admin.ts#

import { ConfigService } from "@nestjs/config"
import * as bcrypt from "bcrypt"
import { config } from "dotenv"
import { Migration } from "kysely"
 
import { Database } from "../database/database"
import { EnvironmentVariables } from "../types/environmentVariables"
 
config()
const configService = new ConfigService<EnvironmentVariables>()
export const up: Migration["up"] = async (database) => {
  const email = configService.get("ADMIN_EMAIL")
  const password = configService.get("ADMIN_PASSWORD")
  const hashedPassword = await bcrypt.hash(password, 10)
  await database
    .insertInto("users")
    .values({
      email,
      password: hashedPassword,
      name: "Admin",
    })
    .execute()
}
export const down: Migration["up"] = async (database) => {
  const email = configService.get("ADMIN_EMAIL")
  await database.deleteFrom("users").where("email", "=", email).execute()
}

Creating the migration#

When writing the migration file that adds the author_id column, we can implement the following approach:

get the id of the admin,
add the author_id column as nullable,
set the value in the author_id column for articles that don’t have it,
make the author_id column non-nullable.

20230817230950_add_author_id.ts#

import { ConfigService } from "@nestjs/config"
import { config } from "dotenv"
import { Kysely, Migration } from "kysely"
 
import { EnvironmentVariables } from "../types/environmentVariables"
 
config()
const configService = new ConfigService<EnvironmentVariables>()
export const up: Migration["up"] = async (database) => {
  const email = configService.get("ADMIN_EMAIL")
  const adminDatabaseResponse = await database
    .selectFrom("users")
    .where("email", "=", email)
    .selectAll()
    .executeTakeFirstOrThrow()
  const adminId = adminDatabaseResponse.id
  await database.schema
    .alterTable("articles")
    .addColumn("author_id", "integer", (column) => {
      return column.references("users.id")
    })
    .execute()
  await database
    .updateTable("articles")
    .set({
      author_id: adminId,
    })
    .execute()
  await database.schema
    .alterTable("articles")
    .alterColumn("author_id", (column) => {
      return column.setNotNull()
    })
    .execute()
}
export async function down(database: Kysely<unknown>): Promise<void> {
  await database.schema.alterTable("articles").dropColumn("author_id").execute()
}

Many-to-one vs one-to-one#

Previously, we’ve covered the one-to-one relationship. When writing the migration, we’ve run the following query:

await database.schema
  .alterTable('users')
  .addColumn('address_id', 'integer', (column) => {
    return column.unique().references('addresses.id');
  })
  .execute();

Adding the unique constraint ensures that a particular address belongs to only one user.

On the contrary, when adding the author_id column, we ran the query without adding the unique constraint:

await database.schema
  .alterTable('articles')
  .addColumn('author_id', 'integer', (column) => {
    return column.references('users.id');
  })
  .execute();

Thanks to the above approach, multiple articles can share the same author.

Creating an article with the author#

The next thing we need to do is to modify the TypeScript definition of our articles table.

articlesTable.ts#

import { Generated } from "kysely"
 
export interface ArticlesTable {
  id: Generated<number>
  title: string
  article_content: string
  author_id: number
}

Let’s also add the author’s id to the article’s model.

articles.model.ts#

interface ArticleModelData {
  id: number
  title: string
  article_content: string
  author_id: number
}
export class Article {
  id: number
  title: string
  content: string
  authorId: number
  constructor({ id, title, article_content, author_id }: ArticleModelData) {
    this.id = id
    this.title = title
    this.content = article_content
    this.authorId = author_id
  }
}

We must also handle the author_id column when inserting the article into our database.

articles.service.ts#

import { Database } from "../database/database"
import { Article } from "./article.model"
import { Injectable } from "@nestjs/common"
import ArticleDto from "./dto/article.dto"
@Injectable()
export class ArticlesRepository {
  constructor(private readonly database: Database) {}
  async create(data: ArticleDto, authorId: number) {
    const databaseResponse = await this.database
      .insertInto("articles")
      .values({
        title: data.title,
        article_content: data.content,
        author_id: authorId,
      })
      .returningAll()
      .executeTakeFirstOrThrow()
    return new Article(databaseResponse)
  } // ...
}

When figuring out who is the author of the new article, we don’t expect the information to be provided directly through the body of the POST request. Instead, we get this information by decoding the JWT token.

articles.controller.ts#

import { Controller, Post, Body, UseGuards, Req } from "@nestjs/common"
import ArticleDto from "./dto/article.dto"
import { ArticlesService } from "./articles.service"
import JwtAuthenticationGuard from "../authentication/jwt-authentication.guard"
import { RequestWithUser } from "../authentication/requestWithUser.interface"
@Controller("articles")
export class ArticlesController {
  constructor(private readonly articlesService: ArticlesService) {}
  @Post()
  @UseGuards(JwtAuthenticationGuard)
  create(@Body() data: ArticleDto, @Req() request: RequestWithUser) {
    return this.articlesService.create(data, request.user.id)
  } // ...
}

Fetching articles of a particular user#

We can query the articles written by a particular author using the where function.

articles.repository.ts#

import { Database } from "../database/database"
import { Article } from "./article.model"
import { Injectable } from "@nestjs/common"
@Injectable()
export class ArticlesRepository {
  constructor(private readonly database: Database) {}
  async getByAuthorId(authorId: number) {
    const databaseResponse = await this.database
      .selectFrom("articles")
      .where("author_id", "=", authorId)
      .selectAll()
      .execute()
    return databaseResponse.map((articleData) => new Article(articleData))
  } // ...
}

Let’s use a different method from our repository based on whether the author’s id is provided.

articles.service.ts#

import { Injectable } from "@nestjs/common"
import { ArticlesRepository } from "./articles.repository"
@Injectable()
export class ArticlesService {
  constructor(private readonly articlesRepository: ArticlesRepository) {}
  getAll(authorId?: number) {
    if (authorId) {
      return this.articlesRepository.getByAuthorId(authorId)
    }
    return this.articlesRepository.getAll()
  } // ...
}

A good way to use the above feature through our REST API is with a query parameter. Let’s define a class that validates if it is provided using the correct format.

getArticlesByAuthorQuery.service.ts#

import { Transform } from "class-transformer"
import { IsNumber, IsOptional, Min } from "class-validator"
export class GetArticlesByAuthorQuery {
  @IsNumber()
  @Min(1)
  @IsOptional()
  @Transform(({ value }) => Number(value))
  authorId?: number
}

We can now use the above class in our controller.

articles.controller.ts#

import { Controller, Get, Query } from "@nestjs/common"
import { ArticlesService } from "./articles.service"
import { GetArticlesByAuthorQuery } from "./getArticlesByAuthorQuery"
@Controller("articles")
export class ArticlesController {
  constructor(private readonly articlesService: ArticlesService) {}
  @Get()
  getAll(@Query() { authorId }: GetArticlesByAuthorQuery) {
    return this.articlesService.getAll(authorId)
  } // ...
}

Combining the data from both tables#

It is common to want to combine the data from more than one table. Let’s create a model containing detailed information about the article and its author.

articleWithAuthor.model.ts#

import { User } from "../users/user.model"
import { Article, ArticleModelData } from "./article.model"
 
interface ArticleWithAuthorModelData extends ArticleModelData {
  user_id: number
  user_email: string
  user_name: string
  user_password: string
  address_id: number | null
  address_street: string | null
  address_city: string | null
  address_country: string | null
}
export class ArticleWithAuthor extends Article {
  author: User
  constructor(articleData: ArticleWithAuthorModelData) {
    super(articleData)
    this.author = new User({
      id: articleData.user_id,
      email: articleData.user_email,
      name: articleData.user_name,
      password: articleData.user_password,
      address_city: articleData.address_city,
      address_country: articleData.address_country,
      address_street: articleData.address_street,
      address_id: articleData.address_id,
    })
  }
}

We need to perform a join to fetch the author’s data together with the article.

SELECT
  articles.id AS id, articles.title AS title, articles.article_content AS article_content, articles.author_id as author_id,
  users.id AS user_id, users.email AS user_email, users.name AS user_name, users.password AS user_password
  FROM articles
JOIN users ON articles.author_id = users.id
WHERE articles.id=$1

The default type of join is the inner join. It returns records that have matching values in both tables. Since every article requires an author, it works as expected.

Previously, we implemented the outer join when fetching the user together with the address since the address is optional. Outer joins preserve the rows that don’t have matching values in both tables.

We must perform two joins to query the article, author, and possible address.

articles.service.ts#

import { Database } from "../database/database"
import { Injectable } from "@nestjs/common"
import { ArticleWithAuthorModel } from "./articleWithAuthor.model"
@Injectable()
export class ArticlesRepository {
  constructor(private readonly database: Database) {}
  async getWithAuthor(id: number) {
    const databaseResponse = await this.database
      .selectFrom("articles")
      .where("articles.id", "=", id)
      .innerJoin("users", "users.id", "articles.author_id")
      .leftJoin("addresses", "addresses.id", "users.address_id")
      .select([
        "articles.id as id",
        "articles.article_content as article_content",
        "articles.title as title",
        "articles.author_id as author_id",
        "users.id as user_id",
        "users.email as user_email",
        "users.name as user_name",
        "users.password as user_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 ArticleWithAuthorModel(databaseResponse)
    }
  } // ...
}

Summary#

In this article, we’ve explained the one-to-many relationship in SQL and implemented it using Kysely and NestJS. When doing that, we had to make a SQL query that used more than one join. We also learned how to write a migration that adds a new non-nullable column and how to avoid errors when running it on an existing database. There is still more to cover regarding relationships with PostgreSQL and Kysely, so stay tuned!