pg-promise using named parameters on an update results in ReferenceError

Question

I feel like I'm missing something very simple here.

I'm attempting to use named parameters with pg-promise as outlined here.

I'm using this nested in a db.task function, editProductObj is obtained from an HTML form on my website. A MWE would be as follows:

db.task('edit-product', async t => {

    console.log(editProductObj)

    console.log(typeof(editProductObj))

    console.log('display' in editProductObj)

    // update product
    await t.none(`UPDATE
                      product
                  SET
                      display = ${display},
                      product_date = ${product_date}
                  WHERE
                      product_id = ${product_id}`, editProductObj)

}).catch(err=>{console.log(err)})

Which outputs this to console:

{
   display: 'true',
   product_date: 2018-01-01T00:00:00.000Z,
   product_id: '1'
} // object looks like what I expected
object // object is indeed an object
true // display is in the object passed

and also outputs the error:

ReferenceError: display is not defined

This makes me believe that the t.none function isn't accessing the editProductObj object properly as the console.log output suggests the input is fine.

Answer 1

You are using ES6 template strings, and not Named Parameters of pg-promise.

As documented, the library supports flexible syntax with its Named Parameters:

syntax $propName, with * being any of the following open-close pairs: {}, (), <>, [], //

And if you want to use Named Parameters from inside an ES6 template string, you need to use one of the library's alternative open-close-pairs, to avoid the syntax conflict:

• $(propName)
• $[propName]
• $<propName>
• $/propName/

You can only use syntax ${propName} from inside regular strings or external SQL files.

Examples

This one will work correctly:

await t.none(`UPDATE
                      product
                  SET
                      display = $/display/,
                      product_date = $/product_date/
                  WHERE
                      product_id = $/product_id/`, editProductObj)

And so will this one:

await t.none('UPDATE product SET display = ${display}, ' +
             'product_date = ${product_date} WHERE ' +
             'product_id = ${product_id}', editProductObj)

If you want to properly manage complex SQL, you should use it externally, via Query Files, and you will be able to use the ${propName} syntax there ;)