How to bind a multidimensional array

leosuncin commented 2 months ago

I am trying to load some fixtures to be used in my integration and end-to-end tests

id	name
1	Children Bicycles
2	Comfort Bicycles
3	Cruisers Bicycles
4	Cyclocross Bicycles
5	Electric Bikes
6	Mountain Bikes
7	Road Bikes

I've tried this to reset the value of the fixtures

import sql from 'mssql';

const fixtures = [
  { id: 1, name: 'Children Bicycles' },
  { id: 2, name: 'Comfort Bicycles' },
  { id: 3, name: 'Cruisers Bicycles' },
  { id: 4, name: 'Cyclocross Bicycles' },
  { id: 5, name: 'Electric Bikes' },
  { id: 6, name: 'Mountain Bikes' },
  { id: 7, name: 'Road Bikes' },
];

const pool = await sql.connect({ /* options */})
const transaction = pool.transaction();

try {
  await transaction.begin();

  const request = transaction.request();

  await request.batch`SET IDENTITY_INSERT production.categories ON`;

  await request.batch`MERGE INTO production.categories AS target
  USING (
    VALUES ${fixtures.map((category) => [category.id, category.name])}
  ) AS source (id, name)
  ON target.id = source.id
  WHEN MATCHED THEN
    UPDATE SET name = source.name
  WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (source.id, source.name);`;

  await request.batch`SET IDENTITY_INSERT production.categories OFF`;
} catch (error) {
  console.error(error);
  await transaction.rollback();
} finally {
  await pool.close();
}

I've also tried with this other code, by converting the array into a string

import sql from 'mssql';

const fixtures = [
  { id: 1, name: 'Children Bicycles' },
  { id: 2, name: 'Comfort Bicycles' },
  { id: 3, name: 'Cruisers Bicycles' },
  { id: 4, name: 'Cyclocross Bicycles' },
  { id: 5, name: 'Electric Bikes' },
  { id: 6, name: 'Mountain Bikes' },
  { id: 7, name: 'Road Bikes' },
];

const pool = await sql.connect({ /* options */})
const transaction = pool.transaction();

try {
  await transaction.begin();

  const request = transaction.request();

  await request.batch`SET IDENTITY_INSERT production.categories ON`;

  await request.batch`MERGE INTO production.categories AS target
  USING (
    VALUES ${fixtures.map((category) => `(${category.id}, '${category.name}')`).join(',\n')}
  ) AS source (id, name)
  ON target.id = source.id
  WHEN MATCHED THEN
    UPDATE SET name = source.name
  WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (source.id, source.name);`;

  await request.batch`SET IDENTITY_INSERT production.categories OFF`;
} catch (error) {
  console.error(error);
  await transaction.rollback();
} finally {
  await pool.close();
}

Expected behaviour:

I expect to run the following query

SET IDENTITY_INSERT production.categories ON

MERGE INTO production.categories AS target
USING (
    VALUES (1, 'Children Bicycles'),
    (2, 'Comfort Bicycles'),
    (3, 'Cruisers Bicycles'),
    (4, 'Cyclocross Bicycles'),
    (5, 'Electric Bikes'),
    (6, 'Mountain Bikes'),
    (7, 'Road Bikes')
) AS source (id, name)
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET name = source.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (source.id, source.name);

SET IDENTITY_INSERT production.categories OFF;

Actual behaviour:

Error when an array is passed

RequestError: Validation failed for parameter 'param1_0'. Invalid string.
    at /home/user/project/node_modules/.pnpm/mssql@10.0.2/node_modules/mssql/lib/tedious/request.js:650:29
    at /home/user/project/node_modules/.pnpm/mssql@10.0.2/node_modules/mssql/lib/base/connection-pool.js:371:41
    at processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'EPARAM',
  originalError: TypeError: Validation failed for parameter 'param1_0'. Invalid string.
      at Object.validate (/home/user/project/node_modules/.pnpm/tedious@16.7.1/node_modules/tedious/src/data-types/nvarchar.ts:142:13)
      at /home/user/project/node_modules/.pnpm/mssql@10.0.2/node_modules/mssql/lib/tedious/request.js:647:58
      at /home/user/project/node_modules/.pnpm/mssql@10.0.2/node_modules/mssql/lib/base/connection-pool.js:371:41
      at processTicksAndRejections (node:internal/process/task_queues:95:5),
  number: undefined,
  lineNumber: undefined,
  state: undefined,
  class: undefined,
  serverName: undefined,
  procName: undefined
}

