All Articles

Advanced Sequelize Queries: Going Beyond the Basics

Advanced Sequelize Queries: Going Beyond the Basics

When you first start using Sequelize, you typically learn the basics: creating models, performing simple CRUD operations, and maybe setting up a few associations. But once you start building more complex applications, you’ll quickly realize there’s a whole world of advanced querying capabilities waiting to be explored.

In this deep dive, I’ll walk you through some of the more powerful querying features of Sequelize that can help you write more efficient, elegant, and performant database operations. We’ll explore everything from complex filtering to raw queries, transaction management, and optimization techniques.

TL;DR

  • Complex WHERE Clauses: Use Op operators for advanced filtering
  • Advanced Joins: Master includes with complex conditions and nested associations
  • Subqueries: Leverage literal and Sequelize.query for efficient nested queries
  • Aggregations: Use group, sum, count, and having for data analysis
  • Performance Optimization: Apply indexing, limiting attributes, and proper eager loading
  • Transactions: Ensure data integrity with proper transaction management
  • Raw Queries: Combine the power of raw SQL with Sequelize models

Complex WHERE Clauses with Operators

When you’re first working with Sequelize, you might start with simple equality conditions in your WHERE clauses:

// Basic query with simple conditions
const activeUsers = await User.findAll({
  where: {
    status: 'active',
    accountType: 'premium'
  }
});

But real-world applications often require more sophisticated filtering logic. This is where Sequelize’s powerful Operators (Op) come in. Operators allow you to express conditions like “greater than,” “not equal to,” “between two values,” and much more.

What Are Sequelize Operators?

Operators in Sequelize are special symbols that represent SQL operations. They’re available through the Op object, which you need to import first:

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

These operators correspond to SQL operations but in a JavaScript-friendly format. For example, [Op.gt] translates to > in SQL, and [Op.or] creates an OR condition.

Now let’s explore some practical examples:

Combining Multiple Conditions

Let’s look at a common scenario: finding users who match multiple criteria connected with logical operators.

// Find all users who are either active premium members
// or have joined in the last 30 days
const thirtyDaysAgo = new Date(new Date() - 30 * 24 * 60 * 60 * 1000);

const users = await User.findAll({
  where: {
    [Op.or]: [
      {
        status: 'active',
        membershipType: 'premium'
      },
      {
        createdAt: {
          [Op.gte]: thirtyDaysAgo
        }
      }
    ]
  }
});

This query translates to SQL that looks something like:

SELECT * FROM "Users" 
WHERE ("status" = 'active' AND "membershipType" = 'premium') 
   OR ("createdAt" >= '2025-03-10T00:00:00.000Z')

Why is this useful?

  1. Readability: Notice how we can nest operators to create complex logical conditions that would be cumbersome to write in raw SQL
  2. Maintainability: The JavaScript object structure makes it easier to dynamically build and modify conditions
  3. Safety: This approach helps prevent SQL injection since Sequelize properly escapes all values

When to use complex conditions:

  • User search features with multiple optional filters
  • Reports that need to filter data across multiple dimensions
  • Any situation where you need to combine different types of conditions (dates, text, numbers) in one query

Range and Pattern Matching

// Find products with price between $50-$100 with names starting with "Apple"
const products = await Product.findAll({
  where: {
    price: {
      [Op.between]: [50, 100]
    },
    name: {
      [Op.like]: 'Apple%'
    }
  }
});

Array Operations

// Find posts with any of these tags
const posts = await Post.findAll({
  where: {
    tags: {
      [Op.contains]: ['javascript', 'sequelize', 'nodejs']
    }
  }
});

// This works for PostgreSQL array fields

JSON Filtering (PostgreSQL)

If you’re using PostgreSQL, Sequelize has excellent support for JSON/JSONB querying:

// Find users with a specific preference in their settings JSON field
const usersWithDarkMode = await User.findAll({
  where: {
    'settings.theme': {
      [Op.eq]: 'dark'
    }
  }
});

Date Manipulation

// Find orders placed on weekend days
const weekendOrders = await Order.findAll({
  where: Sequelize.literal('EXTRACT(DOW FROM "createdAt") IN (0, 6)')
});

