Retrieve Records From Tables in Sequelize

For this lesson, let's consider this MySQL table named employees:

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  department ENUM('Sales', 'Engineering', 'HR', 'Marketing') NOT NULL,
  hire_date DATE NOT NULL,
  salary INT NOT NULL
);

That contains these SQL records:

mysql> SELECT * FROM employees;
+----+------------+-----------+-------------+------------+--------+
| id | first_name | last_name | department  | hire_date  | salary |
+----+------------+-----------+-------------+------------+--------+
|  1 | John       | Doe       | Sales       | 2022-01-15 | 50000  |
|  2 | Jane       | Smith     | Engineering | 2020-03-22 | 70000  |
|  3 | Alice      | Johnson   | HR          | 2019-07-30 | 45000  |
|  4 | Bob        | Brown     | Sales       | 2018-11-10 | 52000  |
|  5 | Charlie    | Davis     | Engineering | 2021-06-18 | 75000  |
|  6 | Diana      | Miller    | Marketing   | 2017-05-14 | 60000  |
|  7 | Eve        | Wilson    | HR          | 2023-02-25 | 48000  |
|  8 | Frank      | Moore     | Marketing   | 2020-09-30 | 62000  |
|  9 | Grace      | Taylor    | Engineering | 2021-04-02 | 77000  |
| 10 | Hank       | Anderson  | Sales       | 2019-12-12 | 55000  |
+----+------------+-----------+-------------+------------+--------+
10 rows in set (0.00 sec)

That corresponds to this Sequelize model:

const { DataTypes } = require('sequelize');

const Employee = database.define('Employee', {
  id: {
    type: DataTypes.INTEGER,
    autoIncrement: true,
    primaryKey: true,
  },
  first_name: {
    type: DataTypes.STRING(255),
    allowNull: false,
  },
  last_name: {
    type: DataTypes.STRING(255),
    allowNull: false,
  },
  department: {
    type: DataTypes.ENUM('Sales', 'Engineering', 'HR', 'Marketing'),
    allowNull: false,
  },
  hire_date: {
    type: DataTypes.DATEONLY,
    allowNull: false,
  },
  salary: {
    type: DataTypes.INTEGER,
    allowNull: false,
  },
}, {
  tableName: 'employees',
  timestamps: false
});

Retrieve records

To retrieve all the records from a table, you can use the findAll() method of the associated model:

const records = await model.findAll(options?);

Where:

• records is an array of model instances or a null value if no matching records are found.
• model is an instance of a model created using the define() method of the database handler.
• option is an optional object used to refine the search criteria.

Which is equivalent to this SQL query:

SELECT * FROM table_name [WHERE ...];

Note: The properties (or columns) of the model instance are stored in the dataValues property of the object:

const records = await model.findAll(options?);

for (let record of records) {
  console.log(record.dataValues);
}

Retrieve a single record

To retrieve the first record that matches the search criteria, you can use the findOne() method:

const record = await model.findOne(options?);

Where:

• record is a model instance or a null value if no matching record is found.

Which is equivalent to this SQL query:

SELECT * FROM table_name [WHERE ...] LIMIT 1;

Example

Let's consider this script, that will retrieve all the columns of all the records in the employees table:

const { Sequelize, DataTypes } = require('sequelize');

const database = new Sequelize(/* ... */);

const Employee = database.model('employee', /* ... */);

const records = await Employee.findAll();

for (let record of records) {
  console.log(record.dataValues);
}

Which will produce this output:

{ id: 1, first_name: 'John', last_name: 'Doe', department: 'Sales', hire_date: '2022-01-15', salary: '50000' }
{ id: 2, first_name: 'Jane', last_name: 'Smith', department: 'Engineering', hire_date: '2020-03-22', salary: '70000' }
{ id: 3, first_name: 'Alice', last_name: 'Johnson', department: 'HR', hire_date: '2019-07-30', salary: '45000' }
{ id: 4, first_name: 'Bob', last_name: 'Brown', department: 'Sales', hire_date: '2018-11-10', salary: '52000' }
{ id: 5, first_name: 'Charlie', last_name: 'Davis', department: 'Engineering', hire_date: '2021-06-18', salary: '75000' }
{ id: 6, first_name: 'Diana', last_name: 'Miller', department: 'Marketing', hire_date: '2017-05-14', salary: '60000' }
{ id: 7, first_name: 'Eve', last_name: 'Wilson', department: 'HR', hire_date: '2023-02-25', salary: '48000' }
{ id: 8, first_name: 'Frank', last_name: 'Moore', department: 'Marketing', hire_date: '2020-09-30', salary: '62000' }
{ id: 9, first_name: 'Grace', last_name: 'Taylor', department: 'Engineering', hire_date: '2021-04-02', salary: '77000' }
{ id: 10, first_name: 'Hank', last_name: 'Anderson', department: 'Sales', hire_date: '2019-12-12', salary: '55000' }

