Express route works fine, until it calls separate function, crashes server

Question

The first GET request, (/not-work), calls a separate function, database(), with some code to connect to a database and return data. This successfully runs the database() function and that function returns data, but const result, does not get that data properly returned. Instead an unfufilled database connection object is returned. The second request however, (/works), runs nearly the exact same code as, just within the GET route itself. This works perfectly. I cannot for the life of me understand why.

const http = require('http');
const express = require('express');
const app = express();
const sql = require('mssql')
const sqlConfig = {
    user: 'login',
    password: 'pass',
    database: 'database',
    server: '123.456.789.012'
}

const database = () => {
    return sql.connect(sqlConfig,
        (err) => {
            if (err) console.log(err);
            try {
                return new sql.Request()
                    .execute('dbo.SQLProcedure', (err, recordset) => {
                        if (err) console.log(err)
                        return recordset;
                    });
            }
            catch (err) {
                console.log(err)
            }
        });
}

app.get('/not-work', (req, res) => {
    const result = database();
    console.log(result);
    res.send(result);
});

app.get('/works', (req, res) => {
    sql.connect(sqlConfig,
        (err) => {
            if (err) console.log(err);
            try {
                new sql.Request()
                    .execute('dbo.SQLProcedure', (err, recordset) => {
                        if (err) console.log(err)
                        res.send(recordset);
                    });
            }
            catch (err) {
                console.log(err)
                res.send(err);
            }
        });
});

http.createServer(app).listen(888, () => {
    console.log('Server listening 888');
});

Your `database()` function is just returning the value of `sql.connect()`. The other return statements are just returning from asynchronous callbacks which don't have anything to do with the higher level return value. — jfriend00, Feb 11 '22 at 17:53
See [How to return the response from an asynchronous call](https://stackoverflow.com/questions/14220321/how-to-return-the-response-from-an-asynchronous-call) for advice on how to get your asynchronous result back from your `database()` function call. — jfriend00, Feb 11 '22 at 18:51

1sina1 · Answer 1 · 2022-02-11T17:44:42.030

0

you must use async function and Promise

const database = async () => {
  return new Promise( (resolve, reject) => {
    sql.connect(sqlConfig,
      (err) => {
        if (err) console.log(err); // or reject(err)
        try {
          new sql.Request()
            .execute('dbo.SQLProcedure', (err, recordset) => {
              if (err) console.log(err) // or reject(err)
              return resolve(recordset);
            });
        } catch (err) {
          console.log(err) // or reject(err)
        }
      });
  })
}

app.get('/not-work', async (req, res) => {
    const result = await database();
    console.log(result);
    res.send(result);
});

edited Feb 11 '22 at 17:44

answered Feb 11 '22 at 17:28

1sina1

937
7
11

I've used async/await all throughout the code in many different ways with no success, including your example here. In addition, the working code does not require any async/await and returns the data just fine. I appreciate you looking into it though! – hpi55 Feb 11 '22 at 17:35
i updated the answer, you can use `Promise` – 1sina1 Feb 11 '22 at 17:45
you don't need async await in your working code because your chaining the callbacks – 1sina1 Feb 11 '22 at 17:48
adding a Promise just stalls the function, and the returned data still never makes it back to the GET request – hpi55 Feb 11 '22 at 19:51

Express route works fine, until it calls separate function, crashes server

1 Answers1