title: Sequelize use upsert function with custom where clause
body: <p>I use <code>Sequelize</code> to communicate with my <code>postgres</code> database. I want to use the <code>upsert</code> functionality, but with custom where clause. For example I have this model for volunteers location</p>
  
  <pre><code>module.exports = function (sequelize, DataTypes) {
      var volunteer_location = sequelize.define('volunteer_location', {
          id: {
              type: Sequelize.INTEGER,
              allowNull: false,
              autoIncrement: true,
              primaryKey: true
          },
          deviceId: {
              allowNull: false,
              type: Sequelize.STRING
          },
          latitude: {
              allowNull: false,
              type: Sequelize.DECIMAL(18, 14)
          },
          longitude: {
              allowNull: false,
              type: Sequelize.DECIMAL(18, 14)
          },
          city: {
              allowNull: false,
              type: Sequelize.STRING(90)
          },
          state: {
              type: Sequelize.STRING(90)
          },
          postalCode: {
              type: Sequelize.STRING(16)
          },
          country: {
              allowNull: false,
              type: Sequelize.STRING(70)
          }
      }, {
          instanceMethods: {
              insertOrUpdate: function (requestBody, res) {
                  volunteer_location.upsert(requestBody).then(function () {
                      res.sendStatus(200);
                  });
              }
          }
      });
      return volunteer_location;
  };
  </code></pre>
  
  <p>In the <code>insertOrUpdate</code> method I try to use the <code>upsert</code> by giving a json like</p>
  
  <pre><code>location = {
      deviceId: req.body.deviceId,
      latitude: req.body.latitude,
      longitude: req.body.longitude,
      city: req.body.city,
      state: req.body.state,
      postalCode: req.body.postalCode,
      country: req.body.country,
      volunteer: req.decoded.id
  };
  </code></pre>
  
  <p>where volunteer is a <code>foreign key</code> from table <code>users</code>.
  Now the executing says:</p>
  
  <blockquote>
    <p>CREATE OR REPLACE FUNCTION pg_temp.sequelize_upsert() RETURNS integer
    AS $func$ BEGIN INSERT INTO "volunteer_locations" <em>some values</em> RETURN
    1; EXCEPTION WHEN unique_violation THEN UPDATE "volunteer_locations"
    SET <em>some values</em> <strong>WHERE (("id" IS NULL AND "volunteer" = 1))</strong>;
    RETURN 2; END; $func$ LANGUAGE plpgsql; SELECT * FROM
    pg_temp.sequelize_upsert();</p>
  </blockquote>
  
  <p><code>id</code> and <code>volunteer</code> is a set of primary keys. I want to change the <code>WHERE</code> clause on the <code>UPDATE</code> by checking only the <code>volunteer</code> value, because I don't know the value of the <code>id</code>.</p>
  
  <p>Is this possible? Or I have to use </p>
  
  <pre><code>findOne(...).then(function () {
        //update
  });
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>I set <code>volunteer</code> with the attribute <code>unique: true</code> and it replaced the where clause with <code>WHERE (("id" IS NULL AND "volunteer" = 1) OR "volunteer" = 1);</code>, which means that the update works. But I don't know if this is very efficient. If anyone knows a better solution please let me know.</p>