How update entity if exist or create if not exist entity

Question

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?

Answer 1

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 :)