Sequelize and MySQL debugging quick tips
Tags: mysql, fullstack, javascript, database
Published: 2021-05-25 17:06:36

---
title: Sequelize and MySQL debugging quick tips
published: true
description: some tips and tricks learned while working on a big project where we used Sequelize ORM
tags: #mysql, #fullstack, #javascript, #database

---

In the last few months we happened to be working quite a lot with AWS Aurora Serverless and [Sequelize](https://sequelize.org/v5/), 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](https://sequelize.org/v5/class/lib/sequelize.js~Sequelize.html#instance-constructor-constructor) 

```
   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](https://dbeaver.io/) - 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](https://www.dpriver.com/pp/sqlformat.htm).
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.  
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9swkxurjhbqu2qpnxwcj.png)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](https://www.diffchecker.com/diff) a simple online tool that does that.


### Execution Plan / Flow
Diving into the [Execution plan / flow](https://sqlflow.gudusoft.com/#/) 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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yvs4od016clco6loigx9.png)

### 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](https://sequelize.org/master/manual/advanced-many-to-many.html) 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!