Node-Postgres SELECT WHERE IN dynamic query optimization

Question

We're working on a Node/Express web app with a Postgres database, using the node-postgres package. We followed the instructions in this question, and have our query working written this way:

exports.getByFileNameAndColName = function query(data, cb) {

  const values = data.columns.map(function map(item, index) {
    return '$' + (index + 2);
  });

  const params = [];
  params.push(data.fileName);
  data.columns.forEach(function iterate(element) {
    params.push(element);
  });

  db.query('SELECT * FROM columns ' +
    'INNER JOIN files ON columns.files_id = files.fid ' +
    'WHERE files.file_name = $1 AND columns.col_name IN (' + values.join(', ') + ')',
    params, cb
  );

};

data is an object containing a string fileName and an array of column names columns. We want this query to extract information from our 'columns' and 'files' tables from a dynamic number of columns. db.query takes as parameters (query, args, cb), where query is the SQL query, args is an array of parameters to pass into the query, and cb is the callback function executed with the database results.

So the code written in this way returns the correct data, but (we think) it's ugly. We've tried different ways of passing the parameters into the query, but this is the only format that has successfully returned data.

Is there a cleaner/simpler way to pass in our parameters? (e.g. any way to pass parameters in a way the node-postgres will accept without having to create an additional array from my array + non-array elements.)

Asking this because:

1. perhaps there's a better way to use the node-postgres package/we're using it incorrectly, and
2. if this is the correct way to solve this type of issue, then this code supplements the answer in the question referenced above.

Answer 1

Hello I tried to translate "but (we think) it's ugly" I believe my response answers your question. In that same question you reference you will find this response

In which the user takes the pg-promise with special-case variable formatting

In your case it may look something like this using shared connection but in your example I would actually recommend using a plain db.query Im just using the shared connection to show you how i extended the "ugly":

exports.getByFileNameAndColName = function query(data,cb) {
  var sco; 
  const params = [];
  params.push(data.fileName);
  data.columns.forEach(function iterate(element) {
    params.push(element);
  });
  db.connect()
  .then(function(obj){
    sco=obj;
    return sco.query('SELECT * FROM columns ' +
      'INNER JOIN files ON columns.files_id = files.fid ' +
      'WHERE files.file_name = $1 AND columns.col_name IN ($2^)',
    pgp.as.csv(params)));
  },function(reason){
    console.log(reason);
  })
  .done(function(){
    if(sco){
        sco.done();
        cb();
    }
  });

};

Now again I'm not sure what you meant by ugly but in my use case the return format was something like this:

{
  column:[
         {
          id: data,
          data: data,
          col_name: data,
          files_id: data,
          fid: data,
          files_name: data
         },...
   ]
}

And in my case I really wanted this:

{
      column:[
              {
              id: data,
              data: data,
              col_name: data,
              files_id: data,
              },...
      ],
      file:[
            {
             fid: data,
             files_name: data
            },...
      ]

    }

So in order to do that I took the same shared connection and added a extra variable to manage the results. Now this may not answer your question or I just might be on to something but I suggest looking into pg-promises it could be helpful for advance queries and formatting.