How to use dynamic where clause with placeholder in SQL for node REST API

Question

I want to build a SQL query such that the filter clauses can be added dynamically with placeholder.

Ex :

router.get('/test',(req,res) => {
    const {name , account , id} = req.query
    const nameFilter = name === '' ? ':nameVal' : `and username = :nameVal` 
    const accountFilter = account === '' ? ':accountVal' : `and accountnumber = :accountVal`

    const result = connection.execute(' SELECT * FROM WHERE ID = :idVal ' + nameFilter + accountFilter),
      {
        nameVal : name,
        accountVal : account,
        idVal : id 
      },
    }
  res.send(result.rows)
)

Now the issue is, when the query parameter has data, the filters are working perfectly, but when passing an empty string, I get a SQL error

illegal variable name/number

What could be the best solution to design the query with such requirements?

When query parameters are passed with value or no value the filter could work dynamically, having placeholder in the query.

score 0 · Answer 1 · answered Apr 19 '23 at 19:53

I don't know if I understand, but when you pass only ':nameVal', you are doing this 'SELECT * FROM TABLE WHERE ID = :idVal :nameVal', it would be better not to pass any condition to the query, so it looks like this:

const nameFilter = name === '' ? '' : `and username = :nameVal` 
const accountFilter = account === '' ? '' : `and accountnumber = :accountVal`

score 0 · Answer 2 · answered Apr 19 '23 at 19:59

To handle the case where a query parameter is an empty string, you can use the OR logical operator in your filter clauses.

Ex:

router.get("/test", (req, res) => {
  const { name, account, id } = req.query;
  const nameFilter = name === "" ? ":nameVal" : `and username = :nameVal`;
  const accountFilter =
    account === "" ? ":accountVal" : `and accountnumber = :accountVal`;

  const query =
    "SELECT * FROM table WHERE ID = :idVal " + nameFilter + accountFilter;
  const queryParams = {
    idVal: id,
    nameVal: name || null,
    accountVal: account || null,
  };

  const result = connection.execute(query, queryParams);
});

Please use the above code.

How to use dynamic where clause with placeholder in SQL for node REST API

2 Answers2