Enhance query performance in Node.js applications by writing optimized SQL with Sequelize ORM.

Introduction

Sequelize ORM (Object-Relational Mapping) is a promise-based Node.js library that eases interaction with relational databases such as PostgreSQL, MySQL, MSSQL, and SQLite. It allows you to define models (essentially JavaScript objects) that map to the database tables and includes features like querying, validating, and managing relations between data.

It helps developers build strong and efficient web applications by enabling smooth communication between the Node.js server and an SQL database, reducing the complexity of database operations.

ORMs like Sequelize, while useful, have their own drawbacks, which include

1. Performance Overhead: Abstraction layers can result in less efficient database interactions. Complex queries might perform better with raw SQL.

2. Learning Curve: It takes time to fully grasp the ORM's features and quirks, especially for developers new to the concept.

3. Hidden Complexity: ORMs can obscure what's happening under the hood, making debugging and performance tuning more challenging.

4. Limited Query Flexibility: Some complex queries might not be easily expressed in ORM syntax.

5. Migration Hassles: Large-scale database changes can be cumbersome and error-prone with ORM-managed migrations.

Efficient queries are crucial for scalable applications as they improve load times, enhance user experience, and optimize resources, while inefficient queries can cause slow responses, increased server load, and user frustration, leading to delays and higher costs.

In this example, we will guide you through setting up a Node.js application that connects to a PostgreSQL database using Sequelize as the Object-Relational Mapping (ORM) tool. We'll start by installing the necessary packages and setting up the database connection. Next, we'll define models to represent our data structures and learn how to write SQL queries with Sequelize. By the end of this setup, you'll have a solid foundation for writing optimized queries with Sequelize.

Installation

First, run the following commands in your VSCode terminal to initialize and set up an Express Node.js server:

npm init -y (Run this command if the project is new and you don't have a package.json)

npm install express dotenv

npm install --save pg pg-hstore sequelize

Setup Sequelize CLI
npm install --save-dev sequelize-cli

Project setup

Add a .env file to the project and include the following app configurations:

NODE_ENV=development
APP_PORT=3000

# DB Credentials
POSTGRES_USER=postgres
POSTGRES_PASSWORD=postgres
POSTGRES_DB=fruitappdb
POSTGRES_HOST=localhost
POSTGRES_PORT=5432

After running the following commands, then we can add another file (.sequelizerc) to the root directory of the project and add the following configurations below

const path = require('path');

module.exports = {
  config: path.resolve('config', 'config.js'),
  'models-path': path.resolve('db', 'models'),
  'seeders-path': path.resolve('db', 'seeders'),
  'migrations-path': path.resolve('db', 'migrations'),
};

The configurations above help determine where the database configurations, models, seeders, and migration files will be located in relation to the project's root directory.

After adding the configs in the .sequelizerc file, go ahead to run the following commands on the terminal

npx sequelize-cli init

This command will create a config and db folders with sub folders as models, seeders and migrations as seen on the .sequelizerc file

Go to the config.js file in the config folder and define the database settings for your app

require('dotenv').config({path: `${process.cwd()}/.env`});

module.exports = {
  "development": {
    "username": process.env.POSTGRES_USER,
    "password": process.env.POSTGRES_PASSWORD,
    "database": process.env.POSTGRES_DB,
    "host": process.env.POSTGRES_HOST,
    "port": process.env.POSTGRES_PORT,
    "dialect": "postgres",
    "seederStorage": 'sequelize'
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

Next, we use the Sequelize cli to automatically create our database on our local PostgreSQL server.

npx sequelize-cli db:create

We can confirm from our database server if our fruitappdb has been created.

Use the commands below to generate the model and migration script

npx sequelize-cli model:generate --name fruit --attributes color:string,name:string,price:decimal,season:string

Go to the models folder, find the fruit.js file, and update it with the code below.

'use strict';

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


const fruit = sequelize.define('fruits', {
  id: {
    allowNull: false,
    autoIncrement: true,
    primaryKey: true,
    type: DataTypes.INTEGER
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      notNull: {
        msg: 'name cannot be null'
      },
      notEmpty: {
        msg: 'name cannot be empty'
      }
    }
  },
  color: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      notNull: {
        msg: 'color cannot be null'
      },
      notEmpty: {
        msg: 'color cannot be empty'
      }
    }
  },
  price: {
    type: DataTypes.DECIMAL(18, 4),
    allowNull: false,
    defaultValue: 0,
    validate: {
      notNull: {
        msg: 'price cannot be null'
      },
      notEmpty: {
        msg: 'price cannot be empty'
      }
    }
  },
  createdAt: {
    allowNull: false,
    type: DataTypes.DATE
  },
  updatedAt: {
    allowNull: false,
    type: DataTypes.DATE
  },
  deletedAt: {
    type: DataTypes.DATE
  }
},
{
  paranoid: true
});

