Parameterized/Prepared Statements usage pg-promise

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([email protected])
17:30:54 name="addition", text="SELECT 2 + 2;"
17:30:54 disconnect([email protected])
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?