Node.js – Difference between findOrCreate() and upsert() in sequelize


I was reading over the doc and it mentioned two ways of doing an upsert: findOrCreate() or upsert(). I read the descriptions, but I'm still not clear what the difference is.

The upsert() goes on to say:

Note that the unique index must be defined in your sequelize model and not just in the table. Otherwise you may experience a unique constraint violation, because sequelize fails to identify the row that should be updated.

Does this mean upsert() explicitly requires I define an id UNIQUE primary field in my model? ie:

var Something = sequelize.define("Something", { id: { 
                    type: DataTypes.INTEGER, 
                    primaryKey: true, 
                    unique: true, 
                    allowNull: false}});

If so, why does findOrCreate() not have that restriction?

Can someone explain the use cases for when findOrCreate() and upsert() should be used, and why upsert() has the unique constraint requirement when findOrCreate() doesn't seem to need it?

Best Solution

.findOrCreate will query the database with the parameters you provided, and if there is no match, it will perform an insert operation using those same parameters. Whereas the point of an .upsert, is to either update or insert a record. In the case of an update operation, logic dictates that you look for a record based on a unique key, and only once you find this unique record, update its values based on the parameters provided. If you aren't able to find a unique record, only then would you perform an insert.

I hope that makes sense.