How to create unique constraint with nullable field in TypeORM?

Question

How to create unique constraint with nullable field in TypeORM?

I used postgresql12, node v12.22.7.

Is it better to create a separate raw query?

Without a migration tool?

If I use a raw query, can I use it like the address below?

How to create composite UNIQUE constraint with nullable columns?

Here is my code example.

@Entity('users')
@Unique('username-unique',['username', 'deletedAt'])
export class User {
  @PrimaryGeneratedColumn()
  id: number

  @Column({ length: 100 })
  username: string

  @Column({ length: 100 })
  name: string

  // sha256
  @Exclude()
  @Column({ type: 'bytea', select: false})
  password: Buffer

  @Column({
    nullable: true,
    type: 'timestamp with time zone'
  })
  lastLoginAt: Date | null

  @OneToMany(() => RoleBindingEntity, (roleBinding) => roleBinding.user, { cascade: true })
  @JoinColumn()
  roleBinding: RoleBindingEntity[]

  @CreateDateColumn({
    type: 'timestamp with time zone',
    default: () => 'CURRENT_TIMESTAMP'
  })
  createdAt: Date

  @UpdateDateColumn({
    type: 'timestamp with time zone',
    default: () => 'CURRENT_TIMESTAMP'
  })
  updatedAt: Date

  @DeleteDateColumn({
    type: 'timestamp with time zone'
    nullable: true,
  })
  deletedAt: Date
}

Answer 1

You can set nullable field unique

@Column({ nullable: true, unique: true })
checking_data: string;

Migration that I executed for this

public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "file_store" ADD "checking_data" character varying`);
        await queryRunner.query(`ALTER TABLE "file_store" ADD CONSTRAINT "UQ_ab4f7e345fd08fb9611479b4cee" UNIQUE ("checking_data")`);
    }