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.
1ALTER TABLE posts
2 ADD COLUMN author_id int REFERENCES users(id) NOT NULLERROR: 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.
1knex seed:make 01_create_adminRunning the above command creates the 01_create_admin.ts file that we can now use to define a script that creates our user.
1import { Knex } from 'knex';
2import * as bcrypt from 'bcrypt';
3
4export async function seed(knex: Knex): Promise<void> {
5 const hashedPassword = await bcrypt.hash('1234567', 10);
6 return knex.raw(
7 `
8 INSERT INTO users (
9 email,
10 name,
11 password
12 ) VALUES (
13 'admin@admin.com',
14 'Admin',
15 ?
16 )
17 `,
18 [hashedPassword],
19 );
20}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.
1import { Knex } from 'knex';
2
3export async function up(knex: Knex): Promise<void> {
4 const adminEmail = 'admin@admin.com';
5
6 const defaultUserResponse = await knex.raw(
7 `
8 SELECT id FROM users
9 WHERE email=?
10 `,
11 [adminEmail],
12 );
13
14 const adminId = defaultUserResponse.rows[0]?.id;
15
16 if (!adminId) {
17 throw new Error('The default user does not exist');
18 }
19
20 await knex.raw(
21 `
22 ALTER TABLE posts
23 ADD COLUMN author_id int REFERENCES users(id)
24 `,
25 );
26
27 await knex.raw(
28 `
29 UPDATE posts SET author_id = ?
30 `,
31 [adminId],
32 );
33
34 await knex.raw(
35 `
36 ALTER TABLE posts
37 ALTER COLUMN author_id SET NOT NULL
38 `,
39 );
40}
41
42export async function down(knex: Knex): Promise<void> {
43 return knex.raw(`
44 ALTER TABLE posts
45 DROP COLUMN author_id;
46 `);
47}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:
1ALTER TABLE users
2 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:
1ALTER TABLE posts
2 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
1import {
2 Body,
3 ClassSerializerInterceptor,
4 Controller,
5 Post,
6 Req,
7 UseGuards,
8 UseInterceptors,
9} from '@nestjs/common';
10import { PostsService } from './posts.service';
11import PostDto from './post.dto';
12import JwtAuthenticationGuard from '../authentication/jwt-authentication.guard';
13import RequestWithUser from '../authentication/requestWithUser.interface';
14
15@Controller('posts')
16@UseInterceptors(ClassSerializerInterceptor)
17export default class PostsController {
18 constructor(private readonly postsService: PostsService) {}
19
20 @Post()
21 @UseGuards(JwtAuthenticationGuard)
22 createPost(@Body() postData: PostDto, @Req() request: RequestWithUser) {
23 return this.postsService.createPost(postData, request.user.id);
24 }
25
26 // ...
27}The next step is to handle the author_id property in our INSERT query.
1import { Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import PostModel from './post.model';
4import PostDto from './post.dto';
5
6@Injectable()
7class PostsRepository {
8 constructor(private readonly databaseService: DatabaseService) {}
9
10 async create(postData: PostDto, authorId: number) {
11 const databaseResponse = await this.databaseService.runQuery(
12 `
13 INSERT INTO posts (
14 title,
15 post_content,
16 author_id
17 ) VALUES (
18 $1,
19 $2,
20 $3
21 ) RETURNING *
22 `,
23 [postData.title, postData.content, authorId],
24 );
25 return new PostModel(databaseResponse.rows[0]);
26 }
27
28 // ...
29}
30
31export default PostsRepository;Thanks to the above, we insert the author_id into the database and can use it in our model.
1interface PostModelData {
2 id: number;
3 title: string;
4 post_content: string;
5 author_id: number;
6}
7class PostModel {
8 id: number;
9 title: string;
10 content: string;
11 authorId: number;
12 constructor(postData: PostModelData) {
13 this.id = postData.id;
14 this.title = postData.title;
15 this.content = postData.post_content;
16 this.authorId = postData.author_id;
17 }
18}
19
20export 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.
1import {
2 ClassSerializerInterceptor,
3 Controller,
4 Get,
5 Query,
6 UseInterceptors,
7} from '@nestjs/common';
8import { PostsService } from './posts.service';
9import GetPostsByAuthorQuery from './getPostsByAuthorQuery';
10
11@Controller('posts')
12@UseInterceptors(ClassSerializerInterceptor)
13export default class PostsController {
14 constructor(private readonly postsService: PostsService) {}
15
16 @Get()
17 getPosts(@Query() { authorId }: GetPostsByAuthorQuery) {
18 return this.postsService.getPosts(authorId);
19 }
20
21 // ...
22}Thanks to using the GetPostsByAuthorQuery class, we can validate and transform the query parameter provided by the user.
1import { Transform } from 'class-transformer';
2import { IsNumber, IsOptional, Min } from 'class-validator';
3
4class GetPostsByAuthorQuery {
5 @IsNumber()
6 @Min(1)
7 @IsOptional()
8 @Transform(({ value }) => Number(value))
9 authorId?: number;
10}
11
12export default GetPostsByAuthorQuery;Then, if the user calls the API with the /posts?authorId=10, for example, we use a different method from our repository.
1import { Injectable } from '@nestjs/common';
2import PostsRepository from './posts.repository';
3
4@Injectable()
5export class PostsService {
6 constructor(private readonly postsRepository: PostsRepository) {}
7
8 getPosts(authorId?: number) {
9 if (authorId) {
10 return this.postsRepository.getByAuthorId(authorId);
11 }
12 return this.postsRepository.getAll();
13 }
14
15 // ...
16}Creating a query that gets the posts written by a particular author is a matter of a simple WHERE clause.
1import { Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import PostModel from './post.model';
4
5@Injectable()
6class PostsRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 async getByAuthorId(authorId: number) {
10 const databaseResponse = await this.databaseService.runQuery(
11 `
12 SELECT * FROM posts WHERE author_id=$1
13 `,
14 [authorId],
15 );
16 return databaseResponse.rows.map(
17 (databaseRow) => new PostModel(databaseRow),
18 );
19 }
20
21 // ...
22}
23
24export 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.
1SELECT
2 posts.id AS id, posts.title AS title, posts.post_content AS post_content, posts.author_id as author_id,
3 users.id AS user_id, users.email AS user_email, users.name AS user_name, users.password AS user_password
4 FROM posts
5JOIN users ON posts.author_id = users.id
6WHERE posts.id=$1By 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.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import PostWithAuthorModel from './postWithAuthor.model';
4
5@Injectable()
6class PostsRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 async getWithAuthor(postId: number) {
10 const databaseResponse = await this.databaseService.runQuery(
11 `
12 SELECT
13 posts.id AS id, posts.title AS title, posts.post_content AS post_content, posts.author_id as author_id,
14 users.id AS user_id, users.email AS user_email, users.name AS user_name, users.password AS user_password,
15 addresses.id AS address_id, addresses.street AS address_street, addresses.city AS address_city, addresses.country AS address_country
16 FROM posts
17 JOIN users ON posts.author_id = users.id
18 LEFT JOIN addresses ON users.address_id = addresses.id
19 WHERE posts.id=$1
20 `,
21 [postId],
22 );
23 const entity = databaseResponse.rows[0];
24 if (!entity) {
25 throw new NotFoundException();
26 }
27 return new PostWithAuthorModel(entity);
28 }
29
30 // ...
31}
32
33export default PostsRepository;Let’s also create the PostWithAuthorModel class that extends PostModel.
1import PostModel, { PostModelData } from './post.model';
2import UserModel from '../users/user.model';
3
4interface PostWithAuthorModelData extends PostModelData {
5 user_id: number;
6 user_email: string;
7 user_name: string;
8 user_password: string;
9 address_id: number | null;
10 address_street: string | null;
11 address_city: string | null;
12 address_country: string | null;
13}
14class PostWithAuthorModel extends PostModel {
15 author: UserModel;
16 constructor(postData: PostWithAuthorModelData) {
17 super(postData);
18 this.author = new UserModel({
19 id: postData.user_id,
20 email: postData.user_email,
21 name: postData.user_name,
22 password: postData.user_password,
23 ...postData,
24 });
25 }
26}
27
28export 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!