How to Insert in SQL with NodeJs and mssql library with params

Question

I have 2 arrays, the first one contains the columns for the insert and the second one contains de values

const columns = ['columnName1','columnName2','columnName3','columnName4','columnName5','columnName6'];

const values2 = ['test1', 'test2', 27, 1, 'an address', null];

module.exports = {columns, values2}

And I'm trying to do the insert using mmsql library but not even the documentation contains a sample with multiple columns to insert values to.

This is a sample of my insert

let pool = await sql.connect(config)
      

const result = await pool.request().query(`INSERT INTO [Test].[TableName] (${testData.columns}) VALUES ?`, [[testData.values2]]);
console.dir(result);

And I'm getting the following error:

RequestError: Incorrect syntax near '?'.

I was able to select to the db with no issues but there's something about the syntax that it doesn't seem to like..

Answer 1

At least you forgot to wrap ? with round brackets:

const result = await pool.request().query(`INSERT INTO [Test].[TableName] (${testData.columns}) VALUES (?)`, [[testData.values2]]);

Answer 2

As I can't find a single example, where you pass the parameters of your sql-query to the query function or use ? as a placeholder in their documentation, I'm not sure, whether node-mssql supports this. But if it does, you should at least create as many ? in the sql statement as you add values.

var cols = [...]; //your columns
const sqlquery = `insert into sometable (${cols}) values (${new Array(cols.length).fill('?')})`;

The documented way would be using input parameters

await pool.request()
   .input('p1', sql.Int, value1)
   .input('p2', sql.VarChar(50), value2)
   .query('insert into sometable(col1, col2) values(@p1, @p2)');

To keep this flexible you could use something like the following

var cols = ['col1', 'col2'];
var vals = ['a string', 23];

const request = pool.request();
for (let i = 0; i < vals.length; i++)
  request.input(`param_${i}`, vals[i]);  //if you skip the parametertype, mssql will guess it from the value
const sqlquery = `insert into sometable(${cols}) values (${vals.map((_,i) => `@param_${i}`)`;

const result = await request.query(sqlquery);