Pg-promise: Should I rely on ColumnSet.names method in Query Files?

[illarionvk]

I'm using Query Files in my app to keep the code clean and build verbose queries, e.g. parents + children tree queries.

I rely on ColumnSet.names to provide the column names to the query, but the documentation states the method is for internal use.

Is there a better way or should I continue using cs.names?

Query:

var data = {
  'first_name': 'Test',
  'last_name': 'Testing'
}
var cs = new pgp.helpers.ColumnSet(keys(data))
var params = {
  names: cs.names,
  data: pgp.helpers.values(data, cs),
  sets: pgp.helpers.sets(data, cs)
}
db.one(sql.upsertItem, params).then(reply).catch(reply)

Query file:

/* Upsert an item */
INSERT
  INTO items(${names:raw})
  VALUES ${data:raw}
  ON CONFLICT(id)
  DO UPDATE SET
    ${sets:raw},
    updated_at = now()
  RETURNING *

[vitaly-t]

There is no problem using it this way, the code looks correct. And as for property ColumnSet.names being for internal use, it used to be that way, then later on became exposed. Perhaps I should remove that internal use note, to avoid confusion.

Type ColumnSet represents most value for multi-row inserts and updates. When you plan only on single-row inserts and updates, its value isn't that great, unless you have lots of columns, and/or need to implement additional logic for manipulating values. For sure it is always cleaner to use it.

Will your code work though for multi-row operations? I'm not sure it will, as the sets would need to be prefixed with something like excluded.. Actually, I did consider adding such a feature, to provide a prefix automatically, just haven't done it yet.

Anyway, you are on the right track! :wink:

[illarionvk]

My queries have up to 10-12 columns and the number of columns with actual data may vary in partial update requests.

The data is a sanitized output from Joi validator, where the defaults are set and some fields are stripped, e.g created_at.

With cs.names and pgp.helpers, I can rely on the data object shape to build the query.

I haven't tried to build multi-row operations yet. Thank you for the advice!

[vitaly-t]

I've never heard of joi validator before. But I suspect, what you do with it, you can do via ColumnSet natively, as it uses very flexible column configuration. See the Column syntax for details, plus ColumnSet code example ;)