CODEWITHSUNDEEP
postgresqlexpressknex.js
ikhvjs
ikhvjs

Reputation: 5977

Knex(with Expressjs) and postgresSQL: String is automatically double quoted in SQL by knex

I have a issue when I try to pass a value in a SQL using knex.

app.get('/home/:topbarMenuPath', (req, res)=> {
  const { topbarMenuPath } = req.params;

  var toStringQuery =
  db.select('tm1.menu_name','tm1.seq','tm1.menu_path','tm1.menu_id')
  .from('tb_menu as tm1')
  .join('tb_menu as tm2', function() {
    this.on('tm1.parent_menu_id', '=', 'tm2.menu_id')
      .andOn('tm2.menu_level', '=', 1)
      .andOn('tm1.menu_level', '=', 2)
      .andOn('tm2.menu_path','=',topbarMenuPath)
  }).toString()
  ;
  console.log('toStringQuery',toStringQuery);
});

If I pass ':topbarMenuPath' as 'hello', the console.log output would be shown below:

select "tm1"."menu_name", "tm1"."seq", "tm1"."menu_path", "tm1"."menu_id" 
from "tb_menu" as "tm1" 
inner join "tb_menu" as "tm2" on "tm1"."parent_menu_id" = "tm2"."menu_id" 
and "tm2"."menu_level" = 1
and "tm1"."menu_level" = 2 
and "tm2"."menu_path" = "hello"

It seems that Postgres cannot recognize the double quote "hello", it shows an error when I try to send the SQL to Postgres. The error:

{
    "length": 166,
    "name": "error",
    "severity": "ERROR",
    "code": "42703",
    "position": "251",
    "file": "d:\\pginstaller_12.auto\\postgres.windows-x64\\src\\backend\\parser\\parse_relation.c",
    "line": "3359",
    "routine": "errorMissingColumn"
}

Is there any way that I can get a SQL with single quote hello like below?

and "tm2"."menu_path" = 'hello'

instead of

and "tm2"."menu_path" = "hello"

Upvotes: 1

Views: 1337

Answers (1)

Ruben Helsloot
Ruben Helsloot

Reputation: 13129

Yes, from the docs:

If you need to use a literal value (string, number, or boolean) in a join instead of a column, use knex.raw.

knex.select('*').from('users').join('accounts', 'accounts.type', knex.raw('?', ['admin']))
Outputs:
select * from `users` inner join `accounts` on `accounts`.`type` = 'admin'

For you, that means .andOn('tm2.menu_path', '=', knex.raw('?', [topbarMenuPath]))

Upvotes: 3

Related Questions