Sequelize findOrCreate() method explained

The Sequelize findOrCreate() method is a query method that tries to find an entry in your table or create a new entry when nothing is found.

The method requires a WHERE condition to be specified, and when no existing entry is found, it will INSERT a new row to the table using the parameters you specified when you call the method

The findOrCreate() method syntax is as follows:

const [row, created] = await findOrCreate({
  where: {},
  defaults: {},
});

The method returns an array of two items:

• The row variable is an object containing the data retrieved or newly created by the method.
• The created variable is a boolean variable to inform whether the row is newly created (true) or already existed before (false)

The where option is required for the method, while the defaults option can be omitted.

Also, the method retrieves only a single row, so if multiple rows are found, then only the first row will be returned.

Let’s see an example of running the findOrCreate() method next.

Sequelize findOrCreate() method in action

Suppose you have a table named Users with the following data:

+----+-----------+------------+
| id | firstName | lastName   |
+----+-----------+------------+
|  1 | Nathan    | Sebhastian |
+----+-----------+------------+

Now, you want to find if there’s a row with John as the firstName field value. If there’s none, you want to create one.

First, initiate a new connection to your database from Sequelize and create a Sequelize Model for the Users table:

const { Sequelize } = require("sequelize");

const sequelize = new Sequelize("test_db", "root", "root", {
  host: "localhost",
  dialect: "mysql",
});

const Users = sequelize.define("Users", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
},
{
  timestamps: false,
});

Once the model Users is created, you can use the findOrCreate() method with the following options:

const [user, created] = await Users.findOrCreate({
  where: { firstName: "John" },
});

console.log(user.firstName); // John
console.log(user.lastName); // undefined
console.log(created); // true

Or if you prefer Promise based syntax:

Users.findOrCreate({
  where: { firstName: "John" },
}).then(([user, created]) => {
  console.log(user.firstName); // John
  console.log(user.lastName); // undefined
  console.log(created); // true
});

Because no row is returned based on the where option you provide for the method, a new row is created using that where option.

The Users table will now have the following data:

+----+-----------+------------+
| id | firstName | lastName   |
+----+-----------+------------+
|  1 | Nathan    | Sebhastian |
|  2 | John      | NULL       |
+----+-----------+------------+

If you specify the lastName parameter on the where option, it will be inserted as well.

The following JavaScript code:

const [user, created] = await Users.findOrCreate({
  where: { firstName: "John", lastName: "Doe" },
});

Will insert a third row to the table as shown below:

+----+-----------+------------+
| id | firstName | lastName   |
+----+-----------+------------+
|  1 | Nathan    | Sebhastian |
|  2 | John      | NULL       |
|  3 | John      | Doe        |
+----+-----------+------------+

Let’s learn about the defaults option next.

Sequelize findOrCreate() method defaults option

The defaults option in the findOrCreate() method is used as default values to INSERT into your table when there’s no existing row is found.

Any values you put in the defaults option will override the values you put in the where option.

For example, suppose you run a findOrCreate() method for the Users table with the following options:

const [user, created] = await Users.findOrCreate({
  where: { firstName: "Jane" },
  defaults: { firstName: "Jack" },
});

Because we don’t have a row with Jane as the firstName value, a new row will be inserted into the table.

But instead of inserting Jane, Sequelize will insert Jack as the firstName value of the new row.

The data in the Users table will be as shown below:

+----+-----------+------------+
| id | firstName | lastName   |
+----+-----------+------------+
|  1 | Nathan    | Sebhastian |
|  2 | John      | NULL       |
|  3 | John      | Doe        |
|  4 | Jack      | NULL       |
+----+-----------+------------+

The defaults option can be used to specify the default values for any column, so you can also put a defaults for the lastName column as follows:

const [user, created] = await Users.findOrCreate({
  where: { firstName: "Jane" },
  defaults: { lastName: "Doe" },
});

By putting the lastName column in the defaults instead of the where option, only the firstName value will be searched by Sequelize for a matching row.

The code above will insert a fifth row into the table as follows:

+----+-----------+------------+
| id | firstName | lastName   |
+----+-----------+------------+
|  1 | Nathan    | Sebhastian |
|  2 | John      | NULL       |
|  3 | John      | Doe        |
|  4 | Jack      | NULL       |
|  5 | Jane      | Doe        |
+----+-----------+------------+

And that’s how the defaults option works in Sequelize findOrCreate() method.