It's Safe To Use Empty Arrays With The $in Operator In Sequelize
I'm used to writing SQL by hand. Which is why traditionally, when interpolating data into an IN() clause, I prefix a "0" or "-1" in order to ensure that the resultant IN() clause doesn't end up empty (which would throw a SQL exception). When I moved over to a team that uses Sequelize, I started doing the same thing with the $in operator. Upon seeing this, however, my teammate Casey Flynn told me that this wasn't necessary. As it turns out, the Sequelize library is smart enough to handle empty arrays with the IN() clause. Since this wasn't obvious to me (and doesn't appear to be documented well), I thought it would be worth sharing.
I don't like ORM (Object-Relational Mapping) tools specifically for this reason - it creates a layer of indirection between me and the SQL. As far as I'm concerned, an ORM just becomes two more things that I have to learn: the ORM API and the way in which the ORM generates SQL statements. In this case, I had to learn about the special handling that the ORM, Sequelize, used to deal with the $in operator.
To investigate the inner workings, I set up a small demo that passes empty and non-empty arrays to an $in operator. Then, I looked at the SQL statements that were generated from the various tests:
// Require the core node modules.
var chalk = require( "chalk" );
var Sequelize = require( "sequelize" );
var util = require( "util" );
// ----------------------------------------------------------------------------------- //
// ----------------------------------------------------------------------------------- //
// Setup our Sequelize instance.
var sequelize = new Sequelize(
"****",
"****",
"****",
{
host: "localhost",
dialect: "mysql",
logging: logSqlExecution
}
);
// Define the ORM (Object-Relational Mapping) models.
var FriendModel = sequelize.define(
"FriendModel",
{
id: {
type: Sequelize.DataTypes.INTEGER(0).UNSIGNED,
allowNull: false,
primaryKey: true,
autoIncrement: true
},
name: {
type: Sequelize.DataTypes.STRING(30),
allowNull: false
}
},
{
tableName: "friend",
timestamps: false
}
);
// ----------------------------------------------------------------------------------- //
// ----------------------------------------------------------------------------------- //
Promise.resolve()
.then(
function handleResolve() {
console.log( chalk.red.bold( "\nUsing $in with populated array:" ) );
// Here, the $in operator will be translated to a SQL IN() clause, using
// the given array to create a comma-delimited list of values.
var promise = FriendModel.findAll({
where: {
id: {
$in: [ 1, 2, 3 ]
}
}
});
return( promise );
}
)
.then(
function handleResolve( friends ) {
console.log( chalk.bold( "Friends:" ) );
console.log( unwrap( friends ) );
}
)
.then(
function handleResolve() {
console.log( chalk.red.bold( "\nUsing $in with empty array:" ) );
// In this case, we're passing an empty array to $in. My FEAR was that this
// would be converted into an empty IN() clause, which would throw a SQL
// exception. However, Sequelize is smart enough to handle an empty array as
// a special case - it generates an IN() clause that contains a NULL value.
var promise = FriendModel.findAll({
where: {
id: {
$in: []
}
}
});
return( promise );
}
)
.then(
function handleResolve( friends ) {
console.log( chalk.bold( "Friends:" ) );
console.log( unwrap( friends ) );
}
)
.then(
function handleResolve() {
console.log( chalk.red.bold( "\nUsing direct property comparison with array:" ) );
// BONUS LEARNING: You can generate IN() clauses without the $in operator.
// If you do a direct property comparison with an array, it will implicitly
// create an $in-type comparison.
var promise = FriendModel.findAll({
where: {
id: [ 4, 5, 6 ] // Notice there's no $in operator here.
}
});
return( promise );
}
)
.then(
function handleResolve( friends ) {
console.log( chalk.bold( "Friends:" ) );
console.log( unwrap( friends ) );
}
)
;
// ----------------------------------------------------------------------------------- //
// ----------------------------------------------------------------------------------- //
// I provide a custom logger for the SQL used by Sequelize.
function logSqlExecution( value ) {
// Split each query "section" onto its own line for easier reading.
console.log( chalk.dim( value.replace( /\b(SELECT|FROM|WHERE)\b/g, "\n $1" ) ) );
}
// I convert an array of Sequelize instances into an array of plain-old objects.
function unwrap( records ) {
var plainRecords = records.map(
function operator( record ) {
return( record.get({ plain: true }) );
}
);
return( plainRecords );
}
As you can see, I have two $in operator tests, one that accepts a collection of IDs and one that accepts an empty array. And, as a bonus, I am also demonstrating that you can use collections with direct property comparisons (without the $in operator). Now, when we run this code through Node.js, we get the following terminal output:
As you can see, when we pass an empty array to the $in operator, Sequelize pushes a NULL value into the resultant IN() clause. This prevents the IN() clause from throwing a SQL exception while at the same time also ensuring that no records are accidentally returned (since no value in the column will ever match NULL - not even NULL). This makes it perfectly safe to use empty arrays with the $in operator.
The more you try to decouple portions of your application, the more important IN()-based queries become since you move the JOINs out of the database and into the application layer. It's nice to know that Sequelize handles arrays gracefully, allowing you to safely pass empty arrays to the $in operator without it causing a SQL exception (or unexpected results).
Want to use code from this post? Check out the license.
Reader Comments