Mastering Complex Joins with Include

In relational databases, joins are essential for connecting data across tables. Sequelize abstracts this through the include option, which is much more powerful than it might initially appear.

What is include and why use it?

The include option allows you to fetch associated data in a single query. Under the hood, Sequelize is creating JOIN statements in SQL, but it presents the results in a nested JavaScript object structure that’s much easier to work with.

Without include, you’d need multiple queries to fetch related data, leading to:

  1. The N+1 query problem - making separate queries for each related record
  2. Complex data merging in JavaScript - manually connecting related records
  3. Increased network round trips - slowing down your application

With proper use of include, you get:

  1. Data in a ready-to-use nested structure
  2. Better performance through fewer database queries
  3. Cleaner, more maintainable code

Let’s explore how to leverage the full power of this feature.

Multi-Level Includes

Sequelize allows you to fetch data from multiple related tables in a single query through nested includes. This is particularly valuable when your data has a hierarchical relationship.

// Get all users with their orders and each order's order items
const users = await User.findAll({
  include: [{
    model: Order,
    include: [{
      model: OrderItem,
      include: [{
        model: Product
      }]
    }]
  }]
});

This single query will generate SQL with multiple joins to fetch:

  1. All users
  2. All orders for each user
  3. All order items for each order
  4. The product details for each order item

The hidden cost

While this looks elegant, it could be extremely inefficient if not used carefully. The query above might return a massive amount of data, potentially causing:

  • Excessive memory usage
  • Slow query execution
  • Network bandwidth issues
  • Application performance degradation

This is particularly problematic if you have users with many orders, and orders with many items. The result set grows multiplicatively, not additively.

When to use multi-level includes:

  • When retrieving complete object graphs for a small number of records
  • When the data will be immediately used in its entirety
  • In admin panels or detailed views where comprehensive data is needed

When to avoid multi-level includes:

  • For large datasets where only portions of the data are needed
  • In list views or when paginating results
  • When performance is critical

Let’s make this more practical with selective includes:

Conditional Includes with Where

// Get users with their high-value orders (over $500) from the last month
const lastMonth = new Date();
lastMonth.setMonth(lastMonth.getMonth() - 1);

const users = await User.findAll({
  include: [{
    model: Order,
    required: false, // This makes it a LEFT JOIN
    where: {
      total: {
        [Op.gt]: 500
      },
      createdAt: {
        [Op.gte]: lastMonth
      }
    }
  }]
});

The required: false setting is crucial here - it means “include users even if they don’t have matching orders” (LEFT JOIN). If set to true, only users with matching orders would be included (INNER JOIN).

Includes with Custom Attributes

// Get users with only specific order fields
const users = await User.findAll({
  include: [{
    model: Order,
    attributes: ['id', 'total', 'createdAt'],
    include: [{
      model: OrderItem,
      attributes: ['quantity', 'price']
    }]
  }]
});

Using Aliases with Include

When you have multiple associations to the same model, you’ll need to use aliases:

// Product model with associations
Product.belongsTo(User, { as: 'creator' });
Product.belongsTo(User, { as: 'updater' });

// Query using these aliases
const products = await Product.findAll({
  include: [
    { model: User, as: 'creator' },
    { model: User, as: 'updater' }
  ]
});

Through Associations with Include

For many-to-many relationships, you can include through a junction table:

// Models
User.belongsToMany(Project, { through: 'UserProject' });
Project.belongsToMany(User, { through: 'UserProject' });

// Query
const users = await User.findAll({
  include: [{
    model: Project,
    through: {
      attributes: ['role', 'joinedAt'], // Include specific attributes from the junction table
      where: {
        role: 'owner'
      }
    }
  }]
});

This will give you all users who are owners of any project, including both user and project details, plus the selected fields from the junction table.

Subqueries: Queries Within Queries

Subqueries represent one of the most powerful SQL features, allowing you to use the results of one query within another. In Sequelize, implementing subqueries requires a slightly different approach than standard query builders, but the payoff in terms of performance and capability is substantial.

What are subqueries and why are they valuable?

