Sequelize - how to COUNT table rows number with code example

Sequelize Model.count() method is used to generate and execute a SELECT COUNT SQL query to your connected database.

The method accepts an object of options that you can define to modify the generated query.

Let’s see the count() method in action. Imagine you have a table named Users with the following data:

+----+-----------+------------+
| id | firstName | lastName   |
+----+-----------+------------+
|  1 | Nathan    | Sebhastian |
|  2 | John      | Sebhastian |
|  3 | Jane      | Doe        |
|  4 | Silvana   | Gomez      |
|  5 | Sarah     | NULL       |
|  6 | Richter   | NULL       |
+----+-----------+------------+

You need to create a Sequelize Model for the above table and call the count() method from there.

Take a look at the following example:

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

const count = await User.count();
console.log(count); // 6

Calling the count() method without any argument would generate a similar query to the one below:

SELECT
  count(*) AS `count`
FROM
  `Users` AS `User`;

The count() method returns a single number type value that represents the number of rows matching your COUNT condition.

You can add the where option to the count() method to filter the result as follows:

const count = await User.count({
  where: { lastName: "Sebhastian" },
});

console.log(count); // 2

The generated SQL query would be as follows:

SELECT
  count(*) AS `count`
FROM
  `Users` AS `User`
WHERE
  `User`.`lastName` = 'Sebhastian';

When you need to perform a DISTINCT count, you need to add the distinct and col options as the argument to the count() method.

For example, you see that in the example table we have two rows with the same lastName value.

You can tell Sequelize to generate and execute a COUNT( DISTINCT( lastName ) ) query with the code below:

const count = await User.count({
  distinct: true,
  col: 'lastName',
});

console.log(count); // 3

The code above will generate the following SQL query:

SELECT
  count(DISTINCT (`lastName`)) AS `count`
FROM
  `Users` AS `User`;

Finally, you can specify the column on which you want to call the count() method with the col option:

const count = await User.count({
  col: 'lastName',
});

console.log(count); // 4

Instead of count(*), the above code will generate count(lastName) as shown below:

SELECT 
  count(`lastName`) AS `count` 
FROM 
  `Users` AS `User`;

For a full list of options supported by the count() method, check out Sequelize Model.count() documentation.

Now you’ve learned how to use the Sequelize Model.count() method to count rows in your SQL database table. Good work! 👍