Here are my entities.

// user

@PrimaryGeneratedColumn()
public id: number;

@Column({ type: 'varchar', nullable: false })
public email: string;

@Column({ type: 'varchar', nullable: false })
public password: string;

@OneToOne(() => Token, (token: Token) => token.user)
public token: Token;

// token

@PrimaryGeneratedColumn()
public id: number;

@Column({ type: 'varchar', nullable: false })
public uuid: string;

@Column({ type: 'integer', nullable: false })
public userId: number;

@OneToOne(() => User, (user: User) => user.hash, { cascade: ['insert', 'remove'] })
@JoinColumn({ name: 'userId' })
public user: User;

This is how I save current in my database.

private async savePayload(tokenDto: CreateTokenDto) {
    const token = this.tokenRepository.create(tokenDto);
    return await this.tokenRepository.save(token);
}

When I first save my token to the database, all of it is saved.

When I save a second time, I get an error.

ER_DUP_ENTRY: Duplicate entry '36' for key 'REL_d417e5d35f2434afc4bd48cb4d'

I read in the documentation about the save method. But why I get the error, I can not understand. I expect the record to be updated. Why are my token details not updated?

I understand how to do this using sql.

INSERT INTO "Tokens" (UUID, USERID)
    VALUES ('d93ab036-768c-420a-98d6-2f80c79e6ae7', 36)
        ON CONFLICT (USERID) 
            DO UPDATE SET UUID = 'd93ab036-768c-420a-98d6-2f80c79e6ae7', 
                USERID = 36'

After some experiments, I noticed that when I specify the token id, then saving or updating is successful.

private async savePayload(tokenDto: CreateTokenDto) {
    const a = {
        id: 15,
        uuid: '343443443444444444444444',
        userId: 36,
    };
    const token = this.tokenRepository.create(a);
    return await this.tokenRepository.save(token);
}

But if I didn’t indicate the id of the token, I get an error.

private async savePayload(tokenDto: CreateTokenDto) {
    const a = {
        // id: 15,
        uuid: '343443443444444444444444',
        userId: 36,
    };
    const token = this.tokenRepository.create(a);
    return await this.tokenRepository.save(token);
}

ER_DUP_ENTRY: Duplicate entry '36' for key 'REL_d417e5d35f2434afc4bd48cb4d'

I searched and found some examples.

1. TypeORM upsert - create if not exist

They say that the value must be a primary key or a unique value. But my userId field is an index, and is also unique.

What options can be, why is my token not updated?

Here are my entities.

// user

@PrimaryGeneratedColumn()
public id: number;

@Column({ type: 'varchar', nullable: false })
public email: string;

@Column({ type: 'varchar', nullable: false })
public password: string;

@OneToOne(() => Token, (token: Token) => token.user)
public token: Token;

// token

@PrimaryGeneratedColumn()
public id: number;

@Column({ type: 'varchar', nullable: false })
public uuid: string;

@Column({ type: 'integer', nullable: false })
public userId: number;

@OneToOne(() => User, (user: User) => user.hash, { cascade: ['insert', 'remove'] })
@JoinColumn({ name: 'userId' })
public user: User;

This is how I save current in my database.

private async savePayload(tokenDto: CreateTokenDto) {
    const token = this.tokenRepository.create(tokenDto);
    return await this.tokenRepository.save(token);
}

When I first save my token to the database, all of it is saved.

When I save a second time, I get an error.

ER_DUP_ENTRY: Duplicate entry '36' for key 'REL_d417e5d35f2434afc4bd48cb4d'

I read in the documentation about the save method. But why I get the error, I can not understand. I expect the record to be updated. Why are my token details not updated?

I understand how to do this using sql.

INSERT INTO "Tokens" (UUID, USERID)
    VALUES ('d93ab036-768c-420a-98d6-2f80c79e6ae7', 36)
        ON CONFLICT (USERID) 
            DO UPDATE SET UUID = 'd93ab036-768c-420a-98d6-2f80c79e6ae7', 
                USERID = 36'

After some experiments, I noticed that when I specify the token id, then saving or updating is successful.

