How can I bind a variable to sequelize literal?

Question

I have this subquery that is used to check the existence of a column related to the source model.

const defaultingLoans = await Loan.findAll({
    where: {
      [Op.and]: database.sequelize.literal('EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" = 'pending')')
    }
  });

The query works fine but the value pending ideally won't be fixed so I'll like to have a variable there that can be used to query for different status.

How can I replace the pending string with a variable.

Concatenation didn't work here because Sequelize has a weird way of parsing concatenated SQL queries which result in an error. An example is here https://pastebin.com/u8tr4Xbt and I took a screenshot of the error here

Solution

You can turn defaultingLoans into a function which accepts an amount:

const defaultingLoans = amount => await Loan.findAll({
  where: {
    [Op.and]: database.sequelize.literal(`EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."amount" = ${amount})`)
  }
});

usage:

const loans = defaultingLoans(2000);

EDIT: Although it should be noted here that you need to sanitize or quote the amount getting passed into this function to avoid SQL injection attacks.

Comments

[Taplar]

Are you familiar with concatenating variables to strings?

[proton]

@Hydrothermal String concatenation doesn't work with sequelize literal

[Taplar]

What makes you say that? literal() is being given a string as an argument. It doesn't matter how it's constructed. Just that it is a valid string. literal('abc') and literal('ab'+ 'c') would both pass the same string to the method

[proton]

There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO

[proton]

There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO

[ic3b3rg]

That's because clause1 and clause2 are strings - you need to include quotes around the concatenated values when using strings

[proton]

Something like this database.sequelize.literal(EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" IN ("${clause1}", "${clause2}"))) yeah? Still gives me the same error.

[proton]

If not, can you help share a sample of what you're talking about, thanks.

[ic3b3rg]

I believe sequelize requires single quotes for strings, so try ('${clause1}', '${clause2}')

[Rishabh Jha]

This isn't exactly binding. It'll concatenate the param to the query string.

[Guido Dizioli]

Be careful with SQL Injection in this cases if this is exposed to users. Amount could be '2000; DELETE FROM "Instalments";'