A subquery is a query nested inside another query. They’re particularly useful when:

  1. You need to filter based on aggregated data - Example: “Find products that have been ordered more than 10 times”
  2. You want to avoid multiple round-trips to the database - Doing the work in SQL rather than in JavaScript
  3. You need to find records based on the existence/non-existence of related records - Example: “Find users who have never placed an order”
  4. You’re working with complex data relationships that are difficult to express with regular joins

In Sequelize, subqueries typically involve using either the Sequelize.literal() method to inject raw SQL, or the sequelize.query() method for more complex cases. Let’s see how these approaches work in practice.

Subqueries in Where Clauses

// Find products that have been ordered more than 10 times
const popularProducts = await Product.findAll({
  where: {
    id: {
      [Op.in]: Sequelize.literal(`(
        SELECT "ProductId" FROM "OrderItems"
        GROUP BY "ProductId"
        HAVING COUNT(*) > 10
      )`)
    }
  }
});

Correlated Subqueries

// Find users who have at least one order with total > $1000
const highValueCustomers = await User.findAll({
  where: {
    id: {
      [Op.in]: Sequelize.literal(`(
        SELECT "UserId" FROM "Orders"
        WHERE "Orders"."UserId" = "User"."id"
        AND "Orders"."total" > 1000
        LIMIT 1
      )`)
    }
  }
});

Using Sequelize.query with Replacements

For complex subqueries, sometimes using a raw query can be cleaner:

const [inactiveUsers] = await sequelize.query(
  `SELECT *
   FROM "Users" AS "User"
   WHERE NOT EXISTS (
     SELECT 1
     FROM "Orders" AS "Order"
     WHERE "Order"."UserId" = "User"."id"
     AND "Order"."createdAt" > :since
   )`,
  {
    replacements: { since: new Date(new Date() - 90 * 24 * 60 * 60 * 1000) },
    model: User,
    mapToModel: true
  }
);

This gives you users who haven’t placed an order in the last 90 days, returned as proper User model instances.

Aggregations and Grouping

Database aggregations are essential for data analysis and reporting. They allow you to summarize data by calculating totals, averages, counts, and other statistical measures. Sequelize provides robust support for these operations, though the syntax can be a bit different from raw SQL.

When to use aggregations

Aggregations shine in scenarios like:

  1. Reporting dashboards - calculating totals, averages, and trends
  2. Data analysis - identifying patterns and outliers in your data
  3. Summary views - showing condensed information about large datasets
  4. Grouping related records - for example, counting orders by status or products by category

The key advantage of performing aggregations in the database rather than in your application code is performance. Database engines are highly optimized for these operations and can process large datasets much more efficiently than JavaScript running in Node.js.

Let’s explore how Sequelize implements these powerful features:

Basic Counting

// Count users by status
const userCountsByStatus = await User.findAll({
  attributes: [
    'status',
    [sequelize.fn('COUNT', sequelize.col('id')), 'userCount']
  ],
  group: ['status']
});

console.log(userCountsByStatus);
// [
//   { status: 'active', userCount: '42' },
//   { status: 'inactive', userCount: '11' },
//   ...
// ]

Multiple Aggregations

// Get order stats by month
const orderStats = await Order.findAll({
  attributes: [
    [sequelize.fn('date_trunc', 'month', sequelize.col('createdAt')), 'month'],
    [sequelize.fn('COUNT', sequelize.col('id')), 'orderCount'],
    [sequelize.fn('SUM', sequelize.col('total')), 'totalSales'],
    [sequelize.fn('AVG', sequelize.col('total')), 'avgOrderValue']
  ],
  group: [sequelize.fn('date_trunc', 'month', sequelize.col('createdAt'))],
  order: [sequelize.fn('date_trunc', 'month', sequelize.col('createdAt'))]
});

Having Clauses

// Find categories with more than 10 products
const popularCategories = await Category.findAll({
  attributes: [
    'id',
    'name',
    [sequelize.fn('COUNT', sequelize.col('Products.id')), 'productCount']
  ],
  include: [{
    model: Product,
    attributes: []
  }],
  group: ['Category.id'],
  having: sequelize.literal('COUNT("Products"."id") > 10')
});

Working with Transactions