private async savePayload(tokenDto: CreateTokenDto) {
    const a = {
        id: 15,
        uuid: '343443443444444444444444',
        userId: 36,
    };
    const token = this.tokenRepository.create(a);
    return await this.tokenRepository.save(token);
}

But if I didn’t indicate the id of the token, I get an error.

private async savePayload(tokenDto: CreateTokenDto) {
    const a = {
        // id: 15,
        uuid: '343443443444444444444444',
        userId: 36,
    };
    const token = this.tokenRepository.create(a);
    return await this.tokenRepository.save(token);
}

ER_DUP_ENTRY: Duplicate entry '36' for key 'REL_d417e5d35f2434afc4bd48cb4d'

I searched and found some examples.

1. TypeORM upsert - create if not exist

2. https://github.com/typeorm/typeorm/issues/3342

They say that the value must be a primary key or a unique value. But my userId field is an index, and is also unique.

What options can be, why is my token not updated?

• javascript
• sql
• nestjs
• typeorm

3 Answers 3

The whole problem is a result of the behaviour of the Repository<T>.save() function.

According to the docs, the save() function has this behaviour:

Saves all given entities in the database. If entities do not exist in the database then inserts, otherwise updates.

But, without the id field inside the entity (without the PrimaryKey), the save() method presumes that the entity doesn't exist in the database and proceeds to create a new one instead of updating an existing. So that's why it works when you define the id field in your entity.

Taking this into account, it seems that the save() method is inadequate for your case. You'll need to write a custom query with TypeORM's query builder. This custom query will be pretty close to the one you wrote in your question, using raw SQL.

This is how you could write it (DISCLAIMER: I have not tested the code at all!):

const values = {
    uuid: '343443443444444444444444',
    userId: 36
}

await connection.createQueryBuilder()
            .insert()
            .into(Tokens)
            .values(post2)
            .onConflict(`("userId") DO UPDATE SET UUID = :uuid`)
            .setParameter("title", values.uuid)
            .execute();

Maybe, another option for you, would be to make the userId field the Primary Key of your table. This would solve the upsert problem with the save() function. As you described, the userId field is an index and it is also unique. So, you can easily make it a primary field.

This could be done by modifying your entity, removing the @PrimaryGeneratedId and making the userId a @PrimaryColumn:

@Column({ type: 'varchar', nullable: false })
public uuid: string;

@PrimaryColumn()
public userId: number;

@OneToOne(() => User, (user: User) => user.hash, { cascade: ['insert', 'remove'] })
@JoinColumn({ name: 'userId' })
public user: User;

Hope that helps :)

It isn't saying that there is a duplicate entry in the table already, it is saying that there is already one entry in there with that value for the primary key and it is refusing to insert a second for that reason.

You will find one matching row, and that your code at the point of the error is trying to insert a second.

Dealing with duplicates on insert:

If you try INSERT a duplicate values for a primary key (or a unique index) you will always get that error. There are a couple of ways around it: check before you insert and either do an update (if something might have changed) or just don't do anything.

You can use .save for both update and insert or check if exists then .update else .save exemple:

async CreateNewRole(data: any): Promise<Role | any> {
    try {
      const entity = await this.roleRepository.create(data);
      const role = await this.roleRepository.save(entity);
      this.trackingService.create(data.user);
      return {
        success: true,
        role,
      };
    } catch (e) {
      //  code == 23505 means duplication key
      if (parseInt(e.code) === 23505) {
        console.log('error : ', e.detail);
        return {
          success: false,
          message: ROLE_ERROR_MESSAGES.ROLE_IS_FOUND,
        };
      } else {
        return {
          success: false,
        };
      }
    }
  }

  async UpdateRole(data: any, id: number): Promise<Role | any> {
    try {
      await this.roleRepository.update(id, { ...data.payload });
      this.trackingService.create(data.user);
      // todo this need to be refactored !!
      // return back the updated entity
      const role = await this.roleRepository.find({ id });
      console.log('role updated ', role);
      return {
        role,
        success: true,
      };
    } catch (e) {
      if (parseInt(e.code) === 23505) {
        console.log('error : ', e.detail);
        return {
          success: false,
          message: ROLE_ERROR_MESSAGES.ROLE_IS_FOUND,
        };
      } else {
        return {
          success: false,
        };
      }
    }
  }