3

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.
Community
  • 1
  • 1
artis3n
  • 810
  • 2
  • 12
  • 23

2 Answers2

2

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.

Community
  • 1
  • 1
artsmc
  • 134
  • 1
  • 8
  • The variable formatting allowing `IN ($2^)` is interesting. It does clean up the query a bit, but adds some obscurity surrounding what `pgp.as.csv` actually does. In that case, I think my original code is clearer in its intent. We're also actually fine with how the data is outputted - it's passed to our `cb` callback function and handled without a problem elsewhere. – artis3n Jul 31 '15 at 05:12
  • @eugene1832, `pgp.as.csv` takes an array of values and converts it into a properly formatted comma-separated list of values that can be passed into a function or `WHERE IN(...)` like in this case. – vitaly-t Aug 01 '15 at 23:37
  • @artsmc, in the example you provided, use of a shared connection is absolutely pointless, it only needlessly complicates the example. A regular `db.query` would do the job just fine. – vitaly-t Aug 02 '15 at 00:37
  • @vitaly-t i also mention that a regular db.query would work just fine. I personally used the shared connection to extend some functionality i did not provide an example of that – artsmc Aug 02 '15 at 16:28
0

My question was asking if there was a way to use the node-postgres library in way that cleaned up our params creation code before the query. However, from the several deleted answers as well as the remaining one, it seems like we're being ornery and those few extra lines aren't that big of a deal and that this is the best way to write this code. So, I'm marking this question "answered," although now it appears that it wasn't the greatest question and perhaps we shouldn't have asked it in the first place.

artis3n
  • 810
  • 2
  • 12
  • 23
  • Perhaps [some basic examples of using parameters within pg-promise](https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example#parameters) will clarify the matter for you. If not, you can always ask a question on the [pg-promise home page](https://github.com/vitaly-t/pg-promise). – vitaly-t Aug 01 '15 at 23:44
  • Let me clarify: I meant only that using pg-promise for the variable formatting does take away 3 lines of previous code, but I've exchanged three lines of clearly written code for an entire library's worth of contents just for that one feature. That's what I meant when I said the original code is clearer in its intent than the provided example using pg-promise. Following a principle like [YAGNI](http://blog.codinghorror.com/kiss-and-yagni/), in this case I don't think using the pg-promise library is the better solution. – artis3n Aug 03 '15 at 03:42
  • Perhaps, but you get a higher level of a consistent abstraction that will benefit you everywhere else ;) And besides, `WHERE IN` is just one special case where standard parameter formatting has to be tweaked, so it wouldn't affect the `cleanness` of your code in general ;) – vitaly-t Aug 14 '15 at 12:29