Transactions are a fundamental database concept that ensures data integrity by treating multiple operations as a single, atomic unit of work. In simpler terms, either all operations within a transaction succeed, or none of them do - there’s no in-between state where only some changes are applied.

Why transactions are critical

Imagine a banking scenario where you’re transferring money from Account A to Account B. This involves two operations:

  1. Deduct money from Account A
  2. Add money to Account B

Without a transaction, if the application crashes or an error occurs after step 1 but before step 2, the money would simply vanish - deducted from Account A but never added to Account B. Transactions prevent this by rolling back all changes if any part fails.

When to use transactions

You should use transactions whenever you have multiple database operations that must succeed or fail together, such as:

  1. Financial operations - transfers, purchases, refunds
  2. Multi-step workflows - order processing, user registration with profile creation
  3. Data integrity scenarios - updating a record and its related records
  4. Concurrent access situations - preventing race conditions when multiple users access the same data

Sequelize provides a clean API for transaction management, with both automatic and manual control options:

Basic Transaction Usage

// Start a managed transaction
const result = await sequelize.transaction(async (t) => {
  // Create an order
  const order = await Order.create({
    UserId: user.id,
    total: cartTotal
  }, { transaction: t });

  // Create order items
  const orderItems = await OrderItem.bulkCreate(
    cartItems.map(item => ({
      OrderId: order.id,
      ProductId: item.productId,
      quantity: item.quantity,
      price: item.price
    })),
    { transaction: t }
  );

  // Update product inventory
  for (const item of cartItems) {
    await Product.decrement('stock', {
      by: item.quantity,
      where: { id: item.productId },
      transaction: t
    });
  }

  return { order, orderItems };
});

Isolation Levels

For more control, you can set isolation levels:

const isolationLevels = {
  READ_UNCOMMITTED: 'READ UNCOMMITTED',
  READ_COMMITTED: 'READ COMMITTED',
  REPEATABLE_READ: 'REPEATABLE READ', 
  SERIALIZABLE: 'SERIALIZABLE'
};

const result = await sequelize.transaction({
  isolationLevel: isolationLevels.SERIALIZABLE
}, async (t) => {
  // Your transaction logic here
});

Manual Transaction Management

// Start transaction
const t = await sequelize.transaction();

try {
  // Your operations here
  const user = await User.create({
    name: 'Jane',
    email: 'jane@example.com'
  }, { transaction: t });

  await Profile.create({
    UserId: user.id,
    bio: 'A new user'
  }, { transaction: t });

  // Commit transaction
  await t.commit();
} catch (error) {
  // If error, rollback
  await t.rollback();
  throw error;
}

Performance Optimization

As your application scales and your database grows, query performance becomes increasingly critical. A query that executes in milliseconds with a small dataset might take seconds or even minutes as your data expands to millions of rows. Inefficient queries can lead to slow response times, timeout errors, and poor user experience.

Common performance bottlenecks in Sequelize applications

When working with Sequelize, several common patterns can lead to performance issues:

  1. Fetching too much data - Retrieving all columns when only a few are needed
  2. N+1 query problem - Making separate queries for each related record instead of using joins
  3. Missing indexes - Causing full table scans for frequently filtered columns
  4. Improper eager loading - Loading too many associations at once
  5. Lack of pagination - Attempting to load all records at once instead of in chunks

Let’s explore strategies to address each of these issues:

Selecting Only Needed Attributes

// Instead of fetching all fields
const users = await User.findAll();

// Only fetch what you need
const userEmails = await User.findAll({
  attributes: ['id', 'email']
});

Using Indexes Effectively

Make sure your models define indexes on frequently queried fields:

// In your model definition
const Product = sequelize.define('Product', {
  name: DataTypes.STRING,
  price: DataTypes.DECIMAL,
  categoryId: DataTypes.INTEGER
}, {
  indexes: [
    { fields: ['categoryId'] }, // Simple index
    { fields: ['price', 'categoryId'] }, // Composite index
    { fields: ['name'], unique: true } // Unique constraint
  ]
});

Pagination

Always paginate large result sets:

// Page size
const limit = 20;
// Page number (0-indexed)
const page = req.query.page || 0;

const users = await User.findAndCountAll({
  limit,
  offset: page * limit,
  order: [['createdAt', 'DESC']]
});

