Recommand · October 22, 2021 0

First query using Sequelize is too slow. After that all queries run fast

I am developing an endpoint in Node/Koa which will make several queries to a POSTGRES DB hosted in AWS RDS. The code looks something like this.

const handleGetRequest = async (req, res) => {
  console.time("time:gettingShopName");
  const existingShop = await models.Shops.findOne({
    where: {
      shopName: "ingbe123.myshopify.com",
    },
  });
  console.timeEnd("time:gettingShopName");

  console.time(`time:gettingPlans`);
  const plan = await models.Plans.findOne({
    where: {
      name: "Pro",
    },
  });
  console.timeEnd(`time:gettingPlans`);

  return res.send({
    plan,
    existingShop,
  });
};

The issue is that the first model query (models.Shops.findOne) is taking more than 3 seconds to respond, when the query models.Plans.findOne is only taking about 200ms.

I’ve already tested that the query is not the issue, since if I delete the first query, then the second query will also take around 3 seconds.

So I suspect it may have something to do with how Sequelize is handling connections to the DB.

Here is my models/index.js file:

const { Sequelize } = require("sequelize");
const path = require("path");
const fs = require("fs");

const sequelize = new Sequelize(process.env.POSTGRES_URI, {
  define: {
    freezeTableName: true,
    underscored: true,
    timestamps: false,
  },
  ssl: true,
  dialectOptions: {
    ssl: {
      require: true,
      rejectUnauthorized: false, // <<<<<< YOU NEED THIS
    },
  },
  operatorAliases: Sequelize.Op,
});

const db = {};
fs.readdirSync(path.resolve("server/models/"))
  .filter(
    (file) => file.indexOf(".") !== 0 && file !== "index.js" && file !== "views"
  )
  .forEach((file) => {
    const model = require(`./${file}`)(sequelize, Sequelize);

    db[model.name] = model;
  });

Object.keys(db).forEach((model) => {
  if ("associate" in db[model]) {
    db[model].associate(db);
  }
});

db.Sequelize = Sequelize;
db.sequelize = sequelize;

module.exports = db;

And my two models in case needed:

models/Shops.js

const Sequelize = require("sequelize");

module.exports = (sequelize) => {
  const Shops = sequelize.define(
    "Shops",
    {
      id: {
        type: Sequelize.UUID,
        autoIncrement: true,
        primaryKey: true,
      },
      shopName: {
        type: Sequelize.TEXT,
        field: "shop_name",
      },
      isInstalled: {
        type: Sequelize.BOOLEAN,
        field: "is_installed",
      },
      planId: {
        type: Sequelize.UUID,
        field: "plan_id",
      },
      updatedAt: {
        type: Sequelize.DATE,
        field: "updated_at",
        defaultValue: Sequelize.NOW,
      },
      createdAt: {
        type: Sequelize.DATE,
        field: "created_at",
        defaultValue: Sequelize.NOW,
      },
      deletedAt: {
        type: Sequelize.DATE,
        field: "deleted_at",
      },
    },
    {
      tableName: "shops",
    }
  );

  Shops.associate = (models) => {
    Shops.hasMany(models.Sessions, { as: "sessions", foreignKey: "shopId" });
    Shops.hasMany(models.ShopsMergers, {
      as: "shopMergers",
      foreignKey: "shopId",
    });
    Shops.belongsTo(models.Plans, { as: "plan", foreignKey: "planId" });
  };

  return Shops;
};

models/Plans.js

const Sequelize = require("sequelize");

module.exports = (sequelize) => {
  const Plans = sequelize.define(
    "Plans",
    {
      id: {
        type: Sequelize.UUID,
        autoIncrement: true,
        primaryKey: true,
      },
      name: {
        type: Sequelize.TEXT,
        field: "name",
      },
      price: {
        type: Sequelize.FLOAT,
        field: "price",
      },
      mergeLimits: {
        type: Sequelize.INTEGER,
        field: "merge_limits",
      },
      isDefault: {
        type: Sequelize.BOOLEAN,
        field: "is_default",
        defaultValue: "false",
      },
      terms: {
        type: Sequelize.TEXT,
        field: "terms",
      },
      description: {
        type: Sequelize.JSON,
        field: "description",
      },
      operationLimit: {
        type: Sequelize.INTEGER,
        field: "operation_limit",
      },
    },
    {
      tableName: "plans",
    }
  );

  Plans.associate = (models) => {
    Plans.hasOne(models.Shops, { as: "shop", foreignKey: "planId" });
  };

  return Plans;
};