Parameterized/Prepared Statements usage pg-promise

Question

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 1

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?