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

items

A basket will have many items

So let’s define our models using Sequelize:

instances

And we will need to define our instance:

expected result

the number of items inside the basket

the total price of the basket

So something like this:

doing this server side

It should be quite simple:

query the basket with his items

process everything to have the right information

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:

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:

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")

Postgres will fail with → we should format it like this 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:

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:

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:

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 😎