Error when the array is converted to string

RequestError: Incorrect syntax near '@param1'.
    at handleError (/home/user/project/node_modules/.pnpm/mssql@10.0.2/node_modules/mssql/lib/tedious/request.js:384:15)
    at Connection.emit (node:events:518:28)
    at Connection.emit (node:domain:551:15)
    at Connection.emit (/home/user/project/node_modules/.pnpm/tedious@16.7.1/node_modules/tedious/src/connection.ts:1902:18)
    at RequestTokenHandler.onErrorMessage (/home/user/project/node_modules/.pnpm/tedious@16.7.1/node_modules/tedious/src/token/handler.ts:388:21)
    at Readable. (/home/user/project/node_modules/.pnpm/tedious@16.7.1/node_modules/tedious/src/token/token-stream-parser.ts:22:55)
    at Readable.emit (node:events:518:28)
    at Readable.emit (node:domain:551:15)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3) {
  code: 'EREQUEST',
  originalError: Error: Incorrect syntax near '@param1'.
      at handleError (/home/user/project/node_modules/.pnpm/mssql@10.0.2/node_modules/mssql/lib/tedious/request.js:382:19)
      at Connection.emit (node:events:518:28)
      at Connection.emit (node:domain:551:15)
      at Connection.emit (/home/user/project/node_modules/.pnpm/tedious@16.7.1/node_modules/tedious/src/connection.ts:1902:18)
      at RequestTokenHandler.onErrorMessage (/home/user/project/node_modules/.pnpm/tedious@16.7.1/node_modules/tedious/src/token/handler.ts:388:21)
      at Readable. (/home/user/project/node_modules/.pnpm/tedious@16.7.1/node_modules/tedious/src/token/token-stream-parser.ts:22:55)
      at Readable.emit (node:events:518:28)
      at Readable.emit (node:domain:551:15)
      at addChunk (node:internal/streams/readable:559:12)
      at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3) {
    info: ErrorMessageToken {
      name: 'ERROR',
      handlerName: 'onErrorMessage',
      number: 102,
      state: 1,
      class: 15,
      message: "Incorrect syntax near '@param1'.",
      serverName: '73747810d065',
      procName: '',
      lineNumber: 9
    }
  },
  number: 102,
  lineNumber: 9,
  state: 1,
  class: 15,
  serverName: '73747810d065',
  procName: '',
  precedingErrors: []
}

The only way I've found so far it's to build the query first and the pass it to the library

import sql from 'mssql';

const fixtures = [
  { id: 1, name: 'Children Bicycles' },
  { id: 2, name: 'Comfort Bicycles' },
  { id: 3, name: 'Cruisers Bicycles' },
  { id: 4, name: 'Cyclocross Bicycles' },
  { id: 5, name: 'Electric Bikes' },
  { id: 6, name: 'Mountain Bikes' },
  { id: 7, name: 'Road Bikes' },
];

const pool = await sql.connect({ /* options */})
const transaction = pool.transaction();
const query = `MERGE INTO production.categories AS target
  USING (
    VALUES ${fixtures.map((category) => `(${category.id}, '${category.name}')`).join(',\n')}
  ) AS source (id, name)
  ON target.id = source.id
  WHEN MATCHED THEN
    UPDATE SET name = source.name
  WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (source.id, source.name);`;

try {
  await transaction.begin();

  const request = transaction.request();

  await request.batch`SET IDENTITY_INSERT production.categories ON`;

  await request.batch(query);

  await request.batch`SET IDENTITY_INSERT production.categories OFF`;
} catch (error) {
  console.error(error);
  await transaction.rollback();
} finally {
  await pool.close();
}

Configuration:

docker-compose.yaml

