Sequelize Model Querying

Querying Data
a) Basic Retrieval (Finder Methods)
findAll() - Retrieves multiple records that match the query. If no conditions are specified, it fetches all records.

 User.findAll().then(users => {
   console.log(users);
 });

findOne() - Retrieves the first record that matches the specified query. If no record is found, it returns null.

 User.findOne({
   where: { firstName: 'John' }
 }).then(user => {
   console.log(user);
 });

findByPk(): Fetches a record based on its primary key. If no record is found, it returns null.

 User.findByPk(1).then(user => {
   console.log(user);
 });

findOrCreate : Attempts to find a record that matches the specified query. If no such record exists, it creates a new one with the provided values.

 User.findOrCreate({
   where: { username: 'john_doe' },
   defaults: { email: 'john@example.com' }
 }).then(user => {
   console.log(user);
 });

findAndCountAll : Combines findAll with a count of the total number of records that match the query. It returns an object with two properties: rows (the records) and count (the total count). This method is useful for the pagination.

 User.findAndCountAll({
   where: { status: 'active' },
   limit: 10
 }).then(user => {
   console.log(user);
 });

findAndCount : Similar to findAll, but it also returns the total count of the records that match the query.

 User.findAndCount({
   where: { status: 'active' },
   limit: 10,
   offset: 0
 }).then(user => {
   console.log(user);
 });

b) Filtering Data (WHERE Clause)
Sequelize uses JavaScript objects to represent SQL conditions. The where option is used to specify conditions for filtering results.

 User.findAll({
   where: {
     age: 25
   }
 }).then(users => {
   console.log(users);
 });

You can use operators for more complex queries,

 const { Op } = require('sequelize');
 User.findAll({
   where: {
     age: {
       [Op.gt]: 18, // greater than 18
     },
     firstName: {
       [Op.like]: '%John%' // firstName contains 'John'
     },
     lastName: {
       [Op.or]: [{ [Op.like]: '%Doe%' }, { [Op.like]: '%Fow%' }],
     }, 
   }
 }).then(users => {
   console.log(users);
 });

c) Limit, Offset, and Order

limit - Restrict the number of returned rows.

offset - Skip a specified number of rows.

order - Sort the results.

 User.findAll({
   limit: 10,
   offset: 20,
   order: [['age', 'DESC']] // Order by age, descending
 }).then(users => {
   console.log(users);
 });

Creating Data
To insert a new row into the table, use create()

 User.create({
   firstName: 'Jane',
   lastName: 'Doe',
   age: 28
 }).then(user => {
   console.log(user);
 });

You can also insert multiple rows at once using bulkCreate()

 User.bulkCreate([
   { firstName: 'Alice', lastName: 'Smith', age: 24 },
   { firstName: 'Bob', lastName: 'Brown', age: 29 }
 ]).then(users => {
   console.log(users);
 });

Updating Data
To update rows, use update()

 User.update(
   { age: 30 },  // New value(s)
   { where: { firstName: 'John' }} // Condition for rows to update
 ).then(result => {
   console.log(result);
 });

Deleting Data

To delete rows, use destroy()

 User.destroy({
   where: {
     firstName: 'John'
   }
 }).then(() => {
   console.log('Deleted');
 });

Aggregates

Sequelize also supports aggregate functions like count, sum, min, and max.

 // COUNT
 User.count({
   where: { age: { [Op.gt]: 18 } }
 }).then(count => {
   console.log(`There are ${count} users older than 18.`);
 });

 // SUM
 User.sum('age', {
   where: { age: { [Op.gt]: 18 } }
 }).then(totalAge => {
   console.log(`Total age of users older than 18 is ${totalAge}.`);
 });