Sequelize Manual - Part 1


In this post, I'm going to do something easy. I still have corona so I'm not going to do any actual programming but rather just read the sequelize manual and point out some of the things I thought was interesting.

I have used the documentation before and it's all pretty high quality, however I only ever read the parts relevant to the problem I had at that moment so this will be the first time I read a manual from start to finish.

For those that want to follow along at home:

First off, the main page is great as it simply has the command to install sequelize. There is already something of note. The --save in the command is one that I always ignored. I know --save-dev marks the module as a dev dependency which I imagine uses the dev environment variable to decide if it should install or not. However the default is saving to the dependencies which I imagine --save is doing. The flag seems pointless if that is indeed true.

To learn more about the flag I did npm help but it wasn't very helpful.

Instead it looks like there was a time when npm could install packages but not update package.json. This has been changed such that npm now by default updates package.json. There is the --no-save flag which will install a package but not update the package.json.

Now that I learned something new, let's move on to the actual documentation.

The front page of the documentation is fantastic as it starts with a piece of code that you can simply copy and paste into a javascript file and run it.

Most of the code is self explanatory, we create a object that maps to a table and then we create a new user in that table. We are also using an in memory sqlite database to hold the data.

In this post I'll go over the core concepts chapter by chapter. Tomorrow I'll go over the advanced association concepts. I'll probably stop there.

Getting Started

This page has the steps to connect to a database, of which there are three. I'm, curious why. The database connection string seems like the one that should be used but I do prefer the option of passing in each property separately and relying on sequelize to format the URL. I guess it's just nice to have the option of both.

There is also a testing function to check if the database connection works. I've never actually used it but could be handy to do a quick test. Though I'd probably just open up the .env file with the database credentials and then type in the connection stuff and connect directly to the database.

There is a function to close the connection. Hopefully the connection gets closed automatically when the application dies because other wise I've been leaving them open forever

The sequelize ORM also takes in a logging function which is neat. I haven't run into this before. The default is to send all of the SQL queries to the console using console.log but in production I have it set to false so that it doesn't output any of the queries.

The async/await syntax makes it extremely simple to use sequelize. I'm curious what the world was like pre-async. I live it and I remember callback hell but I don't truly remember how bad it was.

Model Basics

The way sequelize works is that you set up a model that reflects the table and this is then mapped to all the various SQL queries you can run. I wonder why the creation of sequelize models can't be automatic. If the table already exists, it should generate the models for me as the data already exists in the table. If the table doesn't exist then I can still use sequelize to write out all the different attributes.

There is a package to do just that!

The section on table name inference is a good explanation of how things work but I'm curious why it exists. The idea is that a table is a collection of data and so it makes sense to have a users table. However apparently programmers want to use the singular uppercase of the table name when we run queries. I get the uppercase part as it is a class when we reference sequelize models but the singular part not so much.

The users table is referenced as User through sequelize which I don't see as being helpful. This is a bit of magic. Users.findAll and Users.create is just as fine. You can use the freezeTableName to force sequelize to define the model and the table with the exact same name but I'm curious why it isn't default. I'm sure there's a good reason and that very well may be that the vast majority of programmers prefer it.

Model synchronization! This was actually adding way too much time to my application's start up. I had always used this line but never thought about what it did. Can you tell I have never written a production grade piece of software in node?

Sequelize.sync is a function that creates tables if they don't exist. You can also pass in force which causes tables to be dropped before being re-created. There is also an alter option which will cause sequelize to set up any attributes that get added to the model.

Just like I'm not a fan of table name inference, I'm also not a fan of using sync. But damn does it make life easier. I've been manually creating columns and then adding them to my models file when I could have used the alter flag. I was wondering why the sync wasn't working so reading this has cleared up quite a bit.

I am a fan of the fact that sequelize will automatically add the create date and update at fields. I almost always want these fields and I like that they get update automatically when using sequelize. This is implemented in the sequelize level instead of as triggers and I'm not sure why. In my head it makes sense to just make these triggers but maybe there are issues with dropping and re-creating triggers programmatically when models change.

The issue becomes that because they aren't triggers, you can't trust that updates didn't occur directly in the database. If you skip sequelize and do raw sql, the updated at field won't be updated. This could be a problem and so you would want to create your own updated at field that is a trigger and you can then tell sequelize to skip using timestamps.

There is a number of sections about the various types and options available when you set up attributes. I however think it would be better to manually write your sql to create attributes and then reflect that into the model rather than write the model out and have the database reflect that.

Model Instances

Interesting note here is that the create function in sequelize is actually two steps. The first being build which sets up the object and then a save function that actually generates and runs the sql query. I wonder that cases there are where you want to build the object but not save it. Maybe to do some sort of validation as the validation might be baked into the model perhaps. Actually pretty useful the next day, when I want an empty object so I can re-use an existing update form, I need manually fill in the fields with blanks. By using the defaultValue option and the build I can just have sequelize generate a pre-built object to pass the form.

Also learned about the toJSON function which is going to definitely come in handy. I've been logging the sequelize models forever and just scrolling through all the stuff that is on those objects, but the toJSON function sets up just the stuff we actually want to see. This exercise in reading the manual is already coming in handy!

Updating has a very curious effect, it only updates the fields that it is given so if you set fields outside the update function, those fields aren't communicated to the database. To do that, you need to use the save function. In this case, it seems that picking the syntax is important as you could screw things up if you don't understand the different. I think calling the save function makes more sense and is probably safer to make sure all of the changes are persisted.

There is a reload function! That's pretty useful as so far I've just re-run the queries to get an updated row. I think the benefit here would be that the query probably uses the primary key and so it is as fast as it can be. I sometimes will re-run the exact same query so there is probably space for optimization for me by calling reload. Very useful!

Sequelize will also not run update queries if nothing has changed. This is a bit strange as I would like to maybe touch the row to change the updated at field. This could be useful from an audit standpoint to track when someone tried to save things, even if they didn't change anything. On the other hand this is a contrived scenario and I could probably get the functionality by using triggers and raw queries.

There is also increment and decrement functions where you can update fields numerically without concurrency issues. I'm guessing increments will get persisted to the database immediately after being called instead of waiting for a save call.

It looks like I was a bit ambitious in how much I would actually cover and I am getting worse. I could technically write more in the morning but I'm going to not. I'll continue with the rest of the core concepts in the next post and push the advanced stuff further. We'll see how much of this stuff will actually stick!