services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2022-latest
    env_file: .env
    healthcheck:
      test: /opt/mssql-tools/bin/sqlcmd -S "$${MSSQL_IP_ADDRESS:-localhost}" -U sa -P "$${MSSQL_SA_PASSWORD}" -Q "SELECT 1" -b -o /dev/null
      interval: 10s
      timeout: 3s
      retries: 5
      start_period: 10s
    ports:
      - '1433:1433'
    volumes:
      - sqlserver-data:/var/opt/mssql
volumes:
  sqlserver-data:

.env

ACCEPT_EULA=Y
MSSQL_SA_PASSWORD=EDehR7KKdcsndaFRJUwlEw
MSSQL_PID=Express
MSSQL_LCID=3082
MSSQL_TCP_PORT=1433
TZ=America/El_Salvador

database-config.ts

export default {
    database: process.env.MSSQL_DATABASE ?? 'master',
    password: process.env.MSSQL_SA_PASSWORD,
    port: Number.parseInt(process.env.MSSQL_TCP_PORT) || 1433,
    server: process.env.MSSQL_IP_ADDRESS ?? 'localhost',
    user: process.env.MSSQL_USER ?? 'sa',
    options: {
      encrypt: false,
      trustServerCertificate: true,
      enableArithAbort: true,
    },
  }

Software versions

NodeJS: 20.11.1
node-mssql: 10.0.2
SQL Server: mcr.microsoft.com/mssql/server:2022-latest

dhensby commented 2 months ago

Because you're using the tagged-template syntax, the library is trying to automatically parameterise your query, hence the error about param1_0.

As this fixture is trusted, you can skip parametirisation of the query by calling the function "traditionally":

import sql from 'mssql';

const fixtures = [
  { id: 1, name: 'Children Bicycles' },
  { id: 2, name: 'Comfort Bicycles' },
  { id: 3, name: 'Cruisers Bicycles' },
  { id: 4, name: 'Cyclocross Bicycles' },
  { id: 5, name: 'Electric Bikes' },
  { id: 6, name: 'Mountain Bikes' },
  { id: 7, name: 'Road Bikes' },
];

const pool = await sql.connect({ /* options */})
const transaction = pool.transaction();

try {
  await transaction.begin();

  const request = transaction.request();

  await request.batch`SET IDENTITY_INSERT production.categories ON`;

-  await request.batch`MERGE INTO production.categories AS target
+  await request.batch(`MERGE INTO production.categories AS target
  USING (
-    VALUES ${fixtures.map((category) => [category.id, category.name])}
+    VALUES ${fixtures.map((category) => `(${category.id}, '${category.name}')`).join(',\n')}
  ) AS source (id, name)
  ON target.id = source.id
  WHEN MATCHED THEN
    UPDATE SET name = source.name
  WHEN NOT MATCHED THEN
-    INSERT (id, name) VALUES (source.id, source.name);`;
+    INSERT (id, name) VALUES (source.id, source.name);`);

  await request.batch`SET IDENTITY_INSERT production.categories OFF`;
} catch (error) {
  console.error(error);
  await transaction.rollback();
} finally {
  await pool.close();
}

The library doesn't have any auto-parameterisation of arrays into values like that because the library is not a query builder and doesn't understand the context of the array being passed in (so it doesn't know to convert it to (@param0_1, @param0_2), (@param1_1, @param1_2), ... and instead just turns it into @param0_1, @param0_2, @param0_3, ... which isn't valid syntax for a MERGE statement.

tl;dr - your "built" query approach is correct.

leosuncin commented 2 months ago

@dhensby Is there a way to nest to use SQL fragments (like slonik does)? so you can tell the library not to escape an already escaped string

By example

await request.batch`MERGE INTO production.categories AS target
  USING (
    VALUES ${fixtures.map((category) => sql`(${category.id}, '${category.name}')`).join(',\n')}
  ) AS source (id, name)
  ON target.id = source.id
  WHEN MATCHED THEN
    UPDATE SET name = source.name
  WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (source.id, source.name);`;

dhensby commented 2 months ago

No, there isn't.

tediousjs / node-mssql