How to perform a search with conditional where parameters using Sequelize

Question

Usually whenever I write a search query for SQL, I do something similar to this:

SELECT * FROM users u
WHERE (@username IS NULL OR u.username like '%' + @username + '%')
AND (@id IS NULL OR u.id = @id)

Basically this simulates a conditional WHERE clause. We only want to compare @searchParam to the column if @searchParam was provided.

Is there a way to replicate this using Sequelize?

EDIT: Here is my best attempt which fails:

models.user.findAll({
  where: {
    username: searchParams.username || models.sequelize.col('user.username'),
    id: searchParams.id || models.sequelize.col('user.id')
  }
})

UPDATE: I found a way to do it, but it feels like a workaround. I'm certain there has to be a more elegant way. This works, but is ugly:

models.user.findAll({
  where: [
    '(? IS NULL OR "user"."username" LIKE ?) AND (? IS NULL OR "user"."id" = ?)',
    searchParams.username,
    `%${searchParams.username}%`,
    searchParams.id,
    searchParams.id
  ]
})

Answer 1

You can just prepare object with needed conditions. Simple and easy to understand

var whereStatement = {};
if(searchParams.id)
    whereStatement.id = searchParams.id;
if(searchParams.username)
    whereStatement.username = {$like: '%' + searchParams.username + '%'};
models.user.findAll({
  where: whereStatement
});