Sequelize Upsert() return value

[hfar]

Hello,

I need to get the id for the inserted/updated record when using .upsert() in sequelize.

right now .upsert() returns a boolean indicating whether the row was created or updated.

thanks for your help.

return db.VenueAddress.upsert({
            ...
        }).then(function(test){
            //test returned here as true or false how can i get the inserted id here so i can insert data in other tables using this new id?
        });

[janmeier]

This is only supported by postgres, so to keep the API consistent across dialects this is not possible

[FredKSchott]

👍 for adding this feature, there's plenty of precedence for dialect-specific features. This seems exactly like [options.returning=false] on Model.update()

[GabeIsman]

I also just ran into this problem. I wanted to upsert a list of records, and then do something with them (associate them with another model). That seems to be impossible the way the API is defined now. Is there a recommended workflow for this use case? I'm trying to avoid just recreating all associated records on update, which seems to be the path of least resistance given the API design (particularly that the records must already exist in the DB before they can be associated).

[coderholic]

+1 for adding options.returning on upsert.

[rfink]

+1, would be tremendously useful

[FredKSchott]

So I did some digging into this, and it won't be easy. I wanted us to be able to take advantage of RETURNING * in postgres, but the problem is that the return value of insert & update are both already being used to detect which one occurred. (link to code)

At this point I think the best way forward is to append a simple SELECT * query to the end of the upsert transaction. We'll need to append the upsert response (1 or 2) in the result as well, which is where I got stuck (got Cannot read property 'sequelize_upsert' of undefined error when appending ; SELECT *, pg_temp.sequelize_upsert() FROM users WHERE ...)

I don't have any more time to work on this for a while, but if anyone wants to take over where I left off feel free!

[kylehotchkiss]

+1; this would be useful for me as well.

I thought there was an update() function with { upsert: true } as an option but it's totally possible i'm getting confused with Mongoose

[janmeier]

@kylehotchkiss We do have ON DUPLICATE KEY UPDATE support for bulkCreate http://docs.sequelizejs.com/en/latest/api/model/#bulkcreaterecords-options-promisearrayinstance

[angelxmoreno]

I agree it would be nice to have this. Aren't we already implementing dialect specific solutions to do upsert() ? That is how I am interpreting the docs:

Implementation details:

MySQL - Implemented as a single query INSERT values ON DUPLICATE KEY UPDATE values
PostgreSQL - Implemented as a temporary function with exception handling: INSERT EXCEPTION WHEN unique_constraint UPDATE
SQLite - Implemented as two queries INSERT; UPDATE. This means that the update is executed regardless of whether the row already existed or not

At any rate, below is my workaround with a mild performance hit:

classMethods: {
    upsertWithReturn: function (options) {
        return this.findOrCreate(options).spread(function (row, created) {
            if (created) {
                return [row, created];
            } else {
                return row.updateAttributes(options.defaults).then(function (updated) {
                    return [updated, created];
                });
            }
        });
    },
}

I foresee only having race condition issues. However, I am under the impression that modelInstance.updateAttributes() will not do anything in the situation where a second call would have already updated the row somewhere between the first insert and the first update.

Thoughts?

[mickhansen]

@angelxmoreno updateAttributes is only idempotent if the instance is currently the source of truth, that would be false in the case where a parallel code path updates the database entry.

Your code is open to race conditions - But our code isn't 100% perfectly race condition free since technically a parallel connection could change the entry while we run our two sequential queries (but less race prone since there's less roundtrips).

[angelxmoreno]

@mickhansen 👍

[felixfbecker]

Please don't +1 issues. It clutters the thread without adding value to the discussion and spams maintainers with notifications. Use GitHub reactions to upvote features.

[trjstewart]

Does anyone have any updates on this? Would be very nice to have this functionality.

[Shahor]

What do you mean this is only supported by Postgres ?

Mysql version : 5.6.35

[janmeier]

@Shahor That only applies to the case where the row doesn't already exist though? We can't get the id of an existing row like we can with RETURNING * in postgres

[Shahor]

@janmeier You have a point on this.

That being said, an ORM should provide abstraction to the developer and make it easy to interact with a database.
In that context, shouldn't sequelize perform the necessary queries in order to return the useful data to the developer instead of just returning the type of query that has been applied (the doc doesn't state which value of the boolean corresponds to update/create by the way)?

A workaround to this problem could be something like this comment but shouldn't it be part of the ORM to begin with?

[felixfbecker]

@Shahor please read #3354 (comment).

[chrisnew]

@Shahor @janmeier where id is the primary key INSERT INTO … ON DUPLICATE KEY UPDATE … id = LAST_INSERT_ID(id) allows you to immediately ask for the id on the same connection with SELECT LAST_INSERT_ID();

Implementing that trick would solve the issue with MySQL and getting the primary key in both inserted and updated case.

[ghost]

Trying to get inserted record after upsert, but no success even with Sequelize v5 and PostgreSQL.
See https://stackoverflow.com/questions/53179703/sequelize-upsert-get-new-id-from-inserted-record

[anshulg93]

Use create() instead of upsert(). It will return you created object.

[mi-mazouz]

any update on this?

[gabegorelick]

Upsert was recently reimplemented in #12301. Are people still having issues?

[sushantdhiman]

@gabegorelick It is still a v6-beta feature & most users are using v5 (latest) release

[cirosantilli]

SQLite also added RETURNING in 2021 BTW: https://www.sqlite.org/lang_returning.html so now this could be solved nicely for it.