The longer title of this article is 5 things about Sequelize that had me scratching my head when I was making an API using Node and Express. I had never used Sequelize before and had not done any programming on Node.js either. So, I ran into a few issues which took me a while to solve. I’m making a list of it here so that people wanting to accomplish the same thing as me would not need to scratch their head for hours the way I did.

Many to Many relationship

One to Many and One to One relationships are pretty straightforward to create on Sequelize. It took me a while to wrap my head around the many to many relationship. Note: I am using the words models, and tables interchangeably here.

Here’s how to do it. Suppose you have two tables Issues and Users. Consider the scenario: A user can vote on many issues, and an issue can be voted on by many users. So, with the intermediate table vote, you have a many to many relationship between Issues and Users.

Now here’s the thing that troubled me, if there’s already a relationship between Issues and Users, creating the new association alone will not do the trick. You have to mention an alias for it as well. For example, I had already a relation between Issues and Users that said one user can create many issues.

So what I needed to do was, add the following association to Issues model:

Issues.belongsToMany(models.Users, {
       as: 'Vote',
       through: models.Votes
});

The Votes model is the intermediate table that holds the foreign keys of both the Issues and Users table, whereas Vote is the alias for the relationship. I needed to add the following in the other model of Many to Many relationship (i.e. in Users model).

Users.belongsToMany(models.Issues, {
      as: 'Vote',
      through: models.Votes
});

And there you have it, many to many relationship between the two tables through the intermediate table Votes.

Note that you will need to mention the alias when you’re making a query.

Issues.findAll({ 
	include: [{model: Users, as: 'Vote',through: {}}]
      })

But, what if you’ve already an existing relationship/association between the models? How do you query both the associations then?

Apparently, it’s pretty easy (now that I know): just mention both of them, one with the alias and one, without.

Issues.findAll({ 
	include: [
		{model: Users, as: 'Vote',through: {}}, 
		{model: Users, attributes: ["firstName", "lastName", "user_photo"]}
	         ]
       })

beforeUpdate doesn’t work?!!

I was using Passport.js to handle the JWT based authentication mechanism. The way it worked was, when the user created his account through a POST request, the plain-text password would be encrypted via the module bcrypt before creating a record in the table User. I did this by adding the encryption code in the beforeCreate hook of Users model.

hooks: {
	beforeCreate: user => {
		const salt = bcrypt.genSaltSync();
		user.password = bcrypt.hashSync(user.password, salt);
                }
       }

A user could also change his password by updating his record via the PUT request, which meant, I had to encrypt the plain-text password before updating a user record as well.

I added the exact same code under a beforeUpdate hook as follows.

hooks: {
	beforeCreate: user => {
		const salt = bcrypt.genSaltSync();
		user.password = bcrypt.hashSync(user.password, salt);
	        },
	beforeUpdate: user =>{ 
		const salt = bcrypt.genSaltSync();
		user.password = bcrypt.hashSync(user.password, salt);
		}
	}

But it didn’t work.

What was missing was the individualHooks: true parameter. So, when making an update query, I had to do as following.

Users.update(req.body, {
             where: {id: req.user.id
             },
	     individualHooks: true
      })

And now the plain-text passwords were being encrypted during update as well.

Multiple queries using Promises

I had to make two queries one after another, and return both the results combined.

First, I needed to query for the issues, and include the users model’s info. Then I needed to find out if the current user had voted on the issue. I could not do both of these in a single query. I used findOne method to find an issue, whereas count method to determine if the user had voted on the issue.

Sequelize.Promise.all([
	Issues.findOne({
		include: [{
			model: Users,
			attributes: ["firstName", "lastName", "user_photo"],
			duplicating: false
		}],
		where: {
			id: req.params.id
		}
	}),

	Votes.count({
		where: {
			issue_id: req.params.id,
			user_id: req.user.id
		}
	}),
]).spread(function(result, count) {
	//do stuff
})

Instead of chaining queries inside the functions one after another, Promises is the way to go. In the above code, there are two methods findOne and count, that run one after another on different models. Then, I have used spread to handle the results. The first parameter result holds the result of the first method Issues.findOne whereas, the second parameter count holds the result of the second method Votes.count.

How do I combine both the results? That’s the next point.

Custom response / Editing a result before sending as response

You cannot modify the results directly. You first need to convert them to JSON.

What I did inside the spread function above is this:

if (result.id != null) {
	var result = result.toJSON();
	result.user_vote = count;
	return res.json(result);
} else {
	res.sendStatus(404);
}

If the result exists, then convert  it to JSON, and then insert the count value to a new key user_vote. Send it as response. Otherwise, send a 404 error response.

If it didn’t work for you, for example, you may get an error saying there’s no toJSON method, then you can define it in the instanceMethods and send custom response from there as well.

Count function returns a single row / record / result

This had me struggling for a long time. I could retrieve all the records whenever I sent a GET request to /issues. But as soon as I added [Sequelize.fn(‘COUNT’, Sequelize.col(‘Vote.id’)), ‘count’] function, it would only return a single record / row.

The problem, as I figured out, was because I had not specified a group by parameter. Doing so solved it, and I could get all the records, along with the count I wanted.

I grouped on the basis of the main model’s primary key i.e. Issue’s primary key id.

However, as soon as I added limit:10 to the query, the problem reappeared.

Going through the same github issue I linked above, adding duplicating: false solved the problem at the end.

So, my final code was like this. It includes three models, groups the result by Issues.id, selects the records based on a where criteria that is not shown here, orders the records by updated_at date in descending order, and returns only 10 records at a time.

Issues.findAll({

		group: ['Issues.id'],

		include: [{
				model: Users,
				as: 'Vote',
				attributes: [
					[app.db.sequelize.fn('COUNT', app.db.sequelize.col('Vote.id')), 'count']
				],
				through: {
					attributes: []
				},
				duplicating: false
			}, {
				model: Users,
				attributes: ["firstName", "lastName", "user_photo"],
				duplicating: false
			}, {
				model: Categories,
				attributes: ['title'],
				duplicating: false
			},

		],


		where: criteria,
		order: [['updated_at', 'DESC']],
		limit: 10

	})

 

No related posts.