Retrieve and exclude columns

By default, the findAll() and findOne() method will retrieve all the columns of all the matched rows.

To only retrieve specific columns, you can use the attributes property:

{
  attributes: [<column_name>, ...]
}

Alternatively, to exclude specific columns, you can use the exclude property within the attributes property:

{
  attributes: {
    exclude: [<column_name>, ...]
  }
}

Example

For example, this query will only retrieve the first_name, last_name, department columns of all the records in the employees table:

const records = await Employee.findAll({
  attributes: ['first_name', 'last_name', 'department']
});

Which is equivalent to this SQL statement:

mysql> SELECT first_name, last_name, department FROM employees;
+------------+-----------+-------------+
| first_name | last_name | department  |
+------------+-----------+-------------+
| John       | Doe       | Sales       |
| Jane       | Smith     | Engineering |
| Alice      | Johnson   | HR          |
| Bob        | Brown     | Sales       |
| Charlie    | Davis     | Engineering |
| Diana      | Miller    | Marketing   |
| Eve        | Wilson    | HR          |
| Frank      | Moore     | Marketing   |
| Grace      | Taylor    | Engineering |
| Hank       | Anderson  | Sales       |
+------------+-----------+-------------+
10 rows in set (0.00 sec)

Sort records in lexicographic order

To sort records based on the value of a column in ascending or descending order, you can use the order property:

{
  order: [
    [<attribute>, <direction>]
  ]
}

Where:

• attribute is the name of the column.
• direction is one of ASC for ascending or DESC for descending.

Example

For example, this query will retrieve the id, last_name, and department columns, and sort the records in descending order based on the last_name column:

await Employee.findAll({
  attributes: ['id', 'last_name', 'department'],
  order: [['last_name', 'DESC']]
});

Which is equivalent to this SQL statement:

mysql> SELECT id, last_name, department FROM employees ORDER BY last_name DESC;
+----+-----------+-------------+
| id | last_name | department  |
+----+-----------+-------------+
|  7 | Wilson    | HR          |
|  9 | Taylor    | Engineering |
|  2 | Smith     | Engineering |
|  8 | Moore     | Marketing   |
|  6 | Miller    | Marketing   |
|  3 | Johnson   | HR          |
|  1 | Doe       | Sales       |
|  5 | Davis     | Engineering |
|  4 | Brown     | Sales       |
| 10 | Anderson  | Sales       |
+----+-----------+-------------+
10 rows in set (0.00 sec)

Limit the number of records

To limit the number of records in the set, you can use the limit property:

{
  limit: <number>
}

Where:

• number is the maximum number of records to include in the set.

To skip a certain number of records in the result set, you can combine the limit property with the offset property:

{
  limit: <number>,
  offset: <number>
}

This is particularly useful in scenarios where the result set might be too large or implementing pagination.

Example

For example, this query will retrieve the first_name, last_name, department, and salary columns, sort the records in descending order based on the salary column, and limit the result set to 3 rows:

await Employee.findAll({
  attributes: ['first_name', 'last_name', 'department', 'salary'],
  order: [['salary', 'DESC']],
  limit: 3
});

Which is equivalent to this SQL statement:

mysql> SELECT first_name, last_name, department, salary FROM employees ORDER BY salary DESC LIMIT 3;
+------------+-----------+-------------+--------+
| first_name | last_name | department  | salary |
+------------+-----------+-------------+--------+
| Grace      | Taylor    | Engineering |  77000 |
| Charlie    | Davis     | Engineering |  75000 |
| Jane       | Smith     | Engineering |  70000 |
+------------+-----------+-------------+--------+
3 rows in set (0.00 sec)

Summary

Here's a summary of what you've learned in this lesson:

• The findOne() method is used to retrieve the first record from a table that matches a criteria.
• The findAll() method is used to retrieve all the records from a table that match a criteria.
• The attributes property is used to retrieve specific attributes.
• The exclude property is used to exclude specific attributes.
• The order property is used to sort records in lexicographical order.
• The limit property is used to limit the number of records in a set.
• The offset property is used to skip a number of records in a set.