Node js make request to SQL Server, return result

Question

Working with Node.js and am having some trouble understanding how to return the result after making a request to SQL Server. When running independently and writing to the console, I can get the result just fine, however using it as a function and having it return the result is where I am running into problems.

I'm pretty sure I have to use a callback/promise, but don't really understand how either of those are set up. Hoping someone on here can help me out!

Here is my code:

var sql = require("mssql");

var config = {
    user: 'username',
    password: 'password',
    server: 'localhost', 
    database: 'Master' 
};

function updateTable() {
    var connection = new sql.ConnectionPool(config, function(err) {
        var request = new sql.Request(connection);
        request.query('select LastName from Persons', function(err, result) {
           return result.recordset;
        });
    });
};

console.log(updateTable());

Basically trying to print the result to the console by calling the function through console.log. Right now it's printing 'undefined', but I assume putting in a callback would do the trick. Again, just need some help understanding how it works and getting it set up. Thanks!

Welcome to [asynchronous execution](https://stackoverflow.com/questions/2035645/when-is-javascript-synchronous?noredirect=1&lq=1) — Kunal Mukherjee, Apr 18 '19 at 15:43

Kunal Mukherjee · Accepted Answer · 2019-04-18T16:19:37.697

If you just want to print it to the console then you can simply tweak your code as follows:

var sql = require("mssql");

var config = {
    user: 'username',
    password: 'password',
    server: 'localhost', 
    database: 'Master' 
};

function updateTable(callback) {
    var connection = new sql.ConnectionPool(config, function(err) {
        var request = new sql.Request(connection);
        request.query('select LastName from Persons', function(err, result) {
           callback(result.recordset);
        });
    });
};

updateTable(console.log);

To send the result from an express handler, assuming >= NodeJS v8:

Wrap the actual database interfacing logic inside an async function which will not block the main thread and export it from your module.

sqlConnector.js

const sql = require('mssql');

const config = {
    user: 'username',
    password: 'password',
    server: 'localhost', 
    database: 'Master' 
};

const updateTable = async () => {
    try {
        const pool = await sql.connect(config);
        const sqlQuery = 'SELECT LastName FROM Persons';
        const result = await pool.request().query(sqlQuery);
        return result;
    } catch (err) {
        throw err;
    }
};

export.updateTable = updateTable;

In express handler

Import your module which talks with MS-SQL (assumed sqlConnector.js) and mark your handler with the async keyword and return it from res.json at the end.

const sqlConnector = require('sqlConnector');

app.get('/someroute', async (req, res, next) => {
  try {
    const result = await sqlConnector.updateTable();
    return res.status(200).json(result);
  } catch (error) {
    next(error);
  }
});

Thank you sir, makes sense for that application. What if I want to res.send(updateTable)? Is there something I can pass in to accommodate that? Thanks for the help! Obviously very new to node.js and async execution, still wrapping my head around it — Djaenike, Apr 18 '19 at 15:56
@Djaenike Then we need to wrap it inside a promise and resolve it in your express handler. — Kunal Mukherjee, Apr 18 '19 at 16:01
The express handler is something I can do, but wold you mind showing me how to wrap that function in a promise? — Djaenike, Apr 18 '19 at 16:04

Node js make request to SQL Server, return result

1 Answers1