[JavaScript] Tagged SQL Template strings gaps

[themilkman]

What happened?

I've noticed a few points where the JS/TS code is not detected correctly (tested with JS+SQL from master):

SQL`"${this.tableName}"."createdAt" <= $${values.length}` // first ${} section + dollar signs after <=
  
SQL`EXISTS (
        SELECT 1 FROM unnest("${this.tableName}"."${columnName}") AS tag
        WHERE tag ILIKE $${paramCounter}
      )` 

Beside that: really cool that this generally works out of the box!

[deathaxe]

Current MySQL contains a pattern to consume $$ as statement terminator. Maybe someone can point me to some specs about it. I've found it in PostgreSQL's spec only. It starts so-called dollar-quoted strings.

Or should unquoted $${...} resolve to an (deprecated) identifer starting with dollar sign?

[keith-hall]

Current MySQL contains a pattern to consume $$ as statement terminator. Maybe someone can point me to some specs about it.

https://dev.mysql.com/doc/refman/9.6/en/srjs-management.html

This happens to be the first result I found, I think it doesn't just apply to JS procedures. It also seems that the syntax is \$\w*\$, so we may need to tweak the current match rule...

[deathaxe]

Notation is the same in ProstgreSQL, except it is explicitly called a dollar quoted string literal.

see: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

The example of https://dev.mysql.com/doc/refman/9.6/en/srjs-management.html indicates identical use cases and treatments.

Strictly speaking, it would be another string type then, which may contain arbitrary content or languages, not only SQL statements.

With regards to Python using with_prototype to embed SQL, adding another layer of embedded languages likely causes trouble with context sanity limits and makes all of it more fragile.

I'd suggest some simple hotfixes to give $${ precedence over $$ in JS template strings, only, in linked PR if we want to address all of OPs issues, as full support for $$ seems to be a bigger story needing more condideration on how to deal with it in sane ways.

[michaelblyons]

Are we leaving this open for $$ stuff, or is it done?

[deathaxe]

I'd keep it open as reminder of yet unresolved $$ issue.