Designing many-to-one relationships using raw SQL queries

September 12, 2022

Learning how to design and implement relationships between tables is a crucial skill for a backend developer. In this article, we continue working with raw SQL queries and learn about many-to-one relationships.

You can find the code from this article in this repository.

Understanding the many-to-one relationship#

When creating a many-to-one relationship, a row from the first table is linked to multiple rows in the second table. Importantly, the rows from the second table can connect to just one row from the first table. A straightforward example is a post that can have a single author, while the user can be an author of many posts.

A way to implement the above relationship is to store the author’s id in the posts table as a foreign key. A foreign key is a column that matches a column from a different table.

When we create a foreign key, PostgreSQL defines a foreign key constraint. It ensures the consistency of our data.

PostgreSQL will prevent you from having an author_id that refers to a nonexistent user. For example, we can’t:

create a post and provide author_id that does not match a record from the users table,
modify an existing post and provide author_id that does not match a user,
delete a user that the author_id column refers to,
- we would have to either remove the post first or change its author,
- we could also use the CASCADE keyword,
  - it would force PostgreSQL to delete all posts the user is an author of when deleting the user.

Creating a many-to-one relationship#

We want every entity in the posts table to have an author. Therefore, we should put a NON NULL constraint on the author_id column.

Unfortunately, we already have multiple posts in our database, and adding a new non-nullable column without a default value would cause an error.

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

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

Instead, we need to provide some initial value for the author_id column. To do that, we need to define a default user. A good solution for that is to create a seed file. With seeds, we can populate our database with initial data.

knex seed:make 01_create_admin

Running the above command creates the 01_create_admin.ts file that we can now use to define a script that creates our user.

01_create_admin.ts#

import * as bcrypt from "bcrypt"
import { Knex } from "knex"
 
export async function seed(knex: Knex): Promise<void> {
  const hashedPassword = await bcrypt.hash("1234567", 10)
  return knex.raw(
    `
    INSERT INTO users (
      email,
      name,
      password
    ) VALUES (
      'admin@admin.com',
      'Admin',
      ?
    )
  `,
    [hashedPassword],
  )
}

When using knex.run we can use the ? sign to use parameters passed to the query.

After creating the above seed file, we can run npx knex seed:run to execute it.

Creating a migration#

When creating a migration file for the author_id column, we can use the following approach:

check the id of the default user,
add the author_id column as nullable,
set the author_id value for existing posts,
add the NOT NULL constraint for the author_id column.

20220908005809_add_author_column.ts#

import { Knex } from "knex"
 
export async function up(knex: Knex): Promise<void> {
  const adminEmail = "admin@admin.com"
  const defaultUserResponse = await knex.raw(
    `
    SELECT id FROM users
      WHERE email=?
  `,
    [adminEmail],
  )
  const adminId = defaultUserResponse.rows[0]?.id
  if (!adminId) {
    throw new Error("The default user does not exist")
  }
  await knex.raw(`
    ALTER TABLE posts
      ADD COLUMN author_id int REFERENCES users(id)
  `)
  await knex.raw(
    `
    UPDATE posts SET author_id = ?
  `,
    [adminId],
  )
  await knex.raw(`
    ALTER TABLE posts
      ALTER COLUMN author_id SET NOT NULL
  `)
}
export async function down(knex: Knex): Promise<void> {
  return knex.raw(`
    ALTER TABLE posts
      DROP COLUMN author_id;
  `)
}

It is crucial to acknowledge that with Knex, each migration runs inside a transaction by default. This means our migration either succeeds fully or makes no changes to the database.

Transactions in SQL are a great topic for a separate article.

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

Previously, we covered working with one-to-one relationships, where we ran the following query:

ALTER TABLE users
  ADD COLUMN address_id int UNIQUE REFERENCES addresses(id);

By adding the unique constraint, we ensure that no two users have the same address.

In contrast, when adding the author_id column, we ran a query without the unique constraint:

ALTER TABLE posts
  ADD COLUMN author_id int REFERENCES users(id)

Thanks to the above, many posts can share the same author.

Creating posts with authors#

So far, we’ve relied on the user to provide the complete data of a post when creating it. On the contrary, when figuring out the post’s author, we don’t expect the user to provide the id explicitly. Instead, we get that information from the JWT token.

posts.controller.ts#

import {
  Body,
  ClassSerializerInterceptor,
  Controller,
  Post,
  Req,
  UseGuards,
  UseInterceptors,
} from "@nestjs/common"
import { PostsService } from "./posts.service"
import PostDto from "./post.dto"
import JwtAuthenticationGuard from "../authentication/jwt-authentication.guard"
import RequestWithUser from "../authentication/requestWithUser.interface"
@Controller("posts")
@UseInterceptors(ClassSerializerInterceptor)
export default class PostsController {
  constructor(private readonly postsService: PostsService) {}
  @Post()
  @UseGuards(JwtAuthenticationGuard)
  createPost(@Body() postData: PostDto, @Req() request: RequestWithUser) {
    return this.postsService.createPost(postData, request.user.id)
  } // ...
}

The next step is to handle the author_id property in our INSERT query.

posts.repository.ts#

