Sequelize: IS NULL / IS NOT NULL

Sequelize has a neat syntax for turning SQL WHERE clauses into JSON objects. The following shows how to create IS NULL and IS NOT NULL queries.

IS NOT NULL

For “IS NOT NULL”, try this:

 db.Alert.find({
    where: {
      UserId: req.params.user_id,
      url: {
        $ne: null
      }
    }})
    .then(
      (alert) => {
        // handle result
      }
    );

This renders the following SQL (formatted for readability):

SELECT 
   "id", "title", "text", "domain", 
   "lastRun", "lastSeen", "url", 
    "createdAt", "updatedAt", "UserId" 
FROM "Alerts" AS "Alert" 
WHERE "Alert"."UserId" = '1' 
AND "Alert"."url" IS NOT NULL 
LIMIT 1;

Note: Quoting the column names forces everything to be case sensitive in Postgres.

This will force you to quote columns and tables when you query your own database.

IS NULL

You can also query for “is null”, like so:

db.Alert.find({
  where: {
    UserId: req.params.user_id,
    url: null
  }})
  .then(
    (alerts) => {
      res.render('embed', { alerts: alerts });
    }
  );

And there you have it!

6 Replies to “Sequelize: IS NULL / IS NOT NULL”

  1. so you can say
    where: {
    zzz: null
    }
    but what if I just want to pull listings where zzz is not null.
    can I say
    where: {
    zzz: is not null
    }
    ? Thanks in advance for your response.

  2. You have a typo on the “Is not null” part, it says For “IS NULL”, try this:
    thanks for your post, it was very helpful.
    Regards

Leave a Reply

Your email address will not be published. Required fields are marked *