Introduction

If you want to handle a SQL Database in NodeJS, you may want to use Sequelize.

It’s a nice ORM with a promise based API that makes it easy to:

• defines models
• defines relations between those models
• retrieves those relations when accessing an instance.

But I find it hard to handle COUNT and SUM functions inside instances even
after reading issues, trying without success to find the Sequelize way®.
Nothing was working for me 😭

And so this is the story of how I solved it with squel, a SQL query generator

• Some knowledge into the sequelize API will really helps understanding this article 🤓
• the server’s framework used is Koa
• I’ve put together a little repository containing a working example

the database

models

we will have 2 models:

• basket
  • with a name
• items
  • with a name
  • with a price

A basket will have many items

So let’s define our models using Sequelize:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

const Sequelize = require("sequelize");

const sequelize = new Sequelize(`postgres://localhost:5432/sequelize-example`);

const Basket = sequelize.define(`basket`, {
  id: {
    type: Sequelize.UUID,
    defaultValue: Sequelize.UUIDV4,
    primaryKey: true
  },
  name: {
    type: Sequelize.STRING
  }
});

const Item = sequelize.define(`item`, {
  id: {
    type: Sequelize.UUID,
    defaultValue: Sequelize.UUIDV4,
    primaryKey: true
  },
  name: {
    type: Sequelize.STRING
  },
  price: {
    type: Sequelize.FLOAT,
    allowNull: false
  }
});

Item.Basket = Item.belongsTo(Basket);
Basket.Items = Basket.hasMany(Item);

sequelize.sync();

instances

And we will need to define our instance:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Basket.create(
  {
    name: `fruits`,
    items: [
      {
        name: `apples`,
        price: 17.3
      },
      {
        name: `bananas`,
        price: 22.5
      }
    ]
  },
  {
    include: [Basket.Items]
  }
);

expected result

• the number of items inside the basket
• the total price of the basket

So something like this:

1
2
3
4
5
6
7
8

[
  {
    "id": "f0bf0e03-368b-405f-9434-4720efa2728f",
    "name": "fruits",
    "itemsCount": 2,
    "totalPrice": 39.8
  }
]

doing this server side

It should be quite simple:

• query the basket with his items
• process everything to have the right information

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

router.get(`/baskets`, async (ctx, next) => {
  const baskets = await Basket.findAll({
    include: [Basket.Items]
  });
  const result = baskets.map(basket => {
    const withCount = basket.toJSON();
    withCount.itemsCount = withCount.items.length;
    withCount.totalPrice = withCount.items.reduce(
      (total, item) => total + item.price,
      0
    );
    delete withCount.items;
    return withCount;
  });
  ctx.body = result;
});

you can find the equivalent code in the demo.

But it’s a work that can be done on the Database right?
So better doing it there.

the SQL sub-query

In order to have this done with our Database, we need Sequelize to generate something like this in the query:

1
2
3
4
5
6
7
8
9
10

(
  SELECT CAST(COUNT(*) AS int)
  FROM items AS item
  WHERE ("item"."basketId" = "basket"."id")
) AS "itemsCount"
(
  SELECT SUM("item"."price")
  FROM items AS item
  WHERE ("item"."basketId" = "basket"."id")
) AS "totalPrice"

We could have written this manually but we have NodeJS by our side, and its full ecosystem.

Se let’s go for squel which does just this: write SQL in a more JS way.

Interfacing Squel with Sequelize

According Sequelize documentation this is how we can define custom attributes:

1
2
3
4
5

Model.findAll({
  attributes: {
    include: [[sequelize.fn("COUNT", sequelize.col("hats")), "no_hats"]]
  }
});

The main goal here will be to generate the right query for the computed attribute

squel configuration & caveats

• we must configure squel to support postgres Database
• even if there is a lot of escaping options I didn’t find one that cover all the use case
  Postgres will fail with WHERE (item.basketId = basket.id)
  → we should format it like this WHERE ("item"."basketId" = "basket"."id")
• enclose our result with parenthesis because Sequelize won’t do it for us
  → (…our query) AS "itemsCount"

All those can be done quite easily with a few helpers:

1
2
3
4
5
6

// use the postgres flavour
const squel = require("squel").useFlavour("postgres");

// custom formatter
const fieldRegExp = /([a-zA-Z]*)\.([a-zA-Z]*)/g;
const formatQuery = query => `(${query.replace(fieldRegExp, `"$1"."$2"`)})`;

Minoring some slight differences this is the equivalent code in the demo.

I’m using Sequelize.static() but I’m not sure it’s necessary. It just prevents Sequelize from escaping the query string.

putting things together

And that will be our final code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

const COUNT_ITEMS = formatQuery(
  squel
    // don't use squel `autoQuoteAliasNames`
    .select({ autoQuoteAliasNames: false })
    // force integer on count
    .field(`CAST(COUNT(*) AS int)`)
    .where(`item.basketId = basket.id`)
    .from(`items`, `item`)
    .toString()
);

router.get(`/baskets`, async (ctx, next) => {
  const basket = await Basket.findAll({
    attributes: {
      include: [[COUNT_ITEMS, `itemsCount`]]
    }
  });
  ctx.body = basket;
});

and the related part in the demo

further notes

find our WHERE query

I’m not a SQL expert so how to write our WHERE query?

• configure Sequelize to output the SQL queries in the console
• make Sequelize fetch a model with his relations
• look at your logs
• copy/paste the interesting parts

build a sub-query generator

Writing all the squel code can be cumbersome.
But we can just make a function that will do that for us:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

const createSubQuery = ({ field, model, relation }) => {
  const query = squel
    .select({ autoQuoteAliasNames: false })
    .field(field)
    .where(`${relation}.${model}Id = ${model}.id`)
    .from(`${relation}s`, relation)
    .toString();
  return `(${formatQuery(query)})`;
};

const COUNT_ITEMS = subQuery({
  field: `CAST(COUNT(*) AS int)`,
  model: `basket`,
  relation: `item`
});

const SUM_ITEMS = subQuery({
  field: `SUM(item.price)`,
  model: `basket`,
  relation: `item`
});

router.get(`/baskets`, async (ctx, next) => {
  const basket = await Basket.findAll({
    attributes: {
      include: [[COUNT_ITEMS, `itemsCount`], [SUM_ITEMS, `totalPrice`]]
    }
  });
  ctx.body = basket;
});

the related code in the demo

conclusion

Sequelize is a very fine piece of code. For 95% of the time it will just work as expected.
For the 5 other percents you can write raw SQL queries 🤓 or use squel to do it for you 🤪

By doing so we have removed the burden of processing the Sequelize result in our server, and that’s a win 😎