import { Injectable } from "@nestjs/common"
import DatabaseService from "../database/database.service"
import PostModel from "./post.model"
import PostDto from "./post.dto"
@Injectable()
class PostsRepository {
  constructor(private readonly databaseService: DatabaseService) {}
  async create(postData: PostDto, authorId: number) {
    const databaseResponse = await this.databaseService.runQuery(
      `
      INSERT INTO posts (
        title,
        post_content,
        author_id
      ) VALUES (
        $1,
        $2,
        $3
      ) RETURNING *
    `,
      [postData.title, postData.content, authorId],
    )
    return new PostModel(databaseResponse.rows[0])
  } // ...
}
export default PostsRepository

Thanks to the above, we insert the author_id into the database and can use it in our model.

post.model.ts#

interface PostModelData {
  id: number
  title: string
  post_content: string
  author_id: number
}
class PostModel {
  id: number
  title: string
  content: string
  authorId: number
  constructor(postData: PostModelData) {
    this.id = postData.id
    this.title = postData.title
    this.content = postData.post_content
    this.authorId = postData.author_id
  }
}
export default PostModel

Getting the posts of a particular user#

To get the posts of a user with a particular id, we can use a query parameter.

posts.controller.ts#

import { ClassSerializerInterceptor, Controller, Get, Query, UseInterceptors } from "@nestjs/common"
import { PostsService } from "./posts.service"
import GetPostsByAuthorQuery from "./getPostsByAuthorQuery"
@Controller("posts")
@UseInterceptors(ClassSerializerInterceptor)
export default class PostsController {
  constructor(private readonly postsService: PostsService) {}
  @Get()
  getPosts(@Query() { authorId }: GetPostsByAuthorQuery) {
    return this.postsService.getPosts(authorId)
  } // ...
}

Thanks to using the GetPostsByAuthorQuery class, we can validate and transform the query parameter provided by the user.

getPostsByAuthorQuery.ts#

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

Then, if the user calls the API with the /posts?authorId=10, for example, we use a different method from our repository.

posts.service.ts#

import { Injectable } from "@nestjs/common"
import PostsRepository from "./posts.repository"
@Injectable()
export class PostsService {
  constructor(private readonly postsRepository: PostsRepository) {}
  getPosts(authorId?: number) {
    if (authorId) {
      return this.postsRepository.getByAuthorId(authorId)
    }
    return this.postsRepository.getAll()
  } // ...
}

Creating a query that gets the posts written by a particular author is a matter of a simple WHERE clause.

posts.repository.ts#

import { Injectable } from "@nestjs/common"
import DatabaseService from "../database/database.service"
import PostModel from "./post.model"
@Injectable()
class PostsRepository {
  constructor(private readonly databaseService: DatabaseService) {}
  async getByAuthorId(authorId: number) {
    const databaseResponse = await this.databaseService.runQuery(
      `
      SELECT * FROM posts WHERE author_id=$1
    `,
      [authorId],
    )
    return databaseResponse.rows.map((databaseRow) => new PostModel(databaseRow))
  } // ...
}
export default PostsRepository

Querying multiple tables#

There might be a case where we want to fetch rows from both the posts and users table and match them. To do that, we need a JOIN query.

SELECT
  posts.id AS id, posts.title AS title, posts.post_content AS post_content, posts.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 posts
JOIN users ON posts.author_id = users.id
WHERE posts.id=$1

By using the JOIN keyword, we perform the inner join. It returns records with matching values in both tables. Since there are no posts that don’t have the author, it is acceptable in this case.

Previously, we used an outer join when fetching the user with the address because the address is optional. Outer joins preserve the rows that don’t have matching values.

Since we want to fetch the post, author, and possible address, we need to use two JOIN statements.

posts.repository.ts#

import { Injectable, NotFoundException } from "@nestjs/common"
import DatabaseService from "../database/database.service"
import PostWithAuthorModel from "./postWithAuthor.model"
@Injectable()
class PostsRepository {
  constructor(private readonly databaseService: DatabaseService) {}
  async getWithAuthor(postId: number) {
    const databaseResponse = await this.databaseService.runQuery(
      `
      SELECT
        posts.id AS id, posts.title AS title, posts.post_content AS post_content, posts.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.street AS address_street, addresses.city AS address_city, addresses.country AS address_country
      FROM posts
      JOIN users ON posts.author_id = users.id
      LEFT JOIN addresses ON users.address_id = addresses.id
      WHERE posts.id=$1
      `,
      [postId],
    )
    const entity = databaseResponse.rows[0]
    if (!entity) {
      throw new NotFoundException()
    }
    return new PostWithAuthorModel(entity)
  } // ...
}
export default PostsRepository

Let’s also create the PostWithAuthorModel class that extends PostModel.

postWithAuthor.model.ts#

import UserModel from "../users/user.model"
import PostModel, { PostModelData } from "./post.model"
 
interface PostWithAuthorModelData extends PostModelData {
  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
}
class PostWithAuthorModel extends PostModel {
  author: UserModel
  constructor(postData: PostWithAuthorModelData) {
    super(postData)
    this.author = new UserModel({
      id: postData.user_id,
      email: postData.user_email,
      name: postData.user_name,
      password: postData.user_password,
      ...postData,
    })
  }
}
export default PostWithAuthorModel

Summary#

In this article, we’ve implemented an example of a one-to-many relationship using raw SQL queries. When doing that, we also wrote an SQL query that uses more than one JOIN statement. We’ve also learned how to write a migration that adds a new column with a NOT NULL constraint. There is still more to cover when it comes to implementing relationships in PostgreSQL, so stay tuned!