module.exports = fruit;

In the migrations sub-folder in the db folder, find the migration file and add the following code

'use strict';
/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('fruits', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      color: {
        type: Sequelize.STRING
      },
      name: {
        type: Sequelize.STRING
      },
      price: {
        type: Sequelize.DECIMAL
      },
      season: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
       deletedAt: {
        type: Sequelize.DATE
      }
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('fruits');
  }
};

Go to the terminal and run the commands below

npx sequelize-cli db:migrate

The command above will use the migration script to create the fruits table in the fruitsappdb, setting up all the properties as specified.

Let’s go ahead to seed some fruits to our fruitsappdb, we will use the command below to generate a seed file in the seeders sub-folders.

npx sequelize-cli seed:generate --name fruits-seed

In the seed file generated add the following code

module.exports = {
  up: (queryInterface, Sequelize) => {
      return queryInterface.bulkInsert('fruits', [
        {
            "name": "Apple",
            "color": "Red",
            "price": 0.50,
            "season": "Fall",
            "createdAt": new Date(),
            "updatedAt": new Date()
        },
        {
            "name": "Banana",
            "color": "Yellow",
            "price": 0.30,
            "season": "All year",
            "createdAt": new Date(),
            "updatedAt": new Date()
        },
        {
            "name": "Cherry",
            "color": "Red",
            "price": 1.20,
            "season": "Summer",
            "createdAt": new Date(),
            "updatedAt": new Date()
        },
        {
            "name": "Grape",
            "color": "Purple",
            "price": 0.90,
            "season": "Fall",
            "createdAt": new Date(),
            "updatedAt": new Date()
        },
        {
            "name": "Orange",
            "color": "Orange",
            "price": 0.60,
            "season": "Winter",
            "createdAt": new Date(),
            "updatedAt": new Date()
        }
    ]
    );
  },
  down: (queryInterface, Sequelize) => {
      return queryInterface.bulkDelete('fruit', null, {});
  },
};

Run the command below to seed the fruits defined in the seed file to the database

npx sequelize-cli db:seed:all

Create a server.js file on your root directory and add the following code

require('dotenv').config({path: `${process.cwd()}/.env`});
const express = require('express');

const app = express();

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

const env = process.env.NODE_ENV || 'development';
const config = require('./config/config');
const sequelize = new Sequelize(config[env]);

app.use(express.json());
app.use(express.urlencoded({ extended: true }));

app.get('/', (req, res) => {
  res.status(200).json({
      status: 'Success',
      message: `FruitsApi ${process.env.NODE_ENV} environment`,
      data: null
  });
});


app.get('/api/fruit/getfruits', async (req, res) => {
    const { name, color, season, createdAt} = req.query;

    let baseQuery = 'SELECT color, name, price, season, "createdAt" FROM public.fruits WHERE "createdAt" IS NOT null';
    if(name) {
        baseQuery += ' AND name =$name';
    }
    if(color) {
        baseQuery += ' AND color =$color';
    }
    if(season) {
        baseQuery += ' AND season =$season';
    }
    const [result, metadata] = await sequelize.query(baseQuery, {
    bind: {
        name: name,
        color: color,
        season: season
    }
    });

    return res.status(200).json(
        {
            status: 'Success',
            message: 'Fruits fetched successfully',
            data: result
        }
    )
});

const PORT = process.env.APP_PORT || 4000;

app.listen(PORT, () => {
    console.log('Server up and running on port', PORT);
});

We see how to setup and write SQL using the Sequelize ORM. This method will greatly enhance the performance of your app's queries and help you optimize them when dealing with issues like latency, Cartesian explosion, and other query-related problems.

For those interested in exploring the complete source code and understanding how the application is structured, you can access it in this GitHub repository. This repository contains all the files and configurations needed to run the application locally.