Helper for join array of SQLStatements?

[Strate]

Just ran into this: need to join array of condition parts with and separator:

// I have this:
const parts: SQLStatement[] = [
  SQL`a = ${value1}`,
  SQL`b = ${value2}`,
]

// I need this:
SQL`a = ${value1} AND b = ${value2}`

It is not big deal to write function for that, but maybe it is better to have this one in the library? What do you think about it?

[Strate]

This is my implementation for that:

function joinStatements(separator: SQLStatement, parts: SQLStatement[]): SQLStatement {
    const result = SQL``
    for (let i = 0, count = parts.length; i < count; i++) {
        result.append(parts[i])
        if (i < count - 1) {
            result.append(separator)
        }
    }

    return result
}

[KaidenR]

I just ran into this same exact need. My use case is that I want my query to insert multiple rows like this:

INSERT INTO authors (id, user_handle, first_name, last_name) 
VALUES ($1, $2, $3, $4), ($5, $6, $7, $8), ($9, $10, $11, $12), ...

So I played around with monkey-patching a new appendAll() function which lets you pass in an array of SQLStatements or strings and an optional delimiter (defaults to a space):

SQLStatement.prototype.appendAll = function(statements:(SQLStatement|string|number)[], delimiter:string = ' ') {
  for (let i = 0; i < statements.length; i++) {
    this.append(statements[i])
      .append(i < statements.length - 1 ? delimiter : '')
  }
  return this
}

Which allowed me to do:

const query = sql`INSERT INTO authors (id, user_handle, first_name, last_name) VALUES `
  .appendAll(authors.map(a => sql`(${a.id}, ${a.authorHandle}, ${a.userHandle}, ${a.firstName}, ${a.lastName})`), ', ')

Another option would be add this functionality to the .append() function instead of to a new .appendAll().

@felixfbecker What do you think? I'd love to submit a PR (along with tests and type updates) if you're okay with this.

[felixfbecker]

For anyone stumbling upon this, appendAll is equivalent to

toAppend.reduce((prev, curr) => prev.append(delimeter).append(curr), statement)

[Janpot]

For me, the main difference between .append and a join function (like pg-template-tag has) is mutability. join leaves the original query untouched, which leads to a program that is easier to reason about.

[trygveaa]

@felixfbecker: Like @Janpot said, it would be really nice with a join function. This would make inserting multiple rows as simple as:

const queryValues = SQL.join(values.map((value) => SQL`(${value})`), ", ")
const query = SQL`INSERT INTO table (value) VALUES ${queryValues}`

This is really easy to read and write IMO, allows you to be immutable and you don't have to check if you are on the last value or not to know if you should add a ,. Are you opposed to adding support for this?