Sling Academy
Home/Node.js/Sequelize.js: How to use CASE WHEN expression

Sequelize.js: How to use CASE WHEN expression

Last updated: December 29, 2023

Introduction

Sequelize is a powerful ORM for Node.js that provides easy management of database transactions, relationships, and more. For developers accustomed to using SQL, the CASE WHEN expression is a familiar construct for creating advanced queries that conditionally select or calculate values. In this tutorial, we’ll dive into how you can use the CASE WHEN expression within GSequelize.js. We will cover the basics and then proceed to more complex scenarios with multiple code examples.

Basics of CASE WHEN

In SQL, the CASE statement operates similarly to ‘if-else’ logic in any programming language. It returns values based on specific conditions. Here’s how you typically use the CASE expression in raw SQL:

SELECT name, 
	CASE 
	WHEN condition1 THEN result1 
	WHEN condition2 THEN result2 
	ELSE default_result 
	END 
FROM users;

In Sequelize, you can utilize the Sequelize.literal or Sequelize.fn methods to inject raw SQL into your queries. Here’s a basic example using Sequelize:

const Sequelize = require('sequelize');
const Op = Sequelize.Op;
const User = require('./models').User;

User.findAll({
	attributes: ['name',
		[ Sequelize.literal(
			`CASE WHEN age < 20 THEN 'minor' 
			WHEN age >= 20 AND age < 65 THEN 'adult' 
			ELSE 'senior' END`),
			'age_category'
		]
	]
});

Advanced Conditional Logic

Moving on to an advanced scenario, let’s explore a consolidated comparison that features multiple tables and aggregate functions along with CASE WHEN expressions.

const Sequelize = require('sequelize');
const Order = require('./models').Order;

Order.findAll({
	attributes: [
		// Other attributes ...
		[Sequelize.literal(
			`CASE 
			WHEN payment_status = 'confirmed' AND shipment_status = 'delivered' THEN 'completed'
			WHEN payment_status = 'pending' THEN 'awaiting_payment'
			ELSE 'processing' END`
		), 'order_status'
		]
	],
	// Your JOIN conditions ... (if any)
	// WHERE conditions ... (if any)
});

Complex Calculations with CASE WHEN

We are taking a step further by integrating CASE WHEN expressions within complex calculations and aggregate functions. Imagine you want to categorize sales data based on certain thresholds and calculate averages within those categories:

const Sale = require('./models').Sale;

Sale.findAll({
	attributes: [
		'category',
		[Sequelize.fn('AVG', Sequelize.literal(
			`CASE
			 WHEN amount >= 500 THEN amount
			 ELSE NULL END`
		)), 'avg_high_sales'],
		// Similar logic for other sales categories
	],
	group: ['category'],
	// Having or WHERE conditions...
});

Error Handling

Although Sequelize handles a majority of error-catching internally, it is always good practice to include try-catch blocks around your Sequelize queries to manage potential errors, especially when working with raw SQL expressions:

try {
	// Sequelize query using CASE WHEN ... 
} catch (error) {
	console.error('An error occurred: ', error);
}

Conclusion

In summary, the CASE WHEN expression can greatly enhance your Sequelize.js queries by incorporating conditional logic into your model attributes and aggregate calculations. It enriches your app’s ability to interact with database data in a nuanced and efficient manner. With the versatility of Sequelize combined with your grasp of SQL’s CASE WHEN, there is an abundant potential for powerful data queries and analysis in your Node.js applications.

Remember to practice these examples to familiarize yourself with integrating CASE WHEN conditions, and never forget to wrap your raw SQL injections in safe error-handling protocols to guard against potential exceptions. Happy coding!

Next Article: How to Backup and Restore a Database in Sequelize.js

Previous Article: What is Eager Loading in Sequelize.js?

Series: Sequelize.js Tutorials

Node.js

You May Also Like

  • NestJS: How to create cursor-based pagination (2 examples)
  • Cursor-Based Pagination in SequelizeJS: Practical Examples
  • MongooseJS: Cursor-Based Pagination Examples
  • Node.js: How to get location from IP address (3 approaches)
  • SequelizeJS: How to reset auto-increment ID after deleting records
  • SequelizeJS: Grouping Results by Multiple Columns
  • NestJS: Using Faker.js to populate database (for testing)
  • NodeJS: Search and download images by keyword from Unsplash API
  • NestJS: Generate N random users using Faker.js
  • Sequelize Upsert: How to insert or update a record in one query
  • NodeJS: Declaring types when using dotenv with TypeScript
  • Using ExpressJS and Multer with TypeScript
  • NodeJS: Link to static assets (JS, CSS) in Pug templates
  • NodeJS: How to use mixins in Pug templates
  • NodeJS: Displaying images and links in Pug templates
  • ExpressJS + Pug: How to use loops to render array data
  • ExpressJS: Using MORGAN to Log HTTP Requests
  • NodeJS: Using express-fileupload to simply upload files
  • ExpressJS: How to render JSON in Pug templates