Sequelize upsert method - Tutorial with example JS code
Learn how to perform a single row insert/ update using Sequelize upsert method
Posted on January 11, 2022
Sequelize v6 comes with a native Model.upsert() method that you can use to perform an INSERT ... ON CONFLICT DO UPDATE SQL statement.
The upsert() method accepts an object of data with the property keys serving as the column names and the property values as the column values.
The method would then return an array of two elements:
- The
instancetheModelwhere you call the method, returning the new/updated row - The
createdboolean value, which tells you whether the record was created (true) or updated (false)
The syntax of the upsert() method is as shown below
const [instance, created] = await MyModel.upsert({
// your new row data here
});
Or if you prefer promise-based syntax:
MyModel.upsert({
// your new row data here
}).then(([instance, created]) => {
// what to do on returned values
});
Let’s see an example of the upsert method in action next.
Suppose you have a table named Cities with the following data:
+----+------------+------------+
| id | cityName | population |
+----+------------+------------+
| 1 | York | 467000 |
| 2 | Bristol | 467000 |
| 3 | Manchester | 210000 |
| 4 | London | 8000000 |
+----+------------+------------+
Using the upsert() method, you can perform an insert or update to a specific row as shown below:
const [city, created] = await City.upsert({
cityName: "York",
population: 20000,
});
console.log(created); // true
console.log(city); // City object
In MySQL, the generated SQL statement would be as follows:
INSERT INTO `Cities` (`cityName`, `population`)
VALUES (?, ?)
ON DUPLICATE KEY
UPDATE
`cityName` = VALUES (`cityName`),
`population` = VALUES (`population`);
The model name City is transformed into Cities because Sequelize automatically converts the model name into its plural form.
For PostgreSQL and SQLite, the SQL syntax will use ON CONFLICT DO UPDATE as shown below:
INSERT INTO "Cities" ("id", "cityName", "population")
VALUES ($1, $2, $3)
ON CONFLICT ("id")
DO UPDATE SET
"id" = EXCLUDED."id",
"cityName" = EXCLUDED."cityName",
"population" = EXCLUDED."population"
RETURNING
"id", "cityName", "population";
Without specifying a column with a unique constraint in your upsert() method, the method will do a normal INSERT statement.
The data in the table becomes the following:
+----+------------+------------+
| id | cityName | population |
+----+------------+------------+
| 1 | York | 467000 |
| 2 | Bristol | 467000 |
| 3 | Manchester | 210000 |
| 4 | London | 8000000 |
| 5 | York | 20000 |
+----+------------+------------+
To make the upsert() method perform an UPDATE statement, you need to specify the primary key in the method argument:
const [city, created] = await City.upsert({
id: 5,
cityName: "Glasgow",
population: 99999,
});
With the id key specified in the example above, an UPDATE statement will be executed on the corresponding table row:
+----+------------+------------+
| id | cityName | population |
+----+------------+------------+
| 1 | York | 467000 |
| 2 | Bristol | 467000 |
| 3 | Manchester | 210000 |
| 4 | London | 8000000 |
| 5 | Glasgow | 99999 |
+----+------------+------------+
And that’s how the Sequelize upsert() method works.
This method allows you to insert/update a specific row without having to write a raw query.
Please note that the upsert() method is only available in Sequelize version 6. If you’re using Sequelize < 6 then you need to update your sequelize module dependency first.
