Cleaner approach possible for append?

[rightaway]

If I want to use append because I want to have a dynamic column name (e.g. othercolumn1, othercolumn2), it looks like this.

    return sqlTemplateString`
      update thetable set
        thecolumn1 = null,`
      .append(`othercolumn${version}`)
      .append(sql` = null
      where
        id = ${id}
    `

Is it possible to write it somehow like below? I find it looks cleaner for multi-line statements. Would the sqlvalue function I've used below make a good feature request, if it's even possible to work that way?

    return sqlTemplateString`
      update thetable set
        thecolumn1 = null,
        othercolumn${sqlvalue(version)} = null
      where
        id = ${id}
    `

[felixfbecker]

This was actually the API in 1.x, you had to use SQL.raw()

[rightaway]

So instead of ${sqlvalue(version)} in my example above, ${SQL.raw(version)} would work?

Would you consider adding this syntax back to the 2.x API? I use mostly multi-line statements and SQL.raw would really help to preserve the indentation and make for easier to read statements, especially for larger or more complex ones.

[felixfbecker]

Hmm, apparently it would have complicated the implementation: https://github.com/felixfbecker/node-sql-template-strings/releases/tag/v2.0.0

[felixfbecker]

Honest question, do you really think this is that bad?

query(SQL`
  UPDATE thetable
  SET thecolumn1 = null, othercolumn`.append(version).append(SQL` = null
  WHERE id = ${id}
`)

[rightaway]

I think that append and SQL.raw have different ways they can contribute to making SQL statements that are quite clear to understand at a glance.

The example you used in the README is a perfect case for append, where the query is being built dynamically from various clauses (e.g. also in conditionals or loops). The append makes it quite readable, and I use it throughout my code in these cases.

const query = SQL`SELECT * FROM books`
if (params.name) {
  query.append(SQL` WHERE name = ${params.name}`)
}
query.append(SQL` LIMIT 10 OFFSET ${params.offset || 0}`)

SQL.raw however would be useful when substituting a small part of a query within a clause, like a table or column name, or part of it (like the version example I used).

query(SQL`
  UPDATE thetable
  SET thecolumn1 = null, othercolumn${SQL.raw(version)} = null
  WHERE id = ${id}
`)

In these cases, I think readability suffers when using append instead of SQL.raw as I try to piece the query together in my mind.

It's the same way why when building strings, the first (contrived) example below is more readable than the second.

`person ${name} is ${age} years old and lives in ${country}`
'person '.concat(name).concat(' is ').concat(age).concat(' years old and lives in ').concat(country)

If I were able to use only one of append or SQL.raw for all my queries, I think the readability and clarity of the queries would be less than optimal, but having both would cover all cases I can think of (at least throughout the code I'm working on which is using sql-template-strings with hundreds of queries).

[rightaway]

@felixfbecker Just wanted to follow up on this. It's still something that is a bit of a pain point in our projects and it would be great if the SQL.raw syntax were added back to the 2.x API, to get the benefits of clearer code I described in my post above.

[rightaway]

@felixfbecker To give you an example of one of various inconveniences caused by the lack of SQL.raw, there's this query

SQL`update mytable set`
  .append(`column${version}`)
  .append(SQL` = now()
  where othercolumn = ${value}`

This fails because set and column get appended because there's no space in the middle, so the query starts update mytable setcolumn instead of update mytable set column.

One benefit of ES6 template strings was to remove errors like this in regular string concatenation. So 'before' + value + 'after' may look fine at first glance, but of course it becomes 'beforeVALUEafter'. Template strings solve this before ${value} after, which becomes 'before VALUE after' as expected because it's easy to see.

That query would be much clearer in my opinion if it were

SQL`update mytable set
  column${SQL.raw(version)} = now()
  where othercolumn = ${value}`;

[felixfbecker]

I reviewed #45

[skyjur]

I too think append is not pretty and goes against nature of this project: making sql query statements more readable. I think small downside of slightly more complex implementation required to support raw() ought-weights readability benefits by far.

Also I think nested queries would be very great addition, to completely remove need for .append():

master...skyjur:master

function authorFilter(author) {
  return SQL`author = ${author}`;
}
function nameFilter(name) {
  return SQL`name = ${name}`;
}
const query = SQL`SELECT author FROM books WHERE ${authorFilter(
  author
)} AND ${nameFilter(book)}`;
typeof query; // => 'object'
query.text; // => 'SELECT author FROM books WHERE name =  AND author = '
query.sql; // => 'SELECT author FROM books WHERE name = ? AND author = ?'
query.values; // => ['harry potter', 'J. K. Rowling']

[WebReflection]

@rightaway @skyjur

My current PR would allow the following transformation:

SQL`
  UPDATE thetable
  SET thecolumn1 = null, "${'othercolumn' + version}`" = null
  WHERE id = ${id}
`

will result into

UPDATE thetable
SET thecolumn1 = null, `othercolumn123` = null
WHERE id = ?

It will also accept nested statements:

function authorFilter(author) {
  return SQL`author = ${author}`;
}
function nameFilter(name) {
  return SQL`name = ${name}`;
}
SQL`SELECT author FROM books
  WHERE ${authorFilter(author)}
  AND ${nameFilter(book)}`;

aggregating automatically all the ? and the passed values.

Hope that works for this ticket too. 👋

[msikma]

I would also greatly welcome this change. It's the one point where this library makes SQL statements harder to read rather than easier. Unfortunately it seems there's been no progress on the PR #104 since March 1.

[ajwinkworth]

I also seem to have come here hoping to suggest/add something like

SQL`SELECT (${a(columnName}) FROM ${a(tableName)} WHERE firstName = ${name}`;

To find it did exist with SQL.raw() is slightly saddening.

let a = SQL.raw;
SQL`SELECT (${a(columnName}) FROM ${a(tableName)} WHERE firstName = ${name}`;