CODEWITHSUNDEEP
sqlnode.jssql-server
Muirik
Muirik

Reputation: 6289

Avoiding hitting SQL Server db multiple times via the `mssql` package in Node

I am using the mssql package in my Node app to query our SQL Server database. One thing I need to do is look up records from a table that match the IDs I have stored from a separate query to a different SQL database.

Because our SQL Server database is large and always growing, rather than get ALL the records, currently I am doing this to pull in only the records that match on an ID from my array:

const sourceArr = [
  {id: 1, name: "Joe", notes_detail_id: 123}, 
  {id: 2, name: "Jane", notes_detail_id: 456}, 
  {id: 1, name: "Billy", notes_detail_id: 789}
];

const getMatchingRecords = async function() {
  const targetRecords = [];
  const query = `SELECT NoteDetailsId
  FROM SL_Empsheets
  WHERE NoteDetailsId = ${record.notes_detail_id}`;
    for (let record of sourceArr) {
      let matchingRecord = await sqlServerQueryHandler(query);
      if (matchingRecord) {
        targetRecords.push(matchingRecord);
      }
    }
    return targetRecords;
};

And FYI, my sqlServerQueryHandler() looks like this:

const sql = require('mssql');
const config = require('./../../../configuration/sql-server-config');

const pool = new sql.ConnectionPool(config);
const poolConnect = pool.connect();

pool.on('error', err => {
  console.log(err);
});

module.exports = async function sqlServerQueryHandler(query) {
  try {
    await poolConnect;
    const request = pool.request();
    const result = await request.query(query);
    return result;
  } catch (err) {
    console.error(err);
  }
};

My current implementation will work. However, this ends up hitting the database multiple times.

So, my question is, is there a way I do something similar via the mssql package, but where I can pass in an array of values, rather than having to iterate through the array and hit the database one record at a time?

Upvotes: 0

Views: 117

Answers (1)

MGuilmet
MGuilmet

Reputation: 201

At a glance it looks like your getMatchingRecords function may not be copied correctly, otherwise, i'm not sure where your initial variable record is coming from. I think you should be building the query inside the for loop rather than before it.

Assuming that is the case, you could use an IN statement in SQL.

SELECT * FROM table WHERE id IN (1,2,3)

You could write this in JS like so:

const getMatchingRecords = async function() {
    const queryIn = sourceArr.map(r => r.notes_detail_id);
    const query = `SELECT NoteDetailsId
                   FROM SL_Empsheets
                   WHERE NoteDetailsId IN (${queryIn})`;
    return sqlServerQueryHandler(query);
};

Upvotes: 1

Related Questions