DEV Community

Cover image for TypeORM unique indices with multiple fields
Jakub Andrzejewski
Jakub Andrzejewski

Posted on

8 1

TypeORM unique indices with multiple fields

I recently run into an issue that I wanted to create a database unique rule that will throw an error where there will be a duplicate record based on certain fields in the database table.

The complexity was that one of these fields was a relation to another table and I was not sure how to implement that.

To give a bit more context, let's take a look at this following example.

Let's assume that we have a school class that consists of students and in our class we want to avoid situations where there will be two students with the same name (two students can appear in our table but only if they are in a different classes).

Class (1) => Student (N)
Enter fullscreen mode Exit fullscreen mode

Code

Usually, if we want to achieve uniqueness in our table we can do so in TypeORM (and in regular SQL as well) by using the UNIQUE keyword. In TypeORM we would use the @Unique(param) decorators and as a param we would pass the name of the field that we would like to be unique (i.e. @Unique('name'))

In our case however, we have to use a different SQL keyword and TypeORM decorator.

Let's take a look at our Student entity:

import {
  Entity,
  Column,
  PrimaryGeneratedColumn,
  ManyToOne,
} from 'typeorm';
import { SchoolClass } from '../school-class.entity';

@Entity()
export class StudentEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => SchoolClass, (schoolClass) => schoolClass.students)
  schoolClass: SchoolClass;

  @Column()
  name: string;
}
Enter fullscreen mode Exit fullscreen mode

Our Table has three columns:

  1. id which is a unique ID for the certain record.
  2. schoolClass which is a ManyToOne relation with Class table
  3. name which is just a name of the student.

To solve our issue with unique names in the school class we will use the @Index decorator (that is then transformed to the INDEX in SQL).

import {
  Entity,
  Column,
  PrimaryGeneratedColumn,
  ManyToOne,
  Index,
} from 'typeorm';
import { SchoolClass } from '../school-class.entity';

@Entity()
@Index(['name', 'schoolClass'], { unique: true }) // Here
export class StudentEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => SchoolClass, (schoolClass) => schoolClass.students)
  schoolClass: SchoolClass;

  @Column()
  name: string;
}
Enter fullscreen mode Exit fullscreen mode

We will pass as an array the values we want to use (in our case name and schoolClass). We are also passing an options object with the property unique set to true. This will assure that inserting a new record in our database with the same name in the school class will result in an DUPLICATE_ENTRY error.

Summary

Nicely done! You have just learned how to create a unique rules for inserting new data into the database. Hopefully, it will help you solve the similar issue I had recently :)

Warp.dev image

The best coding agent. Backed by benchmarks.

Warp outperforms every other coding agent on the market, and gives you full control over which model you use. Get started now for free, or upgrade and unlock 2.5x AI credits on Warp's paid plans.

Download Warp

Top comments (4)

Collapse
 
rrakso profile image
rrakso • Edited

I like to do it this way:

@Entity('my_data')
@Unique(
  SQL_CONSTRAINTS.UNIQUE_NAME_AND_DATE, // enum as contraint name
  (entity: Entity) => [ entity.name, entity.date ]
)
export class Entity extends Base {
  @Column()
  name: string;

  @Column()
  date: string;
Enter fullscreen mode Exit fullscreen mode
Collapse
 
talenttinaapi profile image
talent

Forward and upward,keep up the good work

Collapse
 
saiteja13427 profile image
Sai Teja

We can actually use @unique['name', 'schoolClass'] itself. Typeorm supports that.

Collapse
 
dinerdas profile image
Diner Das • Edited

Thanks for making your first comment Sai!

Gen AI apps are built with MongoDB Atlas

Gen AI apps are built with MongoDB Atlas

MongoDB Atlas is the developer-friendly database for building, scaling, and running gen AI & LLM apps—no separate vector DB needed. Enjoy native vector search, 115+ regions, and flexible document modeling. Build AI faster, all in one place.

Start Free

👋 Kindness is contagious

Explore this insightful piece, celebrated by the caring DEV Community. Programmers from all walks of life are invited to contribute and expand our shared wisdom.

A simple "thank you" can make someone’s day—leave your kudos in the comments below!

On DEV, spreading knowledge paves the way and fortifies our camaraderie. Found this helpful? A brief note of appreciation to the author truly matters.

Let’s Go!