CODEWITHSUNDEEP
node.jspostgresqlpgpg-promise
Blinhawk
Blinhawk

Reputation: 389

pg-promise. Query works when using raw query, but not when using helpers

I get an error when I use the below code:

  var rows = [{"alex", "matos"},{"john","carmack"}]
  const cs = pg.pgp.helpers.ColumnSet(
    ["name","lastname"],
    { table: "user.user_data" }
  );

  const query = pg.pgp.helpers.insert(rows, cs);

  pg.db
    .none(query)
    .then(data => {
      console.log("success");
      res.status(200).send("OK");
    })
    .catch(err => {
      res.status(400).send("error");
    });

error: relation "user.user_data" does not exist.

however, if I use raw query, it does detect the table:

var sql =
    "insert into user.user_data(name, lastname)" +
    "values($1,$2)";
    var values = [{"alex", "matos"},{"john","carmack"}];

    pg.db
    .none(sql, values)
    .then(data => {})
    .catch(err => {
        console.log(err);
        res.status(400).send("Error");
    });

here's my configuration file, where I get pg and db variables from:

const options = {
  promiseLib: promise,
  capSQL: true
};

//Postgres Connection
var pgp = require("pg-promise")(options);
var connectionString = `pg://${config.db.user}:${config.db.pass}@${
  config.db.url
}/${config.db.dbname}`;

var db = pgp(connectionString);

db.connect()
  .then(function(obj) {
    obj.done();
    console.log("connected");
  })
  .catch(err => {
    throw err;
  });


monitor.attach(options);

module.exports = {
  pgp: pgp,
  db: db
};

How can I fix this issue? I wanna use the first code because it's faster.

Thank you

edit: LOGS

INSERT INTO "user.user_data"("name","lastname") VALUES("alex", "matos")

how do i remove the quotes froms user.user_data?

Upvotes: 1

Views: 941

Answers (1)

Sookie Singh
Sookie Singh

Reputation: 1623

Try it like this:

const cs = pg.pgp.helpers.ColumnSet(
    ['name', 'lastname'], { table: { table: 'user_data', schema: 'user' } }
);

Or you can try like this:

const table = new pgp.helpers.TableName('user_data', 'user');
const cs = pg.pgp.helpers.ColumnSet(
    ['name', 'lastname'], { table }
);

More information here.

Upvotes: 2

Related Questions