Avoid using raw SQL for PgPromise inline query formatting using columnset

Question

 const userPhoneNumber = await transaction.one(pgp.as.format(`${pgp.helpers.update({
      modifiedById: login.objectId,
      modifiedTimestamp: now,
      phoneNumber
    }, columnSets.userPhoneNumbers.forUpdateById)} WHERE object_id = $/objectId/ AND removed = $/removed/ AND userId = $/userId/ RETURNING *`, { objectId, removed: false, userId }));

Right now I am doing this in my code. I want to be able to

1. Utilize column set
2. Use pgp.update method along with column set to generate SQL

However, I am not sure how to utilize query file here as well. How can I use query file so I can avoid using RAW SQL string in my javascript code?

Right now I can only think of doing query file like so

{statement:raw} WHERE object_id = $/objectId/ AND removed = $/removed/ AND userId = $/userId/ RETURNING *

But this feels abit hackish to inject partial raw statement.

Answer 1

First, you declare statically your ColumnSet, object according to the columns and the table that you use. For example:

const cs = new pgp.helpers.ColumnSet(['col1, col2', 'col3'], {table: 'my-table'});

When it is time to execute the query, you prepare your WHERE condition like this:

const where = pgp.as.format(' WHERE object_id = ${objectId} AND removed = ${removed} AND
  userId = ${userId} RETURNING *', {objectId, removed, userId});

Then you can generate a complete UPDATE sql like this:

const updateSql = pgp.helpers.update(data, cs) + where;

Now you can execute that sql.

I am not sure how to utilize query file here

In your example there isn't much point in using an external query file, though you can, if you want, as explained further.

You can create the following file:

${update:raw} WHERE object_id = ${objectId} AND removed = ${removed} AND 
userId = ${userId} RETURNING *

Then you load it like you would any other QueryFile:

const qf = new pgp.QueryFile(path, options);

And then you can generate a complete SQL query like this:

const updateSql = pgp.as.format(qf, {
    update: pgp.helpers.update(data, cs),
    objectId,
    removed,
    userId
});