Studying tips on how to design and implement relationships between tables is a vital ability for a backend developer. On this article, we proceed working with uncooked SQL queries and find out about many-to-one relationships.
Yow will discover the code from this text in this repository.
Understanding the many-to-one relationship
When making a many-to-one relationship, a row from the primary desk is linked to a number of rows within the second desk. Importantly, the rows from the second desk can hook up with only one row from the primary desk. An easy instance is a submit that may have a single creator, whereas the consumer might be an creator of many posts.
A strategy to implement the above relationship is to retailer the creator’s id within the posts desk as a overseas key. A overseas secret is a column that matches a column from a special desk.
After we create a overseas key, PostgreSQL defines a overseas key constraint. It ensures the consistency of our knowledge.
PostgreSQL will stop you from having an author_id that refers to a nonexistent consumer. For instance, we will’t:
- create a submit and supply author_id that doesn’t match a report from the customers desk,
- modify an present submit and supply author_id that doesn’t match a consumer,
- delete a consumer that the author_id column refers to,
- we must both take away the submit first or change its creator,
- we might additionally use the CASCADE key phrase,
- it might power PostgreSQL to delete all posts the consumer is an creator of when deleting the consumer.
Making a many-to-one relationship
We wish each entity within the posts desk to have an creator. Subsequently, we should always put a NON NULL constraint on the author_id column.
Sadly, we have already got a number of posts in our database, and including a brand new non-nullable column with no default worth would trigger an error.
|
ALTER TABLE posts ADD COLUMN author_id int REFERENCES customers(id) NOT NULL |
ERROR: column “author_id” of relation “posts” accommodates null values
As a substitute, we have to present some preliminary worth for the author_id column. To do this, we have to outline a default consumer. A superb resolution for that’s to create a seed file. With seeds, we will populate our database with preliminary knowledge.
|
knex seed:make 01_create_admin |
Working the above command creates the 01_create_admin.ts file that we will now use to outline a script that creates our consumer.
01_create_admin.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
import { Knex } from ‘knex’; import * as bcrypt from ‘bcrypt’;
export async perform seed(knex: Knex): Promise<void> { const hashedPassword = await bcrypt.hash(‘1234567’, 10); return knex.uncooked( ` INSERT INTO customers ( e mail, identify, password ) VALUES ( ‘admin@admin.com’, ‘Admin’, ? ) `, [hashedPassword], ); } |
When utilizing knex.run we will use the ? signal to use parameters handed to the question.
After creating the above seed file, we will run npx knex seed:run to execute it.
Making a migration
When making a migration file for the author_id column, we will use the next method:
- verify the id of the default consumer,
- add the author_id column as nullable,
- set the author_id worth for present posts,
- add the NOT NULL constraint for the author_id column.
20220908005809_add_author_column.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
import { Knex } from ‘knex’;
export async perform up(knex: Knex): Promise<void> { const adminEmail = ‘admin@admin.com’;
const defaultUserResponse = await knex.uncooked( ` SELECT id FROM customers WHERE e mail=? `, [adminEmail], );
const adminId = defaultUserResponse.rows[0]?.id;
if (!adminId) { throw new Error(‘The default consumer doesn’t exist’); }
await knex.uncooked( ` ALTER TABLE posts ADD COLUMN author_id int REFERENCES customers(id) `, );
await knex.uncooked( ` UPDATE posts SET author_id = ? `, [adminId], );
await knex.uncooked( ` ALTER TABLE posts ALTER COLUMN author_id SET NOT NULL `, ); }
export async perform down(knex: Knex): Promise<void> { return knex.uncooked(` ALTER TABLE posts DROP COLUMN author_id; `); } |
It’s essential to acknowledge that with Knex, every migration runs inside a transaction by default. This implies our migration both succeeds totally or makes no modifications to the database.
Transactions in SQL are an awesome matter for a separate article.
Many-to-one vs. one-to-one
Within the earlier article, we’ve coated working with one-to-one relationships. When doing so, we ran the next question:
|
ALTER TABLE customers ADD COLUMN address_id int UNIQUE REFERENCES addresses(id); |
By including the distinctive constraint, we be certain that no two customers have the identical tackle.
In distinction, when including the author_id column, we ran a question with out the distinctive constraint:
|
ALTER TABLE posts ADD COLUMN author_id int REFERENCES customers(id) |
Because of the above, many posts can share the identical creator.
Creating posts with authors
To date, we’ve relied on the consumer to supply the entire knowledge of a submit when creating it. Quite the opposite, when determining the submit’s creator, we don’t anticipate the consumer to supply the id explicitly. As a substitute, we get that info from the JWT token.
If you wish to know extra about authentication and JWT tokens, take a look at API with NestJS #3. Authenticating customers with bcrypt, Passport, JWT, and cookies
posts.controller.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
import { Physique, ClassSerializerInterceptor, Controller, Publish, Req, UseGuards, UseInterceptors, } from ‘@nestjs/widespread’; import { PostsService } from ‘./posts.service’; import PostDto from ‘./submit.dto’; import JwtAuthenticationGuard from ‘../authentication/jwt-authentication.guard’; import RequestWithUser from ‘../authentication/requestWithUser.interface’;
@Controller(‘posts’) @UseInterceptors(ClassSerializerInterceptor) export default class PostsController { constructor(personal readonly postsService: PostsService) {}
@Publish() @UseGuards(JwtAuthenticationGuard) createPost(@Physique() postData: PostDto, @Req() request: RequestWithUser) { return this.postsService.createPost(postData, request.consumer.id); }
// … } |
The following step is to deal with the author_id property in our INSERT question.
posts.repository.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
import { Injectable } from ‘@nestjs/widespread’; import DatabaseService from ‘../database/database.service’; import PostModel from ‘./submit.mannequin’; import PostDto from ‘./submit.dto’;
@Injectable() class PostsRepository { constructor(personal readonly databaseService: DatabaseService) {}
async create(postData: PostDto, authorId: quantity) { const databaseResponse = await this.databaseService.runQuery( ` INSERT INTO posts ( title, post_content, creator_id ) VALUES ( $1, $2, $3 ) RETURNING * `, [postData.title, postData.content, authorId], ); return new PostModel(databaseResponse.rows[0]); }
// … }
export default PostsRepository; |
Because of the above, we insert the author_id into the database and might use it in our mannequin.
submit.mannequin.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
interface PostModelData { id: quantity; title: string; post_content: string; author_id: quantity; } class PostModel { id: quantity; title: string; content material: string; authorId: quantity; constructor(postData: PostModelData) { this.id = postData.id; this.title = postData.title; this.content material = postData.post_content; this.authorId = postData.author_id; } }
export default PostModel; |
Getting the posts of a specific consumer
To get the posts of a consumer with a specific id, we will use a question parameter.
posts.controller.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
import { ClassSerializerInterceptor, Controller, Get, Question, UseInterceptors, } from ‘@nestjs/widespread’; import { PostsService } from ‘./posts.service’; import GetPostsByAuthorQuery from ‘./getPostsByAuthorQuery’;
@Controller(‘posts’) @UseInterceptors(ClassSerializerInterceptor) export default class PostsController { constructor(personal readonly postsService: PostsService) {}
@Get() getPosts(@Question() { authorId }: GetPostsByAuthorQuery) { return this.postsService.getPosts(authorId); }
// … } |
Because of utilizing the GetPostsByAuthorQuery class, we will validate and rework the question parameter offered by the consumer.
getPostsByAuthorQuery.ts
|
import { Rework } from ‘class-transformer’; import { IsNumber, IsOptional, Min } from ‘class-validator’;
class GetPostsByAuthorQuery { @IsNumber() @Min(1) @IsOptional() @Rework(({ worth }) => Quantity(worth)) authorId?: quantity; }
export default GetPostsByAuthorQuery; |
Then, if the consumer calls the API with the /posts?authorId=10, for instance, we use a special technique from our repository.
posts.service.ts
|
import { Injectable } from ‘@nestjs/widespread’; import PostsRepository from ‘./posts.repository’;
@Injectable() export class PostsService { constructor(personal readonly postsRepository: PostsRepository) {}
getPosts(authorId?: quantity) { if (authorId) { return this.postsRepository.getByAuthorId(authorId); } return this.postsRepository.getAll(); }
// … } |
Creating a question that will get the posts written by a specific creator is a matter of a easy WHERE clause.
posts.repository.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
import { Injectable } from ‘@nestjs/widespread’; import DatabaseService from ‘../database/database.service’; import PostModel from ‘./submit.mannequin’;
@Injectable() class PostsRepository { constructor(personal readonly databaseService: DatabaseService) {}
async getByAuthorId(authorId: quantity) { 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 a number of tables
There is perhaps a case the place we wish to fetch rows from each the posts and customers desk and match them. To do this, we want a JOIN question.
|
SELECT posts.id AS id, posts.title AS title, posts.post_content AS post_content, posts.author_id as author_id, customers.id AS user_id, customers.e mail AS user_email, customers.identify AS user_name, customers.password AS user_password FROM posts JOIN customers ON posts.author_id = customers.id WHERE posts.id=$1 |
By utilizing the JOIN key phrase, we carry out the inside be a part of. It returns information with matching values in each tables. Since there aren’t any posts that don’t have the creator, it’s acceptable on this case.
Within the earlier article, we used an outer be a part of when fetching the consumer with the tackle as a result of the tackle is non-compulsory. Outer joins protect the rows that don’t have matching values.
Since we wish to fetch the submit, creator, and doable tackle, we have to use two JOIN statements.
posts.repository.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
import { Injectable, NotFoundException } from ‘@nestjs/widespread’; import DatabaseService from ‘../database/database.service’; import PostWithAuthorModel from ‘./postWithAuthor.mannequin’;
@Injectable() class PostsRepository { constructor(personal readonly databaseService: DatabaseService) {}
async getWithAuthor(postId: quantity) { 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, customers.id AS user_id, customers.e mail AS user_email, customers.identify AS user_name, customers.password AS user_password, addresses.id AS address_id, addresses.avenue AS address_street, addresses.metropolis AS address_city, addresses.nation AS address_country FROM posts JOIN customers ON posts.author_id = customers.id LEFT JOIN addresses ON customers.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 additionally create the PostWithAuthorModel class that extends PostModel.
postWithAuthor.mannequin.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
import PostModel, { PostModelData } from ‘./submit.mannequin’; import UserModel from ‘../customers/consumer.mannequin’;
interface PostWithAuthorModelData extends PostModelData null; address_country: string class PostWithAuthorModel extends PostModel { creator: UserModel; constructor(postData: PostWithAuthorModelData) { tremendous(postData); this.creator = new UserModel({ id: postData.user_id, e mail: postData.user_email, identify: postData.user_name, password: postData.user_password, ...postData, }); } }
export default PostWithAuthorModel; |
Abstract
On this article, we’ve carried out an instance of a one-to-many relationship utilizing uncooked SQL queries. When doing that, we additionally wrote an SQL question that makes use of multiple JOIN assertion. We’ve additionally discovered tips on how to write a migration that provides a brand new column with a NOT NULL constraint. There’s nonetheless extra to cowl in the case of implementing relationships in PostgreSQL, so keep tuned!
