NodeJS and SQL Server connection error

Question

I am connecting SQL Server using NodeJS. My initial code was:

const poolA = new sql.ConnectionPool(config, err => {
         poolA.request()
            .query("select * from AnyTable", function(err, result) {
                if (err)
                    message = "Error!";
                else {                        
                    //Do something else
                    message = "Done!";
                }
            })
    });

I was getting "connection s closed error". I included

poolA.close()

and it didn't solve the problem either.

I changed this to:

new sql.ConnectionPool(config).then(pool => {
        pool.request()
            .query("select * from AnyTable")
    }).then(result => {
        //Do something else
        message = "Done!";
        sql.close();
    }).catch(err => {
        message = "Error!";
        sql.close();
    });

and now I get ".then is not a function" error.

What is the correct way to:

Create a connection if it doesn't exist
If connection already exist, use it

I am getting all sorts of error. Can someone help me sort out this problem?

Jeet · Accepted Answer · 2017-08-25 05:42:45Z

If you are using node then you should use the promise, as you did in your second option. So the correct way to do this should be as follows -

sql.close()
   sql.connect(sqlConfig).then(pool => {
     pool.request()
     .input('param1', sql.Int, valueParam1)
     .input('param2', sql.Int, valueParam2)
     .execute(procedureToExecute).then(result => {
      // Do whatever you want with the result.
})

Do remember that chaining is only possible if you return any thing from the promise. In your case you are not returning the connection pool or anything in the promise hence the ".then is not a function" error. So you should return the pool in case you want to use the pool again or return the result if you want to use the result in then part.

Second a better option is to create the connection once and then use it everywhere. The concept is very similar to the one for MongoDB ConnectionPooling please check it for the detail. For this create a db.js file as below -

'use strict'

const config = require(__dirname + '/index.js')
, mssql = require('mssql')
, sqlConfig = {
, user: config.databaseUsername
, password: config.databasePassword
, server: config.databaseHost
, database: config.database.database
pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000
  }
} 

let connection = mssql.connect(sqlConfig,err => {
  if (err)
   {throw err}
})

module.exports = connection

Then wire (require) the above in your server file or any module where you want to use the connection as below -

 db = require(process.cwd() + '/config/db.js')

You can include this in request options as below -

let options = {db:db}
request.options = options

Hope this helps, let me know in case you need anymore help.

Bruno Poeta · Accepted Answer · 2017-08-25 06:37:20Z

0

I didn't test it, but looking at your code, you're not returning any promise to the second then(), so you'll run into this error .then is not a function.

Try adding a return in the first then():

new sql
    .ConnectionPool(config)
    .then(pool => {
        return pool
        .request()
        .query("select * from AnyTable")
    })
    .then(result => {
        //Do something else
        message = "Done!";
        sql.close();
    })
    .catch(err => {
        message = "Error!";
        sql.close();
    });

edited Aug 25, 2017 at 6:37

answered Aug 25, 2017 at 5:18

Bruno Poeta

5212 silver badges10 bronze badges

Collectives™ on Stack Overflow

NodeJS and SQL Server connection error

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related