Handle sql comments inside the sql tagged template literal

[paulovieira]

Living up to the "Promotes writing raw SQL" motto, I was wondering how can we use sql comments (that is, -- a comment and /* a comment */) inside this library sql tagged template literal.

More concretely, using a simple table like

create table t_dummy(id int, name text, age int)

sometimes I would like to be able to turn this

let id = 1, name = 'abc', age = 50;
let query = sql`

INSERT INTO t_dummy(
  id
  ,name
  ,age
)
VALUES(
  ${id}
  ,${name}
  ,${age}
)

`;

await connection.query(query);

into this:

let id = 1, name = 'abc', age = 50;
let query = sql`

INSERT INTO t_dummy(
  id
  ,name
--  ,age
)
VALUES(
  ${id}
  ,${name}
--  ,${age}
)

`;

await connection.query(query);

The second js snippet above will fail because the query object created by slonik will still create 3 value bindings ($1, $2, $3):

{
    "sql": "\n\nINSERT INTO t_dummy(\n  id\n  ,name\n  --,age\n)\nVALUES(\n  $1\n  ,$2\n  ,$3\n)\n\n",
    "type": "SLONIK_TOKEN_SQL",
    "values": [
        1,
        "abc",
        50
    ]
}

However it will work if we delete the sql comment line in VALUES. In that case we get:

{
    "sql": "\n\nINSERT INTO t_dummy(\n  id\n  ,name\n  --,age\n)\nVALUES(\n  $1\n  ,$2\n\n)\n\n",
    "type": "SLONIK_TOKEN_SQL",
    "values": [
        1,
        "abc"
    ]
}

So the multiline javascript string given to the sql tagged template would have to be pre-processed somehow to have sql comments removed.

Would this be a useful addition to slonik? If so I'd like to contribute.

[gajus]

I cannot think of a straightforward way to implement this.

If you are available, give it a go – it would be a valuable contribution.

[vitaly-t]

This is why within pg-promise I use another module that I wrote - pg-minify, that shrinks SQL, while removing all comments, so that such things would not happen.

I've said it before, and can only repeat it that any serious database module needs proper use of external SQL files. Writing gigantic SQL inside code is a terrible idea. Not only it is unmanageable that way, but you cannot even handle comments like that, or join multiple SQL statements independently.

[gajus]

Eliminating the comments once you have the entire SQL is not the problem. The challenge is removing the associated values after they were bound. We cannot remove values before the query is ready to execute because there are no guarantees that the entire token is present, i.e. unsafe to make assumptions about start/ end of a comment.

[gajus]

🎉 This issue has been resolved in version 24.0.0 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