Sequelize and MySQL debugging quick tips

In the last few months we happened to be working quite a lot with AWS Aurora Serverless and Sequelize, which is a ORM that makes your backend code and your query very easy to manage and maintain.

I'd like to note down and share some tips and tricks that might come useful:

Sequelize Options

Expecially while debugging or writing tests it is very useful to make all the requests as verbose as possible, since you want to see how the Relational Mapping is being handled and how your javascript code and the data object you passed to it are converted to MySQL.

It is also very useful to understand better MySQL queries, associations among tables and different filters.
These are some useful constructor options

   benchmark: true,
   logging: (...msg) => console.log(msg),
   logQueryParameters: true

Benchmark options is, well, telling you how long each individual query to your Database took.

Logging accepts either False, or a function to log/print the query being executed. If you just pass console.log though, you will not see precisely the values that are dynamically replaced by sequelize.
To really see exactly the parameters of the query, and the real MySQL script sent to the db ( which you could then also copy and paste into your DB IDE of choice - like for example Dbeaver - for further testing and shorter feedback loop)
logQueryParameters is printing you might want to use
logQueryParameters: true and log all of them in the logging function. (...msg) => console.log(msg)

Format those scripts!

This is actually very embarrassing.
While trying to make sense out of the long queries spit out by the verbose log I spent quite some time to format the script by hand. Then I google for an alternative, and of course I found a mysql formatter online.
Then, as soon as i found it... i asked myself, "Hey.. now that i think of it, something so simple must exist in DBeaver"
And of course it does!
Just copy paste your script, right click and select format.

Doh!

Diff them!

While working on a big project with lots of queries and associated tables we ended up refactoring and optimizing the
queries many times over time. Often we wrote benchmarking scripts to try out different configurations in Sequelize and we ended up ( thanks to the verbose log above) with long scripts that we were comparing to understand the differences.
After formatting them, diffing them was very useful to spot Inner Joins or WHERE vs ON conditions.
Here a simple online tool that does that.

Execution Plan / Flow

Diving into the Execution plan / flow of the script is also very interesting and helpful to understand why your query became very slow just by adding another table, or a condition on an association.

Junction tables

Often if you have many to many relationship (m:n) in your response you don´t need the data of the junction table ( expecially if there is no additional info than the Ids from the main tables). You can tell Sequelize to avoid sending them and save some data and parsing.
Just add an empty through to your include:

include: [
 {
  association: 'creatives',
  through: {
            attributes: []
            }
  }
 ]
}

Plain

Sequelize returns a Model for each row, so you can interact with it using Sequelize API, lazily load associations, get raw data or data where getters and setters were applied, edit and update data.

The problem is that if you just want your final object you have to call .get() on every property ( and if you have arrays of data coming from association, you also need to iterate a .get() on those (they are also models)

    const plainObjects =  results.map(v => v.get({plain: true}))

is much better than:

    const plainObjects = results.map(object => {
        const plainObj = object.get()
        plainObj.nestedObjects = plainObj.nestedObjects.map(nested => nested.get())
        plainObj.someOtherNestedObjects = plainObj.someOtherNestedObjects.map(nested => nested.get())
       // and so on for every possible nested associated table
        return plainObj
    })

```



### Other tips
#### Advanced filters
[Here](https://dev.to/dvddpl/filter-operators-in-react-admin-and-sequelize-11c0) I explained how we dealt with complex filters and operators

Expecially when working with filters I would suggest checking out the differences between [root level where and association level](https://sequelize.org/master/manual/eager-loading.html) where because it can have very big impact on the final query.

### Subqueries


```
 subQuery: false 
```


Sequelize is very smart when it comes to associations and Joins,  sometimes though, you can give it a boost and explicitely tell it how do handle specific nested / sub queries. Under certain circumstances it could really boost performance.
This parameter in the Query Option is quite undocumented, so just try that out, check the diffs in the final mysql script and check the benchmark if you can benefit from it.

####RTFM
Sequelize is an awesome tool, and it has a very extensive documentation. 
Dont just look at them [manual/docs](https://sequelize.org/master/), really dig into the [API reference](https://sequelize.org/master/identifiers.html) to find all the minor details that really make the difference in your queries / database models.  And eventually dig into the [source code](https://github.com/sequelize/sequelize/blob/main/lib/model.js) to really understand the _magic_ that is going on!

Comments

[Rehan van der Merwe]

[Opinion] My number 1 tip is to not use ORM.

What is your thoughts around that?

I have just found that pure SQL is better within the team as it does not require to learn another framework/library, you have much better control over meaning you explicitly tell SQL what you want, so it is really simple. With ORMs they *might always do some joins unknowingly or write unoptimized queries missing the indexes. It also makes migrations difficult *if you ever need to change data stores.

[Davide de Paolis]

I kinda expected this comment :-)
i don't have a strong opinion about it. we had a similar project 3 years ago where we used plain SQL scripts and it was quite straightforward at the beginning, but as the project grew we ended up with lots of boilerplate, redundant code, very hard to debug parts and writing complex performant queries was also quite difficult, especially for junior/regular fullstack dev ( which normally don't have a deep understanding/knowledge of DBs).
With the latest project we gave ORM a try and I really like how easy it was to set up tables/models. ( and even with migration / changes to the tables) i find it interestingly easy. Furthermore you have a lot of utilities ready and validation and hooks and the "language" is easier to grasp for devs.

Of course this comes to a cost, learning how that specific library does things ( because Sequelize is different from TypeORM for example) and it generally keeps you away from understanding the real thing ( pure SQL) which imho is more important. (that's why i started digging a lot in the debugging part and in the generated queries ) .
In the end as usual, it is a balance between project size, team member skill level and overall speed/deadlines.

[Rehan van der Merwe]

Excellent reply! :) Agree with it 100%, might give it a try next time or in a side project and see how it goes.

Thanks I did not consider the scenario of a language/framework being easier to learn for Juniors than actually learning SQL.