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_adminRunning 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 { 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 { 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 { 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: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. 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, $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,