// Return both the users and pagination info
return {
  users: users.rows,
  total: users.count,
  totalPages: Math.ceil(users.count / limit),
  currentPage: page
};

Optimizing Includes

Inappropriate use of include can lead to the N+1 query problem or excessive data fetching. Be strategic:

// BAD: This gets ALL order items for ALL orders
const users = await User.findAll({
  include: [{
    model: Order,
    include: [OrderItem]
  }]
});

// BETTER: Only include recent orders and specific order item fields
const users = await User.findAll({
  include: [{
    model: Order,
    where: {
      createdAt: {
        [Op.gte]: new Date(new Date() - 30 * 24 * 60 * 60 * 1000)
      }
    },
    include: [{
      model: OrderItem,
      attributes: ['id', 'quantity', 'price']
    }]
  }]
});

Separate Queries vs. Includes

Sometimes, it’s more efficient to run separate queries than to use complex includes:

// Instead of a complex include hierarchy...
const users = await User.findAll();
const userIds = users.map(user => user.id);

const orders = await Order.findAll({
  where: {
    UserId: {
      [Op.in]: userIds
    }
  }
});

// Group orders by user ID for easy access
const ordersByUser = orders.reduce((acc, order) => {
  acc[order.UserId] = acc[order.UserId] || [];
  acc[order.UserId].push(order);
  return acc;
}, {});

// Now you can access a user's orders with ordersByUser[user.id]

Raw SQL Integration

While Sequelize’s query builder is powerful, there are situations where it falls short or becomes unwieldy. Sometimes, the most direct path to getting exactly what you need is to use raw SQL. Fortunately, Sequelize provides several ways to incorporate raw SQL into your application while still maintaining the benefits of the ORM.

When to use raw SQL

Raw SQL makes sense in several scenarios:

  1. Complex queries - When the query involves window functions, recursive CTEs, or other advanced SQL features not directly supported by Sequelize
  2. Performance-critical paths - When you need to squeeze out every bit of performance and the abstraction layer adds overhead
  3. Database-specific optimizations - When you want to leverage features specific to your database engine
  4. Existing SQL code - When integrating with legacy systems or using queries written by DBAs
  5. Readability - When the Sequelize equivalent would be more complex and harder to understand than the SQL

It’s important to note that using raw SQL isn’t “cheating” or a failure of the ORM. Even the Sequelize documentation acknowledges that for some tasks, raw SQL is the most appropriate tool.

The risks of raw SQL

While powerful, raw SQL comes with some cautions:

  1. SQL injection vulnerabilities - If you’re not careful about parameterizing values
  2. Database portability issues - Raw SQL may tie you to a specific database engine
  3. Loss of Sequelize model functionality - Unless you map the results back to models
  4. Maintenance challenges - Changes to your models may not be reflected in your raw SQL

Let’s explore how to use raw SQL while mitigating these risks:

Basic Raw Queries

const [results, metadata] = await sequelize.query(
  "SELECT * FROM Users WHERE role = 'admin'",
  { type: QueryTypes.SELECT }
);

Mapping Raw Results to Models

const users = await sequelize.query(
  `SELECT "User".*, COUNT("Order"."id") AS "orderCount"
   FROM "Users" AS "User"
   LEFT JOIN "Orders" AS "Order" ON "User"."id" = "Order"."UserId"
   GROUP BY "User"."id"
   HAVING COUNT("Order"."id") > :minOrders`,
  {
    replacements: { minOrders: 5 },
    model: User,
    mapToModel: true
  }
);

// users is now an array of User instances with an additional orderCount property

Literal SQL in Sequelize Methods

const users = await User.findAll({
  attributes: {
    include: [
      [
        sequelize.literal(`(
          SELECT COUNT(*)
          FROM "Orders"
          WHERE "Orders"."UserId" = "User"."id"
        )`),
        'orderCount'
      ]
    ]
  },
  order: [
    [sequelize.literal('orderCount'), 'DESC']
  ]
});

Using Stored Procedures

If your database has stored procedures, you can call them:

const [results, metadata] = await sequelize.query(
  'CALL get_active_users(:days)',
  {
    replacements: { days: 30 }
  }
);

