If you are working using NestJS and using TypeORM for the database ORM, you might encounter some issues when trying to create table relationship.
I understand you, the documentation does not seem clear for some people and ChatGPT can't event help us.
This post will help you to create many-to-many relationship on custom pivot table using TypeORM. There are some decorators on TypeORM.
@OneToMany()
@ManyToOne()
@ManyToMany()
@OneToOne()
Here, we will create many to many entities, we will use OneToMany and ManyToOne decorators instead of ManyToMany. We can use ManyToMany if we want our table is created and synchronized automatically and don't have custom field, but here I create my own DDL migration script to create my tables because I want to have custom field on my pivot table.
I recommend you to not sync the db automatically and create your own db migration.
Before we see the table and pivot table, I will share the DDL I use to create the table.
table 1
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`email` varchar(255) NOT NULL,
`fullname` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`firebase_uid` varchar(255) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `IDX_user_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
table 2
CREATE TABLE `social_account` (
`id` int NOT NULL AUTO_INCREMENT,
`source` varchar(255) NOT NULL,
`username` varchar(255) NOT NULL,
`fullname` varchar(255) DEFAULT NULL,
`image` text,
`description` varchar(255) DEFAULT NULL,
`external_id` varchar(255) NOT NULL,
`meta` text,
`last_crawl_at` timestamp NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `IDX_social_account_username` (`username`),
KEY `IDX_social_account_external_id` (`external_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
pivot table
CREATE TABLE `subscriptions` (
`user_id` int NOT NULL,
`social_account_id` int NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`,`social_account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
We can see, in pivot table we are having additional fields for created_at and updated_at.
So basically the relation is as follows,
user -> subscription -> social_account
A user can subscribe to many social account, while a social account can be subscribed by many users.
From here, now we can go to NestJS entity to define the relationship.
User
// user.entity.ts
import {
Entity,
PrimaryGeneratedColumn,
Column,
OneToMany,
} from 'typeorm';
import { SocialAccount } from '../social-accounts/social-account.entity';
import { Subscription } from '../subscriptions/subscription.entity';
@Entity()
export class User {
@PrimaryGeneratedColumn()
id: number;
@Column()
email: string;
@Column({ nullable: true })
fullname: string;
@Column({ nullable: true })
password: string;
@Column({ nullable: true })
firebase_uid: string;
@Column({
type: 'timestamp',
default: () => 'CURRENT_TIMESTAMP',
nullable: false,
})
public created_at: Date;
@Column({
type: 'timestamp',
default: () => 'CURRENT_TIMESTAMP',
onUpdate: 'CURRENT_TIMESTAMP',
})
public updated_at: Date;
@OneToMany(() => Subscription, (subs) => subs.user)
public socialAccounts: SocialAccount[];
}
social account
// social-account.entity.ts
import {
Entity,
PrimaryGeneratedColumn,
Column,
OneToMany
} from 'typeorm';
import { User } from '../user-managements/user.entity';
import { Subscription } from '../subscriptions/subscription.entity';
@Entity()
export class SocialAccount {
@PrimaryGeneratedColumn()
id: number;
@Column()
source: string;
@Column()
username: string;
@Column({ nullable: true })
fullname: string;
@Column({ nullable: true, type: 'text' })
image: string;
@Column({ nullable: true })
description: string;
@Column()
external_id: string;
@Column({ nullable: true, type: 'text' })
meta: string;
@Column({ type: 'timestamp', nullable: false })
last_crawl_at: Date;
@Column({
type: 'timestamp',
default: () => 'CURRENT_TIMESTAMP',
nullable: false,
})
public created_at: Date;
@Column({
type: 'timestamp',
default: () => 'CURRENT_TIMESTAMP',
onUpdate: 'CURRENT_TIMESTAMP',
})
public updated_at: Date;
@OneToMany(() => Subscription, (subs) => subs.socialAccount)
public user: User[];
}
subscription
// subscription.entity.ts
import {
Entity,
PrimaryColumn,
Column,
ManyToOne,
JoinColumn,
} from 'typeorm';
import { User } from '../user-managements/user.entity';
import { SocialAccount } from '../social-accounts/social-account.entity';
@Entity({ name: 'subscriptions' })
export class Subscription {
@PrimaryColumn()
user_id: number;
@PrimaryColumn()
social_account_id: number;
@Column({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP' })
public created_at: Date;
@Column({
type: 'timestamp',
default: () => 'CURRENT_TIMESTAMP',
onUpdate: 'CURRENT_TIMESTAMP',
})
public updated_at: Date;
@ManyToOne(() => User, (user) => user.socialAccounts)
@JoinColumn({ name: 'user_id' })
user: User;
@ManyToOne(() => SocialAccount, (socialAccount) => socialAccount.user)
@JoinColumn({ name: 'social_account_id' })
socialAccount: SocialAccount;
}
We can see we are using OneToMany in table 1 and table 2, while there are two ManyToOne in pivot table.
You can copy the concept as above if you have a problem defining many-to-many relationship with custom table.
See you!!!
Top comments (1)
This solution not that perfect unfortunatly. The reason is when you want to retrive data for example from users the users class will show you there is a socialAccounts property with SocialAccount[] type, but the type actually Subscription[]. Its very missleading.
In my project I leave the type as the connection describes, so the previous example would be Subscription[] type, because this is the type what I will get not the SocialAccount[]