Knex select static "column" as alias

Question

I'm trying to implement the following query in Knex using Postgres, in order to return a static "$type" column (for providing a type hint to a GraphQL server):

select *, 'Patrol' as "$type" from patrol;

When I use the Knex query builder, it's mangling the quotes:

knex('patrol')
  .select(['*', `'Patrol' as "$type"`])
  .where('id', 12345)
  .first()

Returns

ERROR:  column "'Patrol'" does not exist at character 11
STATEMENT:  select *, "'Patrol'" as """$type""" from "patrol" where "id" = $1 limit $2

I can construct the query using knex.raw(), but I really don't want to have to do that:

knex.raw(
  `SELECT *, 'Patrol' as "$type" FROM patrol WHERE id = '${value}' LIMIT 1;`
)

How should I be constructing the select() statement so that the query builder interprets it correctly?

Solution 1

I was able to make it work by using knex.raw() inside the select:

knex('patrol')
  .select(knex.raw(`*, 'Patrol' as "$type"`)
  .where('id', 12345)
  .first()

Solution 2

Doesnt this work (https://runkit.com/embed/g5h8qwmeyoyh)?

const Knex = require('knex');

const knex = Knex({
  client: 'pg'
});

knex('patrol')
  .select('*', 'Patrol as $type')
  .where('id', 12345)
  .toSQL()

// select *, "Patrol" as "$type" from "patrol" where "id" = ?

Or are you really trying to add string literal Patrol with alias '$type' to each row? If so raw is way to go like this to have dialect escaping / quotes right (https://runkit.com/embed/12av9qxxwgyj):

require('sqlite3');
const Knex = require('knex');

const knex = Knex({
  client: 'sqlite',
  connection: ':memory:'
});

await knex.schema.createTable('test', t => {
  t.increments('id').primary();
  t.string('data');
});

await knex('test').insert([{ data: 'foo' }, { data: 'bar' }]);

console.dir(
  await knex('test').select('*', knex.raw('? as ??', ['Patrol', '$type']))
);

Comments

[grahamb]

Yup, adding 'Patrol' as a string literal with alias "$type" is exactly what I'm trying to do. I was able to accomplish it using raw.