API with NestJS #74. Designing many-to-one relationships using raw SQL queries

September 12, 2022

This entry is part 74 of 148 in the API with NestJS

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.

1 2	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.

1	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 { Knex } from 'knex';

import * as bcrypt from 'bcrypt';

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

In the previous article, we’ve covered working with one-to-one relationships. When doing so, we ran the following query:

1 2	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:

1 2	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.

If you want to know more about authentication and JWT tokens, check out API with NestJS #3. Authenticating users with bcrypt, Passport, JWT, and cookies

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,

) 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.

In the previous article, 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 PostModel, { PostModelData } from './post.model';

import UserModel from '../users/user.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!

Series Navigation<< API with NestJS #73. One-to-one relationships with raw SQL queriesAPI with NestJS #75. Many-to-many relationships using raw SQL queries >>