Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Close MSSQL connection using mssql in node.js

I am trying to write a script in node.js to query a MSSQL database. I am new to javascript, new to node.js, new to VSCode, but I know a few things about SQL. I have working code, but the connection never seems to close, and I cannot get the values OUT of the function.

SO, I have this chunk of code, which I got from the example from npm:

const sql = require('mssql');
var dbConfig = {
    server:'theServer',
    database:'theDB',
    user:'un',
    password:'pw',
    port:1433
};

sql.connect(dbConfig).then(pool => {
    // Query     
    return pool.request()
    .query('select top 10 * from THE_TABLE')
}).then(result => {
    console.log(result);
}).catch(err => {
    // ... error checks
})

This works, and I can see the 10 results get logged in the console. However, the code never stops running. How do I get the connection to close up and stop?

I really want the results to be saved into a variable, so I changed the code to this:

const sql = require('mssql');
var dbConfig = {
    server:'theServer',
    database:'theDB',
    user:'un',
    password:'pw',
    port:1433
};

let theList;
sql.connect(dbConfig).then(pool => {
    // Query     
    return pool.request()
    .query('select top 10 * from THE_TABLE')
}).then(result => {
    theList= result;
}).catch(err => {
    // ... error checks
})

console.log(theList);

This returns 'undefined' to the console for theList, and again the connection never seems to cose, and the script never shuts down.

How do I just grab the results of the query and move on down the road??

like image 265
lukehawk Avatar asked Aug 14 '17 19:08

lukehawk


2 Answers

This worked for me. There is a .close() method.

const DB = require("mssql")
const config = require("./config.json")
DB.connect(config)
   .then((conn) => 
      conn.query("SELECT * FROM table1")
         .then((v) => console.log(v))
         .then(() => conn.close())
   )
like image 122
Vikas Gautam Avatar answered Oct 21 '22 20:10

Vikas Gautam


Using Async / Await

Here's how to open a connection, query, and close using async await

const sql = require('mssql/msnodesqlv8')

let config = {
    driver: "msnodesqlv8",
    server: serverName,
    database: databaseName,
    options: { trustedConnection: true }
};

// connect to db
let cnn = await sql.connect(config)

// query
let result = await sql.query(query)

// close connection
await cnn.close()
like image 27
KyleMit Avatar answered Oct 21 '22 19:10

KyleMit