Proper Sequelize flow to avoid duplicate rows?-CodePudding

I am using Sequelize in my node js server. I am ending up with validation errors because my code tries to write the record twice instead of creating it once and then updating it since it's already in DB (Postgresql).

This is the flow I use when the request runs:

const latitude = req.body.latitude;
var metrics = await models.user_car_metrics.findOne({ where: { user_id: userId, car_id: carId } }) 

if (metrics) {
    metrics.latitude = latitude;
    .....
} else {
    metrics = models.user_car_metrics.build({
        user_id: userId,
        car_id: carId,
        latitude: latitude
        ....
    });
}

var savedMetrics = await metrics();
return res.status(201).json(savedMetrics);

At times, if the client calls the endpoint very fast twice or more the endpoint above tries to save two new rows in user_car_metrics, with the same user_id and car_id, both FK on tables user and car.

I have a constraint:

ALTER TABLE user_car_metrics DROP CONSTRAINT IF EXISTS user_id_car_id_unique, ADD CONSTRAINT user_id_car_id_unique UNIQUE (car_id, user_id);

Point is, there can only be one entry for a given user_id and car_id pair.

Because of that, I started seeing validation issues and after looking into it and adding logs I realize the code above adds duplicates in the table (without the constraint). If the constraint is there, I get validation errors when the code above tries to insert the duplicate record.

Question is, how do I avoid this problem? How do I structure the code so that it won't try to create duplicate records. Is there a way to serialize this?

CodePudding user response：

If you have a unique constraint then you can use upsert to either insert or update the record depending on whether you have a record with the same primary key value or column values that are in the unique constraint.

await models.user_car_metrics.upsert({
        user_id: userId,
        car_id: carId,
        latitude: latitude
        ....
    })

See upsert

PostgreSQL - Implemented with ON CONFLICT DO UPDATE. If update data contains PK field, then PK is selected as the default conflict key. Otherwise, first unique constraint/index will be selected, which can satisfy conflict key requirements.