When developing an API, using an ORM like Sequelize can help speed up the process, keep the code readable and facilitate data managing. But that comes with some frustrating downsides that make some simple tasks seem impossible. You would not expect Sequelize to manage all kinds of queries, so you naturally use its raw query function to get things done, but it does not offer any integration with defined models, which on a complex project can make you question the use of an ORM.
Here I will describe my approach to enable Associations on Raw Queries and Sequences to group data. Proceed to the end if you only want to know about Raw Queries.
For this example, I am going to generate sequences with MySQL and MariaDB.
First, let’s define “Sequence” as a list of numbers ordered from x to y. There are many ways to generate sequences in MySQL and MariaDB, but I picked two simple ones to keep things clear.
Generating Sequences:
With that in mind, let’s say you have a table named users with the following schema:
If you needed to count users for each age defined in a range(x, y), also keeping track of ages with 0 users, you could:
In MariaDB:
In MySQL:
You can execute them as raw queries, but as I said before, without the ease of defined models.
My solution involves creating a dynamic model generator to execute the above queries with parameters.
MariaDB Solution
With MariaDB’s Sequence Storage Engine, the only thing we have to do is set the table name according to our range needs.
MySQL Solution + Raw Query Associations
Note that Sequelize quotes all identifiers on a query, making it so that our subquery won’t execute, so we will have to bypass this mechanism:
This code keeps Sequelize from quoting table names starting with “(“, now our SubQuery will work as expected.
In the end, it’s just code, the only limit to what you can do with Sequelize is the amount of work you want to put into it.
