Defining Custom Many-to-many Relationship in NestJS TypeORM.
Tags: nestjs, typeorm, typescript, mysql
Published: 2023-12-31 03:23:30

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**
```sql
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**
```sql
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**
```sql
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**
```typescript
// 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**
```typescript
// 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**
```typescript
// 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!!!