Creating connections is just one of the essential elements of collaborating with SQL data sources. In this write-up, we proceed utilizing Kysely with NestJS as well as carry out many-to-one connections.
Have A Look At this database if you intend to see the complete code from this write-up.
Presenting the many-to-one connection
When applying the many-to-one connection, a row from the very first table is linked to numerous rows in the 2nd table. What’s crucial, a row from the 2nd table can link to simply one row from the very first table.
An instance is a post with a solitary writer, while the customer can be a writer of lots of write-ups. A method to apply it is to conserve the writer’s id in the.
write-ups table as a international secret An international secret is a worth that matches a column from a various table.
Whenever we develop an international type in our data source, PostgreSQL specifies the international vital restriction to make certain the uniformity of our information. Many thanks to that, it stops us from having an.
author_id worth that describes a customer that does not exist. We can not:
- develop a post as well as offer the.
author_id that indicates a customer that can not be discovered in the.
individuals table, - upgrade an existing write-up as well as transform the.
author_id to match a customer that does not exist, - erase a customer with an id utilized in the.
author_id column.- we would certainly need to erase the write-up initially or transform its writer
- additionally, we can make use of the.
WATERFALL choice to compel PostgreSQL to erase all write-ups the erased customer is a writer of
Specifying the many-to-one connection with Kysely
In among the previous components of this collection, we found out just how to create SQL movements when utilizing Kysely. This moment, we intend to include the.
author_id column that is not nullable Sadly, we may currently have some write-ups in our data source, as well as including a brand-new non-nullable column without a default worth would certainly create a mistake.
|
ALTER TABLE write-ups ADD COLUMN author_id int REFERRALS individuals( id) NOT VOID |
MISTAKE: column “author_id” of relationship “write-ups” has void worths
To address the above trouble, we can offer a default worth for the.
author_id column. To do that, we require to have a default customer. Allow’s include a seed documents to our.
movements directory site. Producing seed documents is a method to inhabit our data source with first information.
Including the seed documents
Initially, allow’s include the e-mail as well as password of the admin to the setting variables.
env
|
ADMIN_EMAIL = admin @ admin com ADMIN_PASSWORD = strongPassword |
Currently we can include the seed documents to our movements.
20230817223154_insert_admin. 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 { config } from ‘ dotenv’; import * as bcrypt from ‘ bcrypt’; import { Data Source } from ‘./ database/database’; import { ConfigService } from ‘ @nestjs/ config’; import { EnvironmentVariables } from ‘./ types/environmentVariables’; import { Movement } from ‘ kysely’; config(); const configService = brand-new ConfigService<(); export const up: Movement = async[‘up’] ( data source ) =>> { const e-mail = configService obtain(‘ ADMIN_EMAIL’); const password = configService obtain(‘ ADMIN_PASSWORD’); const hashedPassword = wait for bcrypt hash( password, 10) ; wait for data source insertInto(‘ individuals’) worths( { e-mail , password : hashedPassword, name : ‘ Admin’, } ) carry out();} ; export const down: Movement = async[‘up’] ( data source ) =>> { const e-mail = configService obtain(‘ ADMIN_EMAIL’); wait for data source deleteFrom(‘ individuals’) where( ’em trouble’,‘ =’, e-mail) carry out();} ; Producing the movement When composing the movement documents that includes the. |
author_id
column, we can carry out the complying with strategy: obtain the id of the admin, include the.
- author_id
- column as nullable, established the worth in the.
author_id - column for write-ups that do not have it, make the.
author_id - column non-nullable. 20230817230950_add_author_id. 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 48 import { |
Kysely , Movement} from ‘ kysely’ ; import { config } from ‘ dotenv’ ; import { ConfigService } from ‘ @nestjs/ config’ ; import { EnvironmentVariables } from ‘./ types/environmentVariables’ ; config ( ); const configService = brand-new ConfigService < (); export const up: Movement = async( data source[‘up’] ) = >> { const e-mail = configService obtain ( ‘ ADMIN_EMAIL’); const adminDatabaseResponse = wait for data source selectFrom( ‘ individuals’) where( ’em trouble’,‘ =’, e-mail) selectAll( ) executeTakeFirstOrThrow( ); const adminId = adminDatabaseResponse id ; wait for data source schema alterTable( ‘ write-ups’) addColumn( ‘ author_id’,‘ integer’,( column ) = >> { return column referrals( ‘ users.id’); } ) carry out( ); wait for data source updateTable( ‘ write-ups’) collection( { author_id: adminId , } ) carry out( ); wait for data source schema alterTable( ‘ write-ups’) alterColumn( ‘ author_id’,( column) = >> { return column setNotNull( ); } ) carry out( );} ; export async feature down ( data source: Kysely<): Guarantee< { wait for data source schema alterTable (‘ write-ups’) dropColumn(‘ author_id’) carry out();} Many-to-one vs one-to-one In the previous component of this collection, we have actually covered the one-to-one connection. When composing the movement, we have actually run the complying with inquiry: wait for data source schema |
|
‘ individuals’) addColumn(‘ address_id’,‘ integer’, ( column) =>> { return column one-of-a-kind () referrals( ‘ addresses.id’); } ) carry out() ; Including the one-of-a-kind restriction makes sure that a certain address comes from just one customer. However, when including the. |
schema
alterTable(
|
‘ write-ups’) addColumn(‘ author_id’,‘ integer’, ( column) =>> { return column referrals (‘ users.id’ ) ; } ) carry out() ; Many Thanks to the above strategy, numerous write-ups can share the exact same writer. Producing a post with the writer The following point we require to do is to customize the TypeScript meaning of our. |
Produced
}
from‘ kysely’;
|
export user interface ArticlesTable { id: Produced < ; title : string; article_content: string; author_id: number; } Allow’s additionally include the writer’s id to the write-up’s design. articles.model.ts 1 2 3 4 5 6 7 8 |
9
10
|
11 12 13 14 15 16 17 18 19 user interface ArticleModelData { id : number ; title |
: string ; article_content: string; author_id: number; } export course Post { id : number ; title : string ; web content: string; authorId: number; fitter( { id , title, article_content, writer _ id} : ArticleModelData ) { this id = id; this title = title; this web content = article_content; this authorId = author_id; } } We need to additionally deal with the. 4 5 6 |
7 8 9
10
|
11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 import { Data Source } from ‘./ database/database’ ; import { Post |
} from ‘./ article.model’ ; import { Injectable } from ‘ @nestjs/ typical’ ; import ArticleDto from ‘./ dto/article. dto’ ; @ Injectable () export course ArticlesRepository { fitter ( personal readonly data source : Data Source ) { } async develop( information : ArticleDto , authorId : number) { const databaseResponse = wait for this data source insertInto( ‘ write-ups’ ) worths( { title: information title, article_content: information web content , author_id: authorId, } ) returningAll() executeTakeFirstOrThrow() ; return brand-new Post( databaseResponse); } // … } When identifying that is the writer of the brand-new write-up, we do not anticipate the details to be offered straight with the body of the article demand. Rather, we obtain this details by deciphering the JWT token. If you intend to learn more concerning JWT symbols as well as verification, have a look at API with NestJS # 3. Confirming individuals with bcrypt, Key, JWT, as well as cookies articles.controller.ts 1 2 3 4 5 6 |
7
8 9
10
|
11 12 13 14 15 16 17 18 import { Controller , Blog Post , Body , UseGuards , |
Req } from‘ @nestjs/ typical’ ; import ArticleDto from ‘./ dto/article. dto’; import { ArticlesService } from ‘./ articles.service’; import JwtAuthenticationGuard from ‘./ authentication/jwt-authentication. guard’ ; import { RequestWithUser } from ‘./ authentication/requestWithUser. user interface’; @ Controller ( ‘ write-ups’ ) export course ArticlesController { fitter( personal readonly articlesService : ArticlesService ) { } @ Blog Post() @ UseGuards( JwtAuthenticationGuard ) develop( @ Body() information: ArticleDto , @ Req() demand: RequestWithUser) { return this articlesService develop ( information , demand customer id); } // …} Bring write-ups of a certain customer We can quiz the write-ups composed by a certain writer utilizing the. articles.repository.ts 1 2 3 4 5 |
6
7 8 9
10
|
11 12 13 14 15 16 17 18 19 20 import { Data Source } from ‘./ database/database’ ; import { Post |
} from ‘./ article.model’ ; import { Injectable } from ‘ @nestjs/ typical’ ; @ Injectable ( ) export course ArticlesRepository { fitter ( personal readonly data source : Data Source ) { } async getByAuthorId( authorId : number ) { const databaseResponse = wait for this data source selectFrom( ‘ write-ups’ ) where( ‘ author_id’,‘ =’, authorId) selectAll() carry out () ; return databaseResponse map(( articleData) = >> brand-new Post( articleData)); } // …} Allow’s make use of a various technique from our repository based upon whether the writer’s id is offered. articles.service.ts import { Injectable} from ‘ @nestjs/ typical’; import |
{
ArticlesRepository
|
} from ‘./ articles.repository’ ; @ Injectable ( ) export course ArticlesService { fitter ( personal readonly articlesRepository : ArticlesRepository ) { } getAll ( authorId? : number ) { if( authorId) { return this articlesRepository getByAuthorId( authorId ) ; } return this articlesRepository getAll( ); } // …} An excellent way to make use of the above attribute with our remainder API is with a inquiry criterion Allow’s specify a course that confirms if it is offered utilizing the proper layout. getArticlesByAuthorQuery.service.ts import { Transform } from ‘ class-transformer’ |
; import {
IsNumber
|
, IsOptional , Minutes } from‘ class-validator’ ; export course GetArticlesByAuthorQuery { @ IsNumber () @ Minutes ( 1 ) @ IsOptional( ) @ Transform(( { worth} ) = >> Number( worth)) authorId? : number ;} We can currently make use of the above course in our controller. articles.controller.ts import { Controller, Obtain , Inquiry } |
from
‘ @nestjs/ typical’
|
; import { ArticlesService } from ‘./ articles.service’ ; import { GetArticlesByAuthorQuery } from ‘./ getArticlesByAuthorQuery’ ; @ Controller ( ‘ write-ups’ ) export course ArticlesController { fitter( personal readonly articlesService : ArticlesService ) { } @ Obtain() getAll ( @ Inquiry () { authorId} : GetArticlesByAuthorQuery) { return this articlesService getAll ( authorId ) ; } // …} Incorporating the information from both tables It prevails to intend to incorporate the information from greater than one table. Allow’s develop a design including in-depth details concerning the write-up as well as its writer. articleWithAuthor.model.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 import { Post , ArticleModelData } from ‘./ article.model’ ; import |
{ Customer } from ‘./ users/user. design’ ; user interface ArticleWithAuthorModelData prolongs ArticleModelData void ; address_country: string export course ArticleWithAuthor prolongs Post { writer: Customer ; fitter ( articleData : ArticleWithAuthorModelData) { incredibly ( articleData); this writer = brand-new Customer( { id: articleData user_id , e-mail: articleData user_email , name: articleData user_name , password: articleData user_password , address_city: articleData address_city , address_country: articleData address_country , address_street: articleData address_street , address_id: articleData address_id , } ) ; } } We require to carry out a sign up with to bring the writer’s information along with the write-up. SELECT articles.id AS id, articles.title AS title, articles.article _ web content |
AS
|
article_content, articles.author _ id as author_id, users.id AS user_id, users.email AS user_email, individuals. name AS user_name, individuals. password AS user_password FROM write-ups SIGN UP WITH individuals ON articles.author _ id = users.id IN WHICH articles.id=$ 1 The default sort of sign up with is the internal sign up with It returns documents that have matching worths in both tables Given that every write-up needs a writer, it functions as anticipated. In the previous write-up , we executed the external sign up with when bring the customer along with the address considering that the address is optional. External signs up with maintain the rows that do not have matching worths in both tables. |
We need to carry out 2 signs up with to quiz the write-up, writer, as well as feasible address. articles.service.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 import { Data Source } from ‘./ database/database’ ; import { Injectable |
} from ‘ @nestjs/ typical’ ; import { ArticleWithAuthorModel } from ‘./ articleWithAuthor.model’ ; @ Injectable ( ) export course ArticlesRepository { fitter ( personal readonly data source : Data Source ) { } async getWithAuthor( id : number ) { const databaseResponse = wait for this data source selectFrom( ‘ write-ups’ ) where( ‘ articles.id’,‘ =’, id) innerJoin(‘ individuals’, ‘ users.id’, ‘ articles.author _ id’) leftJoin(‘ addresses’, ‘ addresses.id’, ‘ users.address _ id’) choose() executeTakeFirst () ; if[ Â Â Â Â Â Â Â Â ‘articles.id as id’, Â Â Â Â Â Â Â Â ‘articles.article_content as article_content’, Â Â Â Â Â Â Â Â ‘articles.title as title’, Â Â Â Â Â Â Â Â ‘articles.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.city as address_city’, Â Â Â Â Â Â Â Â ‘addresses.street as address_street’, Â Â Â Â Â Â Â Â ‘addresses.country as address_country’, Â Â Â Â Â Â ]( databaseResponse) { return brand-new ArticleWithAuthorModel ( databaseResponse ); } } // …} Recap In this write-up, we have actually discussed the one-to-many connection in SQL as well as applied it utilizing Kysely as well as NestJS. When doing that, we needed to make a SQL inquiry that utilized greater than one sign up with. We additionally found out just how to create a movement that includes a brand-new non-nullable column as well as just how to prevent mistakes when running it on an existing data source. There is still even more to cover concerning connections with PostgreSQL as well as Kysely, so remain tuned! Collection Navigating<< < < API with NestJS # 120. One-to-one connections with the Kysely inquiry building contractor
 RELATED ARTICLES Recent Comments |