Repository: tediousjs/node-mssql
Pull request #593 (2018-01-05 14:46:55)

Add support for passing array to tagged template litteral
When passing an array to a string litteral template, each item of this array is prepared.

This let you do as follow :

```
connector.query`SELECT * FROM table WHERE name in (${array})`
```

Instead of :
```
connector.query`SELECT * FROM table WHERE name in (${array[0]},${array[1]},${array[2]}...)`
connector.query`SELECT * FROM table WHERE name in (${array.map((v, i) => `@array${i}`).join(', ')})`
```

patriksimek: Thank you, I'm marking this with 5.0 milestone since this is a breaking change.
blaskovicz: Can we update the docs as well as part of this change?
patriksimek: This changes the behavior of how array parameters are serialized into the statement. Currently, an array appears as one parameter. This PR makes an array to appear as multiple parameters split by a comma. That's ok unless there's type coercion happening on the path to the database.

In other words, we need to be sure what's happening with arrays in the current code. If I pass an array like `["a", "b", "c"]` as the value of a parameter, is it going to throw? If not, then it's a breaking change because that means that arrays are cast to something else (maybe `null`, maybe a string like `a, b, c`. It needs some testing.
dhensby: Hmmm. My opinion would be that if passing an array didn't do anything *useful* then this is an enhancement not a breaking change (who is relying on their array being cast as `null` as a feature, that's an unintended side effect of anything). If we make no commitment as to how arrays are handled (IE: it's not a declared API) then we shouldn't be too worried about declaring it's behaviour now.

But I completely agree we need to test that to make sure that we aren't breaking something that could have been useful

Patch:
diff --git a/lib/base.js b/lib/base.js
index d479702..8f0a311 100644
--- a/lib/base.js
+++ b/lib/base.js
@@ -999,8 +999,21 @@ class Request extends EventEmitter {
 
     for (let index = 0; index < values.length; index++) {
       let value = values[index]
-      this.input(`param${index + 1}`, value)
-      command.push(`@param${index + 1}`, strings[index + 1])
+      // if value is an array, prepare each items as it's own comma separated parameter
+      if (Array.isArray(value)) {
+        for (let parameterIndex = 0; parameterIndex < value.length; parameterIndex++) {
+          this.input(`param${index + 1}_${parameterIndex}`, value[parameterIndex])
+          command.push(`@param${index + 1}_${parameterIndex}`)
+          if (parameterIndex < value.length - 1) {
+            command.push(', ')
+          } else {
+            command.push(strings[index + 1])
+          }
+        }
+      } else {
+        this.input(`param${index + 1}`, value)
+        command.push(`@param${index + 1}`, strings[index + 1])
+      }
     }
 
     if (method) {


Test patch:
diff --git a/test/common/templatestring.js b/test/common/templatestring.js
index 04f5a9d..7391d42 100644
--- a/test/common/templatestring.js
+++ b/test/common/templatestring.js
@@ -20,6 +20,15 @@ module.exports = (sql, driver) => {
 
         done()
       }).catch(done)
+    },
+
+    'array params' (done) {
+      let values = [1, 2, 3]
+      sql.query`select 1 as col where 1 in (${values});`.then(result => {
+        assert.strictEqual(result.recordset[0].col, 1)
+
+        done()
+      })
     }
   }
 }
diff --git a/test/common/unit.js b/test/common/unit.js
index 475c6d0..751bea7 100644
--- a/test/common/unit.js
+++ b/test/common/unit.js
@@ -211,4 +211,23 @@ describe('Unit', () => {
       command: 'select * from myTable where id = @param1'
     })
   })
+
+  it('tagged template arrays', () => {
+    function query () {
+      const values = Array.prototype.slice.call(arguments)
+      const strings = values.shift()
+      const input = []
+      return {
+        input: input,
+        command: sql.Request.prototype._template.call({
+          input () { input.push(Array.prototype.slice.call(arguments)) }
+        }, strings, values)
+      }
+    }
+
+    assert.deepEqual(query`select * from myTable where id in (${[1, 2, 3]})`, {
+      input: [['param1_0', 1], ['param1_1', 2], ['param1_2', 3]],
+      command: 'select * from myTable where id in (@param1_0, @param1_1, @param1_2)'
+    })
+  })
 })
diff --git a/test/tedious/tedious.js b/test/tedious/tedious.js
index cef53bb..c583a7c 100644
--- a/test/tedious/tedious.js
+++ b/test/tedious/tedious.js
@@ -170,6 +170,7 @@ describe('tedious', () => {
 
     it('query', done => TEMPLATE_STRING['query'](done))
     it('batch', done => TEMPLATE_STRING['batch'](done))
+    it('array params', done => TEMPLATE_STRING['array params'](done))
 
     after(done => sql.close(done))
   })