Using Scopes for Reusable Query Logic

As your application grows, you’ll likely find yourself repeating the same query conditions in multiple places. For example, you might have an isActive condition that you apply to users, or a date range filter you apply to orders. Duplicating these conditions across your codebase leads to several problems:

  1. Code duplication - Writing the same conditions in multiple places
  2. Maintenance headaches - When conditions change, you need to update every occurrence
  3. Inconsistency - Different parts of your application might implement the same logic slightly differently
  4. Decreased readability - Complex conditions can make queries harder to understand

Sequelize solves these problems with scopes - reusable, predefined query components that can be applied to your models.

Why scopes are valuable

Scopes offer several advantages:

  1. DRY principle - Define your common query logic in one place
  2. Consistency - Ensure the same conditions are applied everywhere
  3. Composability - Combine multiple scopes for complex queries
  4. Readability - Give meaningful names to common query patterns
  5. Maintainability - Update logic in one place when business rules change
  6. Dynamic parameterization - Create reusable query templates with parameters

Let’s explore how to leverage scopes effectively:

Defining Scopes

// In your User model definition
const User = sequelize.define('User', {
  // attributes
}, {
  scopes: {
    active: {
      where: {
        status: 'active'
      }
    },
    withOrders: {
      include: [{
        model: Order
      }]
    },
    recent: {
      where: {
        createdAt: {
          [Op.gte]: new Date(new Date() - 7 * 24 * 60 * 60 * 1000)
        }
      }
    }
  }
});

Using Scopes

// Use a single scope
const activeUsers = await User.scope('active').findAll();

// Chain multiple scopes
const recentActiveUsers = await User.scope(['active', 'recent']).findAll();

// Dynamic scopes
User.addScope('olderThan', age => {
  return {
    where: {
      age: {
        [Op.gt]: age
      }
    }
  }
});

const adultUsers = await User.scope({ method: ['olderThan', 18] }).findAll();

Scopes with Associations

// Define a scope on Order
Order.addScope('withItems', {
  include: [OrderItem]
});

// Use in association include
const users = await User.findAll({
  include: [{
    model: Order.scope('withItems')
  }]
});

Converting Plain Objects with get({plain:true})

As I mentioned in my previous article about plain:true, Sequelize model instances are more than just data containers - they’re objects with methods, getters, setters, and internal properties. While this is powerful for data manipulation, there are many situations where you need just the raw data without all the Sequelize machinery.

Why convert to plain objects?

Converting Sequelize instances to plain JavaScript objects is necessary for several common scenarios:

  1. Sending data to the client - You typically don’t want to expose Sequelize’s internal properties
  2. Serializing data - For caching, message queues, or other storage mechanisms
  3. Data transformation - When you need to manipulate the data structure without triggering Sequelize getters/setters
  4. Performance optimization - Plain objects use less memory and are faster to process
  5. Template rendering - Some template engines work better with plain objects
  6. Debugging - It’s easier to inspect data without all the Sequelize metadata

When to use plain objects vs. Sequelize instances

Keep as Sequelize instances when:

  • You need to call instance methods like .save() or .update()
  • You benefit from virtual fields and getters/setters
  • You’ll be accessing associations that might be lazy-loaded

Convert to plain objects when:

  • You’re done with data manipulation and just need to use the values
  • You’re preparing data to be sent in a response
  • You’re working with large datasets and need better performance
  • You’re integrating with libraries that expect plain objects

Here are the different ways to convert Sequelize instances to plain objects:

// Get a user as a Sequelize instance
const userInstance = await User.findByPk(1, {
  include: [Order]
});

// Convert to a plain object
const user = userInstance.get({ plain: true });

// With arrays of instances
const userInstances = await User.findAll();
const users = userInstances.map(user => user.get({ plain: true }));

// Shorthand using findAll options
const users = await User.findAll({
  raw: true,
  nest: true, // Nests the results of JOINs
  include: [Order]
});

Real-World Example: Reporting Query

Let’s put it all together with a complex reporting query that demonstrates many of these techniques:

// Get monthly sales report with year-over-year comparison
const generateMonthlySalesReport = async (year) => {
  const startDate = new Date(year, 0, 1); // January 1st of the specified year
  const endDate = new Date(year, 11, 31); // December 31st
  const prevYearStart = new Date(year - 1, 0, 1);
  const prevYearEnd = new Date(year - 1, 11, 31);
  
  // Current year monthly sales
  const currentYearSales = await Order.findAll({
    attributes: [
      [sequelize.fn('date_trunc', 'month', sequelize.col('createdAt')), 'month'],
      [sequelize.fn('SUM', sequelize.col('total')), 'monthlySales']
    ],
    where: {
      createdAt: {
        [Op.between]: [startDate, endDate]
      },
      status: 'completed'
    },
    group: [sequelize.fn('date_trunc', 'month', sequelize.col('createdAt'))],
    order: [sequelize.fn('date_trunc', 'month', sequelize.col('createdAt'))],
    raw: true
  });
  
  // Previous year monthly sales (for comparison)
  const prevYearSales = await Order.findAll({
    attributes: [
      [sequelize.fn('date_trunc', 'month', sequelize.col('createdAt')), 'month'],
      [sequelize.fn('SUM', sequelize.col('total')), 'monthlySales']
    ],
    where: {
      createdAt: {
        [Op.between]: [prevYearStart, prevYearEnd]
      },
      status: 'completed'
    },
    group: [sequelize.fn('date_trunc', 'month', sequelize.col('createdAt'))],
    order: [sequelize.fn('date_trunc', 'month', sequelize.col('createdAt'))],
    raw: true
  });
  
  // Top-selling products
  const topProducts = await OrderItem.findAll({
    attributes: [
      'ProductId',
      [sequelize.fn('SUM', sequelize.col('quantity')), 'totalSold'],
      [sequelize.fn('SUM', sequelize.literal('quantity * price')), 'totalRevenue']
    ],
    include: [{
      model: Order,
      attributes: [],
      where: {
        createdAt: {
          [Op.between]: [startDate, endDate]
        },
        status: 'completed'
      }
    }, {
      model: Product,
      attributes: ['name']
    }],
    group: ['ProductId', 'Product.id'],
    order: [[sequelize.literal('totalRevenue'), 'DESC']],
    limit: 10
  });
  
  // Format and combine the data
  return {
    year,
    monthlySales: currentYearSales.map(month => ({
      month: month.month,
      sales: parseFloat(month.monthlySales),
      prevYearSales: parseFloat(
        (prevYearSales.find(
          prev => new Date(prev.month).getMonth() === new Date(month.month).getMonth()
        ) || { monthlySales: 0 }).monthlySales
      ),
      yearOverYearChange: prevYearSales.find(
        prev => new Date(prev.month).getMonth() === new Date(month.month).getMonth()
      ) 
        ? (parseFloat(month.monthlySales) / parseFloat(
            prevYearSales.find(
              prev => new Date(prev.month).getMonth() === new Date(month.month).getMonth()
            ).monthlySales
          ) - 1) * 100
        : null
    })),
    topProducts: topProducts.map(product => ({
      id: product.ProductId,
      name: product.Product.name,
      quantitySold: parseInt(product.totalSold),
      revenue: parseFloat(product.totalRevenue)
    }))
  };
};

// Usage
const report = await generateMonthlySalesReport(2023);
console.log(report);

This report provides monthly sales with year-over-year comparisons and a list of top-selling products for a specified year.

Conclusion

Mastering advanced Sequelize queries opens up a world of possibilities for building efficient and powerful database-driven applications. We’ve covered complex filtering, joins, subqueries, aggregations, transactions, performance optimization, and raw SQL integration.

Remember, the most elegant solution isn’t always the most complex one. Sometimes a simple, focused query will outperform a complex one. Always profile your queries, especially for performance-critical operations, and don’t be afraid to fall back to raw SQL when Sequelize’s abstractions become more hindrance than help.

With these techniques in your toolkit, you’ll be able to tackle even the most challenging database requirements with confidence and write code that’s both maintainable and performant.

Published Apr 09, 2025

SequelizeNodeJSPostgreSQLMySQL Looking for something new Michael LaPan on Twitter

Published Apr 9, 2024

Looking for something new