Parameterized/Prepared Statements usage pg-promise Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of Parameterized/Prepared Statements usage pg-promise without wasting too much if your time.

The question is published on by Tutorial Guruji team.

I’m using koa v2 with pg-promise. I try to do a simple SELECT 2 + 2; within a Parameterized/Prepared Statement to test my setup:

// http://127.0.0.1:3000/sql/2
router.get('/sql/:id', async (ctx) => {
  await db.any({
    name: 'addition',
    text: 'SELECT 2 + 2;',
  })
    .then((data) => {
      console.log('DATA:', data);
      ctx.state = { title: data }; // => I want to return num 4 instead of [Object Object]
    })
    .catch((error) => {
      console.log('ERROR:', error);
      ctx.body = '::DATABASE CONNECTION ERROR::';
    })
    .finally(pgp.end);

  await ctx.render('index');
});

Which is rendering [Object Object] in the templates and returning this to the console from pg-monitor:

17:30:54 connect(postgres@postgres)
17:30:54 name="addition", text="SELECT 2 + 2;"
17:30:54 disconnect(postgres@postgres)
DATA: [ anonymous { '?column?': 4 } ]

My problem:

I want to store result 4 in ctx.state. I don’t know how can I access it within [ anonymous { '?column?': 4 } ]?

Thank You for your help!

Edit:

I found another recommended(1) ways(2) to dealing with named parameters in the official wiki.

// http://127.0.0.1:3000/sql/2
router.get('/sql/:id', async (ctx) => {
  const obj = {
    id: parseInt(ctx.params.id, 10),
  };
  await db.result('SELECT ${id} + ${id}', obj)
    .then((data) => {
      console.log('DATA:', data.rows[0]['?column?']);
      ctx.state = { title: data.rows[0]['?column?'] }; // => 4
    })
    .catch((error) => {
      console.log('ERROR:', error);
      ctx.body = '::DATABASE CONNECTION ERROR::';
    })
    .finally(pgp.end);

  await ctx.render('index');
});

I changed the any object to result, which returning the raw text. Than I access number 4 like a javascript object. Am I doing something wrong? Is there another way to access this value?

What is the recommended, more faster, safer way of usage?

Answer

Since you are requesting just one value, you should use method one:

const {value} = await db.one({
    name: 'addition',
    text: 'SELECT 2 + 2 as value',
}); // value = 4

And for such example you cannot use types PreparedStatement or ParameterizedQuery, because they format query on the server side, and PostgreSQL does not support syntax like $1 + $1.

The real question is – do you really need those types?

We are here to answer your question about Parameterized/Prepared Statements usage pg-promise - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji