Cannot get data using mssql and hapi

Question

Using hapi and mssql for first time, and I'm struggling to get data from mssql query to the hapi reponse. Here is my code:

const Boom = require('boom');
const sql = require('mssql');
const dbConfig = require('../config/sql');

module.exports = [{
        method: 'GET',
        path: '/storeInfos',
        handler: storeInfos
    }
];

function storeInfos(request, h) {
    sql.connect(dbConfig).then(() => {
            const req = new sql.Request();
            req.query('select id, name from StoreInfo').then((data) => {
                    return h.response(data);
                })
                .catch((err) => {
                    reply(Boom.badRequest(err.message, err));
                });
        })
        .catch((err) => {
            reply(Boom.badRequest(err.message, err));
        });
}

When executing the /storeInfos function, following error is returned "Error: storeInfos method did not return a value, a promise, or throw an error."

I tried to following other examples without any luck and could not find good tutorials either. I'm really stucked with this, some help would be highly appreciated and also some links to some good tutorials. Thanks in advance...

score 1 · Answer 1 · answered Feb 23 '19 at 16:02

Figured it out and got it to work by using await/async, thanks to this post How to return value from the promise function in h.reponse. Here is the working code

const Boom = require('boom');
const sql = require('mssql');
const dbConfig = require('../config/sql');

module.exports = [{
        method: 'GET',
        path: '/storeInfos',
        handler: async function (request, h) {
            return await storeInfos(request, h);
        }
    }
];

async function storeInfos(request, h) {
    let pool;
    try {
        pool = await sql.connect(dbConfig)
        let data = await pool.request()
            .query('select Id, Name from StoreInfo')

        if (!data || data.recordset.length == 0) {
            throw Boom.notFound();
        }
        return h.response(data.recordset);
    } catch (err) {
        throw Boom.internal(err.message);
    } finally {
        if (pool) {
            sql.close();
        }
    }
}

Probably, not the most elegant solution but it works. I read, that it is not necessary to close the pool connection, but if sql.close() is not called, an error is raised about "Global connection already exists" second time the function is called.

Cannot get data using mssql and hapi

1 Answers1