Nathan Evans Nathan Evans - 2 months ago 17
Node.js Question

pg-promise inserting text with $()

I'm having trouble inserting text into my database when the text contains the string "$(...)"
as my code return the Error: Property '...' doesn't exist.

const pgp = require('pg-promise')({ promiseLib: bluebird });
const db = pgp(process.env.DATABASE_URL);
let values = [{text: 'this is fine'}, {text: 'this fails $(...)'}];
let cs = new pgp.helpers.ColumnSet(['text']);
let query = pgp.helpers.insert(values, cs);
db.manyOrNone(query);


Is there some sort of "this is just text" property I'm missing?

Thanks

EDIT
Error was only occurring when using $() syntax to add other variables to the whole SQL call

'use strict';
const bluebird = require('bluebird');
const pgp = require('pg-promise')({ promiseLib: bluebird });
const db = pgp('postgres://localhost/okeydokey-local');

db.any(
'CREATE TABLE text_table ( ' +
'text_column text ' +
')'
);
let values = [{ text_column: 'this is fine' }, { text_column: 'this fails $(test)' }];
let cs = new pgp.helpers.ColumnSet(['text_column'], {table: 'text_table'});
let query = pgp.helpers.insert(values, cs);
console.log(query);
db.manyOrNone(query +
'some more SQL dependent on $(somethingElse)',
{
somethingElse: 'someValue'
}
);


Output

insert into "text_table"("text_column") values('this is fine'),('this fails $(test)')
Unhandled rejection Error: Property 'test' doesn't exist.

Answer

The following line generates the final query:

let query = pgp.helpers.insert(values, cs);
//=>insert into "text_table"("text_column") values('this is fine'),('this fails $(test)')

It is not a query template for further formatting, it is supposed to be executed directly.

In your code you are trying to format the final query, which breaks trying to locate variable test in your formatting object.