CODEWITHSUNDEEP
sql-servernode.js
user1640256
user1640256

Reputation: 1719

NodeJS and SQL Server connection error

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:

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

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

Upvotes: 3

Views: 2009

Answers (2)

Bruno Poeta
Bruno Poeta

Reputation: 521

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();
    });

Upvotes: 0

Jeet
Jeet

Reputation: 5659

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.

Upvotes